西南石油大学实验报告课程名称：数据库原理实验项目名称：实验项目2 SQL数据操纵语言专业年级班级、姓名、学号：软件13级4班、张璋、201305020420实验时间地点：2015.11.20 明理楼实验指导教师：刘小玲 成绩批改人批改日期一、实验课时：4二、实验目的(1) 理解查询的概念和方法。(2) 掌握SELECT语句在单表查询中的应用。(3) 掌握SELECT语句在多表连接查询中的应用。(4) 掌握SELECT语句在嵌套查询中的应用。(5) 掌握SELECT语句在集合查询中的应用。(6) 主要掌握使用“查询分析器”进行查询。三、实验要求(1) 使用“查询分析器”进行查询。(2) 严格依照题目要求编写SQL查询语句(鼓励同学们在按照题目要求完成查询的前提下尝试其他的查询方式)。(3) 为了今后可重用本实验项目中编写的SQL语句，最好按实验题目编号将SQL语句保存为.sql文件。四、实验环境(1) PC机。(2) SQL Server 2000。五、实验内容及步骤以下查询均使用实验项目1中创建的COMPANY数据库。1单表查询(1)基本查询。Q0. 使用查询分析器从Employee表中检索出所有员工的姓名。SELECT FNAME,LNAME FROM EmployeeQ1. 使用查询分析器从Employee表中检索出员工的FNAME、LNAME、SSN、BDATE、SALARY等字段，并分别加上“名”、“姓”、“社会保险号”、“生日”、“工资”的标题。员工的排序规则为：首先按工资的降序排列，然后按FNAME的字母升序排列。SELECT FNAME AS 名,LNAME AS 姓 ,SSN AS 社会保险号 , BDATE AS 生日 ,SALARY AS 工资 FROM Employee ORDER BY SALARY desc,FNAME ascQ2. 查询不重复的员工工资值。SELECT DISTINCT SALARY AS 工资 FROM EmployeeQ3. 查询没有直接上司的员工姓名。SELECT FNAME,LNAME FROM Employee WHERE SUPERSSN IS NULL(2) 基于WHERE子句进行数据查询。1）基于比较条件。Q4. 从Employee表中查询出工资大于等于40000的员工资料。SELECT * FROM Employee WHERE SALARY =400002）基于BETWEEN子句的查询。Q5. 从Employee表中查询出1960年1970年之间出生的员工资料。SELECT * FROM Employee WHERE BDATE BETWEEN 1960-1-1 AND 1970-1-13）基于IN子句的查询。Q6. 从Employee表中查询出部门号为4或者5的员工资料。SELECT * FROM Employee WHERE DNO IN (4,5)4）基于LIKE子句的查询。Q7. 从Employee表中查询出LNAME中含有字母o的员工资料。SELECT * FROM Employee WHERE LNAME LIKE %o%(3) 使用计算列查询。Q8. 从Employee表中检索出员工的FNAME、LNAME、SSN、SALARY等字段(其中SALARY需换算成人民币，汇率假定为1美元=8人民币元)，并分别加上“名”、“姓”、“社会保险号”、“人民币工资”的标题。SELECT FNAME AS 名 ,LNAME AS 姓,SSN AS 社会保险号,SALARY*8 AS 人民币工资 FROM Employee2多表连接查询(使用JOIN)Q9. 查询所有为Research部门工作的员工姓名及地址。SELECT FNAME,LNAME,ADDRESS FROM Employee JOIN Department ON DNO=DNUMBER WHERE DNAME=ResearchQ10.对于所有位于Stafford的项目，查询项目的编号、项目负责部门编号以及该部门经理的姓、地址、生日。SELECT PNUMBER,DNUM,FNAME,LNAME,ADDRESS,BDATEFROM (Project JOIN Department ON DNUM=DNUMBER)JOIN EmployeeON SSN=MGRSSNWHERE PLOCATION=StaffordQ11. 查询有两个或以上家属的员工姓名(此题较难，若不能完成者可只查询出员工的SSN而不是姓名)。SELECT FNAME,LNAMEFROM Dependent JOIN EmployeeON ESSN=SSNGROUP BY ESSN,FNAME,LNAMEHAVING COUNT(*)=2Q12. 查询在ProductX项目上每周工作时间超过10小时的部门5的员工姓名。SELECT DISTINCT FNAME,LNAMEFROM Employee JOIN (WORKS_ON JOIN Project ON PNAME=ProductX AND PNO=PNUMBER) ON SSN=ESSNWHERE HOURS10Q13. 对于每个项目，列出项目名称以及所有员工在此项目上工作的总时间。SELECT PNAME,SUM(HOURS) AS 总时间FROM Project JOIN WORKS_ON ON PNO=PNUMBER GROUP BY PNAMEQ14. 对于每个部门，列出部门名称以及此部门员工的平均工资。SELECT DNAME,SUM(SALARY) AS 平均工资FROM Department JOIN Employee ON DNO=DNUMBER GROUP BY DNAME ORDER BY 平均工资Q15. 对于每个员工，查询其姓名以及他/她的直接上司的姓名。SELECT E.FNAME,E.LNAME,M.FNAME,M.LNAMEFROM Employee AS E JOIN Employee AS M ON E.SUPERSSN=M.SSNQ16. 查询Company数据库中所有员工SSN与DNAME(部门名称)的组合。SELECT DISTINCT SSN ,DNAME 部门名称FROM Employee JOIN Department ON DNO=DNUMBER*Q17. 查询有两个或以上“工资大于等于30000员工”的部门名称。SELECT DNAMEFROM Department WHERE DNUMBER IN(SELECT DNUMBERFROM Department JOIN Employee ON DNO=DNUMBERWHERE SALARY=30000GROUP BY DNUMBERHAVING COUNT(*)=2)3嵌套查询(子查询)Q18.查询没有参与任何部门5控制项目的员工姓名。SELECT DISTINCT FNAME,LNAME FROM Employee WHERE SSN NOT IN ( SELECT ESSN FROM WORKS_ON WHERE PNO IN( SELECT PNUMBER FROM Project WHERE DNUM=5)Q19. 查询工资超过部门5所有员工工资的员工姓名。1. SELECT FNAME,LNAMEFROM Employee WHERE SALARY(SELECT TOP 1 SALARYFROM Employee , Department WHERE DNO=5 ORDER BY SALARY DESC)2. SELECT FNAME,LNAMEFROM Employee WHERE SALARY(SELECT MAX(SALARY) FROM Employee WHERE DNO=5)Q20. 查询参与了所有项目的员工姓名。SELECT FNAME,LNAME FROM Employee WHERE SSN IN(SELECT ESSN FROM WORKS_ON JOIN ProjectON PNO=PNUMBERGROUP BY ESSN HAVING COUNT(*)=(SELECT COUNT(*) FROM Project)Q21. 查询至少参与了所有部门4控制项目的员工姓名。SELECT FNAME,LNAMEFROM Employee WHERE SSN NOT IN(SELECT ESSN FROM WORKS_ON WHERE PNO NOT IN(SELECT PNUMBER FROM Project WHERE DNUM=4)Q22. 查询至少参与了所有John Smith参与项目的员工姓名。SELECT FNAME ,LNAME FROM EmployeeWHERE SSN NOT IN(SELECT ESSN FROM WORKS_ON WHERE PNO NOT IN(SELECT PNO FROM WORKS_ON WHERE ESSN IN(SELECT SSN FROM Employee WHERE FNAME=John AND LNAME=Smith)Q23. 查询至少有一个家属的部门经理姓名。1. SELECT FNAME,LNAME FROM Employee WHERE SSN IN(SELECT ESSN FROM Department JOIN Dependent ON MGRSSN=ESSN)2. SELECT MGRSSNFROM Department JOIN Dependent ON MGRSSN=ESSNGROUP BY MGRSSN HAVING COUNT(*)=1Q24. 查询有两个或以上隶属员工的部门名称及其“工资大于等于30000员工”总数。SELECT DNAME FROM Department JOIN Employee ON DNO= DNUMBER AND SALARY=30000GROUP BY DNAMEHAVING COUNT(*)=2Q25.查询有两个或以上家属的员工姓名。SELECT FNAME,LNAMEFROM Employee JOIN DependentON ESSN=SSNGROUP BY FNAME,LNAMEHAVING COUNT(*)=24集合查询Q26. 查询符合以下任意条件的员工：(1) SUPERSSN为123456789的员工；SELECT FNAME,LNAMEFROM Employee WHERE SUPERSSN=123456789(2) SUPERSSN不为123456789的员工。SELECT FNAME,LNAMEFROM Employee WHERE SUPERSSN NOT IN(