sql题目（2）

emp

dept

salgrade

-- 01,列出至少有一个员工的所有部门。
select t2.DNAME,count(t1.DEPTNO) from emp t1 left join dept t2 on (t1.deptno=t2.deptno) group by t1.DEPTNO;
-- 02,列出薪金比“SMITH”多的所有员工。（大于最大薪水SMITH员工）
select * from emp where sal>=(select sal from emp where ename = 'smith');
-- 03,列出所有员工的姓名及其直接上级的姓名。
select t1.empno,t1.ename,t1.mgr,t2.ename from emp t1 join emp t2 on(t1.mgr=t2.empno);
-- 04,列出受雇日期早于其直接上级的所有员工。
select t1.ENAME,t1.HIREDATE,t2.ENAME as mgr_name,t2.HIREDATE from emp t1 join emp t2 on (t1.mgr=t2.empno) where t1.HIREDATE < t2.HIREDATE;
-- 05,列出部门名称和这些部门的员工信息，包括那些没有员工的部门。
select * from emp t1 right join dept t2 on (t1.DEPTNO=t2.DEPTNO);
-- 06,列出所有job为“CLERK”（办事员）的姓名及其部门名称。
select * from emp t1  join dept t2 on (t1.DEPTNO=t2.DEPTNO) where t1.job='CLERK';
-- 07,列出最低薪金大于1500的各种工作。
select t1.JOB from emp t1 join dept t2 on (t1.DEPTNO=t2.DEPTNO) where sal+ifnull(comm,0)>1500;
-- 08,列出在部门“SALES”（销售部）工作的员工的姓名，假定不知道销售部的部门编号。
select t1.ENAME,t2.DNAME from emp t1  join dept t2 on (t1.DEPTNO=t2.DEPTNO) where t2.DNAME='sales';
-- 09,列出薪金高于公司平均薪金的所有员工。
select ENAME,SAL from emp where sal>(select AVG(SAL) from emp);
-- 10,列出与“SCOTT”从事相同工作的所有员工。
select ENAME from emp where JOB=(select JOB from emp where ENAME='SCOTT');
-- 11,列出薪金等于部门30中员工的薪金的所有员工的姓名和薪金。
select ENAME,sal+ifnull(comm,0) from emp where sal+ifnull(comm,0) in (select sal+ifnull(comm,0) from emp where DEPTNO=30);
-- 12,列出薪金高于在部门30工作的所有员工的薪金的员工姓名和薪金。
select ENAME,SAL+IFNULL(comm,0) from emp where SAL+IFNULL(comm,0) > (select max(sal+ifnull(comm,0)) from emp where deptno = 30);
-- 13,列出在每个部门工作的员工数量、平均工资和平均服务期限。
select DEPTNO,count(ENAME),avg(sal),avg(hiredate) from emp group by deptno;
-- 14,列出所有员工的姓名、部门名称和工资。
select t1.ename,t1.sal,t2.dname from emp t1 join dept t2 on (t1.DEPTNO=t2.DEPTNO);
-- 15,列出从事同一种工作但属于不同部门的员工的一种组合。
select t1.ename,t2.ename,t1.job  from emp t1,emp t2 where t1.job= t2.job and t1.deptno<>t2.deptno;
-- 16,列出所有部门的详细信息和部门人数。
select t2.DEPTNO,count(t1.ename),t2.DNAME,t2.LOC from emp t1 right join dept t2 on (t1.DEPTNO=t2.DEPTNO) group by t2.deptno;
-- 17,列出各种工作的最低工资。
select job,min(sal) from emp  group by job; 
-- 18,列出各个部门的MANAGER（经理）的最低薪金（job为MANAGER）。
select min(sal),deptno from emp where job='MANAGER' group by deptno;
-- 19,列出所有员工的年工资，按年薪从低到高排序。
select ename,sal*12 from emp order by sal asc;
-- 20,列出所有job=‘CLERK’ 的员工平均薪资
select avg(sal),job from emp where job='CLERK';
-- 21,列出job=‘CLERK’员工的平均薪资 按照部门分组 
select avg(sal),deptno from emp group by deptno;
-- 22,列出job=‘CLERK’员工的平均薪资 按照部门分组 并且部门编号 in(10,30) 按照平均薪资 降序排列
select avg(sal),deptno from emp group by deptno having deptno in (10,30) order by avg(sal) desc;
-- 23,列出job=‘CLERK’员工的平均薪资 按照部门分组 并且部门编号 in(20,30) 并且部门员工数量>=2人 按照平均薪资 降序排列
select avg(sal),deptno,count(ename) from emp group by deptno having (deptno in (20,30) and count(ename)>=2) order by avg(sal) desc;