数据库实验2无错无漏

1、西南石油大学实验报告课程名称：数据库原理实验项目名称：实验项目2 SQL数据操纵语言专业年级班级、姓名、学号：软件13级4班、张璋、201305020420实验时间地点：2015.11.20 明理楼实验指导教师：刘小玲 成绩批改人批改日期一、实验课时：4二、实验目的(1) 理解查询的概念和方法。(2) 掌握SELECT语句在单表查询中的应用。(3) 掌握SELECT语句在多表连接查询中的应用。(4) 掌握SELECT语句在嵌套查询中的应用。(5) 掌握SELECT语句在集合查询中的应用。(6) 主要掌握使用“查询分析器”进行查询。三、实验要求(1) 使用“查询分析器”进行查询。(2) 严格依照

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、LNAM

3、E、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 S

4、ALARY 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 WH

5、ERE BDATE BETWEEN '1960-1-1' AND '1970-1-1'3）基于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、SALAR

6、Y等字段(其中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=

7、DNUMBER WHERE DNAME='Research'Q10.对于所有位于Stafford的项目，查询项目的编号、项目负责部门编号以及该部门经理的姓、地址、生日。SELECT PNUMBER,DNUM,FNAME,LNAME,ADDRESS,BDATEFROM (Project JOIN Department ON DNUM=DNUMBER)JOIN EmployeeON SSN=MGRSSNWHERE PLOCATION='Stafford'Q11. 查询有两个或以上家属的员工姓名(此题较难，若不能完成者可只查询出员工的SSN而不是姓名)。SELECT

8、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 HOURS>10Q13. 对于每个项目，列出项目名称以及所

9、有员工在此项目上工作的总时间。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

10、.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 Employ

11、ee 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,LNAM

12、EFROM 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

13、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

14、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 Depend

15、ent 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

16、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(SELECT SUPER

17、SSNFROM Employee WHERE SUPERSSN='123456789')Q27. 查询符合以下任意条件的员工：(1) SSN为123456789的员工；SELECT FNAME,LNAMEFROM Employee WHERE SSN='123456789'(2) SSN不为123456789的员工。SELECT FNAME,LNAMEFROM Employee WHERE SSN NOT IN(SELECT SUPERSSNFROM Employee WHERE SSN='123456789')六、注意事项(1) 请在本地服务器中的数据库“Company_你的拼音名字”中进行查询。(2) 如果由于题目翻译时的失误导致中英文题意不相符时，请以英文为准(中文翻译仅供参考)。七、考核要求(1)熟练使用S