第五阶段 -- 数据库：day21_07/03/19【多表查询练习】

本文链接：https://blog.csdn.net/weixin_42375099/article/details/94555167

多表查询练习

列出至少有四位员工的所有部门信息

-- 1.查询每个部门的人数
select deptno,count(*) from emp group by deptno
-- 2.至少有四位
select deptno,count(*) from emp group by deptno having count(*)>4
-- 3.查询部门的信息
select d.*,dd.cou
	from dept d, (select deptno,count(*) cou from emp group by deptno having count(*)>4) dd
	where d.deptno=dd,deptno

列出薪金比“SMITH”多的员工信息

-- 1.查询Smith的薪资
select sal from emp where enanme='smith'
-- 2.查询比Smith工资高的雇员信息
select * from emp
	where sla>(select sal from emp where enanme='smith')

列出所有员工的姓名及其直接上级的姓名。没有领导的雇员也列出

-- 自连接
select e.ename,m.ename
	from emp e left join emp m
	on e.mgr = m.empno

列出受雇日期早于其直接上级的所有员工的编号、姓名、部门名称

select e.empno,e.name
	from emp e join emp m on e.mgr=m.empno
	where e.hiredate<m.hiredate
-- 连接部门表查询部门名称
select e.empno,e.name,d.dname
	from emp e join emp m on e.mgr=m.empno
	join dept d on e.daptno=d.deptno
	where e.hiredate<m.hiredate

列出所有“CLERK”（办事员）的姓名及其部门名称、部门人数

-- 1.查询办事员姓名
select ename from emp where job='clerk'
-- 2.部门的人数
select count(*), deptno from emp group by deptno
-- 3.
select d.name,dd.cou,e.ename
	from dept d join (select count(*) cou, deptno from emp group by deptno) dd on d.deptno=dd.deptno
	join emp e on d.deptno=e.deptno
	whrere job='clerk'

列出最低薪金大于1500的各种工作及此从事此工作的全部雇员人数

-- 1.最低薪资大于1500的各种工作
select job from emp group by job having min(sal)>1500
-- 2.每个工作的雇员人数
select count(*) from emp group by job
-- 3.
select count(*),job from emp
	where job in (select job from emp group by job having min(sal)>1500)
	group by job

列出在部门“SALES”（销售部）工作的员工的姓名，假定不知道销售部的部门编号。

-- 1.部门是SLAE的部门号
select deptno from dept where dname='sales'
-- 2.查询子查询结果雇员的姓名
select enname
	from emp
	where deptno=(select deptno from dept where dname='sales')

列出薪金高于公司平均薪金的所有员工，所在部门、上级领导

-- 1.薪金高于公司平均薪金的所有员工
select e.*
	from emp e
	where sal>(select avg(sal) from emp)
-- 2.连接部门表
select e.*,d.*
	from emp e join dept d on e.deptno=d.deptno
	where sal>(select avg(sal) from emp)
-- 3.上级领导信息，要连接emp表，因为领导也是雇员
select e.*,d.*，m*
	from emp e join dept d on e.deptno=d.deptno
	join emp m on e.mgr=m.empno
	where e.sal>(select avg(sal) from emp)

列出与“SCOTT”从事相同工作的所有员工及部门名称

-- 1.SCOTT从事的工作
select job from emp where ename='scott'
-- 2.查询从事子查询Scott所有的员工
select e.*
	from emp e
	where job=(select job from emp where ename='scott') and e.ename!='scott'
-- 3.连接部门表查询部门名称
select e.*,d.dname
	from emp e join dept d on e.deptno=d.deptno
	where job=(select job from emp where ename='scott') and e.ename!='scott'

列出薪金高于在部门30工作的所有员工的薪金的员工姓名和薪金、部门

-- 1.部门30号的薪资
select sal from emp where deptno=30
-- 2.高于部门30工作的所以员工的薪资
select e.name,e.sal
	from emp e
	where e.sal > all(select sal from emp where deptno=30)
-- 3.连接部门表
select e.name,e.sal,d.dname
	from emp e join dept d on e.deptno=d.deptno
	where e.sal > all(select sal from emp where deptno=30)

列出在每个部门工作的员工数量、部门名称、平均工资和平均服务期限,

-- 1.平均服务天数--datediff()
select datediff(sysdate(),hiredate)/365 from emp
-- 2.部门表和雇员表连表查询
select avg(sal),d.dname,count(*),avg(datediff(sysdate(),hiredate)/365)
	from dept d join emp e on d.deptno=e.deptno
	group by d.dname

列出所有员工的年工资，按年薪从低到高排序

-- 1.员工的年薪
select (sal+ifnull(comm,0))*12 yearsal from emp
-- 2.年薪从低到高排序
select (sal+ifnull(comm,0))*12 yearsal from emp 
	order by yearsal

求出部门名称中，带‘s’字符的部门员工的工资合计、部门人数

-- 1.部门名称中，带‘s’字符的部门号
select deptno from dept where dname like '%s%'
-- 2.每个部门工资合计、部门人数
select sum(sal),count(*) from emp group by deptno
-- 3.方法1：WHERE
select sum(sal),count(*),deptno from emp 
	where deptno in (select deptno from dept where dname like '%s%')
	group by deptno
-- 方法2：HAVING