返回顶部

收藏

子查询以及sql练习

更多
--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 姓名, e.job 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.ename 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;

标签:sql

收藏

0人收藏

支持

0

反对

0

相关聚客文章
  1. 博主 发表 2018-07-12 00:00:00 T-SQL游标
  2. 博主 发表 2018-07-12 00:00:00 T-SQL笔记
  3. techug 发表 2018-07-11 06:22:56 译 | 如何更优雅的写出你的SQL语句
  4. darida 发表 2018-06-19 11:24:44 Vert.x-generator 2.0.0 发布,Vert.x 代码生成器
  5. darida 发表 2018-06-19 11:24:44 Vert.x-generator 2.0.0 发布,Vert.x 代码生成器
  6. yangtingkun 发表 2018-06-04 10:09:22 SQL解决刑侦推理问题
  7. kamus 发表 2018-05-24 17:15:37 All Secondary die, Primary hang? – SQL Server 2017
  8. admin 发表 2018-05-07 09:47:38 查看SQL执行计划的方法及优劣
  9. kamus 发表 2018-05-06 07:13:01 Configuring Always On Availability Groups in SQL S
  10. 博主 发表 2012-08-24 00:00:00 [转]MySQL数据库性能优化之SQL优化
  11. jqka2 发表 2018-04-13 00:56:09 PL/SQL Developer查询中文数据乱码问号?
  12. jqka2 发表 2018-04-12 09:23:00 PL/SQL Developer配置Oracle远程连接

发表评论