子查询以及sql练习

以下代码由PHP站长网 52php.cn收集自互联网

现在PHP站长网小编把它分享给大家，仅供参考

--1列出emp表中各部门的部门号，最高工资，最低工资
--先分组，后计算，

SELECT e.deptno AS 部门号,MAX(e.sal)  AS 最高工资,MIN(e.sal) AS 最低工资 FROM emp AS e GROUP BY e.deptno;

--2 列出emp表中各部门job为'CLERK'的员工的最低工资，最高工资

SELECT MIN(e.sal) AS 最低工资,MAX(e.sal) FROM emp AS e WHERE e.job='CLERK' GROUP BY e.deptno;

--3 对于emp中最低工资小于2000的部门，列出job为'CLERK'的员工的部门号，最低工资，最高工资

SELECT e.deptno AS 部门号,MIN(e.sal) AS 最低工资,MAX(e.sal) AS 最高工资 FROM emp AS e WHERE  e.job ='CLERK' AND (SELECT MIN(b.sal) FROM emp b) <2000 GROUP BY e.deptno;

--4 根据部门号由高而低，工资有低而高列出每个员工的姓名，部门号，工资

SELECT e.ename AS 姓名,e.deptno AS 部门号,e.sal AS 工资 FROM emp AS e ORDER BY e.deptno DESC,e.sal 

--5 列出'buddy'所在部门中每个员工的姓名与部门号

SELECT e.ename AS 姓名,e.deptno AS 部门号 FROM emp AS e WHERE e.deptno =(SELECT b.deptno  FROM  emp  AS b WHERE b.ename='buddy');

--6 列出每个员工的姓名，工作，部门号，部门名

SELECT e.ename 姓名,e.job AS 工作,e.deptno AS 部门号 FROM emp AS e,dept AS d WHERE e.deptno=d.deptno  ;

--7列出emp中工作为'CLERK'的员工的姓名，工作，部门号，部门名

SELECT e.ename AS 姓名,d.dname AS 部门名 FROM emp AS e,dept AS d WHERE e.deptno =d.deptno AND e.job='CLERK';

--8对于emp中有管理者的员工，列出姓名，管理者姓名（管理者外键为mgr）

SELECT a.deptno AS 部门号,a.ename AS 员工,b.ename AS 管理者 FROM emp AS a,emp AS b WHERE a.mgr IS NOT NULL AND a.mgr=b.ename;

--9 对于dept表中，列出所有部门名，部门号，同时列出各部门工作为'CLERK'的员工名与工作

SELECT d.dname AS 部门名,d.deptno AS 部门号,e.ename AS 姓名,e.job AS 工作  FROM  dept AS d,emp AS e WHERE e.deptno =d.deptno AND e.job='CLERK';

--10 对于工资高于本部门平均水平的员工，列出部门号，姓名，工资，按部门号排序

SELECT e.deptno AS 部门号,e.sal AS 工资 FROM emp AS e WHERE e.sal >(SELECT AVG(el.sal) FROM emp AS el WHERE el.deptno=e.deptno) ORDER BY e.deptno;

--11对于emp，列出各个部门中工资高于本部门平均工资的员工数和部门号，按部门号排序
 
SELECT COUNT(e.sal) AS 员工数,e.deptno AS 部门号 FROM emp AS e WHERE e.sal >(SELECT AVG(el.sal) FROM emp AS el WHERE e.deptno =el.deptno) GROUP BY e.deptno ORDER BY e.deptno;

--12对于emp中工资高于本部门平均水平，人数多与1人的，列出部门号，人数，平均工资，按部门号排序

SELECT COUNT(a.empno) AS 员工数,a.deptno AS 部门号,AVG(sal) AS 平均工资
FROM emp AS a WHERE (SELECT COUNT(c.empno) FROM emp AS c WHERE c.deptno=a.deptno AND
c.sal>(SELECT AVG(sal) FROM emp AS b WHERE c.deptno=b.deptno))>1
GROUP BY a.deptno ORDER BY a.deptno;

以上内容由PHP站长网【52php.cn】收集整理供大家参考研究

如果以上内容对您有帮助，欢迎收藏、点赞、推荐、分享。

（编辑：常州站长网）

【声明】本站内容均来自网络，其相关言论仅代表作者个人观点，不代表本站立场。若无意侵犯到您的权利，请及时与联系站长删除相关内容!