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;
MYSQL 寒假自学 2022 十一(十)
于 2022-02-14 01:29:41 首次发布