数据库实验二
合集下载
相关主题
- 1、下载文档前请自行甄别文档内容的完整性，平台不提供额外的编辑、内容补充、找答案等附加服务。
- 2、"仅部分预览"的文档,不可在线预览部分如存在完整性等问题,可反馈申请退款(可完整预览的文档不适用该条件!)。
- 3、如文档侵犯您的权益，请联系客服反馈,我们会尽快为您处理(人工客服工作时间：9:00-18:30)。
Q8.从Employee表中查询出LNAME中含有字母o的员工资料。
SQL文本:
SELECT*
FROMEMPLOYEE
WHERELnameLIKE'%O%';
Q9.检索居住在Houston的员工所有信息。
SQL文本:
SELECT*
FROMEMPLOYEE
WHEREAddressLIKE'%Houston%';
FROMEMPLOYEE
JOINDEPARTMENTONMgr_ssn=Ssn
JOINPROJECTONDnum=Dnumber
WHERELname='SMITH';
五、收获，体会及问题
(请详细书写，写得越详细、越个性化、越真实越好，否则我不知道你做这个实验的心路历程，也就无法充分地判断你是否是独立完成的这个实验、你是否在做这个实验时进行了认真仔细地思考、通过这个实验你是否在实践能力上得到了提高)
SQL文本:
SELECTE.Lname员工姓,E.Fname员工名,
EM.Lname上司姓,EM.Fname上司名
FROM
EMPLOYEEELEFT
JOIN
EMPLOYEEEMON(E.Super_ssn=EM.Ssn);
Q18.查询Company数据库中所有员工SSN与DNAME(部门名称)的组合。
Q1.从Employee表中检索出员工的FNAME、LNAME、SSN、BDATE、SALARY等字段，并分别加上“名”、“姓”、“社会保险号”、“生日”、“工资”的标题。员工的排序规则为：首先按工资的降序排列，然后按FNAME的字母升序排列。
SQL文本:
SELECTFname名,Lname姓,Ssn社会保险号,
FROMPROJECT));
Q23.查询至少参与了所有部门4控制项目的员工姓名。(选做)
SQL文本:
SELECTFname,Lname
FROMEMPLOYEE
WHERESsnIN(
SELECTEssn
FROMWORKS_ON
WHEREPnoIN(SELECTPnumberFROM
PROJECTWHEREDnum=4)
(3)使用计算列查询。
Q10.从Employee表中检索出员工的FNAME、LNAME、SSN、SALARY等字段(其中SALARY需换算成人民币，汇率假定为1美元=8人民币元)，并分别加上“名”、“姓”、“社会保险号”、“人民币工资”的标题。
SQL文本:
SELECTFname名,Lname姓,Ssn社会保险号,
SELECTSsnFROMEMPLOYEE
WHEREFname='JOHN'ANDLname='SMITH')))
ANDSsn!=(SELECTSsnFROMEMPLOYEE
WHEREFname='JOHN'ANDLname='SMITH');
Q25.查询至少有一个家属的部门经理姓名。(必须用EXISTS谓词)
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)
WHEREW.Hours>10ANDP.Pname='PRODUCTX'ANDDno=5;
Q15.对于每个项目，列出项目名称以及所有员工在此项目上工作的总时间。
SQL文本:
SELECTP.Pname,SUM(W.Hours)总时间
FROMWORKS_ONWJOIN
PROJECTPON(P.Pnumber=W.Pno)GROUPBYP.Pname;
WHEREPnoIN
(SELECTDISTINCTPno
FROMWORKS_ONWHEREEssnIN(
SELECTSsnFROMEMPLOYEE
WHEREFname='JOHN'ANDLname='SMITH'))
GROUPBYEssn
HAVINGCOUNT(*)=(SELECTCOUNT(*)
FROMWORKS_ONWHEREEssnIN(
西南石油大学实验报告
课程名称：数百度文库库原理
插入你的照片
实验项目名称：实验项目2 SQL数据操纵语言
专业年级班级、姓名、学号：软件13级2班
电子邮件地址：
实验所用机器名：
实验时间地点：2015.12.03明理楼
实验指导教师：孙瑜
成绩
批改人
批改日期
注意：在粘贴截图时请保留窗口完整标题，但只需保留关键界面，多余的空白界面请删除。
Bdate生日,Salary工资
FROMEMPLOYEE
ORDERBYSalaryDESC,Fname;
Q2.查询不重复的员工工资值。
SQL文本:
SELECTDISTINCTSalary
FROMEMPLOYEE;
Q3.查询没有直接上司的员工姓名。
SQL文本:
SELECTFname,Lname
FROMEMPLOYEE
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';
SQL文本:
SELECTFNAME,LNAME
FROMEMPLOYEE
WHEREEXISTS
(SELECT*FROMDEPARTMENT
WHEREMgr_ssn=SsnANDMgr_ssn=ANY
(SELECTDISTINCTESSNFROMDEPENDENT));
Q26.查询有两个或以上隶属员工的部门名称及其“工资大于等于30000员工”总数。(选做)
通过本次实验让我对SQl所有的查询语句都有所了解。弄明白了where和Having的执行顺序。以及Join连接必须有参照关系才能连接。在做查询没有参与任何部门5控制项目的员工姓名这道题时，自己就陷入错误，在何时取反很重要。查询至少参与了所有部门4控制项目的员工姓名。(选做)这道题时要利用至少参加所有的这个条件，并且在查找是否有员工参与所有项目时，最好使用count（*）来选择。要小心一些题目中的陷阱。
Q13.查询有两个或以上家属的员工姓名(此题较难，若不能完成者可只查询出员工的SSN而不是姓名)。
SQL文本:
SELECTE.Fname,E.Lname
FROMEMPLOYEEE
JOINDEPENDENTD
ON(D.Essn=E.Ssn)
GROUPBYD.Essn,E.Fname,E.Lname
HAVINGCOUNT(*)>=2;
Q16.对于每个部门，列出部门名称以及此部门员工的平均工资。
SQL文本:
SELECTD.Dname,AVG(E.Salary)AS平均工资
FROMEMPLOYEEEJOINDEPARTMENTD
OND.Dnumber=E.Dno
GROUPBYD.Dname;
Q17.对于每个员工(包括无上司员工)，查询其姓名以及他/她的直接上司的姓名。
SELECTEssn
FROMWORKS_ON
WHEREPnoIN(
SELECTPnumber
FROMPROJECT
WHEREDnum=5));
Q21.查询工资超过部门5所有员工工资的员工姓名。
SQL文本:
SELECTFNAME,LNAME
FROMEMPLOYEE
WHERESalary>ALL
(SELECTSalary
SQL文本:
SELECTSsn,Sex,Bdate,Address
FROMEMPLOYEE
WHEREFname='JOHN'ANDLname='SMITH'
ANDMinit='B';
2）基于BETWEEN子句的查询。
Q6. 从Employee表中查询出1960年——1970年之间出生的员工资料。
SQL文本:
FROMEMPLOYEE
WHEREDno=5);
Q22.查询参与了所有项目的员工姓名。(选做)
SQL文本:
SELECTFNAME,LNAME
FROMEMPLOYEE
WHERESSNIN
(SELECTEssn
FROMWORKS_ON
GROUPBYEssn
HAVINGCOUNT(Essn)=(
SELECTCOUNT(*)
Salary*8人民币工资
FROMEMPLOYEE;
2
Q11.查询所有为Research部门工作的员工姓名及地址。
SELECTFname,Lname,Address
FROMEMPLOYEEE
JOINDEPARTMENTD
ONE.Dno=D.Dnumber
WHEREDname='Research';
Q12.对于所有位于Stafford的项目，查询项目的编号、项目负责部门编号以及该部门经理的姓、地址、生日。
(2)SQL Server 2008。
四、实验内容及步骤
以下查询均使用实验项目1中创建的COMPANY数据库。
1．单表查询
(1)基本查询。
Q0.使用查询分析器从Employee表中检索出所有员工的姓名。
SQL文本：SELECT FNAME,LNAME FROM EMPLOYEE;
(以下所有题目都必须按照上图的要求截图)
SQL文本:
SELECTD.Dname,COUNT(*)总数
FROMEMPLOYEEE
JOINDEPARTMENTDONE.Dno=D.Dnumber
WHERESalary>=30000
GROUPBYD.Dname
HAVINGD.DnameIN(
SELECTD.Dname
FROMEMPLOYEEE
JOINDEPARTMENTDONE.Dno=D.Dnumber
SELECT*FROMEMPLOYEE
WHEREBdateBETWEEN'1960-01-01'
AND'1970-01-01';
3）基于IN子句的查询。
Q7.从Employee表中查询出部门号为4或者5的员工资料。
SQL文本:
SELECT*
FROMEMPLOYEE
WHEREDnoIN(4,5);
4）基于LIKE子句的查询。
一、实验课时：
二、实验目的
(1)理解查询的概念和方法。
(2)掌握SELECT语句在单表查询中的应用。
(3)掌握SELECT语句在多表连接查询中的应用。
(4)掌握SELECT语句在嵌套查询中的应用。
(5)掌握SELECT语句在集合查询中的应用。
(6)主要掌握使用“查询分析器”进行查询。
三、实验环境
(1)PC机。
GROUPBYESSN
HAVINGCOUNT(*)=(SELECTCOUNT(*)FROM
PROJECTWHEREDnum=4));
Q24.查询至少参与了所有John Smith参与项目的员工姓名。(选做)
SQL文本:
SELECTFname,LnameFROMEMPLOYEE
WHERESsnIN(
SELECTEssnFROMWORKS_ON
Q14.查询在ProductX项目上每周工作时间超过10小时的部门5的员工姓名。(对于连接操作必须是主外键，但是对于where条件并没有什么特殊要求)
SQL文本:
SELECTE.Fname,E.Lname
FROMEMPLOYEEE
JOIN
WORKS_ONWON(W.Essn=E.Ssn)
JOIN
PROJECTPON(P.Pnumber=W.Pno)
4．
Q28.查询符合以下任意条件的项目编号：(1)参与此项目员工的LNAME是Smith；(2)控制此项目的部门经理的LNAME是Smith。
SQL文本:
SELECTPno
FROMWORKS_ON
JOINEMPLOYEEONEssn=Ssn
WHERELname='SMITH'
UNION
SELECTPnumber
GROUPBYDname
HAVINGCOUNT(*)>=2);
Q27.查询有两个或以上家属的员工姓名。
SQL文本:
SELECTFNAME,LNAME
FROMEMPLOYEE
WHERESsn=ANY
(SELECTESSN
FROMDEPENDENT
GROUPBYEssn
HAVINGCOUNT(*)>=2);
WHERESuper_ssnISNULL;
(2)基于WHERE子句进行数据查询。
1）基于比较条件。
Q4. 从Employee表中查询出工资大于等于40000的员工资料。
SQL文本:
SELECT*
FROMEMPLOYEE
WHERESalary>=40000;
Q5.检索姓名为John B.Smith的员工的SSN、性别、出生日期和地址。
WHEREE.Salary>=30000
----先执行WHERER在执行GROUP BY
GROUPBYD.Dname
HAVINGCOUNT(*)>=2;
3．嵌套查询
Q20.查询没有参与任何部门5控制项目的员工姓名。
SQL文本:
SELECTFname,Lname
FROMEMPLOYEE
WHERESsnNOTIN(
SQL文本:
SELECT*
FROMEMPLOYEE
WHERELnameLIKE'%O%';
Q9.检索居住在Houston的员工所有信息。
SQL文本:
SELECT*
FROMEMPLOYEE
WHEREAddressLIKE'%Houston%';
FROMEMPLOYEE
JOINDEPARTMENTONMgr_ssn=Ssn
JOINPROJECTONDnum=Dnumber
WHERELname='SMITH';
五、收获，体会及问题
(请详细书写，写得越详细、越个性化、越真实越好，否则我不知道你做这个实验的心路历程，也就无法充分地判断你是否是独立完成的这个实验、你是否在做这个实验时进行了认真仔细地思考、通过这个实验你是否在实践能力上得到了提高)
SQL文本:
SELECTE.Lname员工姓,E.Fname员工名,
EM.Lname上司姓,EM.Fname上司名
FROM
EMPLOYEEELEFT
JOIN
EMPLOYEEEMON(E.Super_ssn=EM.Ssn);
Q18.查询Company数据库中所有员工SSN与DNAME(部门名称)的组合。
Q1.从Employee表中检索出员工的FNAME、LNAME、SSN、BDATE、SALARY等字段，并分别加上“名”、“姓”、“社会保险号”、“生日”、“工资”的标题。员工的排序规则为：首先按工资的降序排列，然后按FNAME的字母升序排列。
SQL文本:
SELECTFname名,Lname姓,Ssn社会保险号,
FROMPROJECT));
Q23.查询至少参与了所有部门4控制项目的员工姓名。(选做)
SQL文本:
SELECTFname,Lname
FROMEMPLOYEE
WHERESsnIN(
SELECTEssn
FROMWORKS_ON
WHEREPnoIN(SELECTPnumberFROM
PROJECTWHEREDnum=4)
(3)使用计算列查询。
Q10.从Employee表中检索出员工的FNAME、LNAME、SSN、SALARY等字段(其中SALARY需换算成人民币，汇率假定为1美元=8人民币元)，并分别加上“名”、“姓”、“社会保险号”、“人民币工资”的标题。
SQL文本:
SELECTFname名,Lname姓,Ssn社会保险号,
SELECTSsnFROMEMPLOYEE
WHEREFname='JOHN'ANDLname='SMITH')))
ANDSsn!=(SELECTSsnFROMEMPLOYEE
WHEREFname='JOHN'ANDLname='SMITH');
Q25.查询至少有一个家属的部门经理姓名。(必须用EXISTS谓词)
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)
WHEREW.Hours>10ANDP.Pname='PRODUCTX'ANDDno=5;
Q15.对于每个项目，列出项目名称以及所有员工在此项目上工作的总时间。
SQL文本:
SELECTP.Pname,SUM(W.Hours)总时间
FROMWORKS_ONWJOIN
PROJECTPON(P.Pnumber=W.Pno)GROUPBYP.Pname;
WHEREPnoIN
(SELECTDISTINCTPno
FROMWORKS_ONWHEREEssnIN(
SELECTSsnFROMEMPLOYEE
WHEREFname='JOHN'ANDLname='SMITH'))
GROUPBYEssn
HAVINGCOUNT(*)=(SELECTCOUNT(*)
FROMWORKS_ONWHEREEssnIN(
西南石油大学实验报告
课程名称：数百度文库库原理
插入你的照片
实验项目名称：实验项目2 SQL数据操纵语言
专业年级班级、姓名、学号：软件13级2班
电子邮件地址：
实验所用机器名：
实验时间地点：2015.12.03明理楼
实验指导教师：孙瑜
成绩
批改人
批改日期
注意：在粘贴截图时请保留窗口完整标题，但只需保留关键界面，多余的空白界面请删除。
Bdate生日,Salary工资
FROMEMPLOYEE
ORDERBYSalaryDESC,Fname;
Q2.查询不重复的员工工资值。
SQL文本:
SELECTDISTINCTSalary
FROMEMPLOYEE;
Q3.查询没有直接上司的员工姓名。
SQL文本:
SELECTFname,Lname
FROMEMPLOYEE
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';
SQL文本:
SELECTFNAME,LNAME
FROMEMPLOYEE
WHEREEXISTS
(SELECT*FROMDEPARTMENT
WHEREMgr_ssn=SsnANDMgr_ssn=ANY
(SELECTDISTINCTESSNFROMDEPENDENT));
Q26.查询有两个或以上隶属员工的部门名称及其“工资大于等于30000员工”总数。(选做)
通过本次实验让我对SQl所有的查询语句都有所了解。弄明白了where和Having的执行顺序。以及Join连接必须有参照关系才能连接。在做查询没有参与任何部门5控制项目的员工姓名这道题时，自己就陷入错误，在何时取反很重要。查询至少参与了所有部门4控制项目的员工姓名。(选做)这道题时要利用至少参加所有的这个条件，并且在查找是否有员工参与所有项目时，最好使用count（*）来选择。要小心一些题目中的陷阱。
Q13.查询有两个或以上家属的员工姓名(此题较难，若不能完成者可只查询出员工的SSN而不是姓名)。
SQL文本:
SELECTE.Fname,E.Lname
FROMEMPLOYEEE
JOINDEPENDENTD
ON(D.Essn=E.Ssn)
GROUPBYD.Essn,E.Fname,E.Lname
HAVINGCOUNT(*)>=2;
Q16.对于每个部门，列出部门名称以及此部门员工的平均工资。
SQL文本:
SELECTD.Dname,AVG(E.Salary)AS平均工资
FROMEMPLOYEEEJOINDEPARTMENTD
OND.Dnumber=E.Dno
GROUPBYD.Dname;
Q17.对于每个员工(包括无上司员工)，查询其姓名以及他/她的直接上司的姓名。
SELECTEssn
FROMWORKS_ON
WHEREPnoIN(
SELECTPnumber
FROMPROJECT
WHEREDnum=5));
Q21.查询工资超过部门5所有员工工资的员工姓名。
SQL文本:
SELECTFNAME,LNAME
FROMEMPLOYEE
WHERESalary>ALL
(SELECTSalary
SQL文本:
SELECTSsn,Sex,Bdate,Address
FROMEMPLOYEE
WHEREFname='JOHN'ANDLname='SMITH'
ANDMinit='B';
2）基于BETWEEN子句的查询。
Q6. 从Employee表中查询出1960年——1970年之间出生的员工资料。
SQL文本:
FROMEMPLOYEE
WHEREDno=5);
Q22.查询参与了所有项目的员工姓名。(选做)
SQL文本:
SELECTFNAME,LNAME
FROMEMPLOYEE
WHERESSNIN
(SELECTEssn
FROMWORKS_ON
GROUPBYEssn
HAVINGCOUNT(Essn)=(
SELECTCOUNT(*)
Salary*8人民币工资
FROMEMPLOYEE;
2
Q11.查询所有为Research部门工作的员工姓名及地址。
SELECTFname,Lname,Address
FROMEMPLOYEEE
JOINDEPARTMENTD
ONE.Dno=D.Dnumber
WHEREDname='Research';
Q12.对于所有位于Stafford的项目，查询项目的编号、项目负责部门编号以及该部门经理的姓、地址、生日。
(2)SQL Server 2008。
四、实验内容及步骤
以下查询均使用实验项目1中创建的COMPANY数据库。
1．单表查询
(1)基本查询。
Q0.使用查询分析器从Employee表中检索出所有员工的姓名。
SQL文本：SELECT FNAME,LNAME FROM EMPLOYEE;
(以下所有题目都必须按照上图的要求截图)
SQL文本:
SELECTD.Dname,COUNT(*)总数
FROMEMPLOYEEE
JOINDEPARTMENTDONE.Dno=D.Dnumber
WHERESalary>=30000
GROUPBYD.Dname
HAVINGD.DnameIN(
SELECTD.Dname
FROMEMPLOYEEE
JOINDEPARTMENTDONE.Dno=D.Dnumber
SELECT*FROMEMPLOYEE
WHEREBdateBETWEEN'1960-01-01'
AND'1970-01-01';
3）基于IN子句的查询。
Q7.从Employee表中查询出部门号为4或者5的员工资料。
SQL文本:
SELECT*
FROMEMPLOYEE
WHEREDnoIN(4,5);
4）基于LIKE子句的查询。
一、实验课时：
二、实验目的
(1)理解查询的概念和方法。
(2)掌握SELECT语句在单表查询中的应用。
(3)掌握SELECT语句在多表连接查询中的应用。
(4)掌握SELECT语句在嵌套查询中的应用。
(5)掌握SELECT语句在集合查询中的应用。
(6)主要掌握使用“查询分析器”进行查询。
三、实验环境
(1)PC机。
GROUPBYESSN
HAVINGCOUNT(*)=(SELECTCOUNT(*)FROM
PROJECTWHEREDnum=4));
Q24.查询至少参与了所有John Smith参与项目的员工姓名。(选做)
SQL文本:
SELECTFname,LnameFROMEMPLOYEE
WHERESsnIN(
SELECTEssnFROMWORKS_ON
Q14.查询在ProductX项目上每周工作时间超过10小时的部门5的员工姓名。(对于连接操作必须是主外键，但是对于where条件并没有什么特殊要求)
SQL文本:
SELECTE.Fname,E.Lname
FROMEMPLOYEEE
JOIN
WORKS_ONWON(W.Essn=E.Ssn)
JOIN
PROJECTPON(P.Pnumber=W.Pno)
4．
Q28.查询符合以下任意条件的项目编号：(1)参与此项目员工的LNAME是Smith；(2)控制此项目的部门经理的LNAME是Smith。
SQL文本:
SELECTPno
FROMWORKS_ON
JOINEMPLOYEEONEssn=Ssn
WHERELname='SMITH'
UNION
SELECTPnumber
GROUPBYDname
HAVINGCOUNT(*)>=2);
Q27.查询有两个或以上家属的员工姓名。
SQL文本:
SELECTFNAME,LNAME
FROMEMPLOYEE
WHERESsn=ANY
(SELECTESSN
FROMDEPENDENT
GROUPBYEssn
HAVINGCOUNT(*)>=2);
WHERESuper_ssnISNULL;
(2)基于WHERE子句进行数据查询。
1）基于比较条件。
Q4. 从Employee表中查询出工资大于等于40000的员工资料。
SQL文本:
SELECT*
FROMEMPLOYEE
WHERESalary>=40000;
Q5.检索姓名为John B.Smith的员工的SSN、性别、出生日期和地址。
WHEREE.Salary>=30000
----先执行WHERER在执行GROUP BY
GROUPBYD.Dname
HAVINGCOUNT(*)>=2;
3．嵌套查询
Q20.查询没有参与任何部门5控制项目的员工姓名。
SQL文本:
SELECTFname,Lname
FROMEMPLOYEE
WHERESsnNOTIN(