MySQL入门练习(二）

本篇是上一篇的强化与扩展篇，同样材料来自于传智播客。

一、表格信息

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