一、假设存在如下的关系模式（exerci 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语言来表示下述数据操作要求。
(a)For each agent taking an order, list the product pid and the total quantity ordered
by all customers from that agent.(检索每个经销商销售每一种产品的总数量) (b)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）
(c)Get aid values of agents not taking orders from any customer in Duluth for any
product in Dallas.（检索没有为居住在Duluth的任何客户订购过任何商品的经销商的编号）
(d)Get aid values of agents who order at least one common product for each
customer who is bad in Duluth or Kyoto.（检索为居住在Duluth或Kyoto的所有客户订购过同一种商品的经销商的编号）
(e)Get cid values of customers who make orders only through agent a03 or a05.（检
茉莉花花语索仅通过a03和a05两个经销商订购过商品的客户编号）
(f)Get pid values of products that are ordered by all customers in Dallas.（检索居
住在Dallas的所有客户都订购过的商品编号）
(g)Find agents with the highest percent (percent commission), using the max t
function.（检索享有最高佣金比率的经销商的编号）
(h)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 Inrt statement.
(i)U the Update statement to change Gray’s percent to 11. Then change it back. (j)U a single Update statement to rai the prices of all products warehoud in Duluth or Dallas by 10%. Then restore the original values by rerunning the procedure that you originally ud to create and load the products table.
(k)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号经销商订购过商品的客户编号，并给出每个客户的订购总金额）
(l)Write an SQL query to 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 lect list, we cannot order by the values.)（检索为居住在Duluth的所有客户订购过商品的经销商的编号及其佣金百分比，并按照佣金百分比的降序输出查询结果）
(m)Write an SQL query to get pid values of products ordered by at least one
customer who lives in the same city as the agent taking the order.（检索符合下述条件的商品的编号：至少有一个客户通过与该客户位于同一个城市的经销商订购过该商品）
二、假设关系模式如下：
⏹职工E（姓名ename，工号e#，出生日期bdate，家庭地址addr，年薪
salary，管理员工号#，所在部门编号d#）
⏹部门D（部门名称dname，部门编号d#，部门负责人的工号mgre#）
⏹项目P（项目名称pname，项目编号p#，所在城市city，主管部门编
号d#）
⏹工作W（职工工号e#，项目编号p#，工作时间hours）
⏹职工家属Depend（职工工号e#，家属的姓名name，家属的性别x）
请用SQL语言来表示下述数据查询操作。
1)检索部门Rearch的所有职工的姓名和家庭地址
2)检索位于Stafford的每个项目的编号、主管部门的编号及其部门负责人
的姓名和家庭地址
3)检索参与了5号部门的所有项目的职工的姓名
4)检索职工Smith所参与的或者主管部门为该职工所在部门的项目的编号
5)检索拥有两个或两个以上家属的职工的姓名
6)检索至少有一个家属且具有管理员身份的职工的姓名
7)检索不带家属的职工的姓名
8)检索工资收入最高的职工的姓名
遥远的北方电影参考答案
一、（exerci 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语言来表示下述数据操作要求。
下一页(a) For each agent taking an order, list the product pid and the total
quantity ordered by all customers from that agent.(检索每个经销商销售每一种产品的总数量)
Select aid, pid, sum(qty)
from orders
group by aid, pid
下一题(b) 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）
Select T.cid
From ( lect cid, pid, avg(qty) as q_avg
from Orders
group by cid, pid ) T
Group by T.cid
Having min(T.q_avg) >= 300
下一题
(c) Get aid values of agents not taking orders from any customer in
Duluth for any product in Dallas.（检索没有为居住在Duluth的任何客户订购过任何商品的经销商的编号）
Select aid
审美能力from agents
where aid not in (
lect 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’)
下一题
(d) Get aid values of agents who order at least one common product for
清末之盖世帝王
each customer who is bad in Duluth or Kyoto.（检索为居住在Duluth或Kyoto的所有客户订购过同一种商品的经销商的编号）
Select O1.aid from Orders O1, Customers C1
Where O1.cid = C1.cid and (C1.city = ‘Duluth’ or C1.city = ‘Kyoto’) and not exist (
lect * from Customers C2
where (C2.city = ‘Duluth’ or C2.city = ‘Kyoto’) and C2.cid not in ( lect O2.cid from Orders O2
where O2.aid = O1.aid and O2.pid = O1.pid))
下一题
(e) Get cid values of customers who make orders only through agent
a03 or a05.（检索仅通过a03和a05两个经销商订购过商品的客户编号）Select O1.cid
from Orders O1
where O1.cid not in (
lect O2.cid
from Orders O2
where O2.aid <> ‘a03’ and O2.aid <> ‘a05’)
什么的节目下一题
(f) Get pid values of products that are ordered by all customers in
Dallas.（检索居住在Dallas的所有客户都订购过的商品编号）
Select pid
学习提纲>南京工程学院是几本from Products P
where not exist (
lect * from Customers C
where C.city = ‘Dallas’ and C.cid not in (
lect O.cid from Orders O
where O.pid = P.pid ) )
下一题
(g) Find agents with the highest percent (percent commission), using
the max t function.（检索享有最高佣金比率的经销商的编号）Select *
from Agents
where percent IN (
lect max(A1.percent)
from Agents A1 )
下一题
(h) 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 Inrt statement.
Delete from Agents
Where aname = ‘Gray’
(略)
共青团介绍
下一题
(i) U the Update statement to change Gray’s percent to 11. Then
change it back.
Update Agents
Set percent = 11
Where aname = ‘Gray’;
Rollback;
