34,575
社区成员
发帖
与我相关
我的任务
分享
SELECT E.*
FROM SELLS A
JOIN PRODUCT B ON A.PID=B.PID
JOIN DEPARTMENT C ON A.DID=C.DID
JOIN WORKSLN D ON C.DID=D.DID
JOIN EMPLOYEE E ON D.EID=E.EID
WHERE PCOLOUR='BLUE'
AND AGE<=40
--1 找出销售蓝色产品的部门的名称,这些部门没有超过40岁的员工。
select
d.dname
,e.ename
,e.age
from product as p inner join sells as s
on p.pid=s.pid and p.pcolour='蓝色'
inner join department as d
on d.did=s.did
inner join worksIn as w
on w.did=d.did
inner join employee as e
on w.eid=e.eid and e.age<=40
--2 找到每个部门的ID和在该部门工作的最年长的人的年龄。
select * from (
select
d.did
,d.dname
,e.employee
,e.age
,row_number() over(partition by d.did order by e.age desc) as rid
from
department as d
on d.did=s.did
inner join worksIn as w
on w.did=d.did
inner join employee as e
on w.eid=e.eid
) as tt
where rid=1
--3 找到在“central”部门工作的至少一名员工年龄更大的员工的姓名。
select top 5 *
from
department as d
on d.did=s.did and d.dname='central'
inner join worksIn as w
on w.did=d.did
inner join employee as e
order by e.age desc
--4 找出在没有员工超过40岁的部门工作的员工的名字。
select d.dname
,e.ename
,e.age
from
department as d
on d.did=s.did
inner join worksIn as w
on w.did=d.did
inner join employee as e
where e.age<=40
--5 找出在销售至少五种产品的部门工作的员工的名字。
select e.ename
from employee as e
inner join worksIn as w
where w.did in (
select
d.did
from product as p inner join sells as s
on p.pid=s.pid
inner join department as d
on d.did=s.did
group by d.did
havfing count(pid)>=5
)
--1 找出销售蓝色产品的部门的名称,这些部门没有超过40岁的员工。
select
d.dname
,e.ename
,e.age
from product as p inner join sells as s
on p.pid=s.pid
inner join department as d
on d.did=s.did
inner join worksIn as w
on w.did=d.did
inner join employee as e
on w.eid=e.eid and e.age>40
--2 找到每个部门的ID和在该部门工作的最年长的人的年龄。
select * from (
select
d.did
,d.dname
,e.employee
,e.age
,row_number() over(partition by d.did order by e.age desc) as rid
from
department as d
on d.did=s.did
inner join worksIn as w
on w.did=d.did
inner join employee as e
on w.eid=e.eid
) as tt
where rid=1
--3 找到在“central”部门工作的至少一名员工年龄更大的员工的姓名。
select top 5 *
from
department as d
on d.did=s.did and d.dname='central'
inner join worksIn as w
on w.did=d.did
inner join employee as e
order by e.age desc
--4 找出在没有员工超过40岁的部门工作的员工的名字。
select d.dname
,e.ename
,e.age
from
department as d
on d.did=s.did
inner join worksIn as w
on w.did=d.did
inner join employee as e
where e.age<=40
--5 找出在销售至少五种产品的部门工作的员工的名字。
select e.ename
from employee as e
inner join worksIn as w
where w.did in (
select
d.did
from product as p inner join sells as s
on p.pid=s.pid
inner join department as d
on d.did=s.did
group by d.did
havfing count(pid)>=5
)