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 (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';

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

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 emp_count
FROM dept d LEFT JOIN emp e ON d.deptno = e.deptno
GROUP BY d.deptno, d.dname, d.loc;

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

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 job, MIN(sal) AS min_sal
FROM emp
GROUP BY job;

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

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

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

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

通过完成这些练习，你可以更深入地理解 SQL 语句的语法和功能，并掌握在实际工作中运用 SQL 语句进行数据操作和分析的能力。