数据库实验二

1、西南石油大学实验报告课程名称：数据库原理插入你的照片实验项目名称：实验项目2 SQL数据操纵语言专业年级班级、姓名、学号：软件13级2班电子邮件地址：实验所用机器名：实验时间地点：2015.12.03 明理楼实验指导教师：孙瑜 成绩批改人批改日期注意：在粘贴截图时请保留窗口完整标题，但只需保留关键界面，多余的空白界面请删除。一、实验课时：4二、实验目的(1) 理解查询的概念和方法。(2) 掌握SELECT语句在单表查询中的应用。(3) 掌握SELECT语句在多表连接查询中的应用。(4) 掌握SELECT语句在嵌套查询中的应用。(5) 掌握SELECT语句在集合查询中的应用。(6) 主要掌握使用

2、“查询分析器”进行查询。三、实验环境(1) PC机。(2) SQL Server 2008。四、实验内容及步骤以下查询均使用实验项目1中创建的COMPANY数据库。1单表查询(1)基本查询。Q0. 使用查询分析器从Employee表中检索出所有员工的姓名。SQL文本：SELECT FNAME,LNAME FROM EMPLOYEE;(以下所有题目都必须按照上图的要求截图)Q1.从Employee表中检索出员工的FNAME、LNAME、SSN、BDATE、SALARY等字段，并分别加上“名”、“姓”、“社会保险号”、“生日”、“工资”的标题。员工的排序规则为：首先按工资的降序排列，然后按FNAM

3、E的字母升序排列。SQL文本:SELECT Fname 名,Lname 姓,Ssn 社会保险号,Bdate 生日,Salary 工资FROM EMPLOYEEORDER BY Salary DESC,Fname ; Q2. 查询不重复的员工工资值。SQL文本:SELECT DISTINCT SalaryFROM EMPLOYEE;Q3. 查询没有直接上司的员工姓名。SQL文本:SELECT Fname,LnameFROM EMPLOYEEWHERE Super_ssn IS NULL;(2) 基于WHERE子句进行数据查询。1）基于比较条件。Q4. 从Employee表中查询出工资大于等于40

4、000的员工资料。SQL文本:SELECT *FROM EMPLOYEEWHERE Salary = 40000;Q5.检索姓名为John B.Smith的员工的SSN、性别、出生日期和地址。SQL文本:SELECT Ssn,Sex,Bdate,AddressFROM EMPLOYEEWHERE Fname = JOHN AND Lname = SMITH AND Minit = B;2）基于BETWEEN子句的查询。Q6. 从Employee表中查询出1960年1970年之间出生的员工资料。SQL文本:SELECT * FROM EMPLOYEEWHERE Bdate BETWEEN 196

5、0-01-01 AND 1970-01-01;3）基于IN子句的查询。Q7. 从Employee表中查询出部门号为4或者5的员工资料。SQL文本:SELECT *FROM EMPLOYEEWHERE Dno IN (4,5);4）基于LIKE子句的查询。Q8. 从Employee表中查询出LNAME中含有字母o的员工资料。SQL文本:SELECT *FROM EMPLOYEEWHERE Lname LIKE %O%;Q9.检索居住在Houston的员工所有信息。SQL文本:SELECT *FROM EMPLOYEEWHERE Address LIKE %Houston%;(3) 使用计算列查询

6、。Q10. 从Employee表中检索出员工的FNAME、LNAME、SSN、SALARY等字段(其中SALARY需换算成人民币，汇率假定为1美元=8人民币元)，并分别加上“名”、“姓”、“社会保险号”、“人民币工资”的标题。SQL文本:SELECT Fname 名,Lname 姓,Ssn 社会保险号,Salary * 8 人民币工资FROM EMPLOYEE;2多表连接查询(使用JOIN)Q11. 查询所有为Research部门工作的员工姓名及地址。SELECT Fname, Lname, Address FROM EMPLOYEE E JOIN DEPARTMENT D ON E.Dno=

7、D.Dnumber WHERE Dname=Research;Q12. 对于所有位于Stafford的项目，查询项目的编号、项目负责部门编号以及该部门经理的姓、地址、生日。SQL文本:SELECT E.Lname,E.Address,E.Bdate,P.Pnumber,P.DnumFROM PROJECT P JOINDEPARTMENT D ON (P.Dnum = D.Dnumber)JOIN EMPLOYEE E ON (D.Mgr_ssn = E.Ssn)WHERE Plocation = Stafford;Q13. 查询有两个或以上家属的员工姓名(此题较难，若不能完成者可只查询出员工

8、的SSN而不是姓名)。SQL文本:SELECT E.Fname,E.LnameFROM EMPLOYEE EJOIN DEPENDENT D ON (D.Essn = E.Ssn) GROUP BY D.Essn,E.Fname,E.LnameHAVING COUNT(*) = 2;Q14. 查询在ProductX项目上每周工作时间超过10小时的部门5的员工姓名。(对于连接操作必须是主外键，但是对于where条件并没有什么特殊要求)SQL文本:SELECT E.Fname,E.LnameFROM EMPLOYEE EJOINWORKS_ON W ON (W.Essn = E.Ssn)JOIN

9、PROJECT P ON (P.Pnumber = W.Pno)WHERE W.Hours 10 AND P.Pname = PRODUCTX AND Dno = 5;Q15. 对于每个项目，列出项目名称以及所有员工在此项目上工作的总时间。SQL文本:SELECT P.Pname,SUM(W.Hours) 总时间FROM WORKS_ON W JOIN PROJECT P ON (P.Pnumber = W.Pno) GROUP BY P.Pname;Q16. 对于每个部门，列出部门名称以及此部门员工的平均工资。SQL文本:SELECT D.Dname,AVG(E.Salary)AS 平均工资

10、 FROM EMPLOYEE E JOIN DEPARTMENT D ON D.Dnumber=E.Dno GROUP BY D.Dname;Q17. 对于每个员工(包括无上司员工)，查询其姓名以及他/她的直接上司的姓名。SQL文本:SELECT E.Lname 员工姓,E.Fname 员工名,EM.Lname 上司姓, EM.Fname 上司名FROMEMPLOYEE E LEFTJOINEMPLOYEE EM ON(E.Super_ssn = EM.Ssn);Q18. 查询Company数据库中所有员工SSN与DNAME(部门名称)的组合。SQL文本:SELECT E.Ssn,D.Dnam

11、eFROMEMPLOYEE E JOINDEPARTMENT D ON (D.Dnumber = E.Dno);Q19. 查询有两个或以上“工资大于等于30000员工”的部门名称。SQL文本:SELECT D.DnameFROMEMPLOYEE E JOINDEPARTMENT D ON (D.Dnumber = E.Dno)WHERE E.Salary = 30000-先执行WHERER在执行GROUP BYGROUP BY D.DnameHAVING COUNT(*)=2;3嵌套查询(子查询)Q20. 查询没有参与任何部门5控制项目的员工姓名。SQL文本:SELECT Fname,Lnam

12、eFROM EMPLOYEE WHERE Ssn NOT IN(SELECT EssnFROM WORKS_ONWHERE Pno IN (SELECT PnumberFROM PROJECTWHERE Dnum = 5);Q21. 查询工资超过部门5所有员工工资的员工姓名。SQL文本:SELECT FNAME,LNAMEFROM EMPLOYEEWHERE Salary ALL(SELECT SalaryFROM EMPLOYEEWHERE Dno = 5);Q22. 查询参与了所有项目的员工姓名。(选做)SQL文本:SELECT FNAME,LNAMEFROM EMPLOYEEWHERE

13、SSN IN(SELECT EssnFROM WORKS_ONGROUP BY EssnHAVING COUNT(Essn) = (SELECT COUNT(*) FROM PROJECT); Q23. 查询至少参与了所有部门4控制项目的员工姓名。(选做)SQL文本:SELECT Fname,LnameFROM EMPLOYEEWHERE Ssn IN(SELECT EssnFROM WORKS_ONWHERE Pno IN (SELECT Pnumber FROMPROJECT WHERE Dnum = 4)GROUP BY ESSNHAVING COUNT(*) = (SELECT COU

14、NT(*) FROM PROJECT WHERE Dnum = 4);Q24. 查询至少参与了所有John Smith参与项目的员工姓名。(选做)SQL文本:SELECT Fname,Lname FROM EMPLOYEEWHERE Ssn IN(SELECT Essn FROM WORKS_ONWHERE Pno IN(SELECT DISTINCT PnoFROM WORKS_ON WHERE Essn IN(SELECT Ssn FROM EMPLOYEEWHERE Fname = JOHN AND Lname = SMITH)GROUP BY EssnHAVING COUNT(*) =

15、 (SELECT COUNT(*)FROM WORKS_ON WHERE Essn IN(SELECT Ssn FROM EMPLOYEEWHERE Fname = JOHN AND Lname = SMITH)AND Ssn != (SELECT Ssn FROM EMPLOYEEWHERE Fname = JOHN AND Lname = SMITH);Q25. 查询至少有一个家属的部门经理姓名。(必须用EXISTS谓词)SQL文本:SELECT FNAME,LNAMEFROM EMPLOYEEWHERE EXISTS(SELECT * FROM DEPARTMENTWHERE Mgr_s

16、sn = Ssn AND Mgr_ssn = ANY(SELECT DISTINCT ESSN FROM DEPENDENT);Q26. 查询有两个或以上隶属员工的部门名称及其“工资大于等于30000员工”总数。(选做)SQL文本:SELECT D.Dname,COUNT(*) 总数FROM EMPLOYEE EJOIN DEPARTMENT D ON E.Dno = D.DnumberWHERE Salary = 30000 GROUP BY D.DnameHAVING D.Dname IN (SELECT D.DnameFROM EMPLOYEE EJOIN DEPARTMENT D ON

17、 E.Dno = D.DnumberGROUP BY DnameHAVING COUNT(*) = 2);Q27. 查询有两个或以上家属的员工姓名。SQL文本:SELECT FNAME,LNAMEFROM EMPLOYEEWHERE Ssn = ANY(SELECT ESSN FROM DEPENDENTGROUP BY Essn HAVING COUNT(*) = 2);4集合查询Q28. 查询符合以下任意条件的项目编号：(1)参与此项目员工的LNAME是Smith；(2)控制此项目的部门经理的LNAME是Smith。SQL文本:SELECT PnoFROM WORKS_ONJOIN EMPLOYEE ON Essn = SsnWHERE Lname = SMITHUNION SELECT PnumberFROM EMPLOYEE JOIN DEPARTMENT ON Mgr_ssn = SsnJOIN PROJECT ON Dnum = DnumberWHERE Lname = SMITH;五、收获，体会及问题(请详细书写，写得越详细、越个性化、越真实越好，否则我不知道你