homework3sol

# homework3sol - Database Management Systems (COP 5725)...

This preview shows pages 1–4. Sign up to view the full content.

Database Management Systems (COP 5725) (Spring 2010) Instructor: Dr. Markus Schneider TA: Hechen Liu, Ravi Tiwari, Wenjie Yuan Homework 3 Solution Name: UFID: Email Address: Pledge (Must be signed according to UF Honor Code) On my honor, I have neither given nor received unauthorized aid in doing this assignment. _______________________________________________Signature For scoring use only: Maximum Received Question 1 40 Question 2 25 Question 3 20 Question 4 15 Total 100

This preview has intentionally blurred sections. Sign up to view the full version.

View Full Document
E x e r c i s e 1 ( S Q L ) [40 points] 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] 1. For each agent taking an order, list the product pid and the total quantity ordered by all customers FROM that agent. 2. 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? 3. Get aid values of agents not taking orders FROM any customer in Duluth for any product in Dallas. 4. Get aid values of agents who order at least one common product for each customer who is based in Duluth or Kyoto. 5. Get cid values of customers who make orders only through agent a03 or a05. 6. Get pid values of products that are ordered by all customers in Dallas. 7. Find the average, over all agents, of the maximum dollar sales made by each agent 8. 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. 9. 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.) 10. Get pid values of products ordered by at least one customer who lives in the same city as the agent taking the order.
Solution: 1. SELECT aid, pid, sum(qty) FROM orders GROUP BY aid, pid 2. sSELECT T.cid FROM ( SELECT cid, pid, avg(qty) AS q_avg FROM Orders GROUP BY cid, pid ) T GROUP BY T.cid HAVING min(T.q_avg) >= 300 3. SELECT aid FROM agents WHERE aid NOT IN ( SELECT aid FROM Customers C, Products P, Orders O WHERE C.cid = O.cid AND P.pid = C.pid AND C.city = ‘Duluth’ AND P.city = ‘Dallas’)

This preview has intentionally blurred sections. Sign up to view the full version.

View Full Document
This is the end of the preview. Sign up to access the rest of the document.

## This note was uploaded on 04/08/2010 for the course INFO 5555 taught by Professor Ggdkk during the Spring '10 term at Aberystwyth University.

### Page1 / 13

homework3sol - Database Management Systems (COP 5725)...

This preview shows document pages 1 - 4. Sign up to view the full document.

View Full Document
Ask a homework question - tutors are online