SQL 练习题：部门和员工信息查询

本页面提供了一组 SQL 练习题，涵盖单表和多表查询，适合初学者学习和练习 SQL 语句的编写。

数据表

部门表

CREATE TABLE dept(
  deptno INT PRIMARY KEY,  ##部门编号
  dname VARCHAR(14),  ##部门名称
  loc VARCHAR(13)  ##部门地址
);

INSERT INTO dept VALUES (10,'财务部','北京'),(20,'市场部','上海'),
(30,'销售部','广州'),(40,'运营部','深圳');

员工表

CREATE TABLE emp(
  empno INT PRIMARY KEY,  #员工编号
  ename VARCHAR(10),  #员工姓名
  job VARCHAR(20),  #员工工作
  mgr INT,  #员工直属领导编号
  hiredate DATE,  #入职时间
  sal DOUBLE,  #工资
  comm DOUBLE,  #奖金
  deptno INT  #对应dept表的外键
);

ALTER TABLE emp ADD CONSTRAINT FOREIGN KEY emp(deptno) REFERENCES dept (deptno);

INSERT INTO emp VALUES(7369,'smith','保洁工作',7902,'1980-12-17',800,NULL,20);
INSERT INTO emp VALUES(7499,'allen','销售工作',7698,'1981-02-20',1600,300,30);
INSERT INTO emp VALUES(7521,'ward','销售工作',7698,'1981-02-22',1250,500,30);
INSERT INTO emp VALUES(7566,'jones','管理工作',7839,'1981-04-02',2975,NULL,20);
INSERT INTO emp VALUES(7654,'martin','销售工作',7698,'1981-09-28',1250,1400,30);
INSERT INTO emp VALUES(7698,'blake','管理工作',7839,'1981-05-01',2850,NULL,30);
INSERT INTO emp VALUES(7782,'clark','管理工作',7839,'1981-06-09',2450,NULL,10);
INSERT INTO emp VALUES(7788,'scott','策划工作',7566,'1987-07-03',3000,NULL,20);
INSERT INTO emp VALUES(7839,'king','大BOSS',NULL,'1981-11-17',5000,NULL,10);
INSERT INTO emp VALUES(7844,'turner','销售工作',7698,'1981-09-08',1500,0,30);
INSERT INTO emp VALUES(7876,'adams','保洁工作',7788,'1987-07-13',1100,NULL,20);
INSERT INTO emp VALUES(7900,'james','保洁工作',7698,'1981-12-03',950,NULL,30);
INSERT INTO emp VALUES(7902,'ford','策划工作',7566,'1981-12-03',3000,NULL,20);
INSERT INTO emp VALUES(7934,'miller','保洁工作',7782,'1981-01-23',1300,NULL,10);

工资等级表

CREATE TABLE salgrade(
  grade INT,  #等级
  losal DOUBLE,  #最低工资
  hisal DOUBLE  #最高工资
);

INSERT INTO salgrade VALUES (1,700,1200),(2,1201,1400),
(3,1401,2000),(4,2001,3000),(5,3001,9999);

单表查询

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

SELECT * FROM emp WHERE deptno = 30;

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

SELECT empno, ename, deptno FROM emp WHERE job = 'clerk';

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

SELECT * FROM emp WHERE comm > sal;

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

SELECT * FROM emp WHERE comm > sal * 0.6;

找出获得奖金的员工的工作。

SELECT DISTINCT job FROM emp WHERE comm IS NOT NULL;

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

SELECT * FROM emp WHERE comm < 100 OR comm IS NULL;

找出姓名以 'a'、'b'、's' 开始的员工信息。

SELECT * FROM emp WHERE ename LIKE 'a%' OR ename LIKE 'b%' OR ename LIKE 's%';

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

SELECT * FROM emp WHERE LENGTH(ename) = 6;

名字中不包含 'r' 字符的员工信息。

SELECT * FROM emp WHERE ename NOT LIKE '%r%';

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

SELECT * FROM emp ORDER BY ename;

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

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

找出姓名中包含 'a' 的员工信息。

SELECT * FROM emp WHERE ename LIKE '%a%';

多表查询

返回员工姓名和该员工领导的姓名。

SELECT e.ename, m.ename AS manager_name 
FROM emp e 
LEFT JOIN emp m ON e.mgr = m.empno;

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

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

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

SELECT e.ename, d.dname 
FROM emp e 
LEFT JOIN dept d ON e.deptno = d.deptno 
WHERE e.job = 'clerk';

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

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

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

SELECT d.deptno, d.dname, d.loc, COUNT(e.empno) AS emp_count 
FROM dept d 
LEFT JOIN emp e ON d.deptno = e.deptno 
GROUP BY d.deptno;

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

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

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

SELECT e.*, d.dname 
FROM emp e 
LEFT JOIN dept d ON e.deptno = d.deptno;

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

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

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

SELECT ename, sal + COALESCE(comm, 0) AS annual_salary 
FROM emp 
ORDER BY annual_salary;

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

SELECT ename 
FROM emp 
WHERE sal BETWEEN 
(SELECT losal FROM salgrade WHERE grade = 4) AND 
(SELECT hisal FROM salgrade WHERE grade = 4);