1、在订单明细表中查询订单金额最高的订单。
select orderNo,sum(quantity*price)
from
group by
having sum(quantity*price)=
(select max(orderSum)
from (select orderNo,sum(quantity*price)
from
group by orderNo) b)
2、找出至少被订购3次的商品编号、订单编号、订货数量和订货金额，并按订货数量的降序排序输出。
SELECT a.productNo,orderNo,quantity,(quantity*price) money
FROM OrderDetail a, (SELECT
FROM
GROUP BY
HAVING count(*)>=3)
WHERE a.productNo =b.productNo
ORDER BY a.productNo,quantity DESC
3、查找销售总额少于5000元的销售员编号、姓名和销售额。
select a.employeeNo,a.employeeName,sum(quantity*price)
from Employee a,OrderDetail b,OrderMaster c
where a.employeeNo=c.salerNo and b.orderNo=c.orderNo
group by a.employeeNo,a.employeeName
having sum(quantity*price)<5000
4、找出目前业绩未超过5000元的员工，并按销售业绩的降序排序输出。
select employeeNo,employeeName,orderSum
from Employee a,(select salerNo,sum(orderSum)
from
group by
having sum(orderSum)<5000)
where a.employeeNo=b.salerNo
order by orderSum desc
5、查询订购的商品数量没有超过10个的客户编号和客户名称。
SELECT a.CustomerNo,CustomerName
FROM
WHERE a.CustomerNo IN (  SELECT
FROM OrderMaster b,OrderDetail c
WHERE b.orderNo=c.orderNo
GROUP BY
HAVING sum(quantity)<10)
6、查找订货金额最大的客户名称和总货款。
SELECT customerName ,sum(orderSum)
FROM OrderMaster a,Customer b
WHERE a.customerNo=b.customerNo
GROUP BY a.customerNo,customerName
HAVING sum(orderSum)=(SELECT max(orderSum)
FROM(SELECT customerNo,sum(orderSum)
FROM
GROUP BY customerNo)c)
7、查找至少订购了3种商品的客户编号、客户名称、商品编号、商品名称、数量和金额。
SELECT  a.CustomerNo,CustomerName,b.ProductNo,
,quantity,sum(quantity*price) sum
FROM  Customer a,Product b,OrderMaster c,OrderDetail d
WHERE a.CustomerNo=c.CustomerNo and c.orderNo=d.orderNo
and b.ProductNo=d.ProductNo and
EXISTS (
SELECT
FROM   OrderMaster e,OrderDetail f
WHERE  e.orderNo=f.orderNo and a.customerNo=e.customerNo
GROUP BY
HAVING count(distinct ProductNo)>=3)
GROUP BY  a.CustomerNo,CustomerName,b.ProductNo,
,quantity

8、找出目前销售业绩超过4000元的业务员编号及销售业绩，并按销售业绩从大到小排序。

SELECT employeeNo ,
FROM Employee a,(SELECT salerNo,sum(orderSum)
FROM
GROUP BY salerNo)
WHERE a.employeeNo=b.salerNo AND sumOrder>4000
ORDER BY sumOrder DESC

9、求每位客户订购的每种商品的总数量及平均单价，并按客户号、商品号从小到大排列。

SELECT customerNo,productNo,sum(quantity) quantitys,
(sum(quantity*price)/sum(quantity))
FROM OrderMaster a,OrderDetail b
WHERE a.orderNo=b.orderNo
GROUP BY customerNo,productNo
ORDER BY customerNo,productNo

10、查询业绩最好的的业务员号、业务员名及其总销售金额。

SELECT  salerNo,employeeName,sum(orderSum)
FROM  Employee a,OrderMaster b
WHERE a.employeeNo=b.salerNo
GROUP BY salerNo,employeeName
having sum(orderSum) = (select max(orderSum)
from (select sum(orderSum)
from
group by salerNo) x)

11、查询订购的商品至少包含了订单“200803010001”中所订购商品的订单。

SELECT  *
FROM
WHERE not exists
(select *
from
where orderNo='200803010001' and  not exists
(select *
from
where y.productNo=z.productNo and
.orderNo=z.orderNo))

12、查询总订购金额超过“C20070002”客户的总订购金额的客户号、客户名及其住址。

SELECT a.customerNo,customerName,address
FROM OrderMaster a,Customer b
WHERE a.customerNo=b.customerNo
GROUP BY a.customerNo,customerName,address
HAVING sum(orderSum)>=(SELECT sum(orderSum)
FROM
WHERE customerNo='C20070002'
GROUP BY customerNo)

13、查询总销售金额最高的销售员编号、订单编号、订单日期和订单金额。

SELECT  salerNo,b.orderNo,orderDate,
FROM  Employee a,OrderMaster b
WHERE a.employeeNo=b.salerNo
and orderSum =(select max(orderSum)
from OrderMaster)

另一种解法：

select a.salerNo,a.orderNo,a.orderDate,a.orderSum
from OrderMaster a,( select salerNo,sum(orderSum)总订单金额
from
group by
having sum(orderSum)=(select max(ordersum1) from(
select y.salerno,sum(y.ordersum) ordersum1
from ordermaster
group by y.salerno )c))b
where a.salerNo=b.salerNo

14、用存在量词查找没有订货记录的客户名称。

SELECT
FROM
WHERE  NOT EXISTS
(SELECT * FROM
WHERE a.customerNo=c.customerNo  )

15、查询既订购了“52倍速光驱”商品，又订购了“17寸显示器”商品的客户编号、订单编号和订单金额。

Select customerNo,orderNo,orderSum
from
where customerNo in
(select
from OrderMaster a,OrderDetail b,Product c
where a.orderNo=b.orderNo and b.productNo=c.productNo and
='52倍速光驱')
and customerNo in (select
from OrderMaster a,OrderDetail b,Product c
where a.orderNo=b.orderNo and
b.productNo=c.productNo and 
productName='17寸显示器')

16、求每位客户订购的每种商品的总数量及平均单价，并按客户号、商品号从小到大排列。

SELECT customerNo,productNo,sum(quantity) quantitys,
(sum(quantity*price)/sum(quantity))
FROM OrderMaster a,OrderDetail b
WHERE a.orderNo=b.orderNo
GROUP BY customerNo,productNo
ORDER BY customerNo,productNo

17、 实验问题：

① 存在量词与集合运算IN、连接运算和全称量词之间的关系如何？它们可以互相替换吗？给出你的理由。

答：存在量词EXISTS可以用连接运算或集合运算IN来实现，而SQL中没有全称量词，只能用存在量词和取非运算来实现；

② 请写出例2.51的执行过程。

答：1. 首先将表Employee a, OrderMaster b, OrderDetail c, Product d进行连接

    2. 对连接后的记录，取出员工编号，判断是否至少销售了5种商品

    3. 如果是，将salerNo, employeeName, productName, quantity, price这五个值作为输出结果

    4．如果不是，舍弃该连接记录

    5. 取下一条连接记录，转2，直到所有的连接记录处理完毕

    6. 最后将结果输出

③ 存在量词一般用在相关子查询中，请分别给出存在量词用在相关子查询和非相关子查询的查询例子。

答：

相关子查询：

SELECT studentName,classNo
        FROM Student x
        WHERE EXISTS 
              (SELECT * FROM Score a,Course b
              WHERE a.courseNo=b.courseNo
                      AND a.studentNo=x.studentNo AND courseName=’操作系统’）

非相关子查询：

SELECT studentNo,classNo
FROM Student
WHERE NOT EXISTS 
                   (SELECT *
                    FROM Student
                    WHERE studentName=‘王红’)