本篇是上一篇的强化与扩展篇,同样材料来自于传智播客。
一、表格信息
1、部门表:由部门号、部门名称、部门地址组成:
CREATE TABLE dept(
deptno int PRIMARY KEY,
dname VARCHAR(14) ,
loc VARCHAR(13)
) ;
INSERT INTO DEPT VALUES (10,'ACCOUNTING','NEW YORK');
INSERT INTO DEPT VALUES (20,'RESEARCH','DALLAS');
INSERT INTO DEPT VALUES (30,'SALES','CHICAGO');
INSERT INTO DEPT VALUES (40,'OPERATIONS','BOSTON');
2、员工表:由员工编号、姓名、工作、直属领导、入职日期、工资、奖金、部门号组成:
CREATE TABLE emp(
empno int PRIMARY KEY,
ename VARCHAR(10),
job VARCHAR(9),
mgr int,
hiredate DATE,
sal double,
comm double,
deptno int ,
foreign key emp(deptno) REFERENCES dept(deptno)
);
INSERT INTO EMP VALUES(7369,'SMITH','CLERK',7902,"1980-12-17",800,NULL,20);
INSERT INTO EMP VALUES(7499,'ALLEN','SALESMAN',7698,'1981-02-20',1600,300,30);
INSERT INTO EMP VALUES(7521,'WARD','SALESMAN',7698,'1981-02-22',1250,500,30);
INSERT INTO EMP VALUES(7566,'JONES','MANAGER',7839,'1981-04-02',2975,NULL,20);
INSERT INTO EMP VALUES(7654,'MARTIN','SALESMAN',7698,'1981-09-28',1250,1400,30);
INSERT INTO EMP VALUES(7698,'BLAKE','MANAGER',7839,'1981-05-01',2850,NULL,30);
INSERT INTO EMP VALUES(7782,'CLARK','MANAG