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