3
EMPLOYEE (Ssn#, Fname, Lname, Bdate, Address, Sex, Salary, Super_ssn)
WORKS_ON (Essn#, Pno, Hours)
PROJECT (Pnumber#, Pname, Plocation)

Question 1: Write an SQL query (equivalent to relational division of relational algebra) to retrieve the project name which is contributed by all employees.

Question 2: Write an SQL query (equivalent to relational division of relational algebra) to retrieve the first name and last name of all employees who work on every project.

ITEM (Item#, Item_Name, Unit_Price)
ORDER (Order#, Ord_Date, Cust#, Ord_Amt)
ORDER_ITEM (Order#, Item#, Qty)
WAREHOUSE (Warehouse#, Warehouse_City)
SHIPMENT (Order#, Warehouse#, Ship_Date)
WAREHOUSE_ITEM (Item#, Warehouse#, Qty)

Question 3: Write an SQL query (equivalent to relational division of relational algebra) to list Item_Name and Quantity that are stored in all warehouses in Sydney.

My answer for Question 1:

SELECT Pname 
FROM PROJECT
WHERE EXISTS (
    SELECT Pname 
    FROM PROJECT, EMPLOYEE 
    WHERE PROJECT.Pnumber = WORKS_ON.Pno);

My answer for Question 2:

SELECT Fname, Lname 
FROM EMPLOYEE
WHERE EXISTS (
    SELECT Fname, Lname 
    FROM EMPLOYEE, WORKS_ON, PROJECT
    WHERE EMPLOYEE.Ssn = WORKS_ON.Essn 
    AND WORKS_ON.Pno = PROJECT.Pnumber);

My answer for Question 3:

SELECT Item_Name, Qty 
FROM ITEM, ORDER_ITEM
WHERE ITEM.Item# = ORDER_ITEM.Item#
AND EXISTS(
    SELECT * 
    FROM WAREHOUSE 
    WHERE Warehouse_City = 'Sydney');

(If these are correct, I will able to convert them in relational algebra with relational division.)

What are the mistakes in my SQL queries?

0

2 Answers 2

1

Here is a short and concise article describing the various techniques for relational division in SQL, with an example similar to yours using students and courses instead of employees and projects: http://www.inf.usi.ch/faculty/soule/teaching/2016-fall/db/division.pdf

It offers three equivalent solutions:

SELECT DISTINCT x.Student
FROM taken AS x
WHERE NOT EXISTS (
SELECT *
FROM required AS y
WHERE NOT EXISTS (
SELECT *
FROM taken AS z
WHERE (z.Student=x.Student)
AND (z.Course=y.Course)));

SELECT Student FROM Taken
EXCEPT
SELECT Student FROM (
SELECT Student,Course
FROM (select Student FROM Taken), Required
EXCEPT
SELECT Student,Course FROM Taken);

SELECT Student
From Taken
WHERE Course IN (SELECT Course FROM Required)
GROUP BY Student
HAVING COUNT(*) = (SELECT COUNT(*) FROM Required);
0
-1

The third seems correct. The first and second strike me as wrong. You are looking for projects where any employee contributed, not where all contributed. In essence you are doing a division on an ANY rather than an ALL criteria.

In these cases you need to change your queries to use NOT EXISTS and look for mismatches.

1
  • 1
    All 3 seem wrong. Question 3 has "all", too. Commented Sep 13, 2017 at 10:58

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Not the answer you're looking for? Browse other questions tagged or ask your own question.