西南石油大学实验报告数据库原理

　 西南石油大学实验报告

　课程名称：数据库原理

　插入你的照片

　实验项目名称：实验项目2 SQL数据操纵语言

　专业年级班级、姓名、学号：软件13级2班

　电子邮件地址：

　实验所用机器名：

　实验时间地点：2015.12.03 明理楼

　实验指导教师：孙瑜

　成绩

　批改人

　批改日期

　注意：在粘贴截图时请保留窗口完整标题，但只需保留关键界面，多余的空白界面请删除。

　一、实验课时：4

　二、实验目的(1) 理解查询的概念和方法。

　(2) 掌握SELECT语句在单表查询中的应用。

　(3) 掌握SELECT语句在多表连接查询中的应用。

　(4) 掌握SELECT语句在嵌套查询中的应用。

　(5) 掌握SELECT语句在集合查询中的应用。

　(6) 主要掌握使用“查询分析器”进行查询。

　三、实验环境

　(1) PC机。

　(2) SQL Server 2008。

　四、实验内容及步骤

　以下查询均使用实验项目1中创建的COMPANY数据库。

　1．单表查询

　(1)基本查询。

　Q0. 使用查询分析器从Employee表中检索出所有员工的姓名。

　SQL文本：SELECT FNAME,LNAME FROM EMPLOYEE;

　(以下所有题目都必须按照上图的要求截图)

　Q1.从Employee表中检索出员工的FNAME、LNAME、SSN、BDATE、SALARY等字段，并分别加上“名”、“姓”、“社会保险号”、“生日”、“工资”的标题。员工的排序规则为：首先按工资的降序排列，然后按FNAME的字母升序排列。

　SQL文本:

　SELECT Fname 名,Lname 姓,Ssn 社会保险号,

　Bdate 生日,Salary 工资

　FROM EMPLOYEE

　ORDER BY Salary DESC,Fname ;

　Q2. 查询不重复的员工工资值。

　SQL文本:

　SELECT DISTINCT Salary

　FROM EMPLOYEE;

　Q3. 查询没有直接上司的员工姓名。

　SQL文本:

　SELECT Fname,Lname

　FROM EMPLOYEE

　WHERE Super_ssn IS NULL;

　(2) 基于WHERE子句进行数据查询。

　1）基于比较条件。

　Q4. 从Employee表中查询出工资大于等于40000的员工资料。

　SQL文本:

　SELECT *

　FROM EMPLOYEE

　WHERE Salary >= 40000;

　Q5.检索姓名为John B.Smith的员工的SSN、性别、出生日期和地址。

　SQL文本:

　SELECT Ssn,Sex,Bdate,Address

　FROM EMPLOYEE

　WHERE Fname = 'JOHN' AND Lname = 'SMITH'

　AND Minit = 'B';

　2）基于BETWEEN子句的查询。

　Q6. 从Employee表中查询出1960年——1970年之间出生的员工资料。

　SQL文本:

　SELECT * FROM EMPLOYEE

　WHERE Bdate BETWEEN '1960-01-01'

　AND '1970-01-01';

　3）基于IN子句的查询。

　Q7. 从Employee表中查询出部门号为4或者5的员工资料。

　SQL文本:

　SELECT *

　FROM EMPLOYEE

　WHERE Dno IN (4,5);

　4）基于LIKE子句的查询。

　Q8. 从Employee表中查询出LNAME中含有字母o的员工资料。

　SQL文本:

　SELECT *

　FROM EMPLOYEE

　WHERE Lname LIKE '%O%';

　Q9.检索居住在Houston的员工所有信息。

　SQL文本:

　SELECT *

　FROM EMPLOYEE

　WHERE Address LIKE '%Houston%';

　(3) 使用计算列查询。

　Q10. 从Employee表中检索出员工的FNAME、LNAME、SSN、SALARY等字段(其中SALARY需换算成人民币，汇率假定为1美元=8人民币元)，并分别加上“名”、“姓”、“社会保险号”、“人民币工资”的标题。

　SQL文本:

　SELECT Fname 名,Lname 姓,Ssn 社会保险号,

　Salary * 8 人民币工资

　FROM EMPLOYEE;

　2． 多表连接查询(使用JOIN)

　Q11. 查询所有为Research部门工作的员工姓名及地址。

　SELECT Fname, Lname, Address

　FROM EMPLOYEE E

　JOIN DEPARTMENT D

　ON E.Dno=D.Dnumber

　WHERE Dname='Research';

　Q12. 对于所有位于Stafford的项目，查询项目的编号、项目负责部门编号以及该部门经理的姓、地址、生日。

　SQL文本:

　SELECT E.Lname,E.Address,E.Bdate,P.Pnumber,P.Dnum

　FROM PROJECT P JOIN

　DEPARTMENT D ON (P.Dnum = D.Dnumber)

　JOIN EMPLOYEE E ON (D.Mgr_ssn = E.Ssn)

　WHERE Plocation = 'Stafford';

　Q13. 查询有两个或以上家属的员工姓名(此题较难，若不能完成者可只查询出员工的SSN而不是姓名)。

　SQL文本:

　SELECT E.Fname,E.Lname

　FROM EMPLOYEE E

　JOIN DEPENDENT D

　ON (D.Essn = E.Ssn)

　GROUP BY D.Essn,E.Fname,E.Lname

　HAVING COUNT(*) >= 2;

　Q14. 查询在ProductX项目上每周工作时间超过10小时的部门5的员工姓名。(对于连接操作必须是主外键，但是对于where条件并没有什么特殊要求)

　SQL文本:

　SELECT E.Fname,E.Lname

　FROM EMPLOYEE E

　JOIN

　WORKS_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. 对于每个项目，列出项目名称以及所有员工在此项目上工作的总时间。

　SQL文本:

　SELECT P.Pname,SUM(W.Hours) 总时间

　FROM WORKS_ON W JOIN

　PROJECT P ON (P.Pnumber = W.Pno) GROUP BY P.Pname;

　Q16. 对于每个部门，列出部门名称以及此部门员工的平均工资。

　SQL文本:

　SELECT D.Dname,AVG(E.Salary)AS 平均工资

　FROM EMPLOYEE E JOIN DEPARTMENT D

　ON D.Dnumber=E.Dno

　GROUP BY D.Dname;

　Q17. 对于每个员工(包括无上司员工)，查询其姓名以及他/她的直接上司的姓名。

　SQL文本:

　SELECT E.Lname 员工姓,E.Fname 员工名,

　EM.Lname 上司姓, EM.Fname 上司名

　FROM

　EMPLOYEE E LEFT

　JOIN

　EMPLOYEE EM ON(E.Super_ssn = EM.Ssn);

　Q18. 查询Company数据库中所有员工SSN与DNAME(部门名称)的组合。

　SQL文本:

　SELECT E.Ssn,D.Dname

　FROM

　EMPLOYEE E

　JOIN

　DEPARTMENT D ON (D.Dnumber = E.Dno);

　Q19. 查询有两个或以上“工资大于等于30000员工”的部门名称。

　SQL文本:

　SELECT D.Dname

　FROM

　EMPLOYEE E

　JOIN

　DEPARTMENT D ON (D.Dnumber = E.Dno)

　WHERE E.Salary >= 30000

　----先执行WHERER在执行GROUP BY

　GROUP BY D.Dname

　HAVING COUNT(*)>=2;

　3．嵌套查询(子查询)

　Q20. 查询没有参与任何部门5控制项目的员工姓名。

　SQL文本:

　SELECT Fname,Lname

　FROM EMPLOYEE

　WHERE Ssn NOT IN(

　SELECT Essn

　FROM WORKS_ON

　WHERE Pno IN (

　SELECT Pnumber

　FROM PROJECT

　WHERE Dnum = 5));

　Q21. 查询工资超过部门5所有员工工资的员工姓名。

　SQL文本:

　SELECT FNAME,LNAME

　FROM EMPLOYEE

　WHERE Salary > ALL

　(SELECT Salary

　FROM EMPLOYEE

　WHERE Dno = 5);

　Q22. 查询参与了所有项目的员工姓名。(选做)

　SQL文本:

　SELECT FNAME,LNAME

　FROM EMPLOYEE

　WHERE SSN IN

　(SELECT Essn

　FROM WORKS_ON

　GROUP BY Essn

　HAVING COUNT(Essn) = (

　SELECT COUNT(*)

　FROM PROJECT));

　Q23. 查询至少参与了所有部门4控制项目的员工姓名。(选做)

　SQL文本:

　SELECT Fname,Lname

　FROM EMPLOYEE

　WHERE Ssn IN(

　SELECT Essn

　FROM WORKS_ON

　WHERE Pno IN (SELECT Pnumber FROM

　PROJECT WHERE Dnum = 4)

　GROUP BY ESSN

　HAVING COUNT(*) = (SELECT COUNT(*) FROM

　PROJECT WHERE Dnum = 4));

　Q24. 查询至少参与了所有John Smith参与项目的员工姓名。(选做)

　SQL文本:

　SELECT Fname,Lname FROM EMPLOYEE

　WHERE Ssn IN(

　SELECT Essn FROM WORKS_ON

　WHERE Pno IN

　(SELECT DISTINCT Pno

　FROM WORKS_ON WHERE Essn IN(

　SELECT Ssn FROM EMPLOYEE

　WHERE Fname = 'JOHN' AND Lname = 'SMITH'))

　GROUP BY Essn

　HAVING COUNT(*) = (SELECT COUNT(*)

　FROM WORKS_ON WHERE Essn IN(

　SELECT Ssn FROM EMPLOYEE

　WHERE Fname = 'JOHN' AND Lname = 'SMITH')))

　AND Ssn != (SELECT Ssn FROM EMPLOYEE

　WHERE Fname = 'JOHN' AND Lname = 'SMITH');

　Q25. 查询至少有一个家属的部门经理姓名。(必须用EXISTS谓词)

　SQL文本:

　SELECT FNAME,LNAME

　FROM EMPLOYEE

　WHERE EXISTS

　(SELECT * FROM DEPARTMENT

　WHERE Mgr_ssn = Ssn AND Mgr_ssn = ANY

　(SELECT DISTINCT ESSN FROM DEPENDENT));

　Q26. 查询有两个或以上隶属员工的部门名称及其“工资大于等于30000员工”总数。(选做)

　SQL文本:

　SELECT D.Dname,COUNT(*) 总数

　FROM EMPLOYEE E

　JOIN DEPARTMENT D ON E.Dno = D.Dnumber

　WHERE Salary >= 30000

　GROUP BY D.Dname

　HAVING D.Dname IN (

　SELECT D.Dname

　FROM EMPLOYEE E

　JOIN DEPARTMENT D ON E.Dno = D.Dnumber

　GROUP BY Dname

　HAVING COUNT(*) >= 2);

　Q27. 查询有两个或以上家属的员工姓名。

　SQL文本:

　SELECT FNAME,LNAME

　FROM EMPLOYEE

　WHERE Ssn = ANY

　(SELECT ESSN

　FROM DEPENDENT

　GROUP BY Essn

　HAVING COUNT(*) >= 2);

　4．集合查询

　Q28. 查询符合以下任意条件的项目编号：(1)参与此项目员工的LNAME是Smith；(2)控制此项目的部门经理的LNAME是Smith。

　SQL文本:

　SELECT Pno

　FROM WORKS_ON

　JOIN EMPLOYEE ON Essn = Ssn

　WHERE Lname = 'SMITH'

　UNION

　SELECT Pnumber

　FROM EMPLOYEE

　JOIN DEPARTMENT ON Mgr_ssn = Ssn

　JOIN PROJECT ON Dnum = Dnumber

　WHERE Lname = 'SMITH';

　五、收获，体会及问题

　(请详细书写，写得越详细、越个性化、越真实越好，否则我不知道你做这个实验的心路历程，也就无法充分地判断你是否是独立完成的这个实验、你是否在做这个实验时进行了认真仔细地思考、通过这个实验你是否在实践能力上得到了提高)

　 通过本次实验让我对SQl所有的查询语句都有所了解。弄明白了where和Having的执行顺序。以及Join连接必须有参照关系才能连接。在做查询没有参与任何部门5控制项目的员工姓名这道题时，自己就陷入错误，在何时取反很重要。查询至少参与了所有部门4控制项目的员工姓名。(选做)这道题时要利用至少参加所有的这个条件，并且在查找是否有员工参与所有项目时，最好使用count（*）来选择。要小心一些题目中的陷阱。