数据库系统原理设计实验教程实验2详解.doc

实验二题目(1) 查找有销售记录的客户编号、名称和订单总额。select a.customerNo,a.customerName,b.orderNo,sum(quantity*price)from Customer a,OrderMaster b,OrderDetail cwhere a.customerNo=b.customerNo and b.orderNo=c.orderNogroup by a.customerNo,a.customerName,b.orderNo(2) 在订单明细表中查询订单金额最高的订单。select top 1 orderNo,sum(quantity*price) orderSumfrom OrderDetailgroup by orderNoorder by orderSum desc或：select orderNo,sum(quantity*price) orderSumfrom OrderDetailgroup by orderNohaving sum(quantity*price)= (select max(orderSum) from (select orderNo,sum(quantity*price) orderSum from OrderDetail group by orderNo) b)(3) 查询没有订购商品的客户编号和客户名称。SELECT a.customerNo,customerNameFROM Customer aWHERE a.customerNo NOT IN (SELECT customerNo FROM OrderMaster )(3) 找出至少被订购3次的商品编号、订单编号、订货数量和订货金额，并按订货数量的降序排序输出。SELECT ductNo,orderNo,quantity,(quantity*price) moneyFROM OrderDetail a, (SELECT productNo FROM OrderDetail GROUP BY productNo HAVING count(*)=3) bWHERE ductNo =ductNo ORDER BY ductNo,quantity DESC(4) 使用子查询查找16M DRAM的销售情况，要求显示相应的销售员的姓名、性别，销售日期、销售数量和金额，其中性别用“男”、“女”表示。SELECT employeeName,case sex when F then 女 when M then 男 end sex, orderDate,quantity,quantity*price 金额FROM Employee a,OrderMaster b,OrderDetail cWHERE a.employeeNo=b.salerNo AND b.orderNo=c.orderNo AND c.ProductNo IN ( SELECT f.ProductNo FROM OrderMaster d,OrderDetail e,Product f WHERE d.orderNo=e.orderNo AND ProductName=32M DRAM)(5) 查询OrderMaster表中订单金额最高的订单号及订单金额。select orderNo,orderSumfrom OrderMaster where orderSum = (select max(orderSum) from OrderMaster)(6) 计算出一共销售了几种商品。select count(distinct productNo)from OrderDetail(7) 显示OrderDetail表中每种商品的订购金额总和，并且依据销售金额由大到小排序输出。select productNo,sum(quantity*price) summoneyfrom OrderDetailgroup by productNoorder by summoney desc(8) 查找销售总额大于1000元的销售员编号、姓名和销售额。 select a.employeeNo,a.employeeName,sum(quantity*price) sunmoneyfrom Employee a,OrderDetail b,OrderMaster cwhere a.employeeNo=c.salerNo and b.orderNo=c.orderNogroup by a.employeeNo,a.employeeNamehaving sum(quantity*price)1000(9) 找出目前业绩未超过5000元的员工，并按销售业绩的降序排序输出。select employeeNo,employeeName,orderSumfrom Employee a,(select salerNo,sum(orderSum) orderSum from OrderMaster group by salerNo having sum(orderSum) ( SELECT avg(salary) avgsalary FROM Employee)(11) 计算每一种商品的销售数量、平均销售单价和总销售金额。SELECT ProductNo,sum(quantity) 数量,avg(price) 平均单价,sum(price*quantity) 总额FROM OrderDetailGROUP BY ProductNo(12) 查找至少有3次销售的业务员名单和销售日期。SELECT employeeName ,orderDate FROM Employee a,OrderMaster b,( SELECT salerNo FROM OrderMaster GROUP BY salerNo HAVING count(*)=3) cWHERE a.employeeNo =b.salerNo AND b.salerNo =c.salerNo(13) 用存在量词查找没有订货记录的客户名称。SELECT customerNameFROM Customer cWHERE NOT EXISTS (SELECT * FROM OrderMaster a WHERE a.customerNo=c.customerNo )(14) 查询订单中所订购的商品数量没有超过10个的客户编号和客户名称。SELECT a.CustomerNo,CustomerNameFROM Customer aWHERE a.CustomerNo IN (SELECT CustomerNoFROM OrderMaster b,OrderDetail cWHERE b.orderNo=c.orderNo GROUP BY CustomerNoHAVING sum(quantity)8000 (18) 显示每种商品的销售金额总和，并依销售金额由大到小输出。 SELECT productNo 商品编号,sum(quantity*price) 销售金额FROM OrderDetailGROUP BY productNoORDER BY sum(quantity*price)(19) 查找销售金额最大的客户名称和总货款。SELECT customerName ,sum(orderSum) FROM OrderMaster a,Customer bWHERE a.customerNo=b.customerNo GROUP BY a.customerNo,customerNameHAVING sum(orderSum)=(SELECT max(orderSum) FROM(SELECT customerNo,sum(orderSum) orderSum FROM OrderMaster GROUP BY customerNo)c)(20) 查找至少销售了3种商品的客户编号、客户名称、商品编号、商品名称、数量和金额。SELECT a.CustomerNo,CustomerName,b.ProductNo, ProductName,quantity,sum(quantity*price) sumFROM Customer a,Product b,OrderMaster c,OrderDetail dWHERE a.CustomerNo=c.CustomerNo and c.orderNo=d.orderNo and b.ProductNo=d.ProductNo and EXISTS ( SELECT CustomerNo FROM OrderMaster e,OrderDetail f WHERE e.orderNo=f.orderNo and a.customerNo=e.customerNo GROUP BY CustomerNo HAVING count(distinct ProductNo)=3)GROUP BY a.CustomerNo,CustomerName,b.ProductNo, ProductName,quantityORDER BY a.CustomerNo,sum DESC(21) 找出目前业绩超过232000元的员工编号和姓名。 SELECT employeeName , sumOrder FROM Employee a,(SELECT salerNo,sum(orderSum) sumOrder FROM OrderMaster GROUP BY salerNo) bWHERE a.employeeNo=b.salerNo AND sumOrder232000(22) 找出目前销售业绩超过40000元的业务员编号及销售业绩，并按销售业绩从大到小排序。SELECT employeeNo , sumOrder FROM Employee a,(SELECT salerNo,sum(orderSum) sumOrder FROM OrderMaster GROUP BY salerNo) bWHERE a.employeeNo=b.salerNo AND sumOrder40000ORDER BY sumOrder DESC(23) 求出每位客户的总订购金额，显示出客户号及总订购金额，并按总订购金额降序排列。SELECT customerNo ,sum(orderSum) FROM OrderMasterGROUP BY customerNoORDER BY sum(orderSum) DESC(24) 求每位客户订购的每种商品的总数量及平均单价，并按客户号、商品号从小到大排列。SELECT customerNo,productNo,sum(quantity) quantitys, (sum(quantity*price)/sum(quantity) avgpriceFROM OrderMaster a,OrderDetail bWHERE a.orderNo=b.orderNoGROUP BY customerNo,productNoORDER BY customerNo,productNo(25) 查询业绩最好的的业务员号、业务员名及其总销售金额。SELECT top 1 salerNo,employeeName,sum(quantity*price)FROM Employee a,OrderMaster x,OrderDetail yWHERE x.orderNo=y.orderNo and a.employeeNo=x.salerNoGROUP BY salerNo,employeeNameORDER BY sum(quantity*price) DESC或：SELECT salerNo,employeeName,sum(orderSum)FROM Employee a,OrderMaster bWHERE a.employeeNo=b.salerNoGROUP BY salerNo,employeeNamehaving sum(orderSum) = (select max(orderSum) from (select sum(orderSum) orderSum from OrderMaster group by salerNo) x)(26) 查询订购的商品至少包含了订单“200803010001”中所订购商品的订单。SELECT *FROM OrderMaster aWHERE not exists (select * from OrderDetail y where orderNo=200803010001 and not exists (select * from OrderDetail z where ductNo=ductNo and a.orderNo=z.orderNo)(27) 求每种商品的总销售数量及总销售金额，要求显示出商品编号、商品名称、总数量及总金额，并按商品号从小到大排列。SELECT ductNo 商品编号,productName 商品名称,qtySum 总数量,totalSum 总金额FROM Product a, (SELECT productNo,sum(quantity) qtySum,sum(quantity*price) totalSum FROM OrderDetail GROUP BY productNo)bWHERE ductNo=ductNoORDER BY ductNo(28) 查询总订购金额超过“C20070002”客户的总订购金额的客户号、客户名及其住址。SELECT a.customerNo,customerName,addressFROM OrderMaster a,Customer bWHERE a.customerNo=b.customerNoGROUP BY a.customerNo,customerName,addressHAVING sum(orderSum)=(SELECT sum(orderSum) FROM OrderMaster WHERE customerNo=C20070002 GROUP BY customerNo) (29)查询销售金额最高的销售员编号、订单编号、订单日期和订单金额。SELECT top 1 salerNo,b.orderNo,orderDate, orderSumFROM Employee a,OrderMaster b,OrderDetail cWHERE b.orderNo=c.orderNo and a.employeeNo=b.salerNoGROUP BY salerNo,b.orderNo,orderDate,orderSumORDER BY orderSum DESC或：SELECT salerNo,b.orderNo,orderDate, orderSumFROM Employee a,OrderMaster bWHERE a.employeeNo=b.salerNo and orderSum =(select max(orderSum) from OrderMaster)(30) 实验问题存在量词与集合运算in、连接运算和全称量词之间的关系如何？它们可以互相替换吗？给出你的理由。答：存在量词EXISTS可以用连接运算或集合运算IN来实现，而SQL中没有全称量词，只能用存在量词和取非运算来实现；给出SQL语句实现分组聚集操作的执行过程。答：1，首先执行FROM语句，查找到各表数据2，其次执行WHERE语句，对数据进行筛选以及表与表的连接3，然后执行GROUP BY语句，将数据划分组4，使用聚集函数进行计算5，使用HAVING语句进行筛选分组请写出例3.55的执行过程。1. 将Score表的数据以studentNo进行分组2. 对每组执行avg（score）和count(*)运算3. 计算最大的avg（score）3. 筛选出count大于5并且平均值等于最大的avg（score）的组5. 将满足条件的组输出存在量词一般用在相关子查询中，请分别给出存在量词用在相关子查询和非相关子查询的查询例子。相关子查询：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,classNoFROM StudentWHERE NOT EXISTS (SELECT * FROM Student WHERE studentName=王红) WHERE和HAVING子句都是用于指定查询条件的，请给出你对这两个子句的理解，用实例说明。WHERE子句：作用于整个查询对象，对元组进行过滤，不可以和函数一起用。HAVING子句：仅作用于分组，对分组进行过滤。必须与GROUP BY配合使用。举例：WHERE：(1) 查询所有业务部门的员工姓名、职称、薪水。select employeeName,headShip,salaryfrom employeewhere department=业务科HAVING：查询