MySQL sql语句练习笔记

/*1. 查出至少有一个员工的部门。显示部门编号、部门名称、部门位置、部门人数。*/
select d.deptno,d.dname,d.loc,a.count from dept d,
(select deptno,count(*) count from emp group by deptno) a where d.deptno = a.deptno;

/*2. 列出薪金比关羽高的所有员工。*/
select * from emp where sal > 
(select sal from emp where ename = '关羽');

select ename from emp;
/*3. 列出所有员工的姓名及其直接上级的姓名。*/
select e1.empno,e1.ename,e2.ename from emp e1 left join emp e2
on  e1.mgr = e2.empno

select b.empno,b.ename,b.mgr,a.ename 上级 from emp a,emp b where a.empno = b.mgr;
/*
select * from
(select b.ename,a.ename from emp a,emp b where a.empno = b.mgr and b.hiredate < a.hiredate);
*/
/*4. 列出受雇日期早于直接上级的所有员工的编号、姓名、部门名称。*/
select e1.empno,e1.ename,d.dname 
from emp e1 left join emp e2 
on e1.mgr = e2.empno
LEFT JOIN dept d 
ON e1.deptno = d.deptno
where     e1.hiredate < e2.hiredate;

select dname from dept;
/*5. 列出部门名称和这些部门的员工信息，同时列出那些没有员工的部门。*/
select e.*,d.dname 
from emp e 
right join dept d 
on e.deptno = d.deptno 
order by d.dname;

#/*6. 列出所有文员的姓名及其部门名称，部门的人数。*/  ×
select a.ename,d.dname,count from dept d,
(select deptno,count(*) count from emp group by deptno) b,
(select e.ename ename,e.deptno deptno from emp e where e.job = '文员') a 
where a.deptno = d.deptno and a.deptno = b.deptno;

/*7. 列出最低薪金大于15000的各种工作及从事此工作的员工人数。*/
select a.job,b.count,a.minsal from 
(select min(sal) minsal,job,deptno from emp group by job) a,
(select job,count(*) count from emp GROUP BY job) b
 where a.minsal > 15000 and a.job = b.job;

/*8. 列出在销售部工作的员工的姓名，假定不知道销售部的部门编号。*/
select ename from emp e left join dept d on e.deptno = d.deptno 
where d.dname = '销售部';

/*9. 列出薪金高于公司平均薪金的所有员工信息，所在部门名称，上级领导，工资等级。*/
/*select *,e2.empno from emp e1, emp e2,
(select avg(sal) avgsal from emp) a
 where e1.sal > a.avgsal and e1.mgr = e2.empno;

select e1.empno,e1.ename,e1.job,e1.mgr,e1.hiredate,e1.sal,e2.ename '上级' from emp e1,emp e2,
(select avg(sal) avgsal from emp) a
 where e1.sal > a.avgsal and e1.mgr = e2.empno*/

select dept.dname '部门名称',b.empno,b.ename,b.shangji '上级',b.job,b.mgr,b.hiredate,b.sal,b.grade '工资等级' from dept,
(select e1.empno,e1.ename,e1.job,e1.mgr,e1.hiredate,e1.sal,e2.ename shangji,e1.deptno,s.grade 
from (select avg(sal) avgsal from emp) a,emp e1
LEFT JOIN emp e2
on e1.mgr = e2.empno
 LEFT JOIN salgrade s
on e1.sal>=s.losal and e1.sal<=s.hisal
 where e1.sal > a.avgsal) b
where b.deptno = dept.deptno;

select e.sal,s.grade from salgrade s LEFT JOIN emp e
on e.sal>=s.losal and e.sal<=s.hisal;

select e1.empno,e1.ename,e1.job,e1.mgr,e1.hiredate,e1.sal,e1.deptno,s.grade '工资等级',e2.ename
from (select avg(sal) avgsal from emp) a,
emp e1
LEFT JOIN emp e2
on e1.mgr = e2.empno
LEFT JOIN salgrade s
on e1.sal>=s.losal and e1.sal<=s.hisal
 where e1.sal > a.avgsal

/*10.列出与庞统从事相同工作的所有员工及部门名称。*/
select empno,ename,a.deptno,dept.dname from emp e,dept,
(select job,deptno from emp where ename like '庞统') a
where e.job = a.job and a.deptno = dept.deptno;

/*11.列出薪金高于在部门30工作的所有员工的薪金的员工姓名和薪金、部门名称。*/
select e.ename,e.sal,e.deptno,d.dname from emp e,dept d,
(select max(sal) maxsal from emp where deptno = 30) a
where e.sal>a.maxsal and e.deptno = d.deptno;

/*12.列出在每个部门工作的员工数量、平均工资。*/
select e.deptno '部门编号',d.dname,count(*) '员工数量',round(avg(sal),2) '平均工资' 
    from emp e
    left join dept d
    on d.deptno = e.deptno
    where d.deptno is not null
    group by e.deptno;

/*9. 列出薪金高于公司平均薪金的所有员工信息，所在部门名称，上级领导，工资等级。*/
select * from emp e1 left join 
(select * from emp where sal > (select avg(sal) from emp)) e2
on e2.mgr = e1.mgr
;