Consider the following relational schema (primary keys are underlined): Product(
ID: 3869589 • Letter: C
Question
Consider the following relational schema (primary keys are underlined): Product(pid, name, price, mfgr) Buys(cid, pid) Customer(cid, cname, age) a) Consider the following SQL query: Rewrite the above SQL query without using the "HAVING" clause so that the resulting query still produces the same query result. b) Write the following query in SQL: Find the cids of customers who buy only the products made by manufactory "D" (i.e., mfgr = 'D'). c) Write the following query in SQL: Find the cids of customers who have bought all the products made by manufactory "D" (i.e., mfgr = 'D').Explanation / Answer
Query for question a: without using having clause
select * from (select C.cid,C.name from Customer C, Buys B where C.cid=B.cid groupb by C.cid) where count(pid)>100;
Query for question b:
select C.cid from Customers C left join Buys B
on C.cid=B.cid left join Product P
on B.pid=P.id where P.mfgr='D';
Query for question c:
I do not think there is difference in question b and c. The reason is each product is unique(based on primary key)product id and so each product is made by only one manufactory.
For example: there is productid 104(lets assume product name is pen) which is unique in the product table and this product is made by manufactory D. So since its a unique prodcut id, this prodcut(pen) can not be made by other manufactories as the product id for the pen is already would be in the prodcut table.
so customers who buy only products made by manufactory 'D' would be all the customers who have bought all the products made by manufactory 'D'.
So it would be the same query.
select C.cid from Customers C left join Buys B
on C.cid=B.cid left join Product P
on B.pid=P.id where P.mfgr='D';