SQL 数据库设计与操作示例 - 部门与员工管理 - 常规

部门表

create table dept(
  deptno int primary key auto_increment, -- 部门编号
dname varchar(14) , -- 部门名字
loc varchar(13) -- 地址
) ;

员工表

create table emp(
  empno int primary key auto_increment,-- 员工编号
ename varchar(10), -- 员工姓名
job varchar(9), -- 岗位
mgr int, -- 直接领导编号
hiredate date, -- 雇佣日期，入职日期
sal int, -- 薪水
comm int, -- 提成
deptno int not null, -- 部门编号
foreign key (deptno) references dept(deptno)
);

数据插入

insert into dept values(10,'财务部','北京');
insert into dept values(20,'研发部','上海');
insert into dept values(30,'销售部','广州');
insert into dept values(40,'行政部','深圳');

insert into emp values(7369,'刘一','职员',7902,'1980-12-17',800,null,20);
insert into emp values(7499,'陈二','推销员',7698,'1981-02-20',1600,300,30);
insert into emp values(7521,'张三','推销员',7698,'1981-02-22',1250,500,30);
insert into emp values(7566,'李四','经理',7839,'1981-04-02',2975,null,20);
insert into emp values(7654,'王五','推销员',7698,'1981-09-28',1250,1400,30);
insert into emp values(7698,'赵六','经理',7839,'1981-05-01',2850,null,30);
insert into emp values(7782,'孙七','经理',7839,'1981-06-09',2450,null,10);
insert into emp values(7788,'周八','分析师',7566,'1987-06-13',3000,null,20);
insert into emp values(7839,'吴九','总裁',null,'1981-11-17',5000,null,10);
insert into emp values(7844,'郑十','推销员',7698,'1981-09-08',1500,0,30);
insert into emp values(7876,'郭靖','职员',7788,'1987-06-13',1100,null,20);
insert into emp values(7900,'令狐冲','职员',7698,'1981-12-03',950,null,30);
insert into emp values(7902,'张无忌','分析师',7566,'1981-12-03',3000,null,20);
insert into emp values(7934,'杨过','职员',7782,'1983-01-23',1300,null,10);

练习题

1. 列出至少有一个员工的所有部门。

SELECT DISTINCT dept.dname 
FROM emp 
JOIN dept 
ON emp.deptno = dept.deptno;

2. 列出薪金比'刘一'多的所有员工。

SELECT * 
FROM emp 
WHERE sal > (SELECT sal FROM emp WHERE ename = '刘一');

3. 列出所有员工的姓名及其直接上级的姓名。

SELECT e1.ename, e2.ename AS manager_name 
FROM emp e1 
JOIN emp e2 
ON e1.mgr = e2.empno;

4. 列出受雇日期早于其直接上级的所有员工。

SELECT * 
FROM emp 
WHERE hiredate < (SELECT hiredate FROM emp WHERE empno=mgr);

5. 列出部门名称和这些部门的员工信息，同时列出那些没有员工的部门。

SELECT dept.dname, emp.empno, emp.ename 
FROM dept 
LEFT JOIN emp 
ON dept.deptno = emp.deptno;

6. 列出所有job为'职员'的姓名及其部门名称。

SELECT emp.ename, dept.dname 
FROM emp 
JOIN dept 
ON emp.deptno = dept.deptno 
WHERE emp.job = '职员';

7. 列出最低薪金大于1500的各种工作。

SELECT job 
FROM emp 
GROUP BY job 
HAVING MIN(sal) > 1500;

8. 列出在部门 '销售部' 工作的员工的姓名，假定不知道销售部的部门编号。

SELECT emp.ename 
FROM emp 
JOIN dept 
ON emp.deptno = dept.deptno 
WHERE dept.dname = '销售部';

9. 列出薪金高于公司平均薪金的所有员工。

SELECT * 
FROM emp 
WHERE sal > (SELECT AVG(sal) FROM emp);

10. 列出与'周八'从事相同工作的所有员工。

SELECT e1.ename 
FROM emp e1 
JOIN emp e2 
ON e1.job = e2.job 
WHERE e2.ename = '周八';

11. 列出薪金等于部门30中员工的薪金的所有员工的姓名和薪金。

SELECT e1.ename, e1.sal 
FROM emp e1 
JOIN emp e2 
ON e1.sal = e2.sal AND e2.deptno = 30;

12. 列出薪金高于在部门30工作的所有员工的薪金的员工姓名和薪金。

SELECT emp.ename, emp.sal 
FROM emp 
WHERE emp.sal > (SELECT MAX(sal) FROM emp WHERE deptno = 30);

13. 列出在每个部门工作的员工数量、平均工资。

SELECT dept.dname, COUNT(emp.empno), AVG(emp.sal) 
FROM dept 
LEFT JOIN emp 
ON dept.deptno = emp.deptno 
GROUP BY dept.dname;

14. 列出所有员工的姓名、部门名称和工资。

SELECT emp.ename, dept.dname, emp.sal 
FROM emp 
JOIN dept 
ON emp.deptno = dept.deptno;

15. 列出所有部门的详细信息和部门人数。

SELECT dept.*, COUNT(emp.empno) 
FROM dept 
LEFT JOIN emp 
ON dept.deptno = emp.deptno 
GROUP BY dept.deptno;

16. 列出各种工作的最低工资。

SELECT job, MIN(sal) 
FROM emp 
GROUP BY job;

17. 列出各个部门的经理的最低薪金。

SELECT dept.dname, MIN(emp.sal) 
FROM emp 
JOIN dept 
ON emp.deptno = dept.deptno 
WHERE emp.job = '经理' 
GROUP BY dept.dname;

18. 列出所有员工的年工资,按年薪从低到高排序。

SELECT ename, sal*12 AS annual_salary 
FROM emp 
ORDER BY annual_salary;

19. 查出emp表中薪水在3000以上（包括3000）的所有员工的员工号、姓名、薪水。

SELECT empno, ename, sal 
FROM emp 
WHERE sal >= 3000;

20. 查询出所有薪水在'陈二'之上的所有人员信息。

SELECT * 
FROM emp 
WHERE sal > (SELECT sal FROM emp WHERE ename = '陈二');

21. 查询出emp表中部门编号为20，薪水在2000以上（不包括2000）的所有员工，显示他们的员工号，姓名以及薪水，以如下列名显示：员工编号员工名字薪水

SELECT empno AS '员工编号', ename AS '员工名字', sal AS '薪水' 
FROM emp 
WHERE deptno = 20 AND sal > 2000;

22. 查询出emp表中所有的工作种类（无重复）

SELECT DISTINCT job 
FROM emp;

23. 查询出所有奖金（comm）字段不为空的人员的所有信息。

SELECT * 
FROM emp 
WHERE comm IS NOT NULL;

24. 查询出薪水在800到2500之间（闭区间）所有员工的信息。（注：使用两种方式实现and以及between and）

-- Using AND 
SELECT * 
FROM emp 
WHERE sal >= 800 AND sal <= 2500;

-- Using BETWEEN AND 
SELECT * 
FROM emp 
WHERE sal BETWEEN 800 AND 2500;

25. 查询出员工号为7521，7900，7782的所有员工的信息。（注：使用两种方式实现，or以及in）

-- Using OR 
SELECT * 
FROM emp 
WHERE empno = 7521 OR empno = 7900 OR empno = 7782;

-- Using IN 
SELECT * 
FROM emp 
WHERE empno IN (7521, 7900, 7782);

26. 查询出名字中有“张”字符，并且薪水在1000以上（不包括1000）的所有员工信息。

SELECT * 
FROM emp 
WHERE ename LIKE '%张%' AND sal > 1000;

27. 查询出名字第三个汉字是“忌”的所有员工信息。

SELECT * 
FROM emp 
WHERE ename LIKE '__忌%';

28. 将所有员工按薪水升序排序，薪水相同的按照入职时间降序排序。

SELECT * 
FROM emp 
ORDER BY sal ASC, hiredate DESC;

29. 将所有员工按照名字首字母升序排序，首字母相同的按照薪水降序排序。

SELECT * 
FROM emp 
ORDER BY CONVERT(ename USING gbk) ASC, sal DESC;

30. 查询出最早工作的那个人的名字、入职时间和薪水。

SELECT ename, hiredate, sal 
FROM emp 
ORDER BY hiredate ASC 
LIMIT 1;

31. 显示所有员工的名字、薪水、奖金，如果没有奖金，暂时显示100。

SELECT ename, sal, COALESCE(comm, 100) AS comm 
FROM emp;

32. 显示出薪水最高人的职位。

SELECT job 
FROM emp 
WHERE sal = (SELECT MAX(sal) FROM emp);

33. 查出emp表中所有部门的最高薪水和最低薪水，部门编号为10的部门不显示。

SELECT deptno, MAX(sal), MIN(sal) 
FROM emp 
WHERE deptno != 10 
GROUP BY deptno;

34. 删除10号部门薪水最高的员工。

DELETE FROM emp 
WHERE deptno = 10 AND sal = (SELECT MAX(sal) FROM emp WHERE deptno = 10);

35. 将薪水最高的员工的薪水降30%。

UPDATE emp 
SET sal = sal*0.7 
WHERE sal = (SELECT MAX(sal) FROM emp);