数据库实验二文档格式.docx

1、在粘贴截图时请保留窗口完整标题，但只需保留关键界面，多余的空白界面请删除。一、实验课时：4二、实验目的（1） 理解查询的概念和方法。（2） 掌握SELECT语句在单表查询中的应用。（3） 掌握SELECT语句在多表连接查询中的应用。（4） 掌握SELECT语句在嵌套查询中的应用。（5） 掌握SELECT语句在集合查询中的应用。（6） 主要掌握使用“查询分析器”进行查询。三、实验环境（1） PC机。（2） SQL Server 2008。四、实验内容及步骤以下查询均使用实验项目1中创建的COMPANY数据库。1单表查询（1）基本查询。Q0. 使用查询分析器从Employee表中检索出所有员工的姓

2、名。SQL文本：SELECT FNAME,LNAME FROM EMPLOYEE;（以下所有题目都必须按照上图的要求截图）Q1.从Employee表中检索出员工的FNAME、LNAME、SSN、BDATE、SALARY等字段，并分别加上“名”、“姓”、“社会保险号”、“生日”、“工资”的标题。员工的排序规则为：首先按工资的降序排列，然后按FNAME的字母升序排列。SQL文本:SELECT Fname 名,Lname 姓,Ssn 社会保险号,Bdate 生日,Salary 工资FROM EMPLOYEEORDER BY Salary DESC,Fname ; Q2. 查询不重复的员工工资值。SE

3、LECT DISTINCT SalaryFROM EMPLOYEE;Q3. 查询没有直接上司的员工姓名。SELECT Fname,LnameWHERE Super_ssn IS NULL;（2） 基于WHERE子句进行数据查询。1）基于比较条件。Q4. 从Employee表中查询出工资大于等于40000的员工资料。SELECT *WHERE Salary = 40000;Q5.检索姓名为John B.Smith的员工的SSN、性别、出生日期和地址。SELECT Ssn,Sex,Bdate,AddressWHERE Fname = JOHN AND Lname = SMITHAND Minit

4、= B;2）基于BETWEEN子句的查询。Q6. 从Employee表中查询出1960年1970年之间出生的员工资料。SELECT * FROM EMPLOYEEWHERE Bdate BETWEEN 1960-01-01AND 1970-01-013）基于IN子句的查询。Q7. 从Employee表中查询出部门号为4或者5的员工资料。WHERE Dno IN （4,5）;4）基于LIKE子句的查询。Q8. 从Employee表中查询出LNAME中含有字母o的员工资料。WHERE Lname LIKE %O%Q9.检索居住在Houston的员工所有信息。WHERE Address LIKE %

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

6、. 对于所有位于Stafford的项目，查询项目的编号、项目负责部门编号以及该部门经理的姓、地址、生日。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 = StaffordQ13. 查询有两个或以上家属的员工姓名（此题较难，若不能完成者可只查询出员工的SSN而不是姓名）。SELECT E.Fname,E.LnameFROM EMP

7、LOYEE EJOIN DEPENDENT D ON （D.Essn = E.Ssn） GROUP BY D.Essn,E.Fname,E.LnameHAVING COUNT（*） = 2;Q14. 查询在ProductX项目上每周工作时间超过10小时的部门5的员工姓名。（对于连接操作必须是主外键，但是对于where条件并没有什么特殊要求）JOINWORKS_ON W ON （W.Essn = E.Ssn）JOIN PROJECT P ON （P.Pnumber = W.Pno）WHERE W.Hours 10 AND P.Pname = PRODUCTX AND Dno = 5;Q15. 对

8、于每个项目，列出项目名称以及所有员工在此项目上工作的总时间。SELECT P.Pname,SUM（W.Hours） 总时间FROM WORKS_ON W JOIN PROJECT P ON （P.Pnumber = W.Pno） GROUP BY P.Pname;Q16. 对于每个部门，列出部门名称以及此部门员工的平均工资。SELECT D.Dname,AVG（E.Salary）AS 平均工资 FROM EMPLOYEE E JOIN DEPARTMENT D ON D.Dnumber=E.Dno GROUP BY D.Dname;Q17. 对于每个员工（包括无上司员工），查询其姓名以及他/她

9、的直接上司的姓名。SELECT E.Lname 员工姓,E.Fname 员工名,EM.Lname 上司姓, EM.Fname 上司名FROMEMPLOYEE E LEFTEMPLOYEE EM ON（E.Super_ssn = EM.Ssn）;Q18. 查询Company数据库中所有员工SSN与DNAME（部门名称）的组合。SELECT E.Ssn,D.DnameEMPLOYEE E DEPARTMENT D ON （D.Dnumber = E.Dno）;Q19. 查询有两个或以上“工资大于等于30000员工”的部门名称。SELECT D.DnameDEPARTMENT D ON （D.Dnu

10、mber = E.Dno）WHERE E.Salary = 30000-先执行WHERER在执行GROUP BYGROUP BY D.DnameHAVING COUNT（*）=2;3嵌套查询（子查询）Q20. 查询没有参与任何部门5控制项目的员工姓名。FROM EMPLOYEE WHERE Ssn NOT IN（SELECT EssnFROM WORKS_ONWHERE Pno IN （SELECT PnumberFROM PROJECTWHERE Dnum = 5）;Q21. 查询工资超过部门5所有员工工资的员工姓名。SELECT FNAME,LNAME ALL（SELECT SalaryW

11、HERE Dno = 5）;Q22. 查询参与了所有项目的员工姓名。（选做）WHERE SSN IN（SELECT EssnGROUP BY EssnHAVING COUNT（Essn） = （SELECT COUNT（*） FROM PROJECT）;Q23. 查询至少参与了所有部门4控制项目的员工姓名。WHERE Ssn IN（WHERE Pno IN （SELECT Pnumber FROMPROJECT WHERE Dnum = 4）GROUP BY ESSNHAVING COUNT（*） = （SELECT COUNT（*） FROM PROJECT WHERE Dnum = 4）;Q24. 查询至少参与了所有John Smith参与项目的员工姓名。SELECT Fname,Lname FROM EMPLOYEESELECT Essn FROM WORKS_ONWHERE Pno IN（SELECT DISTINCT PnoFROM WORKS_ON WHERE Essn IN（SELECT Ssn FROM EMPLOYEE）HAVING COUNT（*） = （SELECT COUNT（*）AND Ssn != （SELECT Ssn FROM EMPLOYEE）;Q25. 查询至少有一个家属的部门经理姓名。（必须用EXISTS谓词）SELECT FNAME,