SQL面试题（十）

部门和员工表如下示：

dept:
 deptno(primary key),dname,loc
emp:
 empno(primary key),ename,job,mgr,sal,deptno

建表：

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);

 

 

评论

14 楼 zeyuphoenix 2009-03-31  

问题14:
对于emp中同一部门低于自己工资至少3人的员工，列出其部门号，姓名，工资，以及工资少于自己的人数
Sql:
？？？？？

13 楼 zeyuphoenix 2009-03-31  

问题13:
对于emp中低于自己工资至少5人的员工，列出其部门号，姓名，工资，以及工资少于自己的人数
Sql:

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;

12 楼 zeyuphoenix 2009-03-31  

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

SELECT COUNT(a.empno) AS allcount, a.deptno FROM emp AS a ,
(SELECT c.deptno AS deptno, AVG(c.empsalary) AS avgsalary FROM emp AS c GROUP BY c.deptno) AS b
WHERE a.empsalary > b.avgsalary AND a.deptno = b.deptno
GROUP BY a.deptno HAVING allcount > 1 ORDER BY a.deptno;

11 楼 zeyuphoenix 2009-03-31  

问题11:
对于emp，列出各个部门中平均工资高于本部门平均水平的员工数和部门号，按部门号排序
Sql:

SELECT COUNT(a.empno) AS allcount, a.deptno FROM emp AS a ,
(SELECT c.deptno AS deptno, AVG(c.empsalary) AS avgsalary FROM emp AS c GROUP BY c.deptno) AS b
WHERE a.empsalary > b.avgsalary AND a.deptno = b.deptno
GROUP BY a.deptno ORDER BY a.deptno;

10 楼 zeyuphoenix 2009-03-31  

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

SELECT a.deptno, a.empname, a.empsalary FROM emp AS a ,
(SELECT c.deptno AS deptno, AVG(c.empsalary) AS avgsalary FROM emp AS c GROUP BY c.deptno) AS b
WHERE a.empsalary > b.avgsalary AND a.deptno = b.deptno
ORDER BY a.deptno;

9 楼 zeyuphoenix 2009-03-31  

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

SELECT dept.deptno, dept.deptname, emp.empname, emp.empjob 
FROM dept, emp WHERE dept.deptno = emp.deptno AND emp.empjob = 'code';

8 楼 zeyuphoenix 2009-03-31  

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

SELECT a.empname AS emp, b.empname AS manager FROM emp AS a, emp AS b
WHERE a.empmanager IS NOT NULL AND a.empmanager = b.empno;

7 楼 zeyuphoenix 2009-03-31  

问题7:
列出emp中工作为'CLERK'的员工的姓名，工作，部门号，部门名
Sql:

SELECT emp.empname, emp.empjob, dept.deptno, dept.deptname 
FROM dept, emp WHERE emp.deptno = dept.deptno AND emp.empjob = 'code';

6 楼 zeyuphoenix 2009-03-31  

问题6:
列出每个员工的姓名，工作，部门号，部门名
Sql:

SELECT emp.empname, emp.empjob, dept.deptno, dept.deptname 
FROM dept, emp WHERE emp.deptno = dept.deptno;

5 楼 zeyuphoenix 2009-03-31  

问题5:
列出'张三'所在部门中每个员工的姓名与部门号
Sql:

SELECT empname, deptno FROM emp WHERE deptno = 
(SELECT a.deptno FROM emp AS a WHERE a.empname = 'zhang');

4 楼 zeyuphoenix 2009-03-31  

问题4:
根据部门号由高而低，工资由低而高列出每个员工的姓名，部门号，工资
Sql:

SELECT empname, deptno, empsalary FROM emp ORDER BY deptno, empsalary asc;

3 楼 zeyuphoenix 2009-03-31  

问题3:
对于emp中最低工资小于3000的部门，列出job为'CLERK'的员工的部门号，最低工资，最高工资
Sql:

SELECT a.deptno, MIN(a.empsalary) AS minsalary, 
      MAX(a.empsalary) AS maxsalary FROM emp AS a, 
      (SELECT c.deptno , MIN(c.empsalary) FROM emp AS c 
      GROUP BY c.deptno HAVING MIN(c.empsalary) < 3000) AS b
      WHERE a.empjob = 'code' AND a.deptno = b.deptno GROUP BY a.deptno;

2 楼 zeyuphoenix 2009-03-31  

问题2 :
列出emp表中各部门job为'code'的员工的最低工资，最高工资
Sql:

SELECT emp.deptno, MIN(emp.empsalary) AS minsalary, 
      MAX(emp.empsalary) AS maxsalary FROM  emp 
      WHERE emp.empjob = 'code' GROUP BY emp.deptno;

1 楼 zeyuphoenix 2009-03-31  

问题1:
列出emp表中各部门的部门号, 部门，最高工资，最低工资
Sql:

SELECT emp.deptno, .dept.deptname, MIN(emp.empsalary) AS minsalary, 
      MAX(emp.empsalary) AS maxsalary FROM dept, emp 
      WHERE dept.deptno = emp.deptno GROUP BY emp.deptno;