数据库查询.docx
《数据库查询.docx》由会员分享,可在线阅读,更多相关《数据库查询.docx(14页珍藏版)》请在冰豆网上搜索。
数据库查询
西南石油大学实验报告
一、实验课时:
4
二、实验目的
(1)理解查询的概念和方法。
(2)掌握SELECT语句在单表查询中的应用。
(3)掌握SELECT语句在多表连接查询中的应用。
(4)掌握SELECT语句在嵌套查询中的应用。
(5)掌握SELECT语句在集合查询中的应用。
(6)主要掌握使用“查询分析器”进行查询。
三、实验环境
(1)PC机。
(2)SQLServer2005。
四、实验内容及步骤
以下查询均使用实验项目1中创建的COMPANY数据库。
1.单表查询
(1)基本查询。
Q0.使用查询分析器从Employee表中检索出所有员工的姓名。
SELECTFNAME,LNAMEFROMEMPLOYEE;
Q1.使用查询分析器从Employee表中检索出员工的FNAME、LNAME、SSN、BDATE、SALARY等字段,并分别加上“名”、“姓”、“社会保险号”、“生日”、“工资”的标题。
员工的排序规则为:
首先按工资的降序排列,然后按FNAME的字母升序排列。
SELECTFNAMEAS名,LNAMEAS姓,SSNAS社会保险号,BDATEAS生日,
SALARYAS工资FROMEMPLOYEEORDERBYSALARYDESC;
SELECTFNAMEAS名,LNAMEAS姓,SSNAS社会保险号,BDATEAS生日,SALARYAS工资FROMEMPLOYEEORDERBYFNAMEASC;
Q2.Retrievealldistinctsalaryvalues.
查询不重复的员工工资值。
SELECTDISTINCTSALARYFROMEMPLOYEE;
Q3.Retrievethenamesofallemployeeswhodonothavesupervisors.
查询没有直接上司的员工姓名。
SELECTFNAME,LNAMEFROMEMPLOYEEWHERESUPERSSNISNULL;
(2)基于WHERE子句进行数据查询。
1)基于比较条件。
Q4.从Employee表中查询出工资大于等于40000的员工资料。
SELECT*FROMEMPLOYEEWHERESALARY>=40000
SELECT*FROMEMPLOYEEWHEREBDATEBETWEEN'19600101'AND'19691231'
2)基于BETWEEN子句的查询。
Q5.从Employee表中查询出1960年——1970年之间出生的员工资料。
SELECT*FROMEMPLOYEEWHEREBDABETWEEN‘1960-01-01’AND‘19691231’(BDA>=’19600101’ANDBDA<=’19691231’)
3)基于IN子句的查询。
Q6.从Employee表中查询出部门号为4或者5的员工资料。
SELECT*FROMEMPLOYEEWHEREDNOIN(4,5);
4)基于LIKE子句的查询。
Q7.从Employee表中查询出LNAME中含有字母o的员工资料。
SELECT*FROMEMPLOYEEWHERELNAMELIKE'%O%';
(3)使用计算列查询。
Q8.从Employee表中检索出员工的FNAME、LNAME、SSN、SALARY等字段(其中SALARY需换算成人民币,汇率假定为1美元=8人民币元),并分别加上“名”、“姓”、“社会保险号”、“人民币工资”的标题。
SELECTFNAME名,LNAME姓,SSN社会保险号
Y*8人民币工资FROMEMPLOYEE;
2.多表连接查询(使用JOIN)
Q9.Retrievethenameandaddressofallemployeeswhoworkforthe‘Research’department.
查询所有为Research部门工作的员工姓名及地址。
SELECTFNAME,LNAME,ADDRESSFROMEMPLOYEEEJOINDEPARTMENTDONE.DNO=D.DNUMBERWHEREDNAME='Research';
Q10.Foreveryprojectlocatedin‘Stafford’,listtheprojectnumber,thecontrollingdepartmentnumber,andthedepartmentmanager’slastname,address,andbirthdate.
对于所有位于Stafford的项目,查询项目的编号、项目负责部门编号以及该部门经理的姓、地址、生日。
SELECTP.PNUMBER,P.DNUM,E.LNAME,E.ADDRESS,E.BDATEFROMPROJECTPJOINDEPARTMENTDONP.DNUM=D.DNUMBERJOINEMPLOYEEEOND.MGRSSN=E.SSNWHEREPLOCATION='Stafford'
Q11.Listthenamesofallemployeeswithtwoormoredependents.
查询有两个或以上家属的员工姓名(此题较难,若不能完成者可只查询出员工的SSN而不是姓名)。
SELECTFNAME,LNAMEFROMEMPLOYEEWHERESSN
IN(SELECTESSNFROMDEPENDENTGROUPBYESSNHAVINGCOUNT(*)>=2)
Q12.Retrievethenamesofallemployeesindepartment5whoworkmorethan10hoursperweekonthe‘ProductX’project.
查询在ProductX项目上每周工作时间超过10小时的部门5的员工姓名。
SELECTE.FNAME,E.LNAMEFROMPROJECTP
JOINWORKS_ONWONP.PNUMBER=W.PNO
JOINEMPLOYEEEONW.ESSN=E.SSN
WHEREP.PNAME='PRODUCTX'
ANDW.HOURS>10ANDE.DNO='5';
Q13.Foreachproject,listtheprojectnameandthetotalhoursperweek(byallemployees)spentonthatproject.
对于每个项目,列出项目名称以及所有员工在此项目上工作的总时间。
SELECTP.PNAME,SUM(HOURS)AS总时间
FROMWORKS_ONW
JOINPROJECTPONW.PNO=P.PNUMBER
GROUPBYP.PNAME;
Q14.Foreachdepartment,retrievethedepartmentnameandtheaveragesalaryofallemployeesworkinginthatdepartment.
对于每个部门,列出部门名称以及此部门员工的平均工资。
SELECTD.DNAME,AVG(SALARY)AS平均工资
FROMEMPLOYEEEJOINDEPARTMENTDOND.DNUMBER=E.DNO
GROUPBYD.DNAME;
Q15.Foreachemployee,retrievetheemployee’sfirstandlastnameandthefirstandlastnameofhisorherimmediatesupervisor.
对于每个员工,查询其姓名以及他/她的直接上司的姓名。
SELECTEE.FNAME+','+EE.LNAMEAS员工姓名,
ES.FNAME+','+ES.LNAMEAS上级姓名
FROMEMPLOYEEEEJOINEMPLOYEEESONEE.SUPERSSN=ES.SSN;
Q16.SelectallcombinationsofEMPLOYEESSNandDEPARTMENTDNAMEintheCompanydatabase.
查询Company数据库中所有员工SSN与DNAME(部门名称)的组合。
SELECTE.SSN,D.DNAMEFROMEMPLOYEEEINNER
JOINDEPARTMENTDONE.DNO=D.DNUMBER
Q17.查询有两个或以上“工资大于等于30000员工”的部门名称。
SELECTD.DNAMEFROMEMPLOYEEE
JOINDEPARTMENTDOND.DNUMBER=E.DNO
WHERESALARY>=30000GROUPBYD.DNAME
HAVINGCOUNT(SALARY)>=2;
3.嵌套查询(子查询)
Q18.Retrievethenamesofallemployeeswhodonotworkonanyprojectcontrolledbydepartmentnumber5.
查询没有参与任何部门5控制项目的员工姓名。
SELECTFNAME,LNAMEFROMEMPLOYEE
WHERESSNNOTIN(SELECTESSNFROMWORKS_ON
WHEREPNOIN(SELECTPNUMBERFROMPROJECT
WHEREDNUM=5));
Q19.Retrievethenamesofemployeeswhosesalaryisgreaterthanthesalaryofalltheemployeesindepartment5.
查询工资超过部门5所有员工工资的员工姓名。
SELECTFNAME,LNAMEFROMEMPLOYEE
WHERESALARY>ALL(SELECTSALARY
FROMEMPLOYEEWHEREDNO=5);
Q20.Retrievethenamesofallemployeeswhoworkoneveryproject.
查询参与了所有项目的员工姓名。
SELECTFNAME,LNAMEFROMEMPLOYEE
WHERESSNIN(SELECTESSNFROMWORKS_ON
WHEREPNO=ALL(SELECTPNAMEFROMPROJECT));
Q21.Findthenamesofemployeeswhoworkonalltheprojectscontrolledbydepartmentnumber4.
查询至少参与了所有部门4控制项目的员工姓名。
SELECTFNAME,LNAMEFROMEMPLOYEE
WHERESSNIN
(SELECTESSNFROMWORKS_ONWHEREPNO=ANY
(SELECTPNUMBERFROMPROJECTWHEREDNUM=4));
Q22.Retrievethenamesofemployeeswhoworkonalltheprojectsthat‘JohnSmith’workson.
查询至少参与了所有JohnSmith参与项目的员工姓名。
SELECTFNAME,LNAMEFROMEMPLOYEE
WHERESSNIN(SELECTESSNFROMWORKS_ON
WHEREPNO=ANY(SELECTPNUMBERFROMPROJECT
WHEREPNUMBERIN(SELECTPNOFROMWORKS_ON
WHEREESSNIN(SELECTSSNFROMEMPLOYEE
WHEREFNAME='JOHN'ANDLNAME='SMITH'))));
Q23.Listthenamesofmanagerswhohaveatleastonedependent.
查询至少有一个家属的部门经理姓名。
SELECTFNAME,LNAMEFROMEMPLOYEE
WHERESSNIN(SELECTMGRSSNFROMDEPARTMENT
WHEREMGRSSNIN(SELECTESSNFROMDEPENDENT));
Q24.查询有两个或以上隶属员工的部门名称及其“工资大于等于30000员工”总数。
SELECTDNAME,(SELECTCOUNT(*)
FROMEMPLOYEE
WHERESALARY>=30000ANDDNO=E.DNO)AS总数
FROMEMPLOYEEE
JOINDEPARTMENTONE.DNO=DNUMBER
GROUPBYDNAME,DNOHAVINGCOUNT(*)>=2;
Q25.Listthenamesofallemployeeswithtwoormoredependents.
查询有两个或以上家属的员工姓名。
SELECTFNAME,LNAMEFROMEMPLOYEE
WHERESSNIN
(SELECTESSNFROMDEPENDENT
GROUPBYESSNHAVINGCOUNT(ESSN)>=2);
4.集合查询
Q26.Makealistofprojectnumbersforprojectsthatinvolveanemployeewhoselastnameis‘Smith’,eitherasaworkerorasamanagerofthedepartmentthatcontrolstheproject.
查询符合以下任意条件的项目编号:
(1)参与此项目员工的LNAME是Smith;
(2)控制此项目的部门经理的LNAME是Smith。
SELECTPNAMEFROMPROJECT
WHEREPNUMBERIN
(SELECTPNOFROMWORKS_ON
WHEREESSNIN
(SELECTSSNFROMEMPLOYEE
WHERELNAME='SMITH'))
ANDDNUMIN
(SELECTDNUMBERFROMDEPARTMENT
WHEREMGRSSNIN
(SELECTSSNFROMEMPLOYEE
WHERELNAME='SMITH'));
五、收获,体会及问题
本次实验是数据库的查询,用的是第一个实验建立的数据库,刚开始就在查询时发现了一些实验一的错误,以至于不能正常查询结果。
这次实验主要完成五个方面的内容:
理解查询的概念和方法;掌握SELECT语句在单表查询中的应用;掌握SELECT语句在多表连接查询中的应用;掌握SELECT语句在嵌套查询中的应用;掌握SELECT语句在集合查询中的应用。
主要是对各种语句进行熟悉,有好几题都遇到了问题,发现自己的SELECT语句掌握的还不行,有很多功能不知道如何实现。