-- 1.返回拥有员工的部门名、部门号select a.ename,b.dname,b.deptno from emp as a,dept as b where a.deptno = b.deptno;selectDISTINCT a.ename,b.dname,b.deptno FROM emp as a INNERJOIN dept as b ON a.deptno = b.deptno;-- 2.工资水平多余smith的员工信息select*from emp where sal >(SELECT sal FROM emp WHERE ename ='smith');select*from emp where sal >(SELECT sal FROM emp WHERE ename ='smith');-- 3.返回员工和所属经理的姓名SELECT a.ename,b.ename FROM emp as a,emp as b where a.mgr = b.empno;-- 4.返回员工的雇佣日期早于其经理雇佣日期的员工及其经理姓名SELECT a.ename,a.hiredate,b.ename,b.hiredate FROM emp as a,emp as b where a.mgr = b.empno AND a.hiredate < b.hiredate;-- 5.返回员工姓名及其所在的部门名称。select a.ename,b.dname from emp as a innerjoin dept as b on a.deptno = b.deptno;-- 6.返回从事clerk工作的员工姓名和所在部门名称。select a.ename,b.dname,a.job from emp as a innerjoin dept as b on a.deptno = b.deptno and a.job ='clerk';-- 7.返回部门号及其本部门的最低工资select b.deptno,min(a.sal)from emp as a innerjoin dept as b on a.deptno = b.deptno groupby b.deptno;-- 8.返回销售部(sales)所有员工的姓名select a.ename from emp as a innerjoin dept as b on a.deptno = b.deptno and b.dname ='sales';-- 9.返回工资水平多于平均工资的员工select*from emp where sal >(selectavg(sal)from emp);-- 10.返回与scott从事相同工作的员工select*from emp where job =(select job from emp where ename ='scott');-- 11.返回工资高于30部门所有员工工作水平的员工select*from emp where sal >(selectmax(sal)from emp where deptno =30);-- 或者select*from emp where sal >all(select sal from emp where deptno =30);-- 12.返回员工工作及其从事此工作的最低工资selectmin(sal),job from emp groupby job asc;-- 13.计算员工年薪,并以年薪排序select12*sal + ifnull(comn,0)as salary from emp groupby salary;select ename,12*sal + ifnull(comn,0)as salary_year from emp orderby salary_year desc;-- 14.返回工资处于第四级的员工姓名。select*from emp where sal between(select loasal from salgrade where grade =4)and(select hisal from salgrade where grade =4);-- 15.返回工资为2等级的职员名字、部门所在地select*from dept as a
join emp as b on a.deptno = b.deptno
join salgrade as c on grade =2and b.sal >= c.loasal and b.sal <= c.hisal;