关系数据库练习题(SQL语言)答案.doc

假设存在如下的关系模式（exercise 3.11）： Customers (cid, cname, city, discnt) Agents (aid, aname, city, percent) Products (pid, pname, city, quantity, price) Orders (ordno, month, cid, aid, pid, qty, dollars) 请用SQL语言来表示下述数据操作要求。 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?（检索符合下述要求的客户的编号：在该客户订购过的所有商品中，每一种商品的平均每笔订单的订购数量均达到或超过300） Get aid values of agents not taking orders from any customer in Duluth for any product in Dallas.（检索没有为居住在Duluth的任何客户订购过任何商品的经销商的编号） Get aid values of agents who order at least one common product for each customer who is based in Duluth or Kyoto.（检索为居住在Duluth或Kyoto的所有客户订购过同一种商品的经销商的编号） Get cid values of customers who make orders only through agent a03 or a05.（检索仅通过a03和a05两个经销商订购过商品的客户编号） Get pid values of products that are ordered by all customers in Dallas.（检索居住在Dallas的所有客户都订购过的商品编号） Find agents with the highest percent (percent commission), using the max set function.（检索享有最高佣金比率的经销商的编号） In the agents table, delete the row with the agent named Gray, print out the resulting table in full, then put Gray back, using the Insert statement. Use the Update statement to change Gray’s percent to 11. Then change it back. Use a single Update statement to raise the prices of all products warehoused in Duluth or Dallas by 10%. Then restore the original values by rerunning the procedure that you originally used to create and load the products table. Write an SQL query to 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.（检索仅仅通过a04号经销商订购过商品的客户编