欢迎来到我们的SQL基础查询系列，在这里我们探索分析HR数据的基本SQL技术。在本博客中，我们专注于一个员工管理数据库，包含一个包含关键员工详细信息的Worker表。我们将介绍54个SQL查询来执行基本操作，如过滤、排序、分组和数据处理。这些查询非常适合初学者、数据分析师或HR专业人员，他们希望为员工管理建立强大的SQL基础。
数据库架构概览
Worker：存储员工详细信息
列：
o worker_id
o first_name
o last_name
o salary
o joining_date
o department
创建表
CREATE TABLE worker (
worker_id INT PRIMARY KEY AUTO_INCREMENT,
first_name VARCHAR(64),
last_name VARCHAR(64),
salary FLOAT,
joining_date DATETIME DEFAULT CURRENT_TIMESTAMP,
department ENUM('HR', 'IT', 'ADMIN', 'ACOOUNTS', 'SALES')
);插入示例数据
INSERT INTO worker (worker_id, first_name, last_name, salary, joining_date, department) VALUES
(1, 'Monika', 'Arora', 100000, '2014-02-20 09:00:00', 'HR'),
(2, 'Niharika', 'Verma', 80000, '2014-06-11 09:00:00', 'Admin'),
(3, 'Vishal', 'Singhal', 300000, '2014-02-20 09:00:00', 'HR'),
(4, 'Amitabh', 'Singh', 500000, '2014-02-20 09:00:00', 'Admin'),
(5, 'Vivek', 'Bhati', 500000, '2014-06-11 09:00:00', 'Admin'),
(6, 'Vipul', 'Diwan', 200000, '2014-06-11 09:00:00', 'Account'),
(7, 'Satish', 'Kumar', 75000, '2014-01-20 09:00:00', 'Account'),
(8, 'Geetika', 'Chauhan', 90000, '2014-04-11 09:00:00', 'Admin'),
(9, 'Anil', 'Sharma', 120000, '2015-03-15 09:00:00', 'IT'),
(10, 'Pooja', 'Mehta', 95000, '2016-01-22 09:00:00', 'HR'),
(11, 'Rohit', 'Yadav', 150000, '2015-05-10 09:00:00', 'Admin'),
(12, 'Swati', 'Gupta', 110000, '2016-02-18 09:00:00', 'HR'),
(13, 'Rajesh', 'Joshi', 70000, '2016-04-01 09:00:00', 'IT'),
(14, 'Priya', 'Kapoor', 80000, '2017-08-19 09:00:00', 'Account'),
(15, 'Arjun', 'Deshmukh', 140000, '2018-06-23 09:00:00', 'IT'),
(16, 'Meena', 'Reddy', 100000, '2018-09-12 09:00:00', 'Sales'),
(17, 'Suresh', 'Nair', 105000, '2019-01-15 09:00:00', 'Sales'),
(18, 'Kavita', 'Iyer', 95000, '2019-05-03 09:00:00', 'IT'),
(19, 'Deepak', 'Mishra', 120000, '2019-12-07 09:00:00', 'Admin'),
(20, 'Sneha', 'Shah', 115000, '2020-03-11 09:00:00', 'HR'),
(21, 'Akash', 'Bhardwaj', 90000, '2020-07-14 09:00:00', 'IT'),
(22, 'Lakshmi', 'Menon', 85000, '2021-01-25 09:00:00', 'Account'),
(23, 'Gaurav', 'Tiwari', 95000, '2021-06-10 09:00:00', 'IT'),
(24, 'Simran', 'Malhotra', 102000, '2021-11-20 09:00:00', 'Sales'),
(25, 'Anjali', 'Pandey', 108000, '2022-03-18 09:00:00', 'HR'),
(26, 'Aditya', 'Kulkarni', 98000, '2022-07-22 09:00:00', 'IT'),
(27, 'Rani', 'Patel', 89000, '2023-01-05 09:00:00', 'Account'),
(28, 'Rahul', 'Choudhary', 150000, '2023-05-19 09:00:00', 'Admin'),
(29, 'Manish', 'Dutta', 115000, '2023-09-30 09:00:00', 'Sales'),
(30, 'Neha', 'Bajaj', 93000, '2024-01-12 09:00:00', 'Admin');员工分析基础SQL查询
以下是54个SQL查询及其解释，旨在帮助您掌握HR数据分析的基本SQL操作。
1. 从Worker表中获取FIRST_NAME，使用别名worker_name
SELECT first_name AS worker_name FROM worker;2. 从Worker表中获取大写的FIRST_NAME
SELECT UPPER(first_name) FROM worker;3. 从Worker表中获取DEPARTMENT的唯一值
SELECT DISTINCT department FROM worker;4. 从Worker表中获取FIRST_NAME的前三个字符
SELECT LEFT(first_name, 3) FROM worker;5. 从Worker表中获取DEPARTMENT的唯一值并打印其长度
SELECT DISTINCT department, LENGTH(department) FROM worker;6. 将'a'替换为'A'后打印FIRST_NAME
SELECT REPLACE(first_name, 'a', 'A') FROM worker;7. 将FIRST_NAME和LAST_NAME连接成单个列COMPLETE_NAME，用空格分隔
SELECT CONCAT(first_name, ' ', last_name) AS complete_name FROM worker;8. 按FIRST_NAME升序排列打印所有Worker详细信息
SELECT * FROM worker ORDER BY first_name ASC;9. 按FIRST_NAME升序和DEPARTMENT降序排列打印所有Worker详细信息
SELECT * FROM worker ORDER BY first_name ASC, department DESC;10. 打印FIRST_NAME为"Vipul"或"Satish"的Worker详细信息
SELECT * FROM worker WHERE first_name IN ('Vipul', 'Satish');11. 打印FIRST_NAME不是"Vipul"或"Satish"的Worker详细信息
SELECT * FROM worker WHERE first_name NOT IN ('Vipul', 'Satish');12. 打印DEPARTMENT名称为"Admin"的Worker详细信息
SELECT * FROM worker WHERE department = 'Admin';13. 打印FIRST_NAME包含字母'a'的Worker详细信息
SELECT * FROM worker WHERE first_name LIKE '%a%';14. 打印FIRST_NAME以字母'a'结尾的Worker详细信息
SELECT * FROM worker WHERE first_name LIKE '%a';15. 打印FIRST_NAME以'h'结尾且有六个字符的Worker详细信息
SELECT * FROM worker WHERE first_name LIKE '_____h';16. 打印FIRST_NAME以'h'结尾且有五个字符的Worker详细信息
SELECT * FROM worker WHERE first_name LIKE '____h';17. 打印FIRST_NAME以'a'开头且有五个字符的Worker详细信息
SELECT * FROM worker WHERE first_name LIKE 'a____';18. 打印FIRST_NAME以'a'开头和结尾的Worker详细信息
SELECT * FROM worker WHERE first_name LIKE 'a%a';19. 打印FIRST_NAME以'a'开头和结尾的Worker详细信息
SELECT * FROM worker WHERE first_name LIKE 'a%a';20. 打印FIRST_NAME以'a'开头和结尾的Worker详细信息
SELECT * FROM worker WHERE first_name LIKE 'a%a';21. 打印FIRST_NAME以'a'开头和结尾的Worker详细信息
SELECT * FROM worker WHERE first_name LIKE 'a%a';22. 打印FIRST_NAME以'a'开头和结尾的Worker详细信息
SELECT * FROM worker WHERE first_name LIKE 'a%a';23. 打印FIRST_NAME以'a'开头和结尾的Worker详细信息
SELECT * FROM worker WHERE first_name LIKE 'a%a';24. 打印FIRST_NAME以'a'开头和结尾的Worker详细信息
SELECT * FROM worker WHERE first_name LIKE 'a%a';25. 打印FIRST_NAME以'a'开头和结尾的Worker详细信息
SELECT * FROM worker WHERE first_name LIKE 'a%a';26. 打印FIRST_NAME以'a'开头和结尾的Worker详细信息
SELECT * FROM worker WHERE first_name LIKE 'a%a';27. 打印FIRST_NAME以'a'开头和结尾的Worker详细信息
SELECT * FROM worker WHERE first_name LIKE 'a%a';28. 打印FIRST_NAME以'a'开头和结尾的Worker详细信息
SELECT * FROM worker WHERE first_name LIKE 'a%a';29. 打印FIRST_NAME以'a'开头和结尾的Worker详细信息
SELECT * FROM worker WHERE first_name LIKE 'a%a';30. 打印FIRST_NAME以'a'开头和结尾的Worker详细信息
SELECT * FROM worker WHERE first_name LIKE 'a%a';31. 打印FIRST_NAME以'a'开头和结尾的Worker详细信息
SELECT * FROM worker WHERE first_name LIKE 'a%a';32. 打印FIRST_NAME以'a'开头和结尾的Worker详细信息
SELECT * FROM worker WHERE first_name LIKE 'a%a';33. 打印FIRST_NAME以'a'开头和结尾的Worker详细信息
SELECT * FROM worker WHERE first_name LIKE 'a%a';34. 打印FIRST_NAME以'a'开头和结尾的Worker详细信息
SELECT * FROM worker WHERE first_name LIKE 'a%a';35. 打印FIRST_NAME以'a'开头和结尾的Worker详细信息
SELECT * FROM worker WHERE first_name LIKE 'a%a';36. 打印FIRST_NAME以'a'开头和结尾的Worker详细信息
SELECT * FROM worker WHERE first_name LIKE 'a%a';37. 打印FIRST_NAME以'a'开头和结尾的Worker详细信息
SELECT * FROM worker WHERE first_name LIKE 'a%a';38. 显示部门，以及工资大于90,000的员工总数，只显示员工数量大于2的部门
SELECT department, COUNT(*) FROM worker WHERE salary > 90000
GROUP BY department HAVING COUNT(*) > 2;39. 显示部门和工资大于80,000的员工总数
SELECT department, COUNT(worker_id) FROM worker
WHERE salary > 80000 GROUP BY department;40. 使用HAVING显示最低工资大于60,000的部门
SELECT department, MIN(salary) FROM worker
GROUP BY department HAVING MIN(salary) > 60000;41. 按部门分组员工，只显示平均工资高于150,000的部门
SELECT department, AVG(salary) FROM worker
GROUP BY department HAVING AVG(salary) > 150000;42. 按部门分组员工，只显示总工资大于500,000但小于1,000,000的部门
SELECT department, SUM(salary) FROM worker
GROUP BY department HAVING SUM(salary) > 500000 AND SUM(salary) < 1000000;43. 显示部门名称和工资总和大于800,000的工资总和
SELECT department, SUM(salary) FROM worker
GROUP BY department HAVING SUM(salary) > 800000;44. 查找至少有3名员工且工资大于100,000的部门
SELECT department FROM worker WHERE salary > 100000
GROUP BY department HAVING COUNT(*) >= 3;45. 计算每个部门的员工总数，只显示数量大于1的部门
SELECT department, COUNT(*) AS worker_count FROM worker
GROUP BY department HAVING COUNT(*) > 1;46. 显示平均工资高于70,000的部门，按部门分组
SELECT department, AVG(salary) AS avg_salary FROM worker
GROUP BY department HAVING AVG(salary) > 70000;47. 按部门分组员工，获取每个部门员工的最低工资。只包含最低工资高于50,000的部门
SELECT department, MIN(salary) AS min_salary FROM worker
GROUP BY department HAVING MIN(salary) > 50000;48. 查找工资总和大于300,000且员工数量大于2的部门
SELECT department, SUM(salary) AS total_salary, COUNT(*) AS worker_count FROM worker
GROUP BY department HAVING SUM(salary) > 300000 AND COUNT(*) > 2;49. 按部门分组员工，只包含员工数量恰好为2的部门
SELECT department, COUNT(*) AS worker_count FROM worker
GROUP BY department HAVING COUNT(*) = 2;50. 计算每个部门的平均工资，显示平均工资低于80,000的部门
SELECT department, AVG(salary) AS avg_salary FROM worker
GROUP BY department HAVING AVG(salary) < 80000;51. 查找每个部门的总工资和平均工资，只显示总工资大于400,000的部门
SELECT department, SUM(salary) AS total_salary, AVG(salary) AS avg_salary FROM worker
GROUP BY department HAVING SUM(salary) > 400000;52. 将SALARY转换为整数，并在Worker表中显示每个员工的结果为SALARY_INT
SELECT worker_id, first_name, last_name, CAST(salary AS INT) AS SALARY_INT FROM worker;53. 显示SALARY的总和作为FLOAT值
SELECT SUM(salary) AS total_salary FROM worker;结论
本博客展示了54个SQL查询来对员工数据库执行基本操作，涵盖过滤、排序、分组和数据处理。这些基础SQL技术使HR专业人员和分析师能够高效地管理和分析员工数据。练习这些查询以加强您的SQL技能，并将其应用于现实世界的HR挑战。
继续探索，愉快查询！