mysql 多表查询练习题_MySQL 多表查询练习



/创建部门表/

CREATE TABLE dept(

deptno INT PRIMARY KEY,

dname VARCHAR(50),

loc VARCHAR(50)

);

/创建雇员表/

CREATE TABLE emp(

empno INT PRIMARY KEY,

ename VARCHAR(50),

job VARCHAR(50),

mgr INT,

hiredate DATE,

sal DECIMAL(7,2),

COMM DECIMAL(7,2),

deptno INT,

CONSTRAINT fk_emp FOREIGN KEY(mgr) REFERENCES emp(empno)

);

/创建工资等级表/

CREATE TABLE salgrade(

grade INT PRIMARY KEY,

losal INT,

hisal INT

);

/插入dept表数据/

INSERT INTO dept VALUES (10, ‘教研部’, ‘北京’);

INSERT INTO dept VALUES (20, ‘学工部’, ‘上海’);

INSERT INTO dept VALUES (30, ‘销售部’, ‘广州’);

INSERT INTO dept VALUES (40, ‘财务部’, ‘武汉’);

/插入emp表数据/

INSERT INTO emp VALUES (1009, ‘曾阿牛’, ‘董事长’, NULL, ‘2001-11-17’, 50000, NULL, 10);

INSERT INTO emp VALUES (1004, ‘刘备’, ‘经理’, 1009, ‘2001-04-02’, 29750, NULL, 20);

INSERT INTO emp VALUES (1006, ‘关羽’, ‘经理’, 1009, ‘2001-05-01’, 28500, NULL, 30);

INSERT INTO emp VALUES (1007, ‘张飞’, ‘经理’, 1009, ‘2001-09-01’, 24500, NULL, 10);

INSERT INTO emp VALUES (1008, ‘诸葛亮’, ‘分析师’, 1004, ‘2007-04-19’, 30000, NULL, 20);

INSERT INTO emp VALUES (1013, ‘庞统’, ‘分析师’, 1004, ‘2001-12-03’, 30000, NULL, 20);

INSERT INTO emp VALUES (1002, ‘黛绮丝’, ‘销售员’, 1006, ‘2001-02-20’, 16000, 3000, 30