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 |+---------------------------------------+
页:
[1]