List all the customers (cid and cname) which have completed orders with Chris an

Question

List all the customers (cid and cname) which have completed orders with Chris and all the employees directly
managed by him. Hint: relation Manages stores the “managing” information in the company (e.g., the employee
with eid1 is the direct manager of the employee with eid2 ).

Please use SQL relational algebra (e.g. sigma, pi, calligraphi G, etc.) fro the solution, thank you!

Employee Customer city eid ename 0011 Chris01/03/1986 100,000 0012 Edward07/24/1973 120,000 0013 Anna 08/12/1992 80,000 0014 Kim 08/26/198695,000 0015 Caro 04/15/1988 97,000 cidcname A101Chase New YorkNY A102 A103 |TD Bank BostonMA A104Moto A105 DisneyOrlandoFL dob sa state Citi New York NY Chicago IL Product id pname unit price C17811Deslk C17812 Chair C23453 PC C34451Mac 500 200 1,200 1,300 Sales eidcid 0011 A101 C17811 0011 A102 C17812 0012 A102 C23453 0013 A101 C34451 anages eid1 eid2 0011 0013 0013 0017 0012 0013 pid uantity 700 200 100 50 Hints: . Underlined attribute(s) form the primary key of a relation . Relation Sales stores the sales information: the salesman (employee ID) who is in charge of the ordered product, the product ID, the customer ID, and the quantities of ordered products The attributes eid, cid and pid of relation Sales are the foreign keys to relations Employee (salesman), Customer and Product, respectively. » Relation Manages stores the "managing" information in the company (e.g., the employee with eid1 is the direct manager of the employee with eid2) All the IDs' are strings

Explanation / Answer

SELECT cid, cname

FROM Customer

LEFT JOIN Customer ON Sales.cid = Customer.cid

GROUP BY Sales.eid

HAVING Sales.eid=0011 OR (Sales.eid=Manages.eid2 AND Manages.eid1=0011)

ORDER BY Customer.cid

Related Questions

Navigate

• Browse (All)
• Browse L
• Subjects

---

---

Integrity-first tutoring: explanations and feedback only — we do not complete graded work. Learn more.