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

1、云南大学软件学院数据库实验4云南大学软件学院 实验报告课程： 数据库原理与实用技术实验 学期： 2012-2013学年 第 二 学期 任课教师： 专业： 学号： 姓名： 成绩： 实验4 数据查询一、实验目的理解T-SQL语言的使用；熟练掌握数据查询语句；掌握合计函数的使用。二、实验内容1、CAP数据库的查询（记录每个查询的SQL语句和查询结果）（1）建立CAP数据库，输入C、A、P、O四张表；图表 1 创建cap数据库图表 2创建四个表图表 3向表中插入数据图表 4表的内容（2）完成课后习题3.2b、3.5、3.8a,b、3.11b,f,j,l 3.2 (b)Retrieve aid valu

2、es 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 statementselect cid, aid from customers c. agents a where not exists (select

3、 * 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 in3.8(a

4、) 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）

5、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

6、 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 origin

7、ally 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 l

8、ist, we cannot order by these values.)图表 12 3.11 (i)2、Employee数据库的查询（记录每个查询的SQL语句和查询结果）（1）向表中插入数据。use Employee1INSERT INTO department values(001, 营销部);INSERT INTO department values(002, 财务部);INSERT INTO department values(003, 人事部);INSERT INTO department values(004, 采购部);select * from departmentINSER

9、T 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);INSERT INTO person values(000004,王辉,男,1984/5/11,初级,002);INSERT INTO person values(000005,罗大右,男,1968/12/10,高级,002);INSERT INTO person value

10、s(000006,蔡琴,女,1978/4/20,中级,003);INSERT INTO person values(000007, 李艳, 女,1960/5/14, 高级,001);INSERT INTO person values(000008, 刘德华, 男,1971/2/19, 高级,003);INSERT INTO person values(000009, 黎明, 男,1973/10/9, 中级,002);select * from personINSERT INTO salary values(000001, 2100,500,1);INSERT INTO salary value

11、s(000002, 3000,600,1);INSERT INTO salary values(000003, 2800,450,1);INSERT INTO salary values(000004, 2500,340,1);INSERT INTO salary values(000005, 3200,490,1);INSERT INTO salary values(000006, 2700,600,1);INSERT INTO salary values(000007, 4000,200,1);INSERT INTO salary values(000008, 2800,350,1);IN

12、SERT INTO salary values(000009, 2760,440,1);INSERT INTO salary values(000001, 2400,600,2);INSERT INTO salary values(000002, 2800,400,2);INSERT INTO salary values(000003, 2860,350,2);INSERT INTO salary values(000004, 2540,320,2);INSERT INTO salary values(000005, 3200,480,2);INSERT INTO salary values(

13、000006, 2700,300,2);INSERT INTO salary values(000007, 4000,250,2);INSERT INTO salary values(000008, 2800,300,2);INSERT INTO salary values(000009, 2760,480,2);INSERT INTO salary values(000001, 2200,500,3);INSERT INTO salary values(000002, 3100,400,3);INSERT INTO salary values(000003, 2800,650,3);INSE

14、RT INTO salary values(000004, 2500,540,3);INSERT INTO salary values(000005, 3200,400,3);INSERT INTO salary values(000006, 2700,660,3);INSERT INTO salary values(000007, 4100,400,3);INSERT INTO salary values(000008, 2000,330,3);INSERT INTO salary values(000009, 2760,420,3);select * from salary图表 13 表的

15、内容图表 14 修改Fact的值（2）将职工编号为000006的员工3月份基本工资增加为3000，奖金增加到800。图表 15 更新表的内容（3）员工000009已经离开公司，将该员工的数据删除图表 16 删除表中数据（4）简单条件查询查询person表中所有不重复的职称。图表 17 查询不重复的职称查询具有高级职称的女员工信息图表 18 查询内容查询职工姓名为黎明的员工数据图表 19查询表的内容查询各部门的实发工资总数 （5）复杂条件查询查询平均工资高于3000的部门名和对应的平均工资。图表 20 表的统计查询1月份实发工资比平均实发工资高的员工姓名和实发工资额。图表 21查询表的内容查询2月份实发工资比一月高的员工姓名。图表 22查询表的内容利用sql语句将1，2，3月累积的员工的实发工资按降序排序图表 23 工资降序排列 （注：可编辑下载，若有不当之处，请指正，谢谢!）