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

云南大学软件学院实验报告
课程: 数据库原理与实用技术实验学期: 2012-2013学年第二学期任课教师:
专业: 学号: 姓名: 成绩:
实验4 数据查询
一、实验目的
理解T-SQL语言的使用;熟练掌握数据查询语句;掌握合计函数的使用。
二、实验内容
1、CAP数据库的查询(记录每个查询的SQL语句和查询结果)
(1)建立CAP数据库,输入C、A、P、O四张表;
图表 1 创建cap数据库
图表 2创建四个表
图表 3向表中插入数据
图表 4表的内容
(2)完成课后习题[]b、[]、[]a,b、[]b,f,j,l
[] (b)Retrieve aid values of agents who receive the maximum mission.
图表 5最高佣金百分率
[] Consider the problem to find all (cid, aid) pairs where the customer does not place an order through the agent. This can be plished with the Select statement
select cid, aid
from customers c. agents a
where not exists
(select * from orders x where = and =) ;
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 not in
[](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
(b) Write a query to retrieve the AVERAGE value (over all customers) of the MAXSPENT of query (a)
图表 8 (b)
[] (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 (b)
(f) Get pid values of products that are ordered by all customers in Dallas.
图表 10 (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 (j)
(l) Write an SQL query to get aid and percent values of agents who take orders from all cust