题目:已知公司的员工表EMP(EID, ENAME, BDATE, SEX, CITY),
部门表DEPT(DID, DNAME, DCITY),
工作表WORK(EID,DID,STARTDATE,SALARY)。各个字段说明如下:
EID——员工编号,最多6个字符。例如A00001(主键)
ENAME——员工姓名,最多10个字符。例如SMITH
BDATE——出生日期,日期型
SEX——员工性别,单个字符。F或者M
CITY——员工居住的城市,最多20个字符。例如:上海
DID——部门编号,最多3个字符。例如 A01 (主键)
DNAME——部门名称,最多20个字符。例如:研发部门
DCITY——部门所在的城市,最多20个字符。例如:上海
STARTDATE——员工到部门上班的日期,日期型
SALARY——员工的工资。整型。
查询拥有最多的员工的部门的基本信息(要求只取出一个部门的信息),如果有多个部门人数一样,那么取出部门编号最小的那个部门的基本信息。
部门表DEPT(DID, DNAME, DCITY),
工作表WORK(EID,DID,STARTDATE,SALARY)。各个字段说明如下:
EID——员工编号,最多6个字符。例如A00001(主键)
ENAME——员工姓名,最多10个字符。例如SMITH
BDATE——出生日期,日期型
SEX——员工性别,单个字符。F或者M
CITY——员工居住的城市,最多20个字符。例如:上海
DID——部门编号,最多3个字符。例如 A01 (主键)
DNAME——部门名称,最多20个字符。例如:研发部门
DCITY——部门所在的城市,最多20个字符。例如:上海
STARTDATE——员工到部门上班的日期,日期型
SALARY——员工的工资。整型。
查询拥有最多的员工的部门的基本信息(要求只取出一个部门的信息),如果有多个部门人数一样,那么取出部门编号最小的那个部门的基本信息。
select * from (
select * from (
select * from (
select count(*) as num from WORK group by DID
)order by DID asc
)order by num desc
) where rownum=1
from
(select min(x.DID)
(select * from
(select DID count(EID) ct from WORK w group by DID) a
where a.ct = (select max(count(EID)) from from WORK w group by DID)) x
group by x.ct) y inner join DEPT d on y.DID = d.DID
from dept,
(select did
from
(select count(eid) num,did
from work
group by did
order by num desc, did asc)
where rownum = 1) B
where dept.did = B.did
select top 1 * from(
select count(*) as num from work group by DID
) order by num desc
select * from(
select count(*) as num from work group by DID
) order by DID
) order by num desc
select *
from DEPT
where DID =
(
select top 1 DID from(
select * from(
select count(*) as num from work group by DID
) order by DID
) order by num desc
)
select *
from dept d
where d.did = (select y.did
from (select count(eid) num, did
from work
group by did
order by num desc, did) y
where rownum = 1);