数据库实验二文档格式.docx
《数据库实验二文档格式.docx》由会员分享,可在线阅读,更多相关《数据库实验二文档格式.docx(23页珍藏版)》请在冰豆网上搜索。
在粘贴截图时请保留窗口完整标题,但只需保留关键界面,多余的空白界面请删除。
一、实验课时:
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.查询不重复的员工工资值。
SELECTDISTINCTSalary
FROMEMPLOYEE;
Q3.查询没有直接上司的员工姓名。
SELECTFname,Lname
WHERESuper_ssnISNULL;
(2)基于WHERE子句进行数据查询。
1)基于比较条件。
Q4.从Employee表中查询出工资大于等于40000的员工资料。
SELECT*
WHERESalary>
=40000;
Q5.检索姓名为JohnB.Smith的员工的SSN、性别、出生日期和地址。
SELECTSsn,Sex,Bdate,Address
WHEREFname='
JOHN'
ANDLname='
SMITH'
ANDMinit='
B'
;
2)基于BETWEEN子句的查询。
Q6.从Employee表中查询出1960年——1970年之间出生的员工资料。
SELECT*FROMEMPLOYEE
WHEREBdateBETWEEN'
1960-01-01'
AND'
1970-01-01'
3)基于IN子句的查询。
Q7.从Employee表中查询出部门号为4或者5的员工资料。
WHEREDnoIN(4,5);
4)基于LIKE子句的查询。
Q8.从Employee表中查询出LNAME中含有字母o的员工资料。
WHERELnameLIKE'
%O%'
Q9.检索居住在Houston的员工所有信息。
WHEREAddressLIKE'
%Houston%'
(3)使用计算列查询。
Q10.从Employee表中检索出员工的FNAME、LNAME、SSN、SALARY等字段(其中SALARY需换算成人民币,汇率假定为1美元=8人民币元),并分别加上“名”、“姓”、“社会保险号”、“人民币工资”的标题。
Salary*8人民币工资
2.多表连接查询(使用JOIN)
Q11.查询所有为Research部门工作的员工姓名及地址。
SELECTFname,Lname,Address
FROMEMPLOYEEE
JOINDEPARTMENTD
ONE.Dno=D.Dnumber
WHEREDname='
Research'
Q12.对于所有位于Stafford的项目,查询项目的编号、项目负责部门编号以及该部门经理的姓、地址、生日。
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而不是姓名)。
SELECTE.Fname,E.Lname
FROMEMPLOYEEE
JOINDEPENDENTD
ON(D.Essn=E.Ssn)
GROUPBYD.Essn,E.Fname,E.Lname
HAVINGCOUNT(*)>
=2;
Q14.查询在ProductX项目上每周工作时间超过10小时的部门5的员工姓名。
(对于连接操作必须是主外键,但是对于where条件并没有什么特殊要求)
JOIN
WORKS_ONWON(W.Essn=E.Ssn)
JOIN
PROJECTPON(P.Pnumber=W.Pno)
WHEREW.Hours>
10ANDP.Pname='
PRODUCTX'
ANDDno=5;
Q15.对于每个项目,列出项目名称以及所有员工在此项目上工作的总时间。
SELECTP.Pname,SUM(W.Hours)总时间
FROMWORKS_ONWJOIN
PROJECTPON(P.Pnumber=W.Pno)GROUPBYP.Pname;
Q16.对于每个部门,列出部门名称以及此部门员工的平均工资。
SELECTD.Dname,AVG(E.Salary)AS平均工资
FROMEMPLOYEEEJOINDEPARTMENTD
OND.Dnumber=E.Dno
GROUPBYD.Dname;
Q17.对于每个员工(包括无上司员工),查询其姓名以及他/她的直接上司的姓名。
SELECTE.Lname员工姓,E.Fname员工名,
EM.Lname上司姓,EM.Fname上司名
FROM
EMPLOYEEELEFT
EMPLOYEEEMON(E.Super_ssn=EM.Ssn);
Q18.查询Company数据库中所有员工SSN与DNAME(部门名称)的组合。
SELECTE.Ssn,D.Dname
EMPLOYEEE
DEPARTMENTDON(D.Dnumber=E.Dno);
Q19.查询有两个或以上“工资大于等于30000员工”的部门名称。
SELECTD.Dname
DEPARTMENTDON(D.Dnumber=E.Dno)
WHEREE.Salary>
=30000
----先执行WHERER在执行GROUPBY
GROUPBYD.Dname
HAVINGCOUNT(*)>
=2;
3.嵌套查询(子查询)
Q20.查询没有参与任何部门5控制项目的员工姓名。
FROMEMPLOYEE
WHERESsnNOTIN(
SELECTEssn
FROMWORKS_ON
WHEREPnoIN(
SELECTPnumber
FROMPROJECT
WHEREDnum=5));
Q21.查询工资超过部门5所有员工工资的员工姓名。
SELECTFNAME,LNAME
ALL
(SELECTSalary
WHEREDno=5);
Q22.查询参与了所有项目的员工姓名。
(选做)
WHERESSNIN
(SELECTEssn
GROUPBYEssn
HAVINGCOUNT(Essn)=(
SELECTCOUNT(*)
FROMPROJECT));
Q23.查询至少参与了所有部门4控制项目的员工姓名。
WHERESsnIN(
WHEREPnoIN(SELECTPnumberFROM
PROJECTWHEREDnum=4)
GROUPBYESSN
HAVINGCOUNT(*)=(SELECTCOUNT(*)FROM
PROJECTWHEREDnum=4));
Q24.查询至少参与了所有JohnSmith参与项目的员工姓名。
SELECTFname,LnameFROMEMPLOYEE
SELECTEssnFROMWORKS_ON
WHEREPnoIN
(SELECTDISTINCTPno
FROMWORKS_ONWHEREEssnIN(
SELECTSsnFROMEMPLOYEE
))
HAVINGCOUNT(*)=(SELECTCOUNT(*)
)))
ANDSsn!
=(SELECTSsnFROMEMPLOYEE
);
Q25.查询至少有一个家属的部门经理姓名。
(必须用EXISTS谓词)
SELECTFNAME,