西南石油大学数据库实验项目二

1、西南石油大学实验报告课程名称：数据库原理与应用插入你的照片实验项目名称：实验项目2专业年级班级、姓名、学号：电子邮件地址：实验所用机器名： 实验时间地点：实验指导教师： 成绩批改人批改日期一、实验课时：4二、实验目的(1) 理解查询的概念和方法。(2) 掌握SELECT语句在单表查询中的应用。(3) 掌握SELECT语句在多表连接查询中的应用。(4) 掌握SELECT语句在嵌套查询中的应用。(5) 掌握SELECT语句在集合查询中的应用。(6) 主要掌握使用“查询分析器”进行查询。三、实验环境(1) PC机。(2) SQL Server 2008。四、实验内容及步骤  &#

2、160;以下查询均使用实验项目1中创建的COMPANY数据库。1单表查询(1)基本查询。Q0. 使用查询分析器从Employee表中检索出所有员工的姓名。SELECT FNAME,LNAME FROM EMPLOYEE;Q1. 查询所有员工的FNAME、LNAME、SSN、SEX和SALARY，并分别将SEX和SALARY的列名重命名为“性别”、“工资”。SELECT FNAME,LNAME,SSN,SEX 性别,SALARY 工资FROM EMPLOYEE;Q2.从Employee表中检索出员工的FNAME、LNAME、SSN、BDATE、SALARY等字段，并分别加上“名”、“姓”、“社会

3、保险号”、“生日”、“工资”的标题。员工的排序规则为：首先按工资的降序排列，然后按FNAME的字母升序排列。SELECT FNAME 姓,LNAME 名,SSN 社会保险号,BDATE 生日,SALARY 工资FROM EMPLOYEEORDER BY SALARY DESC,FNAME;Q3. 查询不重复的员工工资值。SELECT DISTINCT SALARY FROM EMPLOYEE;Q4. 查询没有直接上司的员工姓名。SELECT FNAME,LNAME,SUPERSSN FROM EMPLOYEEWHERE SUPERSSN IS NULL;(2) 基于WHERE子句进行数据查询。

4、1）基于比较条件。Q5. 从Employee表中查询出工资大于等于40000的员工资料。SELECT * FROM EMPLOYEEWHERE SALARY>=40000;Q6. 查询所有的女性家属的全部信息，并按生日的降序排列。SELECT * FROM DEPENDENT WHERE SEX='F'ORDER BY BDATE DESC ;Q7. 查询所有不属于5号部门的员工信息。SELECT * FROM EMPLOYEEWHERE (NOT DNO='5');Q8. 利用OR关键字查询属于1号或4号部门的员工信息。SELECT * FROM EMP

5、LOYEEWHERE DNO='1' OR DNO='4'Q9.检索姓名为John B.Smith的员工的SSN、性别、出生日期和地址。SELECT SSN,SEX,BDATE,ADDRESS FROM EMPLOYEEWHERE FNAME='JOHN' AND LNAME ='SMITH' AND MINIT='B'Q10. 查询1960年以后出生的员工信息(注意日期写完整)。SELECT * FROM EMPLOYEEWHERE BDATE >='1960-01-01'2）基于BETWE

6、EN子句的查询。Q11. 从Employee表中查询出1960年1970年之间出生的员工资料(注意日期写完整)。SELECT * FROM EMPLOYEEWHERE BDATE BETWEEN '1960-01-01' AND '1970-12-31' ;3）基于IN子句的查询。Q12. 从Employee表中查询出部门号为4或者5的员工资料。SELECT * FROM EMPLOYEEWHERE DNO IN (4,5);4）基于LIKE子句的查询。Q13. 从Employee表中查询出LNAME中含有字母o的员工资料。SELECT * FROM EMPLO

7、YEEWHERE LNAME LIKE '%o%'Q14. 查询项目名称以P开头的项目信息。SELECT * FROM PROJECTWHERE PNAME LIKE 'P%'Q15.检索居住在Houston的员工所有信息。SELECT * FROM EMPLOYEEWHERE ADDRESS LIKE '%Houston%'(3) 使用计算列和汇总函数查询。Q16. 从Employee表中检索出员工的FNAME、LNAME、SSN、SALARY等字段(其中SALARY需换算成人民币，汇率假定为1美元=6人民币元)，并分别加上“名”、“姓”、“社

8、会保险号”、“人民币工资”的标题。SELECT FNAME 名,LNAME 姓,SSN 社会保险号,SALARY*8 人民币工资 FROM EMPLOYEE;Q17. 统计所有员工的平均工资。SELECT AVG(SALARY) AS 员工平均工资FROM EMPLOYEE;Q18. 查询所有员工在所有项目上每周工作的总小时数。SELECT SUM(HOURS) AS 总小时数FROM WOEKS_ON;Q19. 查询各个部门员工的平均工资，显示部门号和对应的平均工资值。SELECT DNO,AVG(SALARY) AS 平均工资FROM EMPLOYEEGROUP BY DNO;Q20. 查

9、询每个员工所有参与项目的每周工作总小时数，显示员工SSN和总小时数。SELECT ESSN, SUM(HOURS) AS 总小时数 FROM WOEKS_ONGROUP BY ESSN;Q21. 列出所有参与了项目的员工编号及其参与项目数。SELECT DISTINCT ESSN,COUNT(*)FROM WOEKS_ON GROUP BY ESSN Q22. 统计参与了2个以上项目的员工编号和其参与项目的每周工作平均时间。SELECT ESSN ,COUNT(*) AS 项目数,AVG(HOURS) AS 每周工作平均时间FROM WOEKS_ONGROUP BY ESSNHAVING CO

10、UNT(*)>2; Q23. 查询有2个以上男性员工的部门编号和该部门的总人数（包括男女员工）(选做)。SELECT DNO, COUNT(*) AS 总人数FROM EMPLOYEEGROUP BY SEX,DNOHAVING COUNT(*)>2 AND SEX='M'2多表连接查询(使用JOIN)Q24. 查询部门编号、名称及该部门经理的姓名。SELECT DEPARTMENT.DNUMBER,DEPARTMENT.DNAME,EMPLOYEE.FNAME+','+EMPLOYEE.MINIT+','+EMPLOYEE.LNAM

11、E AS 姓名FROM DEPARTMENTJOIN EMPLOYEEON DEPARTMENT.MGRSSN=EMPLOYEE.SSNQ25. 查询所有员工（包括没有家属的员工）姓名、编号及其家属姓名。SELECT EMPLOYEE.*,DEPENDENT.DEPENDENT_NAMEFROM EMPLOYEELEFT OUTER JOIN DEPENDENTON EMPLOYEE.SSN=DEPENDENT.ESSNQ26. 查询所有为Research部门工作的员工姓名及地址。SELECT FNAME,MINIT,LNAME,ADDRESSFROM EMPLOYEE JOIN DEPART

12、MENT ON EMPLOYEE.DNO=DEPARTMENT.DNUMBERWHERE DEPARTMENT.DNAME='RESEARCH'Q27. 对于所有位于Stafford的项目，查询项目的编号、项目负责部门编号以及该部门经理的姓、地址、生日。SELECT PNUMBER,DNUM,LNAME,ADDRESS,BDATEFROM EMPLOYEEJOIN PROJECT ON (EMPLOYEE.DNO=PROJECT.DNUM)JOIN DEPT_LOCATIONSON (PROJECT.DNUM=DEPT_LOCATIONS.DNUMBER)WHERE DLOCA

13、TION='Stafford'Q28. 查询有两个或以上家属的员工姓名(此题较难，若不能完成者可只查询出员工的SSN而不是姓名)。SELECT SSNFROM EMPLOYEEJOIN DEPENDENT ON(EMPLOYEE.SSN=DEPENDENT.ESSN)GROUP BY SSNHAVING COUNT(SSN)>2;Q29. 查询在ProductX项目上每周工作时间超过10小时的部门5的员工姓名。SELECT FNAME,LNAMEFROM EMPLOYEEJOIN WOEKS_ON ON(EMPLOYEE.SSN=WOEKS_ON.ESSN)JOIN PR

14、OJECT ON (WOEKS_ON.PNO=PROJECT.PNUMBER)WHERE PROJECT.PNAME='PRODUCTX'Q30. 对于每个项目，列出项目名称以及所有员工在此项目上工作的总时间。SELECT PNAME,SUM(HOURS)FROM WOEKS_ONJOIN PROJECT ON (WOEKS_ON.PNO=PROJECT.PNUMBER)GROUP BY PNAME;Q31. 对于每个部门，列出部门名称以及此部门员工的平均工资。SELECT DNAME,AVG(SALARY)FROM EMPLOYEEJOIN DEPARTMENT ON (EM

15、PLOYEE.DNO=DEPARTMENT.DNUMBER)GROUP BY DNAME;Q32. 对于每个员工(包括无上司员工)，查询其姓名以及他/她的直接上司的姓名。SELECT E1.LNAME+','+E1.FNAME 员工姓名,E2.LNAME+','+E2.FNAME 上司姓名FROM Company_Henianchen.dbo.EMPLOYEE E1left JOIN Company_Henianchen.dbo.EMPLOYEE E2 ON (E1.SUPERSSN=E2.SSN)Q33. 查询Company数据库中所有员工SSN与DNAME(

16、部门名称)的组合。SELECT SSN,DNAMEFROM EMPLOYEEJOIN DEPARTMENT ON (EMPLOYEE.DNO=DEPARTMENT.DNUMBER)Q34. 查询有两个或以上“工资大于等于30000员工”的部门名称。3嵌套查询(子查询)Q35. 查询Research部门的员工姓名。SELECT LNAME+','+FNAME 姓名FROM EMPLOYEEwhere DNO=(SELECT DNUMBER FROM DEPARTMENT WHERE DNUMBER=5)Q36. 查询参与了项目的员工姓名。(必须用IN谓词)SELECT LNAME

17、+','+FNAME 姓名FROM EMPLOYEEwhere DNO IN (SELECT DNUM FROM PROJECT)Q37. 查询参与了项目的员工姓名。(必须用EXISTS谓词)SELECT LNAME+','+FNAME 姓名FROM EMPLOYEEwhere EXISTS(SELECT* FROM PROJECT WHERE EMPLOYEE.DNO=PROJECT.DNUM)Q38. 查询没有参与任何部门5控制项目的员工姓名。SELECT LNAME+','+FNAME 姓名FROM EMPLOYEEwhere DNO<

18、;>ALL(SELECT DNUMBER FROM DEPARTMENT WHERE DNUMBER=5)Q39. 查询工资超过部门5所有员工工资的员工姓名。SELECT LNAME+','+FNAME 姓名FROM EMPLOYEEwhere SALARY >ALL(SELECT SALARY FROM EMPLOYEE WHERE DNO=5)Q40. 查询参与了所有项目的员工姓名。(选做)Q41. 查询至少参与了所有部门4控制项目的员工姓名。(选做)Q42. 查询至少参与了所有John Smith参与项目的员工姓名。(选做)Q43. 查询至少有一个家属的部门经理姓名。(必须用EXISTS谓词)SELECT LNAME FNAME FROM EMPLOYEEwhere EXISTS(SELECT * FROM Company_Henianchen.dbo.DEPENDENT)JOIN Company_Henianchen.dbo.DEPARTMENT ON (DEPENDENT.ESSN=DEPARTMENT.MGASSN)Q44. 查询有两个或以上隶属员工的部门名称及其“工资大于等于300