Sample Header Ad - 728x90

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