CREATE DATABASE exam;
USE exam;
/*创建部门表*/
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);
INSERT INTO emp VALUES (1003, '殷天正', '销售员', 1006, '2001-02-22', 12500, 5000, 30);
INSERT INTO emp VALUES (1005, '谢逊', '销售员', 1006, '2001-09-28', 12500, 14000, 30);
INSERT INTO emp VALUES (1010, '韦一笑', '销售员', 1006, '2001-09-08', 15000, 0, 30);
INSERT INTO emp VALUES (1012, '程普', '文员', 1006, '2001-12-03', 9500, NULL, 30);
INSERT INTO emp VALUES (1014, '黄盖', '文员', 1007, '2002-01-23', 13000, NULL, 10);
INSERT INTO emp VALUES (1011, '周泰', '文员', 1008, '2007-05-23', 11000, NULL, 20);
INSERT INTO emp VALUES (1001, '甘宁', '文员', 1013, '2000-12-17', 8000, NULL, 20);
/*插入salgrade表数据*/
INSERT INTO salgrade VALUES (1, 7000, 12000);
INSERT INTO salgrade VALUES (2, 12010, 14000);
INSERT INTO salgrade VALUES (3, 14010, 20000);
INSERT INTO salgrade VALUES (4, 20010, 30000);
INSERT INTO salgrade VALUES (5, 30010, 99990);
/*表练习操作*/
-- 列出至少有一个员工的所有部门
SELECT dname FROM dept WHERE deptno IN (SELECT deptno FROM emp);
SELECT dname FROM dept WHERE deptno IN (SELECT deptno FROM emp GROUP BY deptno HAVING COUNT(deptno)>=1);
-- 列出薪金比殷天正多的所有员工
SELECT * FROM emp
WHERE sal>(SELECT sal FROM emp WHERE ename='殷天正');
-- 列出所有员工的姓名及其直接上级的姓名
SELECT e.ename ,(SELECT ename FROM emp z WHERE z.empno=e.mgr)AS '直接领导'
FROM emp e;
SELECT e.ename AS '员工姓名',z.ename AS '直接领导'
FROM emp e LEFT JOIN emp z
ON e.mgr=z.empno;
-- 列出受雇日期早于其直接上级的所有员工
SELECT e.ename FROM emp e
WHERE e.hiredate<(SELECT hiredate FROM emp z WHERE z.empno=e.mgr);
SELECT e.ename FROM emp e
WHERE e.mgr IS NOT NULL AND
NOT EXISTS(SELECT NULL FROM emp z WHERE z.empno=e.mgr AND e.hiredate>z.hiredate);
-- 列出部门名称和这些部门的员工信息,同时列出那些没有员工的部门
SELECT a.dname,b.empno,b.ename,b.job,b.hiredate,b.sal,b.COMM,b.deptno
FROM dept a LEFT JOIN emp b ON a.deptno=b.deptno;
-- 列出所有'文员'的姓名及其部门名称
SELECT e.ename,d.dname
FROM emp e,dept d
WHERE e.deptno=d.deptno AND e.job='文员';
SELECT e.ename,d.dname
FROM emp e JOIN dept d
ON e.deptno=d.deptno AND e.job='文员';
-- 列出最低薪金大于1500的各种工作
SELECT DISTINCT job FROM emp
GROUP BY job HAVING MIN(sal)>1500;
-- 列出在'销售部'工作的员工的姓名,假定不知道销售部门的编号
SELECT e.ename FROM emp e
WHERE e.deptno=(SELECT deptno FROM dept WHERE dname='销售部');
-- 列出薪金高出公司平均薪资的所有员工
SELECT e.ename FROM emp e
WHERE sal>(SELECT AVG(sal) FROM emp);
-- 列出与'张飞'从事相同工作的所有员工
SELECT ename FROM emp
WHERE job=(SELECT job FROM emp WHERE ename='张飞');
-- 列出薪金等于部门30中员工的薪金的所有员工姓名和薪金
SELECT ename,sal FROM emp
WHERE sal IN (SELECT sal FROM emp WHERE deptno=30) AND deptno<>30;
-- 列出薪金高于部门30工作的所有员工的薪金的员工姓名和薪金
SELECT ename,sal FROM emp
WHERE sal>(SELECT MAX(sal) FROM emp WHERE deptno=30);
-- 列出在每个部门工作的员工数量,平均工资和平均服务期限
SELECT (SELECT dname FROM dept WHERE dept.deptno=emp.deptno)AS '部门',COUNT(deptno),AVG(sal) FROM emp
GROUP BY deptno;
-- 列出所有员工的姓名,部门名称和工资
SELECT ename,(SELECT dname FROM dept WHERE dept.deptno=emp.deptno)AS '部门',sal
FROM emp;
-- 列出所有部门的详细信息和部门人数
SELECT dept.*,COUNT(emp.deptno) AS '部门人数' FROM dept LEFT JOIN emp
ON emp.deptno=dept.deptno GROUP BY emp.deptno;
SELECT dept.*,(SELECT COUNT(deptno) FROM emp WHERE emp.deptno=dept.deptno GROUP BY emp.deptno)AS '部门人数'
FROM dept;
-- 列出各种工作的最低工资
SELECT job,AVG(sal)AS '平均工资' FROM emp GROUP BY job;
-- 列出各个部门的经理最低工资
SELECT deptno,MIN(sal)FROM emp WHERE job='经理' GROUP BY deptno;
-- 列出所有员工的年工资,按年薪从低到高排序
SELECT ename,sal*12 AS '年薪' FROM emp ORDER BY sa ASC ;
