SQL整理一

部门信息：dept
+------+-----------+------------+
|deptno| deptname  |deptlocation|  
+------+-----------+------------+
|  1   |designing  | workin121  |
+------+-----------+------------+
|  2   | coding    | workin123  | 
+------+-----------+------------+
|  3   | testing   | workin125  |  
+------+-----------+------------+
|  4   | manager   | workin127  |
+------+-----------+------------+
|  5   |temp       | nolocation |
+------+-----------+------------+
员工信息：emp
+-----+-------+---------+--------+----------+----------+
|empno|deptno | empname | empjob | empsalary|empmanager| 
+-----+-------+---------+--------+----------+----------+
|  1  |   2   |  zhang  |  code  | 3000     |    5     |
+-----+-------+---------+--------+----------+----------+
|  2  |   2   |  wang   | manager| 4000     |    1     |
+-----+-------+---------+--------+----------+----------+
|  3  |   1   |  li     |  code  | 3600     |    1     |
+-----+-------+---------+--------+----------+----------+
|  4  |   5   |  zhao   |  test  | 2556     |    2     |
+-----+-------+---------+--------+----------+----------+
|  5  |   1   |  wu     |  code  | 6500     |   15     |
+-----+-------+---------+--------+----------+----------+
|  6  |   5   |  guo    |  code  | 5653     |   3      |
+-----+-------+---------+--------+----------+----------+
|  7  |   1   |  wang   |  study | 1565     |   4      |
+-----+-------+---------+--------+----------+----------+
|  8  |   1   |  sun    |  code  | 9999.99  |   15     |
+-----+-------+---------+--------+----------+----------+
|  9  |   2   |  feng   |  study |  1999    |   12     |
+-----+-------+---------+--------+----------+----------+
|  10 |   1   |   ma    |  code  |  2343    |   3      |
+-----+-------+---------+--------+----------+----------+
|  11 |   3   |   tu    |  code  |  9999.99 |   16     |
+-----+-------+---------+--------+----------+----------+
|  12 |   1   |   xin   |  code  |  7900    |   6 	|
+-----+-------+---------+--------+----------+----------+
|  13 |   1   |   si    |  code  |  4546    |   2      |
+-----+-------+---------+--------+----------+----------+
|  14 |   1   |   fu    |  test  |  2464    |   15     |
+-----+-------+---------+--------+----------+----------+
|  15 |   3   |   hao   | manager|  9999.99 |  NULL    |
+-----+-------+---------+--------+----------+----------+
|  16 |    4  |   liu   |  test  |  3521    |    15    |
+-----+-------+---------+--------+----------+----------+
SQL代码：
+----------------------------------------------------------------+
drop table if exists dept; 			
drop table if exists emp;   

create table dept   
(   
   deptno               int not null,   
   deptname             char(20),   
   deptlocation         char(50),   
   primary key (deptno)   
);   

create table emp   
(   
   empno                int not null,   
   deptno               int,   
   empname              char(20),   
   empjob               char(20),   
   empsalary            float,   
   empmanager           int,   
   primary key (empno)   
);   

alter table emp add constraint FK_deptincludeemp foreign key (deptno)   
 references dept (deptno) on delete restrict on update restrict;   

INSERT INTO `dept` VALUES (1,'designing','workin121');   
INSERT INTO `dept` VALUES (2,'coding','workin123');   
INSERT INTO `dept` VALUES (3,'testing','workin125');   
INSERT INTO `dept` VALUES (4,'manager','workin127');   
INSERT INTO `dept` VALUES (5,'temp','nolocation');   

INSERT INTO `emp` VALUES (1,2,'zhang','code',3000,15);   
INSERT INTO `emp` VALUES (2,2,'wang','manager',4000,1);   
INSERT INTO `emp` VALUES (3,1,'li','code',3600,1);   
INSERT INTO `emp` VALUES (4,5,'zhao','test',2556,2);   
INSERT INTO `emp` VALUES (5,1,'wu','code',6500,15);   
INSERT INTO `emp` VALUES (6,5,'guo','code',5653,3);   
INSERT INTO `emp` VALUES (7,1,'wang','study',1565,4);   
INSERT INTO `emp` VALUES (8,1,'sun','code',9999.99,15);   
INSERT INTO `emp` VALUES (9,2,'feng','study',1999,12);   
INSERT INTO `emp` VALUES (10,1,'ma','code',2343,3);   
INSERT INTO `emp` VALUES (11,3,'tu','code',9999.99,16);   
INSERT INTO `emp` VALUES (12,1,'xin','code',7900,6);   
INSERT INTO `emp` VALUES (13,1,'si','code',4546,2);   
INSERT INTO `emp` VALUES (14,1,'fu','test',2464,15);   
INSERT INTO `emp` VALUES (15,3,'hao','manager',9999.99,NULL);   
INSERT INTO `emp` VALUES (16,4,'liu','test',3521,15);  
+----------------------------------------------------------+
问题：
1.列出emp表中各部门的部门号, 部门，最高工资，最低工资
+-------------------------------------------------------------+
|SELECT b.deptno, a.deptname, min( b.empsalary ) AS minsalary,|
|max( b.empsalary ) AS maxsalary                              |
|FROM emp b, dept a                                           |
|WHERE b.deptno = a.deptno                                    |
|GROUP BY b.deptno                                            |	
+-------------------------------------------------------------+
2.列出emp表中各部门job为'code'的员工的最低工资，最高工资
+--------------------------------------------------------------------+
|select a.deptname,min(b.empsalary)as minsal,max(b.empsalary) as maxs|
|from emp b,dept a                                                   |
|where empjob='code' and a.deptno=b.deptno                           |
|group by b.deptno;                                                  |
+--------------------------------------------------------------------+
3.对于emp中最低工资小于3000的部门，列出job为'code'的员工的部门号，最低工资，最高工资 
+-------------------------------------------------------------------+
|select a.deptno,min(a.empsalary)as minsal,max(a.empsalary)as maxsal|
|from emp a,(                                                       |
| select deptno,min(empsalary) as minsal                            |
| from emp                                                          |
| group by deptno                                                   |
| having minsal<3000) b                                             |
|where a.deptno=b.deptno and a.empjob='code'                        |
|group by a.deptno;                                                 |
+-------------------------------------------------------------------+
4.根据部门号由高而低，工资由低而高列出每个员工的姓名，部门号，工资 
+--------------------------------------+
|select empname, deptno ,empsalary     | 
|from emp                              |
|group by deptno desc,empsalary asc;   |
+--------------------------------------+
5.列出'zhang'所在部门中每个员工的姓名与部门号 
+--------------------------------------------------------------+
|select empname,deptno from emp                                |
|where deptno = (select deptno from emp where empname='zhang');|
+--------------------------------------------------------------+
6.列出每个员工的姓名，工作，部门号，部门名
+----------------------------------------------+
|select b.empname,b.empjob,b.deptno,a.deptname | 
|from dept a,emp b                             |
|where a.deptno=b.deptno;                      |
+----------------------------------------------+
7.列出emp中工作为'code'的员工的姓名，工作，部门号，部门名
+----------------------------------------------+
|select b.empname,b.empjob,b.deptno,a.deptname |
|from dept a,emp b                             |
|where b.empjob='code' and a.deptno=b.deptno   |
+----------------------------------------------+
8.对于emp中有管理者的员工，列出姓名，管理者姓名（管理者外键为mgr）
+---------------------------------------------------------+
|select a.empname as emp,b.empname as manager             |
|from emp a,emp b                                         |
|where a.empmanager is not NULL and a.empmanager=b.empno; |
+---------------------------------------------------------+
9.对于dept表中，列出所有部门名，部门号，同时列出各部门工作为'code'的员工名与工作
+---------------------------------------------------------+
|SELECT dept.deptno,dept.deptname,emp.empname,emp.empjob  |    
|FROM dept, emp                                           | 
|WHERE dept.deptno = emp.deptno AND emp.empjob = 'code';  | 
+---------------------------------------------------------+
10.对于工资高于本部门平均水平的员工，列出部门号，姓名，工资，按部门号排序 
+-----------------------------------------------------+
|SELECT a.deptno, empname, empsalary                  |
|FROM emp a,(                                         |
| SELECT deptno, avg( empsalary ) AS avgsal           | 
| FROM emp                                            |
| GROUP BY deptno)b                                   |
|WHERE a.deptno = b.deptno AND a.empsalary > b.avgsal |
|ORDER BY a.deptno                                    |
+-----------------------------------------------------+
11.对于emp，列出各个部门中平均工资高于本部门平均水平的员工数和部门号，按部门号排序 
+-----------------------------------------------------+
|SELECT COUNT(a.empno) AS allcount, a.deptno          |
|FROM emp AS a,(                                      |
| SELECT c.deptno AS deptno, AVG(c.empsalary)AS avgsal|
| FROM emp AS c                                       | 
| GROUP BY c.deptno                                   |
| ) AS b                                              |   
|WHERE a.empsalary > b.avgsal AND a.deptno = b.deptno |   
|GROUP BY a.deptno ORDER BY a.deptno;                 |
+-----------------------------------------------------+
12.对于emp中工资高于本部门平均水平，人数多于1人的，列出部门号，人数，按部门号排序 
+------------------------------------------------------+
|SELECT a.deptno,count(a.deptno) AS nums FROM emp a,(  |
| SELECT avg(empsalary) AS avgsal,deptno               |
| FROM emp                                             |
| GROUP BY deptno                                      |
| )b                                                   |
|WHERE a.deptno = b.deptno                             |
|AND a.empsalary > b.avgsal                            |
|GROUP BY deptno                                       |
|HAVING nums >1                                        |
|ORDER BY a.deptno;                                    |
+------------------------------------------------------+
13.对于emp中低于自己工资至少5人的员工，列出其部门号，姓名，工资，以及工资少于自己的人数
+---------------------------------------+
|SELECT a.deptno,a.empname,a.empsalary,(|
| SELECT COUNT(b.empname)               |
| FROM emp AS b                         |
| WHERE b.empsalary < a.empsalary       |
| ) AS allcount                         |
|FROM emp AS a                          |
|WHERE (                                |
| SELECT COUNT( b.empname )             |
| FROM emp AS b                         |
| WHERE b.empsalary < a.empsalary       |
| )>5                                   |
+---------------------------------------+
14.对于emp中同一部门低于自己工资至少3人的员工，列出其部门号，姓名，工资，以及工资少于自己的人数 
+---------------------------------------+
|SELECT a.deptno,a.empname,a.empsalary,(|
| SELECT COUNT(b.empname)               |
| FROM emp AS b                         |
| WHERE b.empsalary < a.empsalary       |
| AND a.deptno = b.deptno               |
| ) AS allcount                         |
|FROM emp AS a                          |
|WHERE (                                |
| SELECT COUNT( b.empname )             |
| FROM emp AS b                         |
| WHERE b.empsalary < a.empsalary       |
| AND a.deptno = b.deptno               |
| )>5                                   |
+---------------------------------------+