数据库实验二.docx
《数据库实验二.docx》由会员分享，可在线阅读，更多相关《数据库实验二.docx（23页珍藏版）》请在冰豆网上搜索。
数据库实验二
西南石油大学实验报告
课程名称：
数据库原理
插入你的照片
实验项目名称：
实验项目2SQL数据操纵语言
专业年级班级、姓名、学号：
软件13级2班
电子邮件地址：
实验所用机器名：
实验时间地点：
2015.12.03明理楼
实验指导教师：
孙瑜
成绩
批改人
批改日期
注意：
在粘贴截图时请保留窗口完整标题，但只需保留关键界面，多余的空白界面请删除。
一、实验课时：
4
二、实验目的
（1）理解查询的概念和方法。
（2）掌握SELECT语句在单表查询中的应用。
（3）掌握SELECT语句在多表连接查询中的应用。
（4）掌握SELECT语句在嵌套查询中的应用。
（5）掌握SELECT语句在集合查询中的应用。
（6）主要掌握使用“查询分析器”进行查询。
三、实验环境
（1）PC机。
（2）SQLServer2008。
四、实验内容及步骤
以下查询均使用实验项目1中创建的COMPANY数据库。
1．单表查询
（1）基本查询。
Q0.使用查询分析器从Employee表中检索出所有员工的姓名。
SQL文本：
SELECTFNAME,LNAMEFROMEMPLOYEE;
（以下所有题目都必须按照上图的要求截图）
Q1.从Employee表中检索出员工的FNAME、LNAME、SSN、BDATE、SALARY等字段，并分别加上“名”、“姓”、“社会保险号”、“生日”、“工资”的标题。
员工的排序规则为：
首先按工资的降序排列，然后按FNAME的字母升序排列。
SQL文本:
SELECTFname名,Lname姓,Ssn社会保险号,
Bdate生日,Salary工资
FROMEMPLOYEE
ORDERBYSalaryDESC,Fname;
Q2.查询不重复的员工工资值。
SQL文本:
SELECTDISTINCTSalary
FROMEMPLOYEE;
Q3.查询没有直接上司的员工姓名。
SQL文本:
SELECTFname,Lname
FROMEMPLOYEE
WHERESuper_ssnISNULL;
（2）基于WHERE子句进行数据查询。
1）基于比较条件。
Q4.从Employee表中查询出工资大于等于40000的员工资料。
SQL文本:
SELECT*
FROMEMPLOYEE
WHERESalary>=40000;
Q5.检索姓名为JohnB.Smith的员工的SSN、性别、出生日期和地址。
SQL文本:
SELECTSsn,Sex,Bdate,Address
FROMEMPLOYEE
WHEREFname='JOHN'ANDLname='SMITH'
ANDMinit='B';
2）基于BETWEEN子句的查询。
Q6.从Employee表中查询出1960年——1970年之间出生的员工资料。
SQL文本:
SELECT*FROMEMPLOYEE
WHEREBdateBETWEEN'1960-01-01'
AND'1970-01-01';
3）基于IN子句的查询。
Q7.从Employee表中查询出部门号为4或者5的员工资料。
SQL文本:
SELECT*
FROMEMPLOYEE
WHEREDnoIN（4,5）;
4）基于LIKE子句的查询。
Q8.从Employee表中查询出LNAME中含有字母o的员工资料。
SQL文本:
SELECT*
FROMEMPLOYEE
WHERELnameLIKE'%O%';
Q9.检索居住在Houston的员工所有信息。
SQL文本:
SELECT*
FROMEMPLOYEE
WHEREAddressLIKE'%Houston%';
（3）使用计算列查询。
Q10.从Employee表中检索出员工的FNAME、LNAME、SSN、SALARY等字段（其中SALARY需换算成人民币，汇率假定为1美元=8人民币元），并分别加上“名”、“姓”、“社会保险号”、“人民币工资”的标题。
SQL文本:
SELECTFname名,Lname姓,Ssn社会保险号,
Salary*8人民币工资
FROMEMPLOYEE;
2．多表连接查询（使用JOIN）
Q11.查询所有为Research部门工作的员工姓名及地址。
SELECTFname,Lname,Address
FROMEMPLOYEEE
JOINDEPARTMENTD
ONE.Dno=D.Dnumber
WHEREDname='Research';
Q12.对于所有位于Stafford的项目，查询项目的编号、项目负责部门编号以及该部门经理的姓、地址、生日。
SQL文本:
SELECTE.Lname,E.Address,E.Bdate,P.Pnumber,P.Dnum
FROMPROJECTPJOIN
DEPARTMENTDON（P.Dnum=D.Dnumber）
JOINEMPLOYEEEON（D.Mgr_ssn=E.Ssn）
WHEREPlocation='Stafford';
Q13.查询有两个或以上家属的员工姓名（此题较难，若不能完成者可只查询出员工的SSN而不是姓名）。
SQL文本:
SELECTE.Fname,E.Lname
FROMEMPLOYEEE
JOINDEPENDENTD
ON（D.Essn=E.Ssn）
GROUPBYD.Essn,E.Fname,E.Lname
HAVINGCOUNT（*）>=2;
Q14.查询在ProductX项目上每周工作时间超过10小时的部门5的员工姓名。
（对于连接操作必须是主外键，但是对于where条件并没有什么特殊要求）
SQL文本:
SELECTE.Fname,E.Lname
FROMEMPLOYEEE
JOIN
WORKS_ONWON（W.Essn=E.Ssn）
JOIN
PROJECTPON（P.Pnumber=W.Pno）
WHEREW.Hours>10ANDP.Pname='PRODUCTX'ANDDno=5;
Q15.对于每个项目，列出项目名称以及所有员工在此项目上工作的总时间。
SQL文本:
SELECTP.Pname,SUM（W.Hours）总时间
FROMWORKS_ONWJOIN
PROJECTPON（P.Pnumber=W.Pno）GROUPBYP.Pname;
Q16.对于每个部门，列出部门名称以及此部门员工的平均工资。
SQL文本:
SELECTD.Dname,AVG（E.Salary）AS平均工资
FROMEMPLOYEEEJOINDEPARTMENTD
OND.Dnumber=E.Dno
GROUPBYD.Dname;
Q17.对于每个员工（包括无上司员工），查询其姓名以及他/她的直接上司的姓名。
SQL文本:
SELECTE.Lname员工姓,E.Fname员工名,
EM.Lname上司姓,EM.Fname上司名
FROM
EMPLOYEEELEFT
JOIN
EMPLOYEEEMON（E.Super_ssn=EM.Ssn）;
Q18.查询Company数据库中所有员工SSN与DNAME（部门名称）的组合。
SQL文本:
SELECTE.Ssn,D.Dname
FROM
EMPLOYEEE
JOIN
DEPARTMENTDON（D.Dnumber=E.Dno）;
Q19.查询有两个或以上“工资大于等于30000员工”的部门名称。
SQL文本:
SELECTD.Dname
FROM
EMPLOYEEE
JOIN
DEPARTMENTDON（D.Dnumber=E.Dno）
WHEREE.Salary>=30000
----先执行WHERER在执行GROUPBY
GROUPBYD.Dname
HAVINGCOUNT（*）>=2;
3．嵌套查询（子查询）
Q20.查询没有参与任何部门5控制项目的员工姓名。
SQL文本:
SELECTFname,Lname
FROMEMPLOYEE
WHERESsnNOTIN（
SELECTEssn
FROMWORKS_ON
WHEREPnoIN（
SELECTPnumber
FROMPROJECT
WHEREDnum=5））;
Q21.查询工资超过部门5所有员工工资的员工姓名。
SQL文本:
SELECTFNAME,LNAME
FROMEMPLOYEE
WHERESalary>ALL
（SELECTSalary
FROMEMPLOYEE
WHEREDno=5）;
Q22.查询参与了所有项目的员工姓名。
（选做）
SQL文本:
SELECTFNAME,LNAME
FROMEMPLOYEE
WHERESSNIN
（SELECTEssn
FROMWORKS_ON
GROUPBYEssn
HAVINGCOUNT（Essn）=（
SELECTCOUNT（*）
FROMPROJECT））;
Q23.查询至少参与了所有部门4控制项目的员工姓名。
（选做）
SQL文本:
SELECTFname,Lname
FROMEMPLOYEE
WHERESsnIN（
SELECTEssn
FROMWORKS_ON
WHEREPnoIN（SELECTPnumberFROM
PROJECTWHEREDnum=4）
GROUPBYESSN
HAVINGCOUNT（*）=（SELECTCOUNT（*）FROM
PROJECTWHEREDnum=4））;
Q24.查询至少参与了所有JohnSmith参与项目的员工姓名。
（选做）
SQL文本:
SELECTFname,LnameFROMEMPLOYEE
WHERESsnIN（
SELECTEssnFROMWORKS_ON
WHEREPnoIN
（SELECTDISTINCTPno
FROMWORKS_ONWHEREEssnIN（
SELECTSsnFROMEMPLOYEE
WHEREFname='JOHN'ANDLname='SMITH'））
GROUPBYEssn
HAVINGCOUNT（*）=（SELECTCOUNT（*）
FROMWORKS_ONWHEREEssnIN（
SELECTSsnFROMEMPLOYEE
WHEREFname='JOHN'ANDLname='SMITH'）））
ANDSsn!
=（SELECTSsnFROMEMPLOYEE
WHEREFname='JOHN'ANDLname='SMITH'）;
Q25.查询至少有一个家属的部门经理姓名。
（必须用EXISTS谓词）
SQL文本:
SELECTFNAME,
升级会员 