SQL语句练习

GROUP BY t.grade
);
1. 列出至少有一个员工的所有部门。
SELECT D.DEPTNO, D.DNAME,COUNT(E.DEPTNቤተ መጻሕፍቲ ባይዱ) FROM DEPT AS D INNER JOIN EMP AS E ON
D.DEPTNO= E.DEPTNO GROUP BY E.DEPTNO HAVING COUNT(E.DEPTNO)>=1; #1
WHERE E.JOB=(SELECT JOB FROM EMP WHERE ENAME ='SCOTT'); #10
SELECT ENAME,SAL FROM EMP WHERE SAL IN(SELECT SAL FROM EMP
WHERE DEPTNO = 30); #11
SELECT count(deptno), CEILING(avg(sal)),ceil(avg(hiredate)) from emp GROUP by deptno;
14. 列出所有员工的姓名、部门名称和工资。
select ename,dname,sal FROM dept as d inner join emp as e on d.deptno=e.deptno ;
SELECT E.ENAME,D.DNAME,E.SAL FROM DEPT AS D INNER JOIN EMP AS E
ON D.DEPTNO= E.DEPTNO; #14
SELECT clerk.empno,clerk.ename,clerk.job ,clerk.hiredate,mananger.hiredate from emp clerk ,emp mananger where clerk.mgr=mananger.empno && clerk.hiredate<mananger.hiredate;
SELECT EMPNO,ENAME,SAL*12 FROM EMP ORDER BY SAL; #18
SELECT ename,sal,dname FROM dept as d inner join emp as e on d.deptno=e.deptno where sal>(SELECT sum(sal) from emp where deptno=30 );
13.(f) 列出每个部门工作的员工的数量、平均工资和平均服务期限。
11. 列出薪金等于部门30中员工的薪金的所有员工的姓名和薪金。
SELECT sal,ename,deptno from emp where sal in (SELECT sal FROM emp where deptno=30);
12. 列出薪金高于在部门30工作的所有员工的薪金的员工和薪金、部门名称。
SELECT EMPNO, ENAME,JOB,MGR,HIREDATE,SAL,DEPTNO FROM EMP WHERE SAL > 800; #2
SELECT DNAME,EMPNO,ENAME,JOB,MGR,HIREDATE,SAL,COMM FROM DEPT AS D
SELECT E.ENAME,E.SAL,D.DNAME FROM DEPT AS D INNER JOIN EMP AS E
ON D.DEPTNO= E.DEPTNO WHERE E.SAL>(SELECT MAX(SAL) FROM EMP WHERE DEPTNO =30); #12
LEFT JOIN EMP AS E ON D.DEPTNO= E.DEPTNO; #5
2. 列出薪金比“SMITH”多的所有员工。
select ename ,sal from emp WHERE sal>(SELECT sal from emp where ename='SMITH');
5. 列出部门名称和这些部门的员工信息，同时列出那些没有员工的部门。
select dname,empno,ename,job,mgr,hiredate,sal,comm from dept as d left join emp as e on d.deptno=e.deptno ;
6. 列出所有“CLERK”（办事员）的姓名及其部门名称、部门的人数。
SELECT E.DEPTNO,DNAME,LOC,COUNT(E.DEPTNO) FROM DEPT AS D LEFT JOIN EMP AS E
ON D.DEPTNO= E.DEPTNO GROUP BY E.DEPTNO; #15
15. 列出所有部门的详细信息和部门人数。
select d.deptno,dname,count(e.deptno) FROM dept as d left join emp as e on d.deptno=e.deptno group by dname;
16. 列出各种工作的最低工资及从事此工作的雇员姓名。
3. 列出所有员工的姓名及其直接上级的姓名。
SELECT clerk.ename,mananger.empno,mananger.ename,clerk.mgr from emp clerk ,emp mananger where clerk.mgr=mananger.empno;
4.(f) 列出受雇日期早于其直接上级的所有员工的编号、姓名、部门名称。
SELECT COUNT(DEPTNO),'平均工资'(AVG(SAL)),'平均服务年限'(AVG(HIREDATE)) FROM EMP
GROUP BY DEPTNO; #13 报错！！！
7. 列出最低薪金大于1500的各种工作及从事此工作的全部雇员人数。
SELECT job,count(job) from emp where sal<2000 group by job ;
8. 列出在部门“SALES”（销售部）工作的员工的姓名。
select ename FROM dept as d inner join emp as e on d.deptno=e.deptno where dname='SALES';
SELECT JOB,COUNT(JOB) FROM EMP WHERE SAL > 1500 GROUP BY JOB; #7
SELECT E.ENAME FROM DEPT AS D INNER JOIN EMP AS E ON D.DEPTNO= E.DEPTNO
SELECT DNAME,EMPNO,ENAME,JOB,MGR,HIREDATE,SAL,COMM FROM DEPT AS D
LEFT JOIN EMP AS E ON D.DEPTNO= E.DEPTNO; #5
SELECT * FROM emp
WHERE sal IN(
SELECT max(sal)
FROM (SELECT e.empno no,e.sal sal,s.grade grade FROM emp e
LEFT JOIN salgrade s ON e.sal BETWEEN s.losal AND s.hisal) t
SELECT DNAME,ENAME,COUNT(E.DEPTNO) FROM DEPT AS D LEFT JOIN EMP AS E
ON D.DEPTNO= E.DEPTNO GROUP BY HAVING WHERE E.JOB='CLERK'; #6 报错！！！
9. 列出薪金高于公司平均薪金的所有员工，所在部门，上级领导，公司的工资等级。
10. 列出与“SCOTT”从事相同工作的所有员工及部门名称。
select ename,dname FROM dept as d inner join emp as e on d.deptno=e.deptno where job=(select job from emp where ename='scott');
select dname,avg(sal) FROM dept as d inner join emp as e on d.deptno=e.deptno group by dname;
SELECT D.DEPTNO, D.DNAME,COUNT(E.DEPTNO) FROM DEPT AS D INNER JOIN EMP AS E ON
select sum(sal) ,count(e.deptno) FROM dept as d left join emp as e on d.deptno=e.deptno where dname LIKE '%s%' group by e.deptno;
21. 求出部门平均月薪最高的部门名和平均月薪。
17. 列出各个部门的MANAGER(经理)的最低薪金。
18. 列出所有员工的年工资，按年薪从低到高排序。
select ename,sal*12 from emp order by sal;
19. 查出上级主管月薪超过3000的员工信息
20. 求出部门名称中带“S”字符的部门员工的工资合计、部门人数。
WHERE D.DNAME = 'SALES'; #8
SELECT E.ENAME,D.DNAME FROM DEPT AS D INNER JOIN EMP AS E ON D.DEPTNO= E.DEPTNO
D.DEPTNO= E.DEPTNO GROUP BY E.DEPTNO HAVING COUNT(E.DEPTNO)>=1; #1
SELECT EMPNO, ENAME,JOB,MGR,HIREDATE,SAL,DEPTNO FROM EMP WHERE SAL > 800; #2