第一步:创建dept 和emp,salgrade表。
– 1:查询雇员表中工资最高的雇员的员工号、员工姓名、工资和部门号。
select EMPNO,ENAME,SAL,DEPTNO from emp where SAL in(select MAX(SAL) from emp);
– 2:薪水大于1200的雇员,按照部门编号进行分组,分组后的平均薪水必须大于1500,查询各分组的平均工资,按照工资的倒序进行排列。
select DEPTNO,AVG(SAL) from emp where SAL>1200 group by DEPTNO having AVG(SAL)>1500 order by AVG(SAL) desc;
– 3:查询每个雇员和其所在的部门名
select ENAME,DEPTNO from emp;
– 4:查询每个雇员姓名及其工资所在的等级
select e.ENAME,e.SAL,s.GRADE from emp as e inner join salgrade s on e.SAL BETWEEN s.LOSAL and s.HISAL ORDER BY e.DEPTNO;
– 5:查询雇员名第2个字母不是a的雇员的姓名、所在的组名、工资所在的等级。
select ENAME,DNAME,GRADE
from emp inner join dept on dept.DEPTNO=emp.DEPTNO
inner join salgrade on SAL between LOSAL and HISAL
where ENAME not like ‘_A%’;
– 6:查询每个雇员和其领导的姓名
select e.ENAME as ENAME,ee.ENAME as MGRNAME from emp as e inner join emp as ee on e.MGR=ee.EMPNO;
– 7:查询每个雇员和其经理的姓名(包括公司老板本身(他上面没有经理))
select e1.ENAME empname,e2.ENAME mgrname from emp e1 left join emp e2 on e1.MGR=e2.EMPNO;
– 8:查询每个雇员的姓名及其所在部门的部门名(包括没有雇员的部门)
select e.ENAME,d.DNAME from dept as d left join emp as e on d.DEPTNO=e.DEPTNO;
– 9:查询每个部门中工资最高的人的姓名、薪水和部门编号
select ENAME,SAL,emp.DEPTNO from emp inner join
(select DEPTNO,max(SAL) msal from emp group by DEPTNO) maxsalresult
on maxsalresult.DEPTNO=emp.DEPTNO and emp.SAL=maxsalresult.msal;
– 10:查询每个部门平均工资所在的等级
select DEPTNO,GRADE, avgdept.avgsal as avg from salgrade inner join(select avg(SAL) as avgsal,DEPTNO from emp group by DEPTNO) as avgdept on avgsal between LOSAL and HISAL;
– 11:查询每个部门内平均的薪水等级
SELECT a.DEPTNO,AVG(a.GRADE) FROM
(SELECT s.GRADE,e.DEPTNO,e.ENAME FROM salgrade s INNER JOIN emp e ON e.SAL BETWEEN s.LOSAL AND s.HISAL) as a
GROUP BY a.DEPTNO;
– 12:查询雇员中有哪些人是经理人:
SELECT DISTINCT e1.EMPNO,e1.ENAME from emp e1 INNER JOIN emp e2 on e1.EMPNO=e2.MGR;
– 13:求雇员表中薪水的最高值。
SELECT MAX(SAL) FROM emp;
– 14:平均薪水最高的部门的部门编号
SELECT avgdept.DEPTNO,MAX(avgdept.avgsal) FROM (SELECT DEPTNO,AVG(SAL) avgsal FROM emp GROUP BY emp.DEPTNO) as avgdept;
– 15:求平均薪水最高的部门的部门名称
SELECT avgdept.DNAME,MAX(avgdept.avgsal) FROM (SELECT d.DNAME,AVG(e.SAL) avgsal FROM emp e INNER JOIN dept d ON e.DEPTNO=d.DEPTNO GROUP BY e.DEPTNO) as avgdept;