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

本页面提供了一系列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 JOIN dept d ON e.deptno=d.deptno;

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

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

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

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

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

SELECT d.deptno, d.dname, d.loc, COUNT(e.empno) AS num_of_employees
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 JOIN dept d ON e.deptno=d.deptno;

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

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

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

SELECT e.job, MIN(e.sal) AS min_salary
FROM emp e
GROUP BY e.job;

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

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

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

SELECT e.ename
FROM emp e JOIN salgrade s ON e.sal BETWEEN s.losal AND s.hisal
WHERE s.grade=4;