SQL数据库实战题总结

SQL1 查找最晚入职员工的所有信息

select * from employees
order by hire_date desc limit 0,1

1. ORDER BY按照某个列进行排序，默认为升序，DESC降序
2. 用LIMIT获取具有最高或最低值的前几行，其中第一个参数指定第一个返回记录行的偏移量，第二个参数指定返回记录行的最大数目（从0开始）

SQL2 查找入职员工时间排名倒数第三的员工所有信息

select * from employees
order by hire_date desc limit 2,1

SQL3 查找当前薪水详情以及部门编号dept_no

select salaries.emp_no,salaries.salary,salaries.from_date,salaries.to_date,dept_manager.dept_no
from salaries INNER JOIN dept_manager on salaries.emp_no = dept_manager.emp_no
order by salaries.emp_no

1. INNER JOIN
   与JOIN是相同的
   
2. ON table1.column_name = table2.column_name

SQL4 查找所有已经分配部门的员工的last_name和first_name以及dept_no

select employees.last_name,employees.first_name,dept_emp.dept_no
from employees INNER join dept_emp on employees.emp_no = dept_emp.emp_no

SQL5 查找所有员工的last_name和first_name以及对应部门编号dept_no

SELECT e.last_name, e.first_name, d.dept_no
FROM employees AS e
LEFT OUTER JOIN dept_emp AS d
ON e.emp_no = d.emp_no

LEFT OUTER JOIN
与LEFT JOIN是相同的

从左表返回所有的行，若右表中没有匹配，则返回NULL

SQL7 查找薪水记录超过15次的员工号emp_no以及其对应的记录次数t

select emp_no, COUNT(emp_no) AS t #WHERE 关键字无法与聚合函数一起使用
FROM salaries
GROUP BY emp_no
HAVING t > 15

1. COUNT()
   该函数返回某列的值的数目
2. GROUP BY
   类似于pandas里面的分组函数，按照某一列来对结果集进行分组
3. HAVING
   HAVING和WHERE的作用是差不多的，但是WHERE关键字无法与聚合函数一起使用，都是用来进行筛选数据的。

SQL8 找出所有员工当前薪水salary情况

SELECT salary
FROM salaries
GROUP BY salary
ORDER BY salary DESC

SQL10 获取所有非manager的员工emp_no

SELECT e.emp_no
FROM employees AS e
WHERE emp_no NOT IN (SELECT emp_no FROM dept_manager)

SQL11 获取所有员工当前的manager

SELECT de.emp_no,dm.emp_no
FROM dept_emp as de
INNER JOIN dept_manager AS dm
ON de.dept_no = dm.dept_no
WHERE de.emp_no != dm.emp_no

SQL12 获取每个部门中当前员工薪水最高的相关信息

SELECT r.dept_no,ss.emp_no,r.maxSalary FROM(
SELECT d.dept_no,MAX(s.salary) AS maxSalary FROM dept_emp d,salaries s
    WHERE d.emp_no = s.emp_no
    AND d.to_date = "9999-01-01"
    AND s.to_date = "9999-01-01"
    GROUP BY d.dept_no
)AS r,salaries ss,dept_emp dd
WHERE r.maxSalary = ss.salary
AND r.dept_no = dd.dept_no
AND dd.emp_no = ss.emp_no
AND ss.to_date = "9999-01-01"
AND dd.to_date = "9999-01-01"
ORDER BY r.dept_no ASC

第12题是有些难度的题，难点一个是要构造新的表格，还有是两个表的交叉关系比较难搞。

SQL15 查找employees表emp_no与last_name的员工信息

SELECT * FROM employees
WHERE emp_no % 2 = 1
AND last_name != "Mary"
ORDER BY hire_date DESC

判断是否为奇数：% 2与1比较

SQL16 统计出当前各个title类型对应的员工当前薪水对应的平均工资

SELECT t.title,AVG(s.salary) 
FROM titles AS t
INNER JOIN salaries AS s
ON t.emp_no = s.emp_no
GROUP BY t.title
ORDER BY AVG(s.salary)

SQL17 获取当前薪水第二多的员工的emp_no以及其对应的薪水salary

SELECT s.emp_no,s.salary FROM salaries s
ORDER BY s.salary LIMIT 1,1

上面这种代码可以把10002的这条记录选择出来，但其实这种代码是错误。存在的问题：第一第二名可能会有多个，所以要先分组后筛选。

SELECT emp_no,salary FROM salaries
WHERE salary = (SELECT salary
                FROM salaries
                GROUP BY salary
                ORDER BY salary DESC LIMIT 1,1
)

SQL18 获取当前薪水第二多的员工的emp_no以及其对应的薪水salary

SELECT s.emp_no, s.salary, e.last_name, e.first_name 
FROM employees AS e
INNER JOIN salaries AS s
ON e.emp_no = s.emp_no
WHERE salary = (SELECT MAX(s2.salary) FROM salaries s2
               WHERE s2.salary < 
                (SELECT MAX(salary) FROM salaries))

这道题跟前面要实现的内容其实是一样的，不一样的要求在于这里是不允许使用OREDER BY的，因为要找出薪水第二多的员工信息，第二大即仅次于第一大，可以先用函数MAX找出第一大的。在小于第二大的记录里面挑选出最大的。

SQL19 查找所有员工的last_name和first_name以及对应的dept_name

SELECT a.last_name,a.first_name,dp.dept_name FROM
(SELECT e.emp_no,e.first_name,e.last_name,d.dept_no FROM employees AS e
LEFT OUTER JOIN dept_emp AS d
ON e.emp_no = d.emp_no)AS a
LEFT OUTER JOIN departments AS dp
ON dp.dept_no = a.dept_no

SQL21 查找在职员工自入职以来的薪水涨幅情况（有难度）

SELECT b.emp_no,(b.salary-a.salary) AS growth
from 
(SELECT e.emp_no,s.salary
FROM employees e LEFT JOIN salaries s 
ON e.emp_no = s.emp_no
 AND e.hire_date = s.from_date)
AS a
INNER JOIN
(SELECT e.emp_no,s.salary
FROM employees e LEFT JOIN salaries s 
ON e.emp_no = s.emp_no
 WHERE s.to_date="9999-01-01")
 AS b
 ON a.emp_no = b.emp_no
 ORDER BY growth

on和WHERE的区别

SQL22 统计各个部门的工资记录数

SELECT de.dept_no,de.dept_name,COUNT(r.dept_no)
FROM(
    SELECT s.emp_no,s.salary,d.dept_no FROM salaries s
    LEFT JOIN dept_emp d
    ON s.emp_no = d.emp_no)AS r
LEFT JOIN departments de
ON de.dept_no = r.dept_no
GROUP BY dept_no 
ORDER BY dept_no

SQL23 对所有员工的薪水按照salary降序进行1-N的排名（中等）

SELECT s1.emp_no,s1.salary,COUNT(DISTINCT s2.salary) AS t_rank
FROM salaries s1, salaries s2
WHERE s1.salary <= s2.salary
GROUP BY s1.emp_no
ORDER BY s1.salary DESC

DISTINCT去重

这里用到的方法比较巧妙，是利用两次salaries表格，WHERE >=的意思是：比如说s1.salary = 72527，符合WHERE条件的s2.salary有88958 72527 72527，先DISTINCT去重，然后再COUNT即排名第二。

SQL29 使用join查询方式找出没有分类的电影id以及名称

SELECT f.film_id,f.title FROM film f
LEFT JOIN film_category fc
ON f.film_id = fc.film_id
WHERE fc.category_id is NULL

SQL26 汇总各个部门当前员工的title类型的分配数目(困难)

SELECT de.dept_no, dp.dept_name, t.title, COUNT(t.title) AS count
FROM titles AS t INNER JOIN dept_emp AS de 
ON t.emp_no = de.emp_no AND de.to_date = '9999-01-01' AND t.to_date = '9999-01-01'
INNER JOIN departments AS dp 
ON de.dept_no = dp.dept_no
GROUP BY de.dept_no, t.title

这道题其实不难，无非是几个表的join，加上各种条件，但是选择Mysql的时候会报错，这里是Sqite

SQL30 使用子查询的方式找出属于Action分类的所有电影对应的title,description

SELECT f.title,f.description 
FROM film f
WHERE f.film_id in(
   SELECT fc.film_id FROM category c
   INNER JOIN film_category fc
   ON c.category_id = fc.category_id
   WHERE c.name = "Action")

SQL32 将employees表的所有员工的last_name和first_name拼接起来作为Name

SELECT CONCAT(last_name," ",first_name) AS Name 
FROM employees

SQL33 创建一个actor表，包含如下列信息

CREATE TABLE actor
(actor_id smallint(5) NOT NULL,
 first_name varchar(45) NOT NULL,
 last_name varchar(45) NOT NULL,
 last_update date NOT NULL,
 PRIMARY KEY(actor_id)
)

SQL34 批量插入数据

INSERT INTO actor(actor_id,
                  first_name,
                  last_name,
                  last_update)
VALUES(1,'PENELOPE','GUINESS','2006-02-15 12:34:33'),
      (2,'NICK','WAHLBERG','2006-02-15 12:34:33');

SQL35 批量插入数据，不使用replace操作

INSERT IGNORE INTO actor VALUES('3','ED','CHASE','2006-02-15 12:34:33') 

SQL36 创建一个actor_name表

CREATE table IF NOT EXISTS actor_name(
first_name varchar(45) NOT NULL,
last_name varchar(45) NOT NULL
)
SELECT first_name,last_name
FROM actor

SQL37 对first_name创建唯一索引uniq_idx_firstname

CREATE UNIQUE INDEX uniq_idx_firstname
ON actor(first_name);
CREATE INDEX idx_lastname
ON actor(last_name)

SQL38 针对actor表创建视图actor_name_view

CREATE VIEW actor_name_view
AS
SELECT first_name AS first_name_v, last_name AS last_name_v
FROM actor;

在 SQL 中，视图是基于 SQL 语句的结果集的可视化的表。
视图包含行和列，就像一个真实的表。视图中的字段就是来自一个或多个数据库中的真实的表中的字段。我们可以向视图添加 SQL 函数、WHERE 以及 JOIN 语句，我们也可以提交数据，就像这些来自于某个单一的表。

SQL39 针对上面的salaries表emp_no字段创建强制索引idx_emp_no

SELECT *
FROM salaries
FORCE INDEX (idx_emp_no)
WHERE emp_no = 10005

FORCE INDEX后面跟的是索引名

SQL40 在last_update后面新增加一列名字为create_date

ALTER TABLE actor 
ADD create_date datetime NOT NULL DEFAULT '2020-10-01 00:00:00' AFTER last_update

SQL41 构造一个触发器audit_log

CREATE trigger audit_log
AFTER INSERT ON employees_test
FOR EACH ROW
BEGIN
    INSERT INTO audit values(new.id,new.name);
END

创建触发器语法如下：
CREATE TRIGGER trigger_name
trigger_time trigger_event ON tbl_name
FOR EACH ROW
trigger_stmt
其中：

• trigger_name：标识触发器名称，用户自行指定；
• trigger_time：标识触发时机，取值为 BEFORE 或 AFTER；
• trigger_event：标识触发事件，取值为 INSERT、UPDATE 或 DELETE；
• tbl_name：标识建立触发器的表名，即在哪张表上建立触发器；
• trigger_stmt：触发器程序体，可以是一句SQL语句，或者用 BEGIN 和 END 包含的多条语句，每条语句结束要分号结尾。

在 INSERT 型触发器中，NEW 用来表示将要（BEFORE）或已经（AFTER）插入的新数据；
在 UPDATE 型触发器中，OLD 用来表示将要或已经被修改的原数据，NEW 用来表示将要或已经修改为的新数据；
在 DELETE 型触发器中，OLD 用来表示将要或已经被删除的原数据；
使用方法： NEW.columnName （columnName 为相应数据表某一列名）

SQL42 删除emp_no重复的记录，只保留最小的id对应的记录。

DELETE FROM titles_test 
WHERE id NOT IN (
    SELECT min_id
    FROM 
    (SELECT MIN(id) AS min_id
    FROM titles_test
    GROUP BY emp_no)t1)

语法：
DELETE FROM
WHERE…

SQL43 将所有to_date为9999-01-01的全部更新为NULL

UPDATE titles_test SET to_date = NULL,from_date = "2001-01-01" WHERE to_date = "9999-01-01"

语法：
UPDATE 表名称 SET 列名称 = 新值 WHERE 列名称 = 某值

SQL44 将id=5以及emp_no=10001的行数据替换成id=5以及emp_no=10005

UPDATE titles_test SET emp_no = REPLACE(emp_no,10001,10005) WHERE id = 5

语法：
UPDATE 表名 SET 列名称 = REPLACE(original-string，search-string，replace-string) WHERE 列名称 = 某值

SQL45 将titles_test表名修改为titles_2017

ALTER TABLE titles_test RENAME TO titles_2017;

语法：
ALTER TABLE old_table RENAME TO new_table

SQL46 在audit表上创建外键约束，其emp_no对应employees_test表的主键id

ALTER TABLE audit ADD CONSTRAINT FOREIGN KEY (emp_no)
REFERENCES employees_test(id)

外键 (FK) 是用于建立和加强两个表数据之间的链接的一列或多列。通过将保存表中主键值的一列或多列添加到另一个表中，可创建两个表之间的链接。这个列就成为第二个表的外键。

语法：
ALTER TABLE 从表 ADD CONSTRAINT FOREIGN KEY (外键字段) REFERENCES 主表(主键字段)

SQL48 将所有获取奖金的员工当前的薪水增加10%

UPDATE salaries SET salary = salary * 1.1 WHERE emp_no IN (
    SELECT emp_no FROM emp_bonus
)AND to_date="9999-01-01"

SQL50 将employees表中的所有员工的last_name和first_name通过引号连接起来

SELECT concat(last_name,"'",first_name) FROM employees

SQL51 查找字符串 10,A,B 中逗号,出现的次数cnt

SELECT (length("10,A,B")-length(replace("10,A,B",",","")))
AS cnt

SQL52 获取Employees中的first_name

SELECT first_name FROM employees
ORDER BY RIGHT(first_name,2)

SQL53 按照dept_no进行汇总

SELECT dept_no, group_concat(emp_no) AS employees
FROM dept_emp
GROUP BY dept_no

聚合函数group_concat（X，Y），其中X是要连接的字段，Y是连接时用的符号，可省略，默认为逗号。

SQL54 平均工资

SELECT AVG(salary) AS avg_salary FROM salaries
WHERE salary NOT IN(SELECT MAX(salary) FROM salaries WHERE to_date = '9999-01-01')
AND salary NOT IN (SELECT MIN(salary) FROM salaries WHERE to_date = '9999-01-01')
AND to_date = "9999-01-01"

SELECT后面只能选择一列

SQL55 分页查询employees表，每5行一页，返回第2页的数据

SELECT * FROM employees LIMIT 5 OFFSET 5

SQL57 使用含有关键字exists查找未分配具体部门的员工的所有信息。

SELECT * FROM employees e
WHERE NOT EXISTS
(SELECT emp_no FROM dept_emp d WHERE d.emp_no=e.emp_no)

EXISTS语句：执行table_name.length次，指定一个子查询，检测行的存在。遍历循环外表，然后看外表中的记录有没有和内表的数据一样的。匹配上就将结果放入结果集中。
IN 语句：只执行一次

SQL59 获取有奖金的员工相关信息。（有难度）

SELECT e.emp_no,e.first_name,e.last_name,eb.btype,s.salary,(
    CASE btype
    WHEN 1 THEN salary*0.1
    WHEN 2 THEN salary*0.2
    ELSE salary*0.3
END) AS bonus
FROM employees e
JOIN salaries s ON e.emp_no = s.emp_no
JOIN emp_bonus eb ON e.emp_no = eb.emp_no
WHERE s.to_date = "9999-01-01"

CASE
WHEN condition1 THEN result1
WHEN condition2 THEN result2
WHEN conditionN THEN resultN
ELSE result
END;
如果没有条件是符合的，则返回ELSE子句部分的结果，如果没有ELSE部分，则返回NULL。

SQL60 统计salary的累计和running_total（较难）

SELECT s1.emp_no, s1.salary,
(SELECT SUM(s2.salary) FROM salaries s2
WHERE s2.emp_no <= s1.emp_no
AND s2.to_date = "9999-01-01") AS running_total
FROM salaries s1
WHERE s1.to_date = "9999-01-01"
ORDER BY s1.emp_no

难点在于：running_total是计算前面累积的salary，方法仍然是表的复用，这个在排序的时候经常用到。

SQL61 对于employees表中，给出奇数行的first_name（较难）

SELECT first_name FROM employees e1
WHERE (SELECT count(first_name) FROM employees e2
      WHERE e1.first_name >= e2.first_name) %2=1

SQL62 出现三次以上相同积分的情况

SELECT number FROM grade
GROUP BY number
HAViNG COUNT(number) >=3

WHERE不能和聚合函数一起使用

SQL63 刷题通过的题目排名

SELECT id,number,dense_rank()over(ORDER BY number DESC) AS t_rank
FROM passing_number

row_number 不存在并列
dense_rank 和rank存在并列，但rank很跳。

SQL64 找到每个人的任务

SELECT p.id,p.name,t.content
FROM person AS p
LEFT JOIN task AS t
ON p.id = t.person_id
ORDER BY p.id

SQL66 牛客每个人最近的登录日期(一)

SELECT user_id,MAX(date) AS d
FROM login
GROUP BY user_id
ORDER BY user_id

SQL72 考试分数(一)

SELECT job,ROUND(AVG(score),3) AS avg
FROM grade
GROUP BY job
ORDER BY AVG(score) DESC

SQL73 考试分数(二)

SELECT id,job,score
FROM grade
WHERE score > (SELECT avg(score) FROM grade g1 WHERE g1.job = grade.job)
ORDER BY id

SQL77 牛客的课程订单分析(一)

SELECT * FROM order_info
WHERE product_name IN ("C++","Java","Python")
AND date > "2025-10-15"
AND status = "completed"
ORDER BY id

SQL78 牛客的课程订单分析(二)

SELECT user_id FROM order_info
WHERE date > '2025-10-15'
AND status = "completed"
AND product_name IN ("C++","java","Python")
GROUP BY user_id
HAVING count(id) >= 2
ORDER BY user_id

SQL79 牛客的课程订单分析(三)

SELECT * FROM order_info
WHERE date > '2025-10-15'
AND status = "completed"
AND product_name IN ("C++","Java","Python")
AND user_id IN(
    SELECT user_id FROM order_info
    WHERE date > '2025-10-15'
    AND status = "completed"
    AND product_name IN ("C++","Java","Python")
    GROUP BY user_id
    HAVING COUNT(DISTINCT product_name) >= 2
)
ORDER BY id

用完GROUP BY函数之后，只会出现不同user_id的第一条信息，但是本题里面是需要出现符合条件的user_id的所有信息。

SQL82 牛客的课程订单分析(六)

SELECT o.id,is_group_buy,name AS client_name
FROM order_info o
LEFT JOIN client c 
ON o.client_id = c.id
WHERE user_id IN(
SELECT user_id FROM order_info o
WHERE date > '2025-10-15'
AND status = "completed"
AND product_name IN ("C++","Java","Python")
GROUP BY user_id
HAVING COUNT(o.id)>1)
AND date > '2025-10-15'
AND status = "completed"
AND product_name IN ("C++","Java","Python")
ORDER BY o.id

SQL84 实习广场投递简历分析(一)

SELECT job,SUM(num) AS cnt
FROM resume_info
WHERE date < "2026-01-01"
GROUP BY job
ORDER BY cnt DESC

SQL85 实习广场投递简历分析(二)

SELECT job,LEFT(date,7) AS mon,SUM(num) AS cnt
FROM resume_info
WHERE date < "2026-01-01"
GROUP BY job,mon
ORDER BY mon DESC,cnt DESC