数据库实验2无错无漏

1、西南石油大学实验报告课程名称：数据库原理实验项目名称：实验项目2 SQL数据操纵语言专业年级班级、姓名、学号：软件13级4班、张璋、201305020420实验时间地点:2015. 11.20明理楼实验指导教师：刘小玲成绩批改人批改日期一、实验课时：4二、实验目的(1) 理解查询的概念和方法。(2) 掌握SELECT语句在单表查询中的应用。(3) 掌握SELECT语句在多表连接查询中的应用。(4) 掌握SELECT语句在嵌套查询中的应用。(5) 掌握SELECT语句在集合查询中的应用。(6) 主要掌握使用“查询分析器”进行查询。三、实验要求(1) 使用“查询分析器”进行查询。(2) 严格依照题

2、目要求编写SQL查询语句(鼓励同学们在按照题目要求完成 查询 的前提下尝试其他的查询方式)o(3) 为了今后可重用本实验项目中编写的 SQL语句，最好按实验题目编号将 SQL语句保存为.sql文件。四、实验环境(1) PC 机。(2) SQL Server 2000五、实验内容及步骤以下查询均使用实验项目1中创建的COMPANY据库。1 单表查询(1) 基本查询。Q0使用查询分析器从Employee表中检索出所有员工的姓名。 结果的消息FMAMELNAME1JohnSmith2FranklinWong3JoyceEnglish4Narapan5JEI蓟Borg6J ennifeiWalac?e

3、7AhmadJtibbar8AliciaSELECT FNAME, LNAME FROM EmployeeQ1 使用查询分析器从Employee表中检索出员工的FNAM、LNAME SSNBDATE SALARY等字段，并分别加上“名”、“姓”、“社会保险号”、“生日”、“工资”的标题。员工的排序规则为：首先按工资的降序排列，然后按FNAME的字 母升序排列。SELECT FNAME AS 名，LNAME AS 姓 “,SSN AS 社会保险号，BDATE AS 生日，SALARY AS 工资 “ FROM Employee ORDER BY SALARY desc , FNAME ascQ2

4、.查询不重复的员工工资值。SELECT DISTINCT SALARY AS 工资FROM EmployeeQ3.查询没有直接上司的员工姓名。SELECT FNAME, LNAME FROM Employee WHERE SUPERSSN IS NULL（2）基于WHERE子句进行数据查询。1）基于比较条件。Q4从Employee表中查询出工资大于等于40000的员工资料。SELECT * FROM Employee WHERE SALARY =400002）基于BETWEE子句的查询。Q5从Employee表中查询出1960年 1970年之间出生的员工资料。SELECT * FROM Emp

5、loyee WHERE BDATE BETWEEN I960-1-1 AND 1970-1-13）基于IN子句的查询。Q6.从Employee表中查询出部门号为4或者5的员工资料。SELECT * FROM Employee WHERE DNO IN ( 4, 5)4) 基于LIKE子句的查询。Q7.从Employee表中查询出LNAM中含有字母o的员工资料SELECT * FROM Employee WHERE LNAME LIKE %o%(3) 使用计算列查询。Q8.从Employee表中检索出员工的FNAMELNAMESSN SALARY等字段(其 中SALARY!换算成人民币，汇率假定

6、为1美元二8人民币元)，并分别加上“名”、“姓”、“社会保险号”、“人民币工资”的标题。SELECT FNAME AS 名 “ ,LNAME AS 姓 ，SSN AS 社会保险号，SALARY * 8 AS 人民币工资 “ FROM Employee2多表连接查询(使用JOIN)Q9.查询所有为Research部门工作的员工姓名及地址。SELECT FNAME, LNAME, ADDRESSFROM Employee JOIN Department ON DNO=DNUMBERWHERE DNAME=，ResearchQ10对于所有位于Stafford的项目，查询项目的编号、项目负责部门编号以

7、及该部门经理的姓、地址、生日。SELECT PLUMBER, DNUM FNAME, LNAME, ADDRESS, BDATEFROM ( Project JOIN DepartmentON DNUM二DNUMBEF)JOIN EmployeeON SSN=MGRSSNWHERE PLOCATION 二StaffordQll.查询有两个或以上家属的员工姓名(此题较难，若不能完成者可只查询出员工的SSN而不是姓名)。SELECT FNAME, LNAMEFROM Dependent JOIN EmployeeON ESSN=SSNGROUP BY ESSN, FNAME, LNAMEHAVIN

8、G COUNT(*)二 2Q12.查询在ProductX项目上每周工作时间超过10小时的部门5的员工姓名。SELECT DISTINCT FNAME, LNAMEFROM Employee JOIN(WORKS_ON JOIN Project ON PNAME= ProductX，AND PNO二PNUMBER)ON SSN=ESSNWHERE H0URS10Q13.对于每个项目，列出项目名称以及所有员工在此项目上工作的总时间。SELECT PNAME, SUM( HOURS) AS 总时间FROM Project JOIN WORKS_ON ON PNO=PNUMBER GROUP BY P

9、NAMEQ14.对于每个部门，列出部门名称以及此部门员工的平均工资。SELECT DNAME SUM( SALARY) AS 平均工资FROM Department JOIN Employee ON DNO二DNUMBER GROUP BY DNAME ORDER BY 平均工资Q15.对于每个员工，查询其姓名以及他/她的直接上司的姓名。SELECT E FNAME, E LNAME, M FNAME, M LNAMEFROM Employee AS E JOIN Employee AS M ON E. SUPERSSN=M SSNQ16.查询Company数据库屮所有员工SSN与DNAME(

10、部门名称)的组合。SELECT DISTINCT SSN , DNAME 部门名称FROM Employee JOIN DepartmentON DNO=DNUMBER*Q17查询有两个或以上“工资大于等于 30000员工”的部门名称SELECT DNAMEFROM Department WHERE DNUMBER IN(SELECT DUMBERFROM Department JOIN Employee ON D0二DNUMBERWHERE SALARY二 30000GROUP BY DNUMBERHAVING COUNT(*)= 2)3 嵌套查询(子查询)Q18 查询没有参与任何部门5控制

11、项目的员工姓名。SELECT DISTINCT FNAME, LNAMEFROM EmployeeWHERE SSN NOT IN(SELECT ESSN FROM WORKS_ON WHERE PNO IN(SELECT PNUMBER FROM Project WHERE DNUM=5)Q19.查询工资超过部门5所有员工工资的员工姓名。1. SELECT FNAME, LNAMEFROM EmployeeWHERE SALARY (SELECT TOP 1 SALARYFROM Employee , DepartmentWHERE DN0=5 ORDER BY SALARY DESC)2.

12、 SELECT FNAME, LNAMEFROM Employee WHERE SALARY (SELECT MAX( SALARY) FROM Employee WHERE DNO=5)Q20.查询参与了所有项目的员工姓名。SELECT FNAME, LNAMEFROM Employee WHERE SSN IN(SELECT ESSNFROM WORKS_ON JOIN ProjectON PNO二PNUMBERGROUP BY ESSNHAVING COUNT(*)=(SELECT COUNT(*) FROM Project )Q21.查询至少参与了所有部门4控制项目的员工姓名。SELE

13、CT FNAME, LNAMEFROM EmployeeWHERE SSN NOT IN(SELECT ESSN FROM WORKS_ON WHERE PNO NOT IN(SELECT PNUMBER FROM Project WHERE DNUM=4)Q22.查询至少参与了所有John Smith参与项目的员工姓名。SELECT FNAME , LNAME FROM EmployeeWHERE SSN NOT IN(SELECT ESSN FROM WORKS_ON WHERE PNO NOT IN(SELECT PNO FROM WORKS_ON WHERE ESSN IN(SELEC

14、T SSN FROM EmployeeWHERE FNAME二JohnAND LNAME二Smith)Q23.查询至少有一个家属的部门经理姓名。1. SELECT FNAME, LNAMEFROM Employee WHERE SSN IN(SELECT ESSNFROM DepartmentJOIN Dependent ON MGRSSN=ESSN)2. SELECT MGRSSNFROM DepartmentJOIN Dependent ON MGRSSN=ESSNGROUP BY MGRSSNHAVING COUNT (*)= 130000 员Q24.查询有两个或以上隶属员工的部门名称及

15、其“工资大于等于 工”总数。SELECT DNAMEFROM DepartmentJOIN EmployeeON DNO= DNUMBER AND SALARY 二 30000GROUP BY DNAMEHAVING COUNT(*)= 2Q25.查询有两个或以上家属的员工姓名。SELECT FNAME, LNAMEFROM Employee JOIN DependentON ESSN=SSNGROUP BY FNAME, LNAMEHAVING COUNT(*)= 24.集合查询Q26.查询符合以下任意条件的员工：(1) SUPERSSN为123456789的员工；SELECT FNAME

16、LNAMEFROM EmployeeWHERE SUPERSSN二123456789，(2) SUPERSSN 不为123456789,的员工。SELECT FNAME, LNAMEFROM EmployeeWHERE SUPERSSN NOT IN(SELECT SUPERSSNFROM EmployeeWHERE SUPERSSN二123456789)Q27查询符合以下任意条件的员工：(1) SSN为123456789的员工；SELECT FNAME LNAMEFROM EmployeeWHERE SSN二123456789(2) SSN 不为123456789,的员工。SELECT FNAME, LNAMEFROM EmployeeWHERE SSN NOT IN(SELECT SUPERSSNFROM EmployeeWHERE SSN二123456789)六、注意事项(1) 请在本地服务器中的数据库“ Company,f尔的拼音名字”中进行查询。(2) 如果由于题目翻译时的失误导致中英文题意不相符时，请以英文为准(中文翻译仅供参考)o七、考核要求(1) 熟练使用S