MYSQL 寒假自学 2022 十一（十）

use test1;
-- 练习
-- 1，返回拥有员工的部门名，部门号
select distinct dname,dept.deptno from dept join emp on dept.deptno = emp.deptno;
 
-- 2,工资水平多于smith的员工信息
select * from emp where sal > (select sal from emp where ename = 'smith');
 
-- 3,返回员工和所属经理的名字
select a.ename,b.ename from emp a  join emp b on a.mgr = b.empno;
 
select a.ename,b.ename from emp a,emp b where a.mgr = b.empno; 
 
-- 4,返回雇员的雇佣日期早于其经理雇佣日期的员工及其经理姓名
select a.ename,a.hiredate,b.hiredate,b.ename from emp a  join emp b on a.mgr = b.empno and a.hiredate<b.hiredate;

-- 5、返回员工姓名及其所在部门的名称
select emp.ename,dept.dname from emp join dept on emp.deptno = dept.deptno;

-- 6,返回从事clerk工作的员工姓名和所在部门的名称
select emp.ename,dept.dname,emp.job from emp join dept on emp.deptno = dept.deptno and emp.job = 'clerk';

-- 7，返回部门号及其本部门的最低工资
select deptno,min(sal) from emp group by deptno;

-- 8,返回销售部（sales）所有员工的姓名
select ename from emp join dept on emp.deptno = dept.deptno and dept.dname = 'sales';

select ename from emp where emp.deptno=(select deptno from dept where dept.dname='sales');

-- 9,返回工资水平多于平均工资的员工
select * from emp where sal>(select avg(sal)from emp);
-- 10,返回与scott从事相同工作的员工
select * from emp where job = (select job from emp where ename = 'scott') and ename !='scott';

-- 11,返回工资高于30部门所有员工工资水平的员工信息
select * from emp where sal > all(select sal from emp where deptno = 30);

-- 12, 返回员工工作及其从事此工作的最低工资
select job,min(sal)from emp group by job;

-- 13,计算员工的年薪，并且以年薪排序
select ename,(sal*12+ifnull(comm,0)) as year_sal from emp order by year_sal desc; 

-- 14,返回工资处于第四级别的员工的姓名
select * from emp 
where sal between (select losal from salgrade where grand = 4) and (select hisal from salgrade where grand = 4);

-- 15,返回工资为二等级的职员名字，部门所在地
select 
*
from dept a
join emp b on a.deptno = b.deptno
join salgrade c on grand = 2 and b.sal>=c.losal and b.sal<=c.hisal;
--  ------------------------------
select 
*
from dept a,emp b,salgrade c
where a.deptno = b.deptno and grand = 2 and b.sal>=c.losal and b.sal<=c.hisal;