《数据库实验2无错无漏[骄阳教育].doc》由会员分享，可在线阅读，更多相关《数据库实验2无错无漏[骄阳教育].doc（7页珍藏版）》请在三一文库上搜索。
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、LNAME、SSN、BDATE、SALARY
3、等字段，并分别加上“名”、“姓”、“社会保险号”、“生日”、“工资”的标题。员工的排序规则为：首先按工资的降序排列，然后按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 WH
4、ERE 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 * FR
5、OM 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
6、人民币工资 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
7、)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 JOI
8、N 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 G
9、ROUP 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员工”的部
10、门名称。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( SELEC
11、T 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. 查询参与了所有项目的员工姓
12、名。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 P
13、roject 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 Employe
14、e 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 DNAMEHA
15、VING 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
16、SUPERSSN NOT IN(SELECT SUPERSSNFROM 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) 请在本地服务器中的
17、数据库“Company_你的拼音名字”中进行查询。(2) 如果由于题目翻译时的失误导致中英文题意不相符时，请以英文为准(中文翻译仅供参考)。七、考核要求(1)熟练使用SELECT语句进行各种类型数据检索。(2)熟练使用“查询分析器”进行数据查询。八、思考题(1)在数据检索时BETWEEN关键字和IN关键字的使用有何区别？BETWEEN 关键字是选择BETWEEN A AND B 中A 或 B 中选值，而 IN关键字是从IN包括的集合里选值 (2)进行连接查询时应注意什么？不要连接太多表，而且要加查询条件九、参考文献(1) 朱如龙，SQL Server数据库应用系统开发技术，机械工业出版社(2) 王珊，SQL Server数据库系统基础，高等教育出版社(3) 王珊，数据库系统概论（第三版），高等教育出版社7教育专业b