SWPU2019数据库实验二
SWPU2019数据库实验二

SWPU2019数据库实验二

针对于2021.5.17日晚的sqlserver数据库实验二的记录,仅供参考,如有抄袭行为与我无关

把USE Company_huanghe改成自己的就可以了。也可以不要这一句,对本次实验不影响。

另外提示一下,语句一定要自己去跑一下,理解一下。千万不要拿原图!图片是唯一的,数据库名是唯一的,直接拿原图的话,会出问题的。

1.单表查询

(1)基本查询。

Q0. 使用查询分析器从Employee表中检索出所有员工的姓名。

代码/截图:

USE Company_huanghe

select LNAME+FNAME as NAME from Employee

Q0

 

Q1. 使用查询分析器从Employee表中检索出员工的FNAME、LNAME、SSN、BDATE、SALARY等字段,并分别加上“名”、“姓”、“社会保险号”、“生日”、“工资”的标题。员工的排序规则为:首先按工资的降序排列,然后按FNAME的字母升序排列。

代码/截图:

USE Company_huanghe

select FNAME AS 名,lname as 姓,SSN as 社会保险号,BDATE as 生日,SALARY as 工资 from Employee

order by SALARY DESC,FNAME ASC

Q1

 

Q2. Retrieve all distinct salary values.

查询不重复的员工工资值。

代码/截图:

USE Company_huanghe

select DISTINCT SALARY from Employee

Q2

 

Q3. Retrieve the names of all employees who do not have supervisors.

查询没有直接上司的员工姓名。

代码/截图:

USE Company_huanghe

select LNAME+FNAME as NAME from Employee

Where superssn is null

Q3

 

 

(2) 基于WHERE子句进行数据查询。

1)基于比较条件。

Q4. 从Employee表中查询出工资大于等于40000的员工资料。

代码/截图:

USE Company_huanghe

select * FROM Employee

WHERE SALARY>=40000

Q4

 

2)基于BETWEEN子句的查询。

Q5. 从Employee表中查询出1960年——1970年之间出生的员工资料。

代码/截图:

USE Company_huanghe

SELECT*FROM EMPLOYEE

WHERE Bdate BETWEEN'1960-01-01'AND'1970-01-01';

Q5

 

3)基于IN子句的查询。

Q6. 从Employee表中查询出部门号为4或者5的员工资料。

代码/截图:

USE Company_huanghe

select * FROM Employee

WHERE DNO IN('4' ,'5')

Q6

 

4)基于LIKE子句的查询。

Q7. 从Employee表中查询出LNAME中含有字母o的员工资料。

代码/截图:

USE Company_huanghe

select * FROM Employee

WHERE LNAME LIKE '%O%'

Q7

 

 

(3) 使用计算列查询。

Q8. 从Employee表中检索出员工的FNAME、LNAME、SSN、SALARY等字段(其中SALARY需换算成人民币,汇率假定为1美元=8人民币元),并分别加上“名”、“姓”、“社会保险号”、“人民币工资”的标题。

代码/截图:

USE Company_huanghe

select FNAME AS 名,lname as 姓,SSN as 社会保险号,SALARY *8 as 人民币工资 from Employee

Q8

 

2. 多表连接查询(使用JOIN)

Q9. Retrieve the name and address of all employees who work for the ‘Research’ department.

查询所有为Research部门工作的员工姓名及地址。

代码/截图:

USE Company_huanghe

SELECT ADDRESS,FNAME+LNAME AS NAME

FROM Department INNER JOIN Employee on Department.DNUMBER=Employee.DNO

where DNUMBER=5

Q9

 

Q10. For every project located in ‘Stafford’, list the project number, the controlling department number, and the department manager’s last name, address, and birthdate.

对于所有位于Stafford的项目,查询项目的编号、项目负责部门编号以及该部门经理的姓、地址、生日。

代码/截图:

USE Company_huanghe

SELECT PNUMBER,DNUM,LNAME,ADDRESS,BDATE

FROM EMPLOYEE JOIN PROJECT ON (EMPLOYEE.DNO=PROJECT.DNUM)

JOIN DEPT_LOCATIONS ON (PROJECT.DNUM=DEPT_LOCATIONS.DNUMBER)

WHERE DLOCATION='Stafford';

Q10

 

Q11. List the names of all employees with two or more dependents.

查询有两个或以上家属的员工姓名(此题较难,若不能完成者可只查询出员工的SSN而不是姓名)。

代码/截图:

USE Company_huanghe

SELECT SSN FROM EMPLOYEE

JOIN DEPENDENT ON(EMPLOYEE.SSN=DEPENDENT.ESSN)

GROUP BY SSN

HAVING COUNT(SSN)>2;

Q11

[reply]
Q12. Retrieve the names of all employees in department 5 who work more than 10 hours per week on the ‘ProductX’ project.

查询在ProductX项目上每周工作时间超过10小时的部门5的员工姓名。

代码/截图:

USE Company_huanghe

SELECT FNAME,LNAME FROM EMPLOYEE

JOIN WORKS_ON ON(EMPLOYEE.SSN=WORKS_ON.ESSN)

JOIN PROJECT ON (WORKS_ON.PNO=PROJECT.PNUMBER)

WHERE PROJECT.PNAME='PRODUCTX'

Q12

 

Q13. For each project, list the project name and the total hours per week (by all employees) spent on that project.

对于每个项目,列出项目名称以及所有员工在此项目上工作的总时间。

代码/截图:

USE Company_huanghe

SELECT PNAME,SUM(HOURS)AS 总时间 FROM WORKS_ON

JOIN PROJECT ON (WORKS_ON.PNO=PROJECT.PNUMBER)

GROUP BY PNAME;

Q13

 

Q14. For each department, retrieve the department name and the average salary of all employees working in that department.

对于每个部门,列出部门名称以及此部门员工的平均工资。

代码/截图:

USE Company_huanghe

SELECT DNAME,AVG(SALARY)as 平均工资 FROM EMPLOYEE

JOIN DEPARTMENT ON (EMPLOYEE.DNO=DEPARTMENT.DNUMBER)

GROUP BY DNAME;

Q14

 

Q15. For each employee, retrieve the employee’s first and last name and the first and last name of his or her immediate supervisor.

对于每个员工,查询其姓名以及他/她的直接上司的姓名。

代码/截图:

SELECT YG.FNAME+YG.LNAME as 员工姓名,SS.FNAME+SS.LNAME as 上司姓名

FROM EMPLOYEE YG LEFT JOIN EMPLOYEE SS ON YG.SUPERSSN=SS.SSN

Q15

 

Q16. Select all combinations of EMPLOYEE SSN and DEPARTMENT DNAME in the Company database.

查询Company数据库中所有员工SSN与DNAME(部门名称)的组合。

代码/截图:

USE Company_huanghe

SELECT SSN,DNAME FROM EMPLOYEE

JOIN DEPARTMENT ON (EMPLOYEE.DNO=DEPARTMENT.DNUMBER)

Q16

 

Q17. 查询有两个或以上“工资大于等于30000员工”的部门名称。

代码/截图:

SELECT DNAME

FROM EMPLOYEE JOIN DEPARTMENT ON DNO=DNUMBER

WHERE SALARY>=30000

GROUP BY DNO,DNAME

HAVING COUNT(*)>=2

Q17

 

3.嵌套查询(子查询)

Q18. Retrieve the names of all employees who do not work on any project controlled by department number 5.

查询没有参与任何部门5控制项目的员工姓名。

代码/截图:

USE Company_huanghe

SELECT LNAME+FNAME AS NAME FROM EMPLOYEE

where DNO<>ALL

(SELECT DNUMBER FROM DEPARTMENT WHERE DNUMBER=5)

Q18

 

Q19. Retrieve the names of employees whose salary is greater than the salary of all the employees in department 5.

查询工资超过部门5所有员工工资的员工姓名。

代码/截图:

USE Company_huanghe

SELECT LNAME+FNAME AS NAME FROM EMPLOYEE

where SALARY>ALL

(SELECT SALARY FROM EMPLOYEE WHERE DNO=5)

Q19

Q20. Retrieve the names of all employees who work on every project.

查询参与了所有项目的员工姓名。(这里不是做错了,是没有参与所有项目的员工,所以才是空)

代码/截图:

USE Company_huanghe

select FNAME+LNAME AS NAME FROM Employee

WHERE SSN IN

(SELECT ESSN FROM WORKS_ON GROUP BY ESSN

HAVING COUNT(ESSN)=(SELECT COUNT(*) FROM Project))

Q20

 

Q21. Find the names of employees who work on all the projects controlled by department number 4.

查询至少参与了所有部门4控制项目的员工姓名。

代码/截图:

USE Company_huanghe

select FNAME+LNAME AS NAME from Employee where SSN in(

select distinct essn from Works_On where PNO

in(select PNUMBER from Project where DNUM=4))

Q21

 

Q22. Retrieve the names of employees who work on all the projects that ‘John Smith’ works on.

查询至少参与了所有John Smith参与项目的员工姓名。

代码/截图:

USE Company_huanghe

select FNAME+LNAME AS name from Employee where SSN in(

select distinct ESSN from Works_On where PNO in(

select pno from Works_On where ESSN=(

select SSN from Employee where FNAME='John' and LNAME='Smith'))

and ESSN<>(select SSN from Employee where FNAME='John' and LNAME='Smith'))

Q22

 

Q23. List the names of managers who have at least one dependent.

查询至少有一个家属的部门经理姓名。

代码/截图:

USE Company_huanghe

SELECT FNAME,LNAME FROM EMPLOYEE WHERE SSN IN

(SELECT MGRSSN FROM DEPARTMENT WHERE MGRSSN IN

(SELECT ESSN FROM DEPENDENT))

Q23

 

Q24. 查询有两个或以上隶属员工的部门名称及其“工资大于等于30000员工”总数。

代码/截图:

USE Company_huanghe

select Department.DNAME ,count(*) from Employee 

join Department on Employee.DNO=Department.DNUMBER

where SALARY>=30000 

group by Department.DNAME

 

Q24

 

Q25. List the names of all employees with two or more dependents.

查询有两个或以上家属的员工姓名。

代码/截图:

USE Company_huanghe

SELECT LNAME+FNAME as 姓名 FROM EMPLOYEE

WHERE SSN IN

(SELECT ESSN FROM DEPENDENT GROUP BY ESSN HAVING

COUNT(*)>=2)

Q25

 

 

4.集合查询

Q26. Make a list of project numbers for projects that involve an employee whose last name is ‘Smith’, either as a worker or as a manager of the department that controls the project.

查询符合以下任意条件的项目编号:(1)参与此项目员工的LNAME是Smith;(2)控制此项目的部门经理的LNAME是Smith。

代码/截图:

USE Company_huanghe

SELECT PNUMBER FROM PROJECT

JOIN EMPLOYEE ON (PROJECT.DNUM=EMPLOYEE.DNO)

WHERE LNAME='SMITH'

UNION

SELECT SSN FROM EMPLOYEE

WHERE LNAME='SMITH'AND SSN IN

(SELECT MGRSSN FROM PROJECT

JOIN DEPARTMENT ON (PROJECT.DNUM=DEPARTMENT.DNUMBER))

Q26

[/reply]
演示到此结束。

5 1 投票
Article Rating
订阅
提醒
4 评论
最新评论
最久评论 最受欢迎的评论
内联反馈
查看所有评论
4
0
有什么想说的吗?评论几句?x
()
x