SQL

一、关系模式如下：

? 职工 Employees（ename， eno， bdate， addr， salary， dno）

姓名 工号 出生日期 家庭地址 工资 所在部门编号

? 部门 Departments（dname， dno， mgreno）

部门名称 部门编号 部门负责人的工号

? 项目 Projects（pname， pno， city， dno）

项目名称 项目编号 所在城市 主管部门编号? 工作 Works（eno， pno， hours）

职工工号 项目编号 工作时间

? 职工家属Depends（eno， name， sex）

职工工号 家属的姓名 家属的性别

请用SQL语言来表示下述数据查询操作。

1) 检索所有部门负责人的工号和姓名； （ 5分）

select E.eno, E.ename

from Employees E, Departments D where E.eno = D.mgreno

2) 检索职工Smith所参与的项目的编号和名称；（ 5分）

select P.pno, P.pname

from Employees E, Projects P, Works W

where E.ename = ‘Smith’ and E.eno = W.eno and

3) 检索参加过‘p2’号项目的职工的工号；（ 5分）

select eno from Works where pno = ‘p2’

4) 检索只参加过‘p2’号项目的职工的姓名；（ 6分）

select E.ename

P.pno = W.pno

from Employees E, Works W where E.eno = W.eno and

E.eno NOT IN ( select eno from Works where pno <> ‘p2’)

5) 检索只参加过一个项目的职工的姓名；（ 8分）

（答案1） select E.ename from Employees E,

(select eno, count(*) as p_num from Works group by eno) T where E.eno = T.eno and T.p_num = 1

（答案2） select E.ename

from Employees E, Works W1 where E.eno = W1.eno and

NOT EXISTS ( select *

from Works W2

where W2.eno=W1.eno and W2.pno<>W1.pno)

6) 检索参加了所有项目的职工的工号；（ 8分）

select eno

from Employees E Where NOT EXISTS (

Select *

from Projects P Where NOT EXISTS (

Select * from Works W

Where W.eno=E.eno and W.pno=P.pno ) )

7) 检索全体3号部门的职工都参加了的项目的编号和名称；（ 8分）

select pno, pname from Projects P Where NOT EXISTS (

Select *

from Employees E Where E.dno =‘3’ and

NOT EXISTS ( Select *

from Works W

Where W.eno=E.eno and W.pno=P.pno ) )

8) 检索工资收入最高的职工的姓名；（ 8分） （答案1）

Select ename from Employees

where salary IN (select max(salary) From Employees )

（答案2）

Select ename from Employees

where salary >= ALL (select salary From Employees )

9) 查询每一个部门中工资收入最高的职工，结果返回部门编号以及该部门中工资收入最高的职工

的工号。（ 8分） （答案1）

Select E1.dno, E1.eno from Employees E1

where E1.salary IN ( select max(E2.salary)

From Employees E2 where E2.dno = E1.dno )

（答案2）

Select E1.dno, E1.eno from Employees E1

where E1.salary >= ALL ( select E2.salary

From Employees E2 where E2.dno = E1.dno )

10) 按部门统计查询每一个部门的职工人数、工资总金额和平均工资金额。（ 7分）

select dno, count(*), sum(salary), avg(salary) from Employees group by dno

11) 检索每个部门的员工的平均工资，结果返回部门编号和该部门员工的平均工资，并按照平均工

资的降序排序输出查询结果。（ 8分） select dno, avg(salary) from Employees group by dno

order by avg(salary) DESC

12) 检索不带家属的职工的姓名；（ 8分） （答案1）

select ename from Employees

where eno NOT IN ( select eno from Depends ) （答案2）

select E.ename from Employees E

where NOT EXISTS ( select * from Depends D where D.eno=E.eno )

13) 检索只有两个家属的职工的姓名；（ 8分） （答案1） select E.ename from Employees E

where 2 = ALL ( select count(*) from Depends D where D.eno=E.eno)

（答案2）

select E.ename from Employees E,

(select eno, count(*) as d_num from Depends group by eno) D where E.eno = D.eno and D.d_num = 2

（答案3）

Select E.ename

from Employees E, Depends D1, Depends D2

Where E.eno = D1. eno and D1. eno = D2. eno and D1.name <> D2.name

and NOT EXISTS ( select *

from Depends D3

where D3.eno=E.eno and D3.name<>D1.name

and D3.name<>D2.name)

14) 检索家属人数大于或等于3个的职工的工号和姓名。（ 8分） （答案1）

select E.eno, E.ename from Employees E

where 3 <= ALL ( select count(*) from Depends D where D.eno=E.eno) （答案2）

select E.ename, E.ename from Employees E,

(select eno, count(*) as d_num from Depends group by eno) D where E.eno = D.eno and D.d_num >= 3

（答案3）

Select E.eno, E.ename

from Employees E, Depends D1, Depends D2, Depends D3 Where E.eno = D1. eno and E. eno = D2. eno and E.eno=D3.eno

and D1.name<>D2.name and D1.name<>D3.name and D2.name<>D3.name

（答案4）

select E.eno, E.ename

from Employees E, Depends D where E.eno = D. eno group by E.eno, E.ename having count(*) >= 3

本文来源：https://www.bwwdw.com/article/u2c5.html