SQL练习题&答案 Part_2

表结构

CREATE DATABASE emp_sys CHARSET=utf8;

USE emp_sys;

CREATE TABLE dept (
deptno INT PRIMARY KEY,
dname VARCHAR(14),
loc VARCHAR(23)
);

CREATE TABLE emp (
empno INT PRIMARY KEY,
ename VARCHAR(20),
job VARCHAR(20),
mgr INT,
hiredate DATE,
sal NUMERIC(7,2),
comm NUMERIC(7,2),
deptno INT,
FOREIGN KEY (deptno) REFERENCES dept(deptno)
);

CREATE TABLE salgrade (
grade NUMERIC(7,2),
losal NUMERIC(7,2),
hisal NUMERIC(7,2)
);

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

INSERT INTO emp VALUES (7369, ‘SMITH’, ‘CLERK’, 7902, ‘1980-12-17’, 1100, NULL, 20);
INSERT INTO emp VALUES (7499, ‘ALLEN’, ‘SALESMAN’, 7698,‘1981-2-20’, 1760, 300, 30);
INSERT INTO emp VALUES (7521, ‘WARD’, ‘SALESMAN’, 7698, ‘1981-02-22’, 1375, 500, 30);
INSERT INTO emp VALUES (7566, ‘JO·NES’, ‘MANAGER’, 7839, ‘1981-04-02’, 3272, NULL, 20);
INSERT INTO emp VALUES (7654, ‘MARTIN’, ‘SALESMAN’, 7698, ‘1981-09-28’, 1375, 1400, 30);
INSERT INTO emp VALUES (7698, ‘BLAKE’, ‘MANAGER’, 7839, ‘1981-05-01’, 3135, NULL, 30);
INSERT INTO emp VALUES (7782, ‘CLARK’, ‘MANAGER’, 7839, ‘1981-06-09’, 2695, NULL, 10);
INSERT INTO emp VALUES (7788, ‘SCOTT’, ‘ANALYST’, 7566, ‘1987-04-19’, 3000, NULL, 20);
INSERT INTO emp VALUES (7839, ‘KING’, ‘PRESIDENT’, NULL, ‘1981-11-17’, 5500, NULL, 10);
INSERT INTO emp VALUES (7844, ‘TURNER’, ‘SALESMAN’, 7698, ‘1981-09-08’, 1650, 0, 30);
INSERT INTO emp VALUES (7876, ‘A