云南大学--软件学院--数据库实验4

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

课程：数据库原理与实用技术实验学期： 2012-2013学年第二学期任课教师：专业：学号：姓名：成绩：

实验4 数据查询

一、实验目的

理解T-SQL语言的使用；熟练掌握数据查询语句；掌握合计函数的使用。

二、实验内容

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

（1）建立CAP数据库，输入C、A、P、O四张表；

图表 1 创建cap数据库

图表 2创建四个表

图表 3向表中插入数据

图表 4表的内容

（2）完成课后习题[3.2]b、[3.5]、[3.8]a,b、[3.11]b,f,j,l

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

图表 5最高佣金百分率

[3.5] 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.

图表 6 3.5 not in

[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: eid, MAXSPENT.

图表 7 3.8

(b) Write a query to retrieve the AVERAGE value (over all customers) of the MAXSPENT of query (a)

图表 8 3.8（b）

[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 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?

图表 9 3.11 (b)

(f) Get pid values of products that are ordered by all customers in Dallas.

图表 10 3.11 (f)

(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.

图表 11 3.11 (j)

(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.)

图表 12 3.11 (i)

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

（1）向表中插入数据。

use Employee1

INSERT INTO department values('001','营销部');

INSERT INTO department values('002','财务部');

INSERT INTO department values('003','人事部');

INSERT INTO department values('004','采购部');

select*from department

INSERT INTO person values('000001','田林','男','1982/4/7','初级','001');

INSERT INTO person values('000002','郭达','男','1953/2/14','高级','001');

INSERT INTO person values('000003','王林','女','1984/8/25','初级','002');