实验5：数据查询

云南大学软件学院 实验报告

课程： 数据库原理与实用技术实验 学期： 2015-2016学年 第二 学期 任课教师： 刘宇 专业： 信息安全 学号： 20141120181 姓名： 钱冬升 成绩：

实验5 数据查询

1、CAP数据库的查询（记录每个查询的SQL语句和查询结果）

完成教科书如下习题：

[3.2]b：Retrieve aid values of agents who receive the maximum percent commission.

[3.5]：[HARD] Consider the problem to find all (cid, aid) pairs where the customer does not place an order through the agent. This can be accomplished with the Select statement

select cid, aid from customers c. agents a where not exists

(select * from orders x where x.cid = c.cid and x.aid =a.aid) ;

Is it possible to achieve this result using the NOT IN predicate in place of the NOT EXISTS predicate with a single Subquery? With more than one Subquery? Explain your answer and demonstrate any equivalent form by execution.

1

[3.8]a：Write a Select statement with no WHERE clause to retrieve all customer cids and the maximum money each spends on any product. Label the columns of the resulting table: eld, MAXSPENT.

[3.8]b：Write a query to retrieve the AVERAGE value (over all customers) of the MAXSPENT of query (a).

2

[3.11]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 c1d = x and p1d = y. Is it possible in a single SQL statement to retrieve ci d values of customers who order all the products that they receive in average quantities (by product) of at least 300?

[3.11]f： Get pid values of products that are ordered by all customers in Dallas.

3

[3.11]j：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.

[3.11]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 select list, we cannot order by these values.)

2、在“学生管理数据库”中完成如下查询(SQL语句及结果截屏)： （1）列出软件2班女同学的名单

4

2）列出2002年元旦前注册的学生名单

3）列出所有年龄在19岁以下，或者女同学的名单

5

（

（

（4）列出没有先修课的课程名

（5）列出既不是“电子系”，也不是“会计系”的学生的学号、姓名和所在院系

（6）查询选修“C801”课程的学生的最高分

6

（7）统计男、女学生各自的人数和平均年龄

（8）列出选修了一门以上课程的学生学号，及其选修门数

（9）查询“沈香娜”同学所在班级的所有学生的学号和姓名

7

（10）统计每一年龄选修课程的学生人数

难题：

1) 在基本表“成绩表”中检索平均成绩最高的学生学号

2) 求表S中男同学的每一年龄组（只取每组 >=2人的年龄组）有多少人？要求查询结

果按人数升序排列，人数相同按年龄降序排列。

3) 列出选修了全部课程的学生学号和姓名

4) 查询这样的学生，该生至少选修了学生20026001所选修的全部课程

8