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');-- 创建员工表
CREATE TABLE emp(
empno INT PRIMARY KEY,
ename VARCHAR(10),
job VARCHAR(9),
mgr INT,
hiredate date,
sal DOUBLE,
comm DOUBLE,
deptno INT
);-- 添加 部门和员工之间的主外键关系
ALTER TABLE emp ADD CONSTRAINT 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,'1921-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,'blacke','manager',7839,'1981-05-01',2850, NULL,30);
INSERT INTO emp VALUES (7782,'clark','manager',7839,'1981-06-09',2450, NULL,10);
INSERT INTO emp VALUES(7788,'scott','analyst',7566,'1987-07-03',3000, NULL,20);
INSERT INTO emp VALUES(7839,'king','president', NULL,'1981-11-17',5000, NULL,10);
INSERT INTO emp VALUES(7844,'turner','salesman',7698,'1981-09-08',1500,0,30);
INSERT INTO emp VALUES(7876,'adams','clerk',7788,'1987-07-13',1100, NULL,20);
INSERT INTO emp VALUES(7900,'james','clerk',7698,'1981-12-03',95, NULL,30);
INSERT INTO emp VALUES(7902,'ford','analyst',7566,'1981-12-03',3000, NULL,20);
INSERT INTO emp VALUES(7934,'miller','clerk',7782,'1981-01-23',1300, NULL,10);
CREATE TABLE salgrade(
grade INT,
losal DOUBLE,
hisal DOUBLE
);
INSERT INTO salgrade VALUES(1,700,1200);
INSERT INTO salgrade VALUES(2,1201,1400);
INSERT INTO salgrade VALUES(3,1401,2000);
INSERT INTO salgrade VALUES(4,2001,3000);
INSERT INTO salgrade VALUES(5,3001,9999);
练习题
返回拥有员工的部门名、部门号
工资水平多于smith的员工信息
返回员工和所属经理的姓名
返回雇员的雇用日期遭遇其经理雇佣的员工及其经理姓名
返回员工姓名及其所在的部门名称
返回从事clerk工作的员工姓名和所在部门名称
返回部门号及其部门的最低工资
返回销售(sales)所有员工的姓名
返回工资水平多于平均工资的员工
返回与scott从事相同工作的员工
返回与30部门员工工资水平相同的员工姓名与工资
返回员工工作及其从事此工作的最低工资
计算出员工的年薪,并且以年薪排序
返回工资处于第四级别的员工的姓名
返回工资为二等级的职员名字、部门所在地
练习题答案
-- 返回拥有员工的部门名、部门号
SELECT DISTINCT d.dname,d.deptno FROM dept d JOIN emp e ON d.deptno = e.deptno;-- 工资水平多于smith的员工信息
SELECT * FROM emp WHERE sal >(SELECT sal FROM emp WHERE ename='smith');-- 返回员工和所属经理的姓名
SELECT a.ename '员工',b.ename '经理'from emp a JOIN emp b ON a.mgr = b.empno;-- 返回雇员的雇用日期遭遇其经理雇佣的员工及其经理姓名
SELECT a.ename '员工姓名',b.ename '经理姓名' FROM emp a JOIN emp b ON a.mgr = b.empno and a.hiredate < b.hiredate;-- 返回员工姓名及其所在的部门名称
SELECT e.ename,d.dname FROM emp e JOIN dept d ON e.deptno= d.deptno;-- 返回从事clerk工作的员工姓名和所在部门名称
SELECT e.ename,d.dname,e.job FROM emp e JOIN dept d ON e.deptno= d.deptno AND job ='clerk';-- 返回部门号及其部门的最低工资
SELECT deptno,min(sal) FROM emp GROUP BY deptno;-- 返回销售(sales)所有员工的姓名
SELECT b.ename FROM dept a JOIN emp b ON a.deptno=b.deptno and a.dname='sales'-- 返回工资水平多于平均工资的员工
SELECT * FROM emp WHERE sal >(SELECT AVG(sal) FROM emp);-- 返回与scott从事相同工作的员工
SELECT * FROM emp WHERE job =(SELECT job FROM emp WHERE ename ='scott')and ename <>'scott';-- 返回与30部门员工工资水平相同的员工姓名与工资
SELECT * FROM emp WHERE sal > ALL(SELECT sal FROM emp WHERE deptno =30);-- 返回员工工作及其从事此工作的最低工资
SELECT job ,min(sal) FROM emp GROUP BY job;-- 计算出员工的年薪,并且以年薪排序
SELECT ename,sal*12+IFNULL(comm,0) FROM emp ORDER BY(sal*12+IFNULL(comm,0))DESC;-- 返回工资处于第四级别的员工的姓名
SELECT * FROM emp WHERE sal
BETWEEN (SELECT losal FROM salgrade WHERE grade =4)and(SELECT hisal FROM salgrade WHERE grade =4);-- 返回工资为二等级的职员名字、部门所在地
SELECT * FROM dept a
JOIN emp b ON a.deptno = b.deptno
JOIN salgrade c ON grade =2and b.sal >= c.losal and b.sal <= c.hisal;
数据准备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 dep