目录
--02列出薪资比‘SMITH’多的所有员工,这里用到了子查询(即子查询SMITH的工资)
--05列出部门名称和这些部门的员工信息,包括那些没有员工的部门
--08列出在SALES部门工作的员工姓名,假定不知道销售部的部门编号
--11列出薪资等于30部门员工薪资的所有员工的姓名和薪资,
--13列出在每个部门工作的员工数量、平均工资和平均服务期限
--22列出职位为clerk的平均工资,按部门进行分组 并且部门编号in(20,30) 且部门员工人数>=2 按照平均工资进行降序排列,
对象
本问题集基于表
dept表:
DNAME | LOC | DEPTNO |
ACCOUNTING | NEW YORK | 10 |
RESEARCH | DALLAS | 20 |
SALES | CHICAGO | 30 |
OPERATIONS | BOSTON | 40 |
emp表:
salgrade表:
问题集:
--01 列出至少有3个员工的所有部门名字
--分析:emp表中有部门的号码以及详细数据、dept表中有部门的号码以及名字,因此需要通过部门号码先将他们连接起来;然后需要将他们分好组;最后用count统计分好组的数据并与3比较。(注意:分组后用having进行组级过滤)
select d.dname from emp e join dept d on(e.deptno = d.deptno) group by d.dname having count(d.dname) >= 3;
--02列出薪资比‘SMITH’多的所有员工,这里用到了子查询(即子查询SMITH的工资)
select * from emp e where sal > (select sal from emp where ename = 'SMITH');
--03列出所有员工的姓名及其直接上级的姓名
select e.ename,m.ename from emp e,emp m where e.mgr = m.empno;
--04列出受雇日期早于其直接上级的员工
select e.ename 员工,m.ename 上级,e.hiredate 员工入职日期,m.hiredate 其上级入职日期 from emp e,emp m where e.mgr = m.empno and m.hiredate > e.hiredate;
--05列出部门名称和这些部门的员工信息,包括那些没有员工的部门
select d.dname,e.ename from emp e right join dept d using(deptno);
--06列出所有job为clerk的员工姓名及其部门名称
--using(deptno)和where(e.deptno=d.deptno)同义
select d.dname,e.ename from emp e join dept d using(deptno) where e.job = 'CLERK';
select d.dname,e.ename from emp e join dept d where(e.deptno=d.deptno) and e.job like 'CLERK';
--07列出最低薪资大于1500的各种工作
select e.job from emp e group by e.job having min(sal) > 1500;
--08列出在SALES部门工作的员工姓名,假定不知道销售部的部门编号
select e.ename from emp e join dept d using(deptno) where d.dname = 'sales';
--09列出薪资高于公司平均薪资的所有员工
select * from emp e where sal > (select avg(sal) from emp);
--10列出与员工'SCOTT'从事相同工作的人
select * from emp e where job = (select job from emp where ename = 'scott') and ename<>'scott';
--11列出薪资等于30部门员工薪资的所有员工的姓名和薪资,
当有多个目标时不可以只用“=”,可用“= any”后者“in”
select * from emp where sal = any(select sal from emp where deptno = 30) and deptno <> 30;
select * from emp where sal in (select sal from emp where deptno = 30) and deptno <> 30;
--12列出薪资比30部门所有员工的薪资都高的员工及其薪资
select * from emp where sal > all(select sal from emp where deptno = 30) and deptno <> 30;
--13列出在每个部门工作的员工数量、平均工资和平均服务期限
select deptno,count(ename),avg(sal),avg(TO_DAYS(now()-hiredate)) from emp group by deptno;
--这里其实缺少没有员工的40部门,为统计到40部门,故连接dept表
select deptno,count(ename),avg(sal),avg(TO_DAYS(now()-hiredate)) from emp right join dept d using(deptno) group by deptno;
--14列出所有员工的姓名、部门名称和工资
select ename 姓名,deptno 部门名称,sal 工资 from emp right join dept using(deptno);
--15列出从事同一种工作但属于不同部门的员工的一种组合,
select e.empno,e.ename,m.empno,m.ename from emp e ,emp m where e.job = m.job and e.deptno<>m.deptno;
这种情况下其实还有重复计算的,比如miller&smith以及smith&miller两种形式都被统计到了,避免这种情况只需要将e.deptno<>m.deptno改为e.deptno > m.deptno或e.deptno < m.deptno 就好了,这样就避免了重复统计的问题
select e.empno,e.ename,m.empno,m.ename from emp e ,emp m where e.job = m.job and e.deptno<m.deptno;
--16列出所有部门的详细信息
select * from emp e join dept d using(deptno) group by d.dname;
--17列出各种工作的最低工资
select job,min(sal) 最低工资 from emp group by deptno;
--18列出各部门的经理的最低薪资
select min(sal) from emp where job = 'manager' group by deptno;
--19列出所有员工的年薪(含津贴),按年薪从低到高排列,
排序(order by sal/order by sal desc),
为避免comm为空的情况,可加一个ifnull(comm,0),
ifnull(a,b)用法:不为空返回a,为空返回b
这里2代指(sal+ifnull(comm,0))*12;若1就指ename
select ename,(sal+ifnull(comm,0))*12 from emp e order by 2;
--20列出职位为clerk的员工平均工资
select avg(sal) from emp e where e.job = 'clerk';
--21列出职位为clerk的平均工资,按部门进行分组
select deptno,avg(sal) from emp e where e.job = 'clerk' group by deptno;
--22列出职位为clerk的平均工资,按部门进行分组 并且部门编号in(20,30) 且部门员工人数>=2 按照平均工资进行降序排列,
in(10,30)指得是只有10、30
select deptno,avg(sal) from emp e where e.job = 'clerk' and deptno in (20,30) group by deptno having count(empno) >=2 order by avg(sal) desc;