Writing SQL query equivalent to relational division of relational algebra?
3
votes
2
answers
3673
views
>
> 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?
Asked by learningIT
(155 rep)
Aug 28, 2017, 02:36 AM
Last activity: Mar 14, 2023, 03:41 PM
Last activity: Mar 14, 2023, 03:41 PM