《云南大学软件学院数据库实验4》由会员分享，可在线阅读，更多相关《云南大学软件学院数据库实验4（11页珍藏版）》请在金锄头文库上搜索。
1、云南大学软件学院 实验报告课程： 数据库原理与实用技术实验 学期： 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 values of agents w
2、ho 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 * from orders
3、 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)Write a Selec
4、t 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 t
5、hat 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?图表
6、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
7、 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
8、 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 departmentINSERT INTO person values
9、(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 values(000006,蔡琴,女,1978/4
10、/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 values(000002, 3000,600,1
11、);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);INSERT INTO salary val
12、ues(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(000006, 2700,300,2);
13、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);INSERT INTO salary value
14、s(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 表的内容图表 14 修改Fact的值（2）将职工编号为000006的员工3月份基本工资增加为3000，奖金增加到800。图表 15 更新表的内容（3）员工000009已经离开公司，将该员工的数据删除图表 16 删除表中数据（4）简单条件查询 查询person表中所有不重复的职称。图表 17 查询不重复的职称 查询具有高级职称的女员工信息图表 18 查询内容 查询职工姓名为黎明的员工数据图表 19查询表的内容 查询各部门的实发工资总数 （5）复杂条件查询 查询平均工资高于3000的部门名和对应的平均工资。图表 20
