探客时代

数据库系列（5部分）

📝 面向新手的DBMS面试题

📝 面向新手的10大SQL查询

掌握SQL基础：（第2部分）

掌握SQL基础：（第1部分）

掌握SQL连接查询：HR员工数据分析

欢迎来到我们SQL子查询系列的第2部分，我们将深入探讨利用子查询从HR数据库中获得强大洞察。

在本博客中，我们专注于一个实用的员工管理系统，包含员工和部门表。我们将介绍模式、插入示例数据，并演示42个高级SQL查询，强调子查询来解决复杂的HR和业务场景。本指南非常适合数据分析师、HR专业人员或开发者，他们希望通过真实世界的示例来提高SQL技能。

🧩 数据库架构概览

tbl_emp：存储员工详细信息

列：

emp_id

emp_name

street_address

city

salary

commission

job

deptno

hiredate

company_name

tbl_department：包含部门信息

列：

deptno

dept_name

🏗️ 创建表

CREATE TABLE tbl_department (
deptno INT PRIMARY KEY,
dept_name VARCHAR(50)
);
CREATE TABLE tbl_emp (
emp_id INT PRIMARY KEY,
emp_name VARCHAR(50),
street_address VARCHAR(100),
city VARCHAR(50),
salary DECIMAL(10,2),
commission DECIMAL(10,2),
job VARCHAR(50),
deptno INT,
hiredate DATE,
company_name VARCHAR(100),
FOREIGN KEY (deptno) REFERENCES tbl_department(deptno)
);

📝 插入示例数据

INSERT INTO tbl_department (deptno, dept_name) VALUES
(10, 'Sales'),
(20, 'HR'),
(30, 'IT'),
(40, 'Finance'),
(50, 'Marketing');
INSERT INTO tbl_emp (emp_id, emp_name, street_address, city, salary, commission, job, deptno, hiredate, company_name) VALUES
(1, 'Amit Sharma', '123 MG Road', 'Delhi', 12000.00, 500.00, 'Salesman', 10, '2015-06-10', 'First Bank Corporation'),
(2, 'Priya Verma', '456 Park Avenue', 'Mumbai', 25000.00, 2000.00, 'Manager', 20, '2012-08-15', 'First Bank Corporation'),
(3, 'Rajesh Kumar', '789 Green Street', 'Bangalore', 18000.00, 1500.00, 'Salesman', 30, '2016-02-20', 'Second Bank Corporation'),
(4, 'Anjali Gupta', '321 Ocean Drive', 'Chennai', 9500.00, 0.00, 'Clerk', 10, '2018-07-10', 'First Bank Corporation'),
(5, 'Vikram Singh', '654 Palm Street', 'Delhi', 22000.00, 3000.00, 'Salesman', 10, '2014-04-01', 'First Bank Corporation'),
(6, 'Rina Patel', '987 Sunset Boulevard', 'Ahmedabad', 13000.00, 0.00, 'Clerk', 20, '2017-05-23', 'First Bank Corporation'),
(7, 'Manoj Desai', '852 Elm Street', 'Mumbai', 30000.00, 3500.00, 'Manager', 30, '2010-01-12', 'Second Bank Corporation'),
(8, 'Sonia Reddy', '741 Maple Lane', 'Hyderabad', 15000.00, 1000.00, 'Salesman', 30, '2019-11-02', 'First Bank Corporation'),
(9, 'Sandeep Jain', '258 High Street', 'Kolkata', 20000.00, 2500.00, 'Manager', 40, '2011-03-22', 'First Bank Corporation'),
(10, 'Neha Kapoor', '963 River Road', 'Pune', 27000.00, 1500.00, 'Clerk', 50, '2013-09-14', 'Second Bank Corporation');

🔍 高级SQL子查询练习

1. First Bank Corporation中收入超过10,000美元的员工

SELECT * FROM tbl_emp
WHERE company_name = 'First Bank Corporation' AND salary > 10000;

2. 选择部门30中的员工

SELECT * FROM tbl_emp
WHERE deptno = 30;

3. 列出所有文员的姓名、编号和部门

SELECT emp_name, emp_id, deptno FROM tbl_emp
WHERE job = 'Clerk';

4. 部门编号大于20的员工的部门编号和名称

SELECT DISTINCT d.deptno, d.dept_name
FROM tbl_emp e
JOIN tbl_department d ON e.deptno = d.deptno
WHERE d.deptno > 20;

5. 佣金大于工资的员工

SELECT * FROM tbl_emp
WHERE commission > salary;

6. 佣金大于工资60%的员工

SELECT * FROM tbl_emp
WHERE commission > salary * 0.6;

7. 列出部门20中收入超过2000的所有员工的姓名、工作和工资

SELECT emp_name, job, salary FROM tbl_emp
WHERE deptno = 20 AND salary > 2000;

8. 部门30中工资超过1,500美元的销售员

SELECT * FROM tbl_emp
WHERE deptno = 30 AND job = 'Salesman' AND salary > 1500;

9. 经理或总裁的员工

SELECT * FROM tbl_emp
WHERE job='Manager' or job='President';

10. 不在部门30的经理

SELECT * FROM tbl_emp
WHERE job = 'Manager' AND deptno != 30;

11. 部门10中的经理和文员

SELECT * FROM tbl_emp
WHERE deptno = 10 AND job IN ('Manager', 'Clerk');

12. 经理（任何部门）和部门20中的文员

SELECT * FROM tbl_emp
WHERE job = 'Manager' OR (job = 'Clerk' AND deptno = 20);

13. 查找部门10中所有经理的详细信息、部门20中所有文员的详细信息，以及既不是经理也不是文员但工资大于等于2000的所有员工

SELECT * FROM tbl_emp
WHERE (job = 'Manager' AND deptno = 10)
OR (job = 'Clerk' AND deptno = 20)
OR (job NOT IN ('Manager', 'Clerk') AND salary >= 2000);

14. 查找部门20中既不是经理也不是文员的任何人姓名

SELECT emp_name FROM tbl_emp
WHERE deptno = 20 AND job NOT IN ('Manager', 'Clerk');

15. 收入在1200到1400之间的员工

SELECT * FROM tbl_emp
WHERE salary BETWEEN 1200 AND 1400;

16. 查找是文员、分析师或销售员的员工

SELECT * FROM tbl_emp
WHERE job IN ('Clerk', 'Analyst', 'Salesman');

17. 查找不是文员、分析师或销售员的员工

SELECT * FROM tbl_emp
WHERE job NOT IN ('Clerk', 'Analyst', 'Salesman');

18. 查找工资大于2000且佣金大于200的员工

SELECT * FROM tbl_emp
WHERE salary > 2000 AND commission > 200;

19. 查找工资大于2000或佣金大于200的员工

SELECT * FROM tbl_emp
WHERE salary > 2000 OR commission > 200;

20. 查找工资大于2000且佣金大于200的员工，或者工资大于2000且佣金为0的员工

SELECT * FROM tbl_emp
WHERE (salary > 2000 AND commission > 200)
OR (salary > 2000 AND commission = 0);

21. 查找姓名以'A'开头的员工

SELECT emp_name
FROM tbl_emp
WHERE emp_name LIKE 'A%';

22. 查找姓名以'A'结尾的员工

SELECT emp_name
FROM tbl_emp
WHERE emp_name LIKE '%A';

23. 查找姓名以'M'开头或结尾的所有员工

SELECT emp_name
FROM tbl_emp
WHERE emp_name LIKE 'M%' OR emp_name LIKE '%M';

24. 查找姓名中包含字母'M'的所有员工（不区分大小写）

SELECT emp_name
FROM tbl_emp
WHERE emp_name LIKE '%M%' OR emp_name LIKE '%m%';

25. 查找姓名最多15个字符且姓名第3个字符为'R'的所有员工

SELECT emp_name
FROM tbl_emp
WHERE LENGTH(emp_name) <= 15 AND emp_name LIKE '__R%';

26. 查找在2月份（任何年份）被雇佣的所有员工

SELECT emp_name
FROM tbl_emp
WHERE MONTH(hiredate) = 2;

27. 查找在月末被雇佣的所有员工

SELECT emp_name
FROM tbl_emp
WHERE LAST_DAY(hiredate) = hiredate;

28. 查找2年多前被雇佣的所有员工

SELECT emp_name
FROM tbl_emp
WHERE hiredate < CURDATE() - INTERVAL 2 YEAR;

29. 查找2003年被雇佣的经理

SELECT emp_name
FROM tbl_emp
WHERE job = 'Manager' AND YEAR(hiredate) = 2003;

30. 显示所有员工的姓名和工作，用空格分隔

SELECT CONCAT(emp_name, ' ', job) AS name_and_job
FROM tbl_emp;

31. 显示所有员工的姓名，右对齐到15个字符

SELECT LPAD(emp_name, 15, ' ') FROM tbl_emp;

*32. 显示所有员工的姓名，用''右填充到15个字符

SELECT RPAD(emp_name, 15, '*') FROM tbl_emp;

33. 显示所有员工的姓名，去掉前导'A'

SELECT TRIM(LEADING 'A' FROM emp_name) FROM tbl_emp;

34. 显示所有员工的姓名，去掉尾随'R'

SELECT TRIM(TRAILING 'R' FROM emp_name) FROM tbl_emp;

35. 显示所有员工姓名的前3个和后3个字符

SELECT CONCAT(LEFT(emp_name, 3), RIGHT(emp_name, 3)) AS first_last_chars
FROM tbl_emp;

36. 显示所有员工的姓名，将'A'替换为'a'

SELECT REPLACE(emp_name, 'A', 'a') FROM tbl_emp;

37. 显示所有员工的姓名和姓名中字符串'AR'出现的位置

SELECT emp_name, POSITION('AR' in emp_name) AS position
FROM tbl_emp;

38. 显示所有员工的工资，四舍五入到最接近的1000卢比

SELECT emp_name, ROUND(salary, -3) AS rounded_salary
FROM tbl_emp;

39. 显示员工的姓名、工作和工资，按工作和工资排序

SELECT emp_name, job, salary
FROM tbl_emp
ORDER BY job, salary;

40. 显示员工的姓名、工作和工资，按工作降序排序，在工作内按工资排序

SELECT emp_name, job, salary
FROM tbl_emp
ORDER BY job DESC, salary ASC;

41. 列出已完成1年服务的员工的姓名、部门名称和工资

SELECT e.emp_name, d.dept_name, e.salary
FROM tbl_emp e
JOIN tbl_department d ON e.deptno = d.deptno
WHERE e.hiredate <= DATE_SUB(CURDATE(), INTERVAL 1 YEAR);

42. 列出2003年加入的员工的姓名、部门名称和雇佣日期。按加入日期排序输出

SELECT e.emp_name, d.dept_name, e.hiredate
FROM tbl_emp e
JOIN tbl_department d ON e.deptno = d.deptno
WHERE YEAR(e.hiredate) = 2003
ORDER BY e.hiredate;

🏁 结论

我们SQL子查询系列的第2部分展示了高级查询来提取关键的HR洞察，重点关注用于复杂过滤和比较的子查询。

从分析员工收入到跟踪雇佣日期和姓名模式，这些查询展示了SQL在HR数据管理中的强大功能。

使用这些示例作为基础来构建你自己的查询并推动明智的业务决策。

关键词：SQL、数据库、教程、初学者、子查询、HR数据分析