These queries use the CUSTOMERS-PRODUCTS-AGENTS-ORDERS tables. CUSTOMERS(cid, cn

ID: 669488 • Letter: T

Question

These queries use the CUSTOMERS-PRODUCTS-AGENTS-ORDERS tables.

CUSTOMERS(cid, cname, city, discnt)

AGENTS(aid, aname, city, percent)

PRODUCTS(pid, pname, city, quantity, price)

ORDERS(ordno, month, cid, aid, pid, qty, dollars)

1) List the name of all products for which an order was placed.

2) List the name of customers that ordered product ‘p07’

3) List name of agents that placed an order for customer c003 or customer c006

4) List name of customers that ordered product ‘p01’ through agent 'a01'

5 List the name of each customer and the total amount ordered by the customers and also list the customers that did not place an order.

1) select distinct p.pname from Products P inner join Orders O on O.pid = P.pid

2) select C.cname from Customers C inner join Orders O on O.cid = C.cid where O.pid = 'p07'

3)select A.aname from Agents A inner join Orders O on A.aid = O.aid where O.cid in ('c003','c006')

4) select C.name from Customers C where exists (select 1 from Orders O where O.cid=C.cid and O.pid = 'p01' and O.aid = 'a01')

5) select C.cid,C.cname,sum(O.dollars) from Customers C inner join Orders O on O.cid = C.cid group by C.cid,C.name

select C.cid,C.name from Customers C where not exists (select 1 from Orders O where O.cid=C.cid)

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