Given following relations:
Customers (cid, cname, city, discnt)
Agents (aid, aname, city, percent)
Products (pid, pname, city, quantity, price)
Orders (ordno, month, cid, aid, pid, qty, dollars)
Write the following queries in SQL
[4 point each, 40 points]
For each agent taking an order, list the product pid and the total quantity ordered by
all customers FROM that agent.
We say that a customer x orders a product y in an average quantity A if A is avg(qty)
for all orders rows with cid=x and pid=y. Is it possible in a single SQL statement to
retrieve cid values of customers who order all the products that they receive in
average quantities (by product) of at least 300?
Get aid values of agents not taking orders FROM any customer in Duluth for any
product in Dallas.
Get aid values of agents who order at least one common product for each customer
who is based in Duluth or Kyoto.
Get cid values of customers who make orders only through agent a03 or a05.
Get pid values of products that are ordered by all customers in Dallas.
Find the average, over all agents, of the maximum dollar sales made by each agent
Retrieve cid values for customers who place at least one order, but only through
agent a04. On the same line with each cid, your query should list the total dollar
amount of orders placed.
Get aid and percent values of agents who take orders FROM all customers who live
in Duluth. The aid values should be reported in order by decreasing percent. (Note
that if percent is not retrieved in the SELECT list, we cannot order by these values.)
Get pid values of products ordered by at least one customer who lives in the same
city as the agent taking the order.