SQL基础练习题

0. 前言

数据库是我们的基础技能之一。平时多练习，工作/学习/面试的过程才会更加顺利。本文分享一套SQL基础的练习。

数据库练习。本文涉及的练习题难度为入门级，简单。读者可根据自身需求有空做一做。

开练！

准备：

准备表:
DROP DATABASE IF EXISTS test1;
CREATE DATABASE test1;
USE test1;
##部门表
#DROP IF EXISTS TABLE DEPT;
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');
##员工表
#DROP IF EXISTS TABLE EMP;
CREATE TABLE EMP(
EMPNO int PRIMARY KEY, #员工编号
ENAME VARCHAR(10), #员工姓名
JOB VARCHAR(9), #员工工作
MGR int, #员工直属领导编号
HIREDATE DATE, #入职时间
SAL double, #工资
COMM double, #奖金
DEPTNO int #对应dept表的外键
);
## 添加 部门 和 员工 之间的主外键关系

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,'1981-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,'BLAKE','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',950,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);
#工资等级表
#DROP IF EXISTS TABLE SALGRADE;
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);

1. 题目

#1、查找部门30中员工的详细信息。

#2、找出从事clerk工作的员工的编号、姓名、部门号。

#3、检索出奖金多于基本工资的员工信息。

#4、检索出奖金多于基本工资60%的员工信息。

#5、找出10部门的经理、20部门的职员 的员工信息。

#6、找出10部门的经理、20部门的职员 或者既不是经理也不是职员但是工资高于2000元的员工信息。

#7、找出获得奖金的员工的工作。

#8、找出奖金少于100或者没有获得奖金的员工的信息。

#9、找出姓名以A、B、S开始的员工信息。

#10、找到名字长度为6个字符的员工信息。

#11、名字中不包含R字符的员工信息。

#12、返回员工的详细信息并按姓名排序。

#13、返回员工的信息并按工作降序工资升序排列。

#14、计算员工的日薪(按30天)。

#15、找出姓名中包含A的员工信息。

#16、返回拥有员工的部门名、部门号。

#17、工资水平多于smith的员工信息。

#18、返回员工和所属经理的姓名。

#19、返回雇员的雇佣日期早于其经理雇佣日期的员工及其经理姓名。

#20、返回员工姓名及其所在的部门名称。

#21、返回从事clerk工作的员工姓名和所在部门名称。

#22、返回部门号及其本部门的最低工资。

#23、返回销售部(sales)所有员工的姓名。

#24、返回工资水平多于平均工资的员工。

#25、返回与SCOTT从事相同工作的员工。

#26、返回与30部门员工工资水平相同的员工姓名与工资。

#27、返回工资高于30部门所有员工工资水平的员工信息。

#28、返回部门号、部门名、部门所在位置及其每个部门的员工总数。

#29、返回员工的姓名、所在部门名及其工资。

#30、返回员工的详细信息。(包括部门名)

#31、返回员工工作及其从事此工作的最低工资。

#32、计算出员工的年薪，并且以年薪排序。

#33、返回工资处于第四级别的员工的姓名。

#34、返回工资为二等级的职员名字、部门所在地、和二等级的最低工资和最高工资

2. 参考答案

1. SELECT EMPNO,ENAME,JOB,MGR,HIREDATE,SAL,COMM,DEPTNO
FROM emp
WHERE DEPTNO=30;

2. SELECT EMPNO,ENAME,DEPTNO FROM emp
WHERE JOB = 'clerk';

3. SELECT EMPNO,ENAME,JOB,MGR,HIREDATE,SAL,COMM,DEPTNO
FROM emp
WHERE COMM > SAL;

4. SELECT EMPNO,ENAME,JOB,MGR,HIREDATE,SAL,COMM,DEPTNO
FROM emp
WHERE COMM > SAL*0.6;

5. SELECT EMPNO,ENAME,JOB,MGR,HIREDATE,SAL,COMM,DEPTNO
FROM emp
WHERE JOB='manager' AND DEPTNO = 10 OR JOB = 'clerk' AND DEPTNO = 20;

6. SELECT EMPNO,ENAME,JOB,MGR,HIREDATE,SAL,COMM,DEPTNO
FROM emp
WHERE JOB='manager' AND DEPTNO = 10 OR JOB = 'clerk' AND DEPTNO = 20 OR JOB NOT IN ('manager','clerk') AND SAL>2000;

7. SELECT JOB FROM emp
WHERE COMM IS NOT NULL;

8. SELECT EMPNO,ENAME,JOB,MGR,HIREDATE,SAL,COMM,DEPTNO
FROM emp
WHERE COMM<100 or COMM IS NULL;

9. SELECT EMPNO,ENAME,JOB,MGR,HIREDATE,SAL,COMM,DEPTNO
FROM emp
WHERE ENAME LIKE 'A%' OR 'S%' OR 'B%';

10. SELECT EMPNO,ENAME,JOB,MGR,HIREDATE,SAL,COMM,DEPTNO
FROM emp
WHERE LENGTH(ENAME) = 6;

11. SELECT EMPNO,ENAME,JOB,MGR,HIREDATE,SAL,COMM,DEPTNO
FROM emp
WHERE ENAME NOT LIKE '%R%';

12. SELECT EMPNO,ENAME,JOB,MGR,HIREDATE,SAL,COMM,DEPTNO
FROM emp
ORDER BY ENAME;

13. SELECT EMPNO,ENAME,JOB,MGR,HIREDATE,SAL,COMM,DEPTNO
FROM emp
ORDER BY JOB DESC, SAL;

14. SELECT EMPNO,ENAME,JOB,MGR,HIREDATE,SAL,COMM,DEPTNO, SAL/30 AS DAYSAL
FROM emp;

15. SELECT EMPNO,ENAME,JOB,MGR,HIREDATE,SAL,COMM,DEPTNO
FROM emp
WHERE ENAME LIKE '%A%';
 
16. SELECT d.DNAME , d.DEPTNO FROM dept d
LEFT JOIN emp e ON d.DEPTNO = e.DEPTNO
GROUP BY d.DEPTNO 
HAVING COUNT(e.DEPTNO) > 0

17. 
//查询 SMITH 的工资等级：
SELECT GRADE FROM salgrade 
WHERE LOSAL < (
SELECT SAL FROM emp WHERE ENAME = 'SMITH'
)  AND HISAL > (
SELECT SAL FROM emp WHERE ENAME = 'SMITH'
)

//连表：
SELECT e.EMPNO, e.ENAME, e.JOB, e.MGR, e.HIREDATE, e.SAL, e.COMM, e.DEPTNO,
s.GRADE, s.LOSAL, s.HISAL
FROM emp e LEFT JOIN salgrade s  
ON e.SAL BETWEEN s.LOSAL AND s.HISAL

//组合：
SELECT e.EMPNO, e.ENAME, e.JOB, e.MGR, e.HIREDATE, e.SAL, e.COMM, e.DEPTNO,
s.GRADE, s.LOSAL, s.HISAL
FROM emp e LEFT JOIN salgrade s  
ON e.SAL BETWEEN s.LOSAL AND s.HISAL
WHERE s.GRADE > (SELECT GRADE FROM salgrade 
WHERE LOSAL < (
SELECT SAL FROM emp WHERE ENAME = 'SMITH'
)  AND HISAL > (
SELECT SAL FROM emp WHERE ENAME = 'SMITH'
))

//这一题，你能想到更优的方案吗？

18. 
//查询不是经理的员工信息
SELECT EMPNO, ENAME, DEPTNO, JOB, MGR, HIREDATE, SAL, COMM FROM emp
WHERE JOB NOT IN ('clerk');

//查询经理的信息
SELECT DEPTNO, ENAME FROM emp WHERE JOB = 'clerk';

//连表
SELECT DISTINCT e1.EMPNO, e1.ENAME, e1.DEPTNO, e1.JOB, e1.MGR, e1.HIREDATE, e1.SAL, e1.COMM, e2.ENAME AS CLERKNAME
FROM emp e1 LEFT JOIN emp e2 ON e1.DEPTNO = e2.DEPTNO
WHERE e1.JOB NOT IN ('clerk') AND e2.JOB = 'clerk'

19.
//这题的一些条件可以复用上一题的
SELECT DISTINCT e1.EMPNO, e1.ENAME, e1.DEPTNO, e1.JOB, e1.MGR, e1.HIREDATE, e1.SAL, e1.COMM, e2.ENAME AS CLERKNAME
FROM emp e1 LEFT JOIN emp e2 ON e1.DEPTNO = e2.DEPTNO
WHERE e1.JOB NOT IN ('clerk') AND e2.JOB = 'clerk' 
AND e1.HIREDATE < e2.HIREDATE

20.SELECT e.ENAME, d.DNAME FROM emp e LEFT JOIN dept d 
ON e.DEPTNO = d.DEPTNO

21.SELECT e.ENAME, d.DNAME FROM emp e LEFT JOIN dept d 
ON e.DEPTNO = d.DEPTNO
WHERE e.JOB = 'clerk'

22.SELECT MIN(SAL),DEPTNO FROM emp GROUP BY DEPTNO

23.SELECT e.ENAME FROM emp e LEFT JOIN dept d ON e.DEPTNO = d.DEPTNO WHERE d.DNAME='SALES'

24.SELECT EMPNO, ENAME, HIREDATE, JOB, MGR, DEPTNO, SAL, COMM
FROM emp WHERE SAL> (SELECT AVG(SAL) FROM emp);

25.SELECT EMPNO, ENAME, HIREDATE, JOB, MGR, DEPTNO, SAL, COMM
FROM emp WHERE JOB = (SELECT JOB FROM emp WHERE ENAME='SCOTT')

26.SELECT e.ENAME, d.LOC, s.LOSAL, s.HISAL
FROM dept d
LEFT JOIN emp e
ON d.DEPTNO = e.DEPTNO

JOIN salgrade s 
ON e.SAL >= s.LOSAL AND e.SAL <= s.HISAL AND s.GRADE = 2

27.SELECT s.GRADE, e.ENAME, e.SAL 
FROM salgrade s LEFT JOIN emp e ON e.SAL >= s.LOSAL AND e.SAL <= s.HISAL 
WHERE s.GRADE > (SELECT MAX(s.GRADE)
FROM salgrade s LEFT JOIN emp e ON e.SAL >= s.LOSAL AND e.SAL <= s.HISAL WHERE e.DEPTNO = 30) AND e.DEPTNO != 30

28.SELECT d.DEPTNO, d.DNAME, d.LOC , COUNT(e.DEPTNO) AS EMPCOUNT
FROM dept d LEFT JOIN emp e ON d.DEPTNO = e.DEPTNO
GROUP BY d.DEPTNO

29.SELECT e.ENAME, d.DNAME, e.SAL
FROM emp e LEFT JOIN dept d ON e.DEPTNO = d.DEPTNO

30.SELECT d.DNAME, e.DEPTNO, e.ENAME, e.SAL, e.HIREDATE, e.JOB, e.MGR, e.HIREDATE, e.COMM
FROM emp e LEFT JOIN dept d ON e.DEPTNO = d.DEPTNO

31.SELECT DISTINCT e.JOB, MIN(SAL) AS MINSAL  
FROM emp e GROUP BY JOB

32.SELECT SAL*12 AS YEARSAL FROM emp ORDER BY YEARSAL DESC

33.SELECT s.GRADE, e.ENAME FROM salgrade s LEFT JOIN emp e ON
e.SAL >= s.LOSAL AND e.SAL <= s.HISAL
WHERE s.GRADE = 4

34.SELECT e.ENAME, d.LOC, s.LOSAL, s.HISAL
FROM dept d
LEFT JOIN emp e
ON d.DEPTNO = e.DEPTNO

JOIN salgrade s 
ON e.SAL >= s.LOSAL AND e.SAL <= s.HISAL AND s.GRADE = 2