李贺数据库4.docx

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

2、ceive the maximum percent commission.Answer：The aid values of agents who receive the maximum percent commission is a03. select aid from AGENTS where percents = any (select max(percents) from AGENTS)3.5 Consider the problem to find all (cid, aid) pairs where the customer does not place an order throu

3、gh the agent. This can be accomplished with the Select statementselect 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 singl

4、e Subquery? With more than one Subquery? Explain your answer and demonstrate any equivalent form by execution.(1) select cid,aid from CUSTOMERS,AGENTS where (cid not in (select cid from ORDERS where ORDERS.aid=AGENTS.aid)select cid,aid from CUSTOMERS,AGENTS where (aid not in (select aid from ORDERS

5、where ORDERS.cid=CUSTOMERS.cid) (2)select cid,aid from CUSTOMERS,AGENTS where (cid not in (select cid from ORDERS where cid in (select cid from ORDERS where ORDERS.aid=AGENTS.aid)3.8 (a) Write a Select statement with no WHERE clause to retrieve all customer cids and the maximum money each spends on

6、any product. Label the columns of the resulting table: eid, MAXSPENT. SELECT cid,max(dollars) as MAXSPENT from orders group by cid (b) Write a query to retrieve the AVERAGE value (over all customers) of the MAXSPENT of query (a) SELECT cid,max(dollars) as MAXSPENT INTO #OrderPay from orders group by

7、 cid SELECT avg(MAXSPENT) as average from #OrderPay3.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

8、 they receive in average quantities (by product) of at least 300?select distinct cid from orders group by cid,pid having avg(qty)=300(f) Get pid values of products that are ordered by all customers in Dallas. select p.pid from products p where not exists (select * from customers c where c.city=Dalla

9、s and not exists (select * from orders x where x.cid=c.cid and x.pid=p.pid)(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

10、 table.UPDATE PRODUCTS SET price=price*1.1 where city=Duluth or city=Dallas(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 retriuse CAP

11、GOselect aid,percents from agents awhere not exists (select * from customers cwhere c.city=Duluth and not exists (select * from orders xwhere x.cid=c.cid and x.aid=a.aid)order by a.percents2、Employee数据库的查询（记录每个查询的SQL语句和查询结果）（1）向表中插入数据。（2）将职工编号为000006的员工3月份基本工资增加为3000，奖金增加到800。Update salarySet base=3

12、000 , Bonus=800where Pno=000006 and Month=3（3）员工000009已经离开公司，将该员工的数据删除 DELETE FROM person WHERE Pno = 000009 DELETE FROM salary WHERE Pno = 000009（4）简单条件查询 查询person表中所有不重复的职称。SELECT DISTINCT Prof FROM person 查询具有高级职称的女员工信息SELECT * FROM person p where p.Prof=高级 and p.Sex=女 查询职工姓名为黎明的员工数据SELECT * FROM

13、 person p where p.Pname=黎明 查询各部门的实发工资总数SELECT Deptno,SUM(Fact) as total from salary,department WHERE pno in (SELECT pno from person where department.deptno=person.deptno) group by deptno（5）复杂条件查询 查询平均工资高于3000的部门名和对应的平均工资。 select deptno,avg(Fact) as average INTO #AVESALARY from department,salary wher

14、e pno in(select pno from person where department.deptno=person.deptno)group by deptno select dname,average from department,#AVESALARY where average3000 and department.deptno=#AVESALARY.deptno 查询1月份实发工资比平均实发工资高的员工姓名和实发工资额。 SELECT pname,Fact from person,salary where Fact=ALL (SELECT average from #AVES

15、ALARY where #AVESALARY.Deptno=person.Deptno) and month=1and person.pno=salary.pno 查询2月份实发工资比一月高的员工姓名。 select pname from person,salary x where month=2 and Fact(select Fact from salary y where x.pno=y.pno and month=1) and person.pno=x.pno 利用sql语句将1，2，3月累积的员工的实发工资按降序排序 select pno,sum(Fact) as ssalary INTO sumsalary from salary group by pno order by ssalary DESC