刷题题库
按推荐度从上往下排
- 牛客网：https://www.nowcoder.com/activity/oj
- HackerRank：https://www.hackerrank.com/challenges/revising-the-select-query/problem
- sqlbolt：https://sqlbolt.com/
- LeeCode：https://leetcode-cn.com/problemset/database/
牛客网
入门级别
#SQL1
查找最晚入职员工的所有信息，为了减轻入门难度，目前所有的数据里员工入职的日期都不是同一天(sqlite里面的注释为--,mysql为comment)
CREATE TABLE `employees` (
`emp_no` int(11) NOT NULL, -- '员工编号'
`birth_date` date NOT NULL,
`first_name` varchar(14) NOT NULL,
`last_name` varchar(16) NOT NULL,
`gender` char(1) NOT NULL,
`hire_date` date NOT NULL,
PRIMARY KEY (`emp_no`));
#解一
SELECT *
FROM employees
ORDER BY hire_date DESC
LIMIT 1;
#解二
SELECT *
FROM employees
WHERE hire_date=(
SELECT MAX(hire_date)
FROM employees
);
简单级别
#SQL2
查找入职员工时间排名倒数第三的员工所有信息，为了减轻入门难度，目前所有的数据里员工入职的日期都不是同一天
CREATE TABLE `employees` (
`emp_no` int(11) NOT NULL,
`birth_date` date NOT NULL,
`first_name` varchar(14) NOT NULL,
`last_name` varchar(16) NOT NULL,
`gender` char(1) NOT NULL,
`hire_date` date NOT NULL,
PRIMARY KEY (`emp_no`));
select *
from employees
order by hire_date desc
limit 2,1;
#SQL4
查找所有已经分配部门的员工的last_name和first_name以及dept_no(请注意输出描述里各个列的前后顺序)
CREATE TABLE `dept_emp` (
`emp_no` int(11) NOT NULL,
`dept_no` char(4) NOT NULL,
`from_date` date NOT NULL,
`to_date` date NOT NULL,
PRIMARY KEY (`emp_no`,`dept_no`));
CREATE TABLE `employees` (
`emp_no` int(11) NOT NULL,
`birth_date` date NOT NULL,
`first_name` varchar(14) NOT NULL,
`last_name` varchar(16) NOT NULL,
`gender` char(1) NOT NULL,
`hire_date` date NOT NULL,
PRIMARY KEY (`emp_no`));
select e.last_name,e.first_name,d.dept_no
from dept_emp d
join employees e
on d.emp_no=e.emp_no;
#这道题考的是求两个表的并集，只是表述和下面输出描述的省略很容易给自己挖坑，省略不是null。
#SQL7
查找薪水变动超过15次的员工号emp_no以及其对应的变动次数t
CREATE TABLE `salaries` (
`emp_no` int(11) NOT NULL,
`salary` int(11) NOT NULL,
`from_date` date NOT NULL,
`to_date` date NOT NULL,
PRIMARY KEY (`emp_no`,`from_date`));
SELECT emp_no,COUNT(salary) t
FROM salaries
GROUP BY emp_no
HAVING t>15;
#SQL15
请你查找employees表所有emp_no为奇数，且last_name不为Mary的员工信息，并按照hire_date逆序排列
select *
from employees
where emp_no % 2 =1
and last_name not in ('Mary')
#Mary这里不要忘记加上''，也可以用！=
order by hire_date desc;
#SQL17
请你获取薪水第二多的员工的emp_no以及其对应的薪水salary
select emp_no,salary
from salaries
order by salary desc
limit 1,1;
#SQL32
将employees表的所有员工的last_name和first_name拼接起来作为Name，中间以一个空格区分
select concat(last_name,' ',first_name) as Name
from employees;
#SQL34
请你对于表actor批量插入如下数据(不能有2条insert语句哦!)
insert into actor
value (1,'PENELOPE',"GUINESS",'2006-02-15 12:34:33'),
(2,'NICK',"WAHLBERG",'2006-02-15 12:34:33');
#SQL42
删除emp_no重复的记录，只保留最小的id对应的记录。
DELETE
FROM titles_test
WHERE id NOT IN ( #注意是删除，所以用not in
SELECT *
FROM(
SELECT MIN(id)
FROM titles_test
GROUP BY emp_no
) AS a
);
#SQL43
将所有to_date为9999-01-01的全部更新为NULL,且 from_date更新为2001-01-01。
update titles_test
set to_date=null,from_date='2001-01-01'
where to_date='9999-01-01';
#SQL44
将id=5以及emp_no=10001的行数据替换成id=5以及emp_no=10005,其他数据保持不变，使用replace实现，直接使用update会报错。
replace into titles_test (id, emp_no, title, from_date, to_date)
values( 5, '10005', 'Senior Engineer', '1986-06-26', '9999-01-01');
#SQL45
将titles_test表名修改为titles_2017。
alter table titles_test
rename to titles_2017;
#SQL62
让你写一个sql查询，积分表里面出现三次以及三次以上的积分
select number
from grade
group by number
having count(number)>=3;
#SQL64
请你找到每个人的任务情况，并且输出出来，没有任务的也要输出，而且输出结果按照person的id升序排序
select p.id,p.name,t.content
from person p
left join task t
on p.id=t.person_id #注意person里的id对应task的person_id
order by p.id;
#SQL66
请你写出一个sql语句查询每个用户最近一天登录的日子，并且按照user_id升序排序
SELECT user_id,MAX(date) AS d
FROM login
GROUP BY user_id
order by user_id;
#SQL72
请你写一个sql语句查询各个岗位分数的平均数，并且按照分数降序排序，结果保留小数点后面3位(3位之后四舍五入)
select job,round(avg(score),3) avg
from grade
group by job
order by avg desc;
#SQL77
请你写出一个sql语句查询在2025-10-15以后状态为购买成功的C++课程或者Java课程或者Python的订单，并且按照order_info的id升序排序，
select id,user_id,product_name,status,client_id,date
from order_info
where date > '2025-10-15'
and product_name in ('C++','Java','Python')
and status='completed'
order by id ;
#SQL84
请你写出SQL语句查询在2025年内投递简历的岗位和数量，并且按数量降序排序
select job,sum(num) as cnt
from resume_info
where year(date)='2025'
group by job
order by cnt desc
中等级别
#SQL3
查找各个部门当前(dept_manager.to_date='9999-01-01')领导当前(salaries.to_date='9999-01-01')薪水详情以及其对应部门编号dept_no
(注:输出结果以salaries.emp_no升序排序，并且请注意输出结果里面dept_no列是最后一列)
CREATE TABLE `salaries` (
`emp_no` int(11) NOT NULL, -- '员工编号',
`salary` int(11) NOT NULL,
`from_date` date NOT NULL,
`to_date` date NOT NULL,
PRIMARY KEY (`emp_no`,`from_date`));
CREATE TABLE `dept_manager` (
`dept_no` char(4) NOT NULL, -- '部门编号'
`emp_no` int(11) NOT NULL, -- '员工编号'
`to_date` date NOT NULL,
PRIMARY KEY (`emp_no`,`dept_no`));
select s.*,dept_no
from salaries s
inner join dept_manager d
on s.emp_no=d.emp_no
where d.to_date='9999-01-01'
and s.to_date='9999-01-01'
order by s.emp_no;
#SQL5
查找所有员工的last_name和first_name以及对应部门编号dept_no，也包括暂时没有分配具体部门的员工(请注意输出描述里各个列的前后顺序)
CREATE TABLE `dept_emp` (
`emp_no` int(11) NOT NULL,
`dept_no` char(4) NOT NULL,
`from_date` date NOT NULL,
`to_date` date NOT NULL,
PRIMARY KEY (`emp_no`,`dept_no`));
CREATE TABLE `employees` (
`emp_no` int(11) NOT NULL,
`birth_date` date NOT NULL,
`first_name` varchar(14) NOT NULL,
`last_name` varchar(16) NOT NULL,
`gender` char(1) NOT NULL,
`hire_date` date NOT NULL,
PRIMARY KEY (`emp_no`));
SELECT last_name,first_name,dept_no
FROM employees e
LEFT JOIN dept_emp d
ON d.emp_no=e.emp_no;
#与SQL4相对应，想不明白可以两道题对应着看
#SQL6
查找所有员工入职时候的薪水情况，给出emp_no以及salary， 并按照emp_no进行逆序(请注意，一个员工可能有多次涨薪的情况)
CREATE TABLE `employees` (
`emp_no` int(11) NOT NULL,
`birth_date` date NOT NULL,
`first_name` varchar(14) NOT NULL,
`last_name` varchar(16) NOT NULL,
`gender` char(1) NOT NULL,
`hire_date` date NOT NULL,
PRIMARY KEY (`emp_no`));
CREATE TABLE `salaries` (
`emp_no` int(11) NOT NULL,
`salary` int(11) NOT NULL,
`from_date` date NOT NULL,
`to_date` date NOT NULL,
PRIMARY KEY (`emp_no`,`from_date`));
#如果从出题人的思路出发，入职薪水即最低(不考虑降薪)，则如下解法：
SELECT e.emp_no,salary
FROM employees e
INNER JOIN salaries s
ON e.emp_no=s.emp_no
HAVING hire_date=from_date
ORDER BY e.emp_no DESC;
#SQL16
请你统计出各个title类型对应的员工薪水对应的平均工资avg。结果给出title以及平均工资avg，并且以avg升序排序
select title,avg(s.salary)
from titles as t
inner join salaries as s
on t.emp_no=s.emp_no
and t.to_date='9999-01-01'
and s.to_date='9999-01-01'
group by title;
#SQL19
请你查找所有员工的last_name和first_name以及对应的dept_name，也包括暂时没有分配部门的员工
select e.last_name,e.first_name,a.dept_name
from employees e
left join (
select de.emp_no,dep.dept_name
from departments dep
left join dept_emp de
on dep.dept_no=de.dept_no
) as a
on e.emp_no=a.emp_no;
#SQl22
请你统计各个部门的工资记录数，给出部门编码dept_no、部门名称dept_name以及部门在salaries表里面有多少条记录sum，按照dept_no升序排序
select dept_no,dept_name,count(salary) as sum
from(
select emp_no,dept_name,d_e.dept_no
from departments d
inner join dept_emp d_e
on d.dept_no=d_e.dept_no
)as a
inner join salaries s
on a.emp_no=s.emp_no
group by dept_no
order by dept_no;
#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
#SQL30
你能使用子查询的方式找出属于Action分类的所有电影对应的title,description吗
select title,description
from film
where film_id in (
select film_id
from film_category
where category_id in(
select category_id
from category
where name='Action'
)
);
#不用子查询的方法
select title,description
from film f,category c,film_category fc
where f.film_id=fc.film_id
and fc.category_id=c.category_id
and c.name='Action';
#SQL33
创建一个actor表，包含如下列信息
create table actor(
actor_id smallint(5) not null primary key,
first_name varchar(45) not null,
last_name varchar(45) not null,
last_update date not null
);
#SQL35
对于表actor插入如下数据,如果数据已经存在，请忽略(不支持使用replace操作)
insert IGNORE into actor
values(3,'ED','CHASE','2006-02-15 12:34:33');
#SQL36
请你创建一个actor_name表，并且将actor表中的所有first_name以及last_name导入该表.
create table actor_name
select first_name,last_name from actor;
#SQL46
在audit表上创建外键约束，其emp_no对应employees_test表的主键id。
(以下2个表已经创建了)
alter table audit
add foreign key(emp_no)
references employees_test(id);
#SQL48
请你写出更新语句，将所有获取奖金的员工当前的(salaries.to_date='9999-01-01')薪水增加10%。(emp_bonus里面的emp_no都是当前获奖的所有员工)
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通过(')连接起来。(sqlite不支持concat，请用||实现，mysql支持concat)
select concat(last_name,"'",first_name) name
from employees;
#SQL51
查找字符串'10,A,B' 中逗号','出现的次数cnt。
select length('10,A,B') -length(replace('10,A,B',",",""))
#SQL52
获取Employees中的first_name，查询按照first_name最后两个字母，按照升序进行排列
SELECT first_name
FROM employees
ORDER BY RIGHT(first_name ,2);
#SQL53
按照dept_no进行汇总，属于同一个部门的emp_no按照逗号进行连接，结果给出dept_no以及连接出的结果employees
select dept_no,group_concat(emp_no SEPARATOR ',')
from dept_emp
group by dept_no;
#group_concat()函数返回X的非null值的连接后的字符串。如果给出了参数Y，将会在每个X之间用Y作为分隔符。如果省略了Y，“，”将作为默认的分隔符。每个元素连接的顺序是随机的。
#SQl54
查找排除最大、最小salary之后的当前(to_date = '9999-01-01' )员工的平均工资avg_salary。
SELECT AVG(salary) AS avg_salary FROM salaries
WHERE to_date = '9999-01-01'
AND 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'
);
#SQL55
分页查询employees表，每5行一页，返回第2页的数据
select *
from employees
limit 5,5;
#SQL57
使用含有关键字exists查找未分配具体部门的员工的所有信息。
SELECT *
FROM employees
WHERE NOT EXISTS (SELECT emp_no
FROM dept_emp
WHERE employees.emp_no = dept_emp.emp_no
);
#用IN来查找
SELECT *
FROM employees
WHERE emp_no NOT IN (SELECT emp_no
FROM dept_emp);
#什么时候用EXISTS，什么时候用IN？
主表为employees，从表为dept_emp，在主表和从表都对关联的列emp_no建立索引的前提下：
当主表比从表大时，IN查询的效率较高；
当从表比主表大时，EXISTS查询的效率较高；
原因如下：
in是先执行子查询，得到一个结果集，将结果集代入外层谓词条件执行主查询，子查询只需要执行一次
exists是先从主查询中取得一条数据，再代入到子查询中，执行一次子查询，判断子查询是否能返回结果，主查询有多少条数据，子查询就要执行多少次
#SQL63
请你根据上表，输出通过的题目的排名，通过题目个数相同的，排名相同，此时按照id升序排列
select id,number,dense_rank() over(order by number desc) as t_tank
from passing_number
order by t_tank,id;
#SQL73
请你写一个sql语句查询用户分数大于其所在工作(job)分数的平均分的所有grade的属性，并且以id的升序排序，如下:
select id,job,score
from(
select *,avg(score) over (partition by job) as avg_score
from grade
) a
where score>avg_score
order by id
#SQL78
请你写出一个sql语句查询在2025-10-15以后，同一个用户下单2个以及2个以上状态为购买成功的C++课程或Java课程或Python课程的user_id，并且按照user_id升序排序
select distinct user_id
from (
select *,count(*) over(partition by user_id) as r
from order_info
where product_name in ('C++','Java','Python')
and status='completed'
and date >'2025-10-15'
) as a
where r>=2
order by user_id;
#SQL79
请你写出一个sql语句查询在2025-10-15以后，同一个用户下单2个以及2个以上状态为购买成功的C++课程或Java课程或Python课程的订单信息，并且按照order_info的id升序排序
select id,user_id,product_name,status,client_id,date
from (
select *, count(*) over(partition by user_id) as r
from order_info
where date>'2025-10-15'
and product_name in ('C++','Java','Python')
and status='completed'
) as a
where r>=2
order by id;
#SQL82
请你写出一个sql语句查询在2025-10-15以后，同一个用户下单2个以及2个以上状态为购买成功的C++课程或Java课程或Python课程的订单id，是否拼团以及客户端名字信息，最后一列如果是非拼团订单，则显示对应客户端名字，如果是拼团订单，则显示NULL，并且按照order_info的id升序排序
select a.id,is_group_buy,if(client_id!=0,name,NULL) as client_name
from (
select *,
count(*) over(partition by user_id) as n
from order_info
where status='completed'
and product_name in('C++','Python','Java')
and date>'2025-10-15'
) as a
left join client c
on a.client_id=c.id
where n>=2
order by id
#SQL85
请你写出SQL语句查询在2025年内投递简历的每个岗位，每一个月内收到简历的数量，并且按先按月份降序排序，再按简历数目降序排序
select job,date_format(date,'%Y-%m') as mon,sum(num) as cnt
from resume_info
where year(date)='2025'
group by job,mon
order by mon desc,cnt desc
#SQL87
请你写出一个SQL查询，如果一个学生知道了自己综合成绩以后，最差是排第几名? 结果按照grade升序排序
select grade ,sum(number) over(order by grade) as t_rank #注意一定要加order by
from class_grade
order by grade;
较难级别
#SQL18
请你查找薪水排名第二多的员工编号emp_no、薪水salary、last_name以及first_name，不能使用order by完成
select e.emp_no,s.salary,e.last_name,e.first_name
from
employees e
join
salaries s on e.emp_no=s.emp_no
and s.to_date='9999-01-01'
and s.salary = (select max(salary)
from salaries
where salary<(select max(salary)
from salaries
where to_date='9999-01-01'
)
and to_date='9999-01-01'
)
#这里再提供一个很棒的思路
通用型可以求任意第几高，并且可以求多个形同工资
select e.emp_no,s.salary,e.last_name,e.first_name
from
employees e
join
salaries s on e.emp_no=s.emp_no
and s.to_date='9999-01-01'
and s.salary =
(
select s1.salary
from
salaries s1
join
salaries s2 on s1.salary<=s2.salary
and s1.to_date='9999-01-01' and s2.to_date='9999-01-01'
group by s1.salary
having count(distinct s2.salary)=2
)
#需要理解的是salaries s2 on s1.salary<=s2.salary 和having count(distinct s2.salary)=2，需要自己画个表来理解，比如s1是100,98,98,96，那么s1=100时，符合s1<=s2的只有100，s1=98时，符合条件的是100、98两个数，s1=96时，符合条件就有三个数，当你想要count(distinct s2.salary)=2时，即只有两个数的时候就是s1=98，即第二大。也就是说 having count 拿出来的是只有两个数据的值，当且仅当s1的值为第二大时，s2的值才会有两条记录，此时的s1的值就是薪水中第二大的值。
#SQL23
对所有员工的薪水按照salary进行按照1-N的排名，相同salary并列且按照emp_no升序排列
select s1.emp_no,s1.salary,count(distinct s2.salary)
from salaries s1,salaries s2
where s1.salary<=s2.salary
and s1.to_date='9999-01-01'
and s2.to_date='9999-01-01'
group by emp_no
order by salary desc ,emp_no;
#SQL24
获取所有非manager员工薪水情况，给出dept_no、emp_no以及salary
select dept_no,d.emp_no,salary
from dept_emp d
inner join salaries s
on d.emp_no=s.emp_no
and s.to_date='9999-01-01'
where d.emp_no not in (
select emp_no
from dept_manager
where to_date='9999-01-01'
);
##SQL28
查找描述信息(film.description)中包含robot的电影对应的分类名称(category.name)以及电影数目(count(film.film_id))，而且还需要该分类包含电影总数量(count(film_category.category_id))>=5部
SELECT c.name, COUNT(fc.film_id)
FROM(
select category_id
FROM film_category
GROUP BY category_id
HAVING count(film_id)>=5
) AS cc,film AS f, film_category AS fc, category AS c
WHERE f.description LIKE '%robot%'
AND f.film_id = fc.film_id
AND c.category_id = fc.category_id
AND c.category_id=cc.category_id
GROUP BY c.name
##SQL37
对first_name创建唯一索引uniq_idx_firstname，对last_name创建普通索引idx_lastname
create unique index uniq_idx_firstname on actor(first_name);
create index idx_lastname on actor(last_name);
##SQL38
针对actor表创建视图actor_name_view，只包含first_name以及last_name两列，并对这两列重新命名，first_name为first_name_v，last_name修改为last_name_v
create view actor_name_view as
select first_name first_name_v,last_name last_name_v
from actor;
##SQL39
针对salaries表emp_no字段创建索引idx_emp_no，查询emp_no为10005, 使用强制索引。
select *
from salaries
force index(idx_emp_no)#注意一下强制索引的书写格式
where emp_no=10005;
##SQL40
现在在last_update后面新增加一列名字为create_date, 类型为datetime, NOT NULL，默认值为'2020-10-01 00:00:00'
alter table actor
add column create_date datetime not null default '2020-10-01 00:00:00';
##SQL41
构造一个触发器audit_log，在向employees_test表中插入一条数据的时候，触发插入相关的数据到audit中。
CREATE TRIGGER audit_log
AFTER INSERT
ON employees_test FOR EACH ROW#on 表名 for each row不能分开
BEGIN
INSERT INTO audit VALUES(NEW.ID,NEW.NAME);
END
#SQL59
给出emp_no、first_name、last_name、奖金类型btype、对应的当前薪水情况salary以及奖金金额bonus。 bonus类型btype为1其奖金为薪水salary的10%，btype为2其奖金为薪水的20%，其他类型均为薪水的30%。 当前薪水表示to_date='9999-01-01'
select e.emp_no,e.first_name,e.last_name,eb.btype,s.salary,(
case eb.btype
when 1 then s.salary*0.1
when 2 then s.salary*0.2
when 3 then s.salary*0.3
end
) as bonus
from employees e,emp_bonus eb,salaries s
where e.emp_no=eb.emp_no
and e.emp_no=s.emp_no
and s.to_date = '9999-01-01';
#SQL60
按照salary的累计和running_total，其中running_total为前N个当前( to_date = '9999-01-01')员工的salary累计和，其他以此类推。 具体结果如下Demo展示。
select emp_no,salary,sum(salary) over(order by emp_no) as running_total #窗口函数，在mysql8.0才能使用
from salaries
where to_date='9999-01-01';
#SQL61
对于employees表中，输出first_name排名(按first_name升序排序)为奇数的first_name
select a.first_name
from (
select emp_no, first_name, row_number() over(order by first_name) as row_num
from employees
) a
where a.row_num % 2 = 1
order by emp_no;
#SQL65
现在让你写一个sql查询，每一个日期里面，正常用户发送给正常用户邮件失败的概率是多少，结果保留到小数点后面3位(3位之后的四舍五入)，并且按照日期升序排序
select date,
ROUND(
AVG(
CASE WHEN type = 'no_completed' THEN 1
ELSE 0 END
),3
) AS p
from email
where send_id not in (select id from user where is_blacklist = 1)
and receive_id not in (select id from user where is_blacklist = 1)
group by date
order by date
#SQL67
请你写出一个sql语句查询每个用户最近一天登录的日子，用户的名字，以及用户用的设备的名字，并且查询结果按照user的name升序排序
select u.name,c.name,l1.date
from login l1,user u,client c
where l1.date=(
select max(l2.date)
from login l2
where l1.user_id=l2.user_id
)
and l1.user_id=u.id
and l1.client_id=c.id
order by u.name
#SQL68
请你写出一个sql语句查询新登录用户次日成功的留存率，即第1天登陆之后，第2天再次登陆的概率,保存小数点后面3位(3位之后的四舍五入)
select
round(count(distinct user_id)/(select count(distinct user_id) from login) ,3)
from login
where (user_id,date) in (
select user_id,DATE_ADD(min(date),INTERVAL 1 DAY)
from login
group by user_id
);
#SQL69
请你写出一个sql语句查询每个日期登录新用户个数，并且查询结果按照日期升序排序，上面的例子查询结果如下
select a.date,
sum(if(r=1,1,0)) new
from (
select date, row_number() over(partition by user_id order by date) r
from login
) a
group by date;#这里需要画个表理解一下
#SQL71
请你写出一个sql语句查询刷题信息，包括: 用户的名字，以及截止到某天，累计总共通过了多少题，并且查询结果先按照日期升序排序，再按照姓名升序排序，有登录却没有刷题的哪一天的数据不需要输出
select name as u_n,date,sum(number) over(partition by user_id order by date) as ps_num
from passing_number p
left join user u
on p.user_id=u.id
order by date,u_n
#SQL74
请你找出每个岗位分数排名前2名的用户，得到的结果先按照language的name升序排序，再按照积分降序排序，最后按照grade的id升序排序，得到结果如下:
select a.id,l.name,a.score
from (
select * ,
dense_rank() over(partition by language_id order by score desc) as r
from grade ) as a,language l
where a.language_id=l.id
and a.r<=2
order by l.name asc,a.score desc ,a.id asc
#SQL75
请你写一个sql语句查询各个岗位分数升序排列之后的中位数位置的范围，并且按job升序排序
select a.job, round(count(a.id)/2), round((count(a.id)+1)/2)
from grade a
group by a.job
order by job
#SQL80
请你写出一个sql语句查询在2025-10-15以后，如果有一个用户下单2个以及2个以上状态为购买成功的C++课程或Java课程或Python课程，那么输出这个用户的user_id，以及满足前面条件的第一次购买成功的C++课程或Java课程或Python课程的日期first_buy_date，以及购买成功的C++课程或Java课程或Python课程的次数cnt，并且输出结果按照user_id升序排序，
select user_id,date first_buy_date,cnt
from (
select *,
row_number() over(partition by user_id order by date) as r,
count(*) over(partition by user_id) as cnt
from order_info
where date>'2025-10-15'
and status='completed'
and product_name in('C++','Java','Python')
) as a
where cnt>=2
and r=1
order by user_id
#SQL83
请你写出一个sql语句查询在2025-10-15以后，同一个用户下单2个以及2个以上状态为购买成功的C++课程或Java课程或Python课程的来源信息，第一列是显示的是客户端名字，如果是拼团订单则显示GroupBuy，第二列显示这个客户端(或者是拼团订单)有多少订单，最后结果按照第一列(source)升序排序
select (case when b.name is null then 'GroupBuy' else b.name end) source,count(*) cnt
from (
select *
from
(select *,count(*) over(partition by user_id) as cc
from order_info
where status='completed'
and date>'2025-10-15'
and product_name in ('C++','Python','Java')
) r
where r.cc>=2
) a
left join client b on a.client_id=b.id
group by a.client_id
order by source
#SQL86
老师想知道学生们综合成绩的中位数是什么档位，请你写SQL帮忙查询一下，如果只有1个中位数，输出1个，如果有2个中位数，按grade升序输出
select grade
from (
select grade,(
select sum(number)
from class_grade
) as total,
sum(number) over(order by grade) a,
sum(number) over(order by grade desc) b
from class_grade
) t1
where a >= total/2 and b >=total/2
order by grade;
困难级别
#SQL12
获取所有部门中员工薪水最高的相关信息，给出dept_no, emp_no以及其对应的salary，按照部门编号升序排列
select de.dept_no, de.emp_no, s.salary
from dept_emp de inner join salaries s
on de.emp_no = s.emp_no
and de.to_date = '9999-01-01'
and s.to_date = '9999-01-01'
where s.salary = (select max(s2.salary)
from dept_emp de2
inner join salaries s2
on de2.emp_no = s2.emp_no
and de2.to_date = '9999-01-01'
and s2.to_date = '9999-01-01'
where de2.dept_no = de.dept_no
group by de2.dept_no)
order by de.dept_no
#再提供一种与普通内连接不一样的方法
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
#SQL21
请你查找所有员工自入职以来的薪水涨幅情况，给出员工编号emp_no以及其对应的薪水涨幅growth，并按照growth进行升序
select s_curry.emp_no,(s_curry.salary-s_start.salary) as growth
from (
select s.emp_no,s.salary
from employees e
inner join salaries s
on e.emp_no=s.emp_no
where s.to_date='9999-01-01'
)as s_curry
inner join (
select s.emp_no,s.salary
from employees e
inner join salaries s
on e.emp_no=s.emp_no
where s.from_date=e.hire_date
)as s_start
on s_curry.emp_no=s_start.emp_no
order by growth;
#还有一种相似逻辑不同合并方法的
select a.emp_no, (b.salary - c.salary) as growth
from
employees as a
inner join salaries as b
on a.emp_no = b.emp_no
and b.to_date = '9999-01-01'
inner join salaries as c
on a.emp_no = c.emp_no
and a.hire_date = c.from_date
order by growth asc
#SQL25
获取员工其当前的薪水比其manager当前薪水还高的相关信息，
第一列给出员工的emp_no，
第二列给出其manager的manager_no，
第三列给出该员工当前的薪水emp_salary,
第四列给该员工对应的manager当前的薪水manager_salary
select a.emp_no as emp_no,b.emp_no as manager_no,a.salary as emp_salary,b.salary as manager_salary
from (
select de_e.emp_no,s.salary,de_e.dept_no
from dept_emp de_e
inner join salaries s
on de_e.emp_no=s.emp_no
and s.to_date='9999-01-01'
) as a,(
select de_m.emp_no,s.salary,de_m.dept_no
from dept_manager de_m
inner join salaries s
on de_m.emp_no=s.emp_no
and s.to_date='9999-01-01'
) as b
where a.dept_no=b.dept_no
and a.salary>b.salary;
#这里使用了并列查询，要学会这个思想。
#SQL26
汇总各个部门当前员工的title类型的分配数目，即结果给出部门编号dept_no、dept_name、其部门下所有的员工的title以及该类型title对应的数目count，结果按照dept_no升序排序
select a.dept_no,a.dept_name,b.title,count(title) as count
from (
select de.emp_no,d.dept_no,d.dept_name
from departments d
inner join dept_emp de
on d.dept_no=de.dept_no
and de.to_date='9999-01-01'
) as a
inner join (
select t.title,t.emp_no
from dept_emp de
inner join titles t
on de.emp_no=t.emp_no
) as b
on a.emp_no=b.emp_no
group by dept_no,title
order by dept_no;
#SQL76
请你写一个sql语句查询各个岗位分数的中位数位置上的所有grade信息，并且按id升序排序，结果如下
select id,job,score,r
from(
select *,
rank()over(partition by job order by score desc)as r,
count(*)over(partition by job)as t
from grade
) as a
where round(abs(r-(t+1)/2),2)<1
#用一条规则统一奇数个数时和偶数个数时的中位数位置。无论奇偶，中位数的位置距离（个数+1）/2 小于1
order by id
#SQL81
请你写出一个sql语句查询在2025-10-15以后，如果有一个用户下单2个以及2个以上状态为购买成功的C++课程或Java课程或Python课程，那么输出这个用户的user_id，以及满足前面条件的第一次购买成功的C++课程或Java课程或Python课程的日期first_buy_date，以及满足前面条件的第二次购买成功的C++课程或Java课程或Python课程的日期second_buy_date，以及购买成功的C++课程或Java课程或Python课程的次数cnt，并且输出结果按照user_id升序排序
select
user_id,
min(date) first_buy_date,
max(date) second_buy_date,
max(cnt) cnt
from(
select*,
row_number() over(partition by user_id order by date) r,
count(*) over(partition by user_id) cnt
from order_info
where date > '2025-10-15'
and status = 'completed'
and product_name in ('C++','Java','Python')
)a
where cnt >= 2 and r <= 2
group by user_id
order by user_id
#SQL86
请你写出SQL语句查询在2025年投递简历的每个岗位，每一个月内收到简历的数量和，对应的2026年的同一个月同岗位，收到简历的数量，最后的结果先按first_year_mon月份降序，再按job降序排序显示
select a.job,
date_format(d1, '%Y-%m') first_year_mon,
c1 first_year_cnt,
date_format(d2, '%Y-%m') second_year_mon,
c2 second_yeat_cnt
from(
select job, date d1, sum(num) c1
from resume_info
where year(date) = 2025
group by month(date), job) a
join(
select job, date d2, sum(num) c2
from resume_info
where year(date) = 2026
group by month(date), job) b
on a.job = b.job
and month(d1) = month(d2)
order by first_year_mon desc, job desc;
SQL面试必会50题
测试数据
#表名和字段
–1.学生表
Student(s_id,s_name,s_birth,s_sex) –学生编号,学生姓名, 出生年月,学生性别
–2.课程表
Course(c_id,c_name,t_id) – –课程编号, 课程名称, 教师编号
–3.教师表
Teacher(t_id,t_name) –教师编号,教师姓名
–4.成绩表
Score(s_id,c_id,s_score) –学生编号,课程编号,分数
--建表
--学生表
CREATE TABLE `Student`(
`s_id` VARCHAR(20),
`s_name` VARCHAR(20) NOT NULL DEFAULT '',
`s_birth` VARCHAR(20) NOT NULL DEFAULT '',
`s_sex` VARCHAR(10) NOT NULL DEFAULT '',
PRIMARY KEY(`s_id`)
);
--课程表
CREATE TABLE `Course`(
`c_id` VARCHAR(20),
`c_name` VARCHAR(20) NOT NULL DEFAULT '',
`t_id` VARCHAR(20) NOT NULL,
PRIMARY KEY(`c_id`)
);
--教师表
CREATE TABLE `Teacher`(
`t_id` VARCHAR(20),
`t_name` VARCHAR(20) NOT NULL DEFAULT '',
PRIMARY KEY(`t_id`)
);
--成绩表
CREATE TABLE `Score`(
`s_id` VARCHAR(20),
`c_id` VARCHAR(20),
`s_score` INT(3),
PRIMARY KEY(`s_id`,`c_id`)
);
--插入学生表测试数据
insert into Student values('01' , '赵雷' , '1990-01-01' , '男');
insert into Student values('02' , '钱电' , '1990-12-21' , '男');
insert into Student values('03' , '孙风' , '1990-05-20' , '男');
insert into Student values('04' , '李云' , '1990-08-06' , '男');
insert into Student values('05' , '周梅' , '1991-12-01' , '女');
insert into Student values('06' , '吴兰' , '1992-03-01' , '女');
insert into Student values('07' , '郑竹' , '1989-07-01' , '女');
insert into Student values('08' , '王菊' , '1990-01-20' , '女');
--课程表测试数据
insert into Course values('01' , '语文' , '02');
insert into Course values('02' , '数学' , '01');
insert into Course values('03' , '英语' , '03');
--教师表测试数据
insert into Teacher values('01' , '张三');
insert into Teacher values('02' , '李四');
insert into Teacher values('03' , '王五');
--成绩表测试数据
insert into Score values('01' , '01' , 80);
insert into Score values('01' , '02' , 90);
insert into Score values('01' , '03' , 99);
insert into Score values('02' , '01' , 70);
insert into Score values('02' , '02' , 60);
insert into Score values('02' , '03' , 80);
insert into Score values('03' , '01' , 80);
insert into Score values('03' , '02' , 80);
insert into Score values('03' , '03' , 80);
insert into Score values('04' , '01' , 50);
insert into Score values('04' , '02' , 30);
insert into Score values('04' , '03' , 20);
insert into Score values('05' , '01' , 76);
insert into Score values('05' , '02' , 87);
insert into Score values('06' , '01' , 31);
insert into Score values('06' , '03' , 34);
insert into Score values('07' , '02' , 89);
insert into Score values('07' , '03' , 98);
1.查询课程编号为“01”的课程比“02”的课程成绩高的所有学生的学号（重点）
SELECT s1.s_id'学号',s1.s_score '课程1成绩',s2.s_score '课程2成绩'
FROM (
SELECT s_id,s_score
FROM score
WHERE c_id='01'
) s1
INNER JOIN (
SELECT s_id,s_score
FROM score
WHERE c_id='02'
) s2
ON s1.s_id=s2.s_id
WHERE s1.s_score>s2.s_score;
2.查询平均成绩大于60分的学生的学号和平均成绩（简单，第二道重点）
SELECT s_id,AVG(s_score) AS '平均成绩'
FROM score
GROUP BY s_id
HAVING AVG(s_score)>60;
3.查询所有学生的学号、姓名、选课数、总成绩
SELECT st.s_id,st.s_name,COUNT(*) AS '选课数',SUM(s_score) AS '总成绩'
FROM student st
INNER JOIN score s
ON st.`s_id`=s.`s_id`
GROUP BY st.`s_id`;
4.查询姓“张”的老师的个数
SELECT COUNT(t_id)
FROM teacher
WHERE LEFT(t_name,1)='张';
#或者用正则表达式
SELECT COUNT(t_id)
FROM teacher
WHERE t_name LIKE '张%';
5.查询没学过“张三”老师课的学生的学号、姓名（重点）
SELECT s_id,s_name
FROM student
WHERE s_id NOT IN(
SELECT s.s_id
FROM score s
INNER JOIN course c
ON s.`c_id`=c.`c_id`
INNER JOIN teacher t
ON t.`t_id`=c.`t_id`
WHERE t.`t_name`='张三'
)
6.查询学过“张三”老师所教的所有课的同学的学号、姓名（重点）
select s_id,s_name
from student
where s_id in(
select s.s_id
from score s
inner join course c
on s.`c_id`=c.`c_id`
inner join teacher t
on t.`t_id`=c.`t_id`
where t.`t_name`='张三'
)
7.查询学过编号为“01”的课程并且也学过编号为“02”的课程的学生的学号、姓名（重点）
select s_id,s_name
from student
where s_id in (
select a.s_id
from (
select s_id
from score
where c_id='01'
) a
inner join (
SELECT s_id
FROM score
WHERE c_id='02'
) b
on a.s_id=b.s_id
);
8.查询课程编号为“02”的总成绩
select sum(s_score)
from score
where c_id='02'
9.查询所有课程成绩小于60分的学生的学号、姓名
select s.s_id,s.s_name,max(sc.s_score)
from Student s
inner join score sc
on s.s_id =sc.s_id
group by s.s_id
HAVING max(sc.s_score) < 60;
10.查询没有学全所有课的学生的学号、姓名(重点)
SELECT st.s_id,st.s_name
FROM student AS st
LEFT JOIN score AS sc
ON st.`s_id`=sc.`s_id`
GROUP BY st.`s_id`
HAVING COUNT(DISTINCT sc.c_id)<(
SELECT COUNT(DISTINCT c_id)
FROM course
)
11.查询至少有一门课与学号为“01”的学生所学课程相同的学生的学号和姓名（重点）
SELECT DISTINCT s_id,s_name
FROM student
WHERE s_id IN(
SELECT s_id
FROM score
WHERE c_id IN(
SELECT c_id
FROM score
WHERE s_id='01'
)
)
AND s_id !='01'
12.查询和“01”号同学所学课程完全相同的其他同学的学号(重点)
SELECT s_id FROM score
WHERE score.s_id NOT IN
(SELECT s_id FROM score
WHERE c_id NOT IN
(SELECT c_id FROM score
WHERE s_id ='01'))
AND s_id !='01'
GROUP BY s_id
HAVING COUNT(c_id) = (SELECT COUNT(c_id) FROM score WHERE s_id ='01')
#这道题有点问题，需要再想想
13.查询没学过"张三"老师讲授的任一门课程的学生姓名（重点)
与第五题重复
14.空白
15.查询两门及其以上不及格课程的同学的学号，姓名及其平均成绩（重点）
SELECT st.s_id,s_name,AVG(s_score)
FROM student st
INNER JOIN score sc
ON st.`s_id`=sc.`s_id`
WHERE st.`s_id`IN (
SELECT s_id
FROM score
WHERE s_score<60
GROUP BY s_id
HAVING COUNT(DISTINCT c_id)>=2
)
GROUP BY s_id;
16.检索"01"课程分数小于60，按分数降序排列的学生信息
select *
from student st
inner join score sc
on st.`s_id`=sc.`s_id`
where st.s_id in(
select distinct s_id
from score
where c_id='01'
and s_score<60
)
order by s_score desc;
17.按平均成绩从高到低显示所有学生的所有课程的成绩以及平均成绩(重点)
SELECT *
FROM (
SELECT *,AVG(s_score) over (PARTITION BY s_id ) AS 平均成绩
FROM score
) AS b
ORDER BY 平均成绩 DESC;
18. 查询各科成绩最高分、最低分和平均分：以如下形式显示：课程ID，课程name，最高分，最低分，平均分，及格率，中等率，优良率，优秀率(超级重点)
#及格为>=60，中等为：70-80，优良为：80-90，优秀为：>=90
SELECT sc.c_id,c_name,MAX(s_score),MIN(s_score),AVG(s_score),
SUM(CASE WHEN s_score>=60 THEN 1 ELSE 0 END )/COUNT(s_id) AS 及格率,
SUM(CASE WHEN s_score>=70 AND s_score<80 THEN 1 ELSE 0 END )/COUNT(s_id) AS 中等率,
SUM(CASE WHEN s_score>=80 AND s_score<90 THEN 1 ELSE 0 END )/COUNT(s_id) AS 优良率,
SUM(CASE WHEN s_score>=90 THEN 1 ELSE 0 END )/COUNT(s_id) AS 优秀率
FROM score sc
INNER JOIN course c
ON sc.`c_id`=c.`c_id`
GROUP BY c_id
19.按各科成绩进行排序，并显示排名(重点)
SELECT *,row_number() over(PARTITION BY c_id ORDER BY s_score DESC) AS r
FROM score
20.查询学生的总成绩并进行排名
SELECT * ,row_number() over(ORDER BY 总成绩 DESC) r
FROM (
SELECT s_id 学号,SUM(s_score) 总成绩
FROM score
GROUP BY s_id
ORDER BY SUM(s_score) DESC
)AS a;
21.查询不同老师所教不同课程平均分从高到低显示
select c.`c_id`,c.`c_name`,avg(sc.`s_score`) avg
from course c
inner join score sc
on c.`c_id`=sc.`c_id`
group by t_id,c.`c_id`
order by AVG desc
22.查询所有课程的成绩第2名到第3名的学生信息及该课程成绩(重点)
select st.*,c_id,s_score
from student st
inner join score s
on st.`s_id`=s.`s_id`
order by s_score desc
limit 1,2
23.使用分段[100-85],[85-70],[70-60],[<60]来统计各科成绩，分别统计各分数段人数：课程ID和课程名称(重点)
SELECT c.`c_id`,c.c_name,
SUM(CASE WHEN s_score<=100 AND s_score>85 THEN 1 ELSE 0 END) AS '[100-85]',
SUM(CASE WHEN s_score<=85 AND s_score>70 THEN 1 ELSE 0 END) AS '[85-70]',
SUM(CASE WHEN s_score<=70 AND s_score>60 THEN 1 ELSE 0 END) AS '[70-60]',
SUM(CASE WHEN s_score<=60 THEN 1 ELSE 0 END) AS '[<60]'
FROM score s
INNER JOIN course c
ON s.`c_id`=c.`c_id`
GROUP BY c.`c_id`,c.c_name
24. 查询学生平均成绩及其名次(重点)
#法1
SELECT *,row_number() over(ORDER BY AVG DESC) AS r
FROM(
SELECT AVG(s_score) AVG,s_id
FROM score
GROUP BY s_id
) AS a
#法2
SELECT s_id,AVG(s_score),row_number() over(ORDER BY AVG(s_score) DESC) AS r
FROM score
GROUP BY s_id
25.查询各科成绩前三名的记录（不考虑成绩并列情况）（重点）
SELECT *
FROM (
SELECT *,row_number() over(PARTITION BY c_id ORDER BY s_score DESC) r
FROM score
) AS a
WHERE r<=3;
26.查询每门课程被选修的学生数
SELECT c_id,COUNT(*) 学生数
FROM score
GROUP BY c_id
27. 查询出只有两门课程的全部学生的学号和姓名
select s_id,s_name
from student
where s_id in (
select s_id
from score
group by s_id
having count(c_id)=2
)
28.查询男生、女生人数
SELECT COUNT(*),s_sex
FROM student
GROUP BY s_sex
29.查询名字中含有"风"字的学生信息
SELECT *
FROM student
WHERE s_name LIKE '%风%';
30.空
31.查询1990年出生的学生名单（重点）
SELECT *
FROM student
WHERE YEAR(s_birth)='1990'
32.查询平均成绩大于等于85的所有学生的学号、姓名和平均成绩
select s.s_id,s_name,avg(s_score)
from student s
inner join score sc
on s.`s_id`=sc.`s_id`
group by s.`s_id`
having avg(sc.`s_score`)>85;
33.查询每门课程的平均成绩，结果按平均成绩升序排序，平均成绩相同时，按课程号降序排列
SELECT s_id,c_id,AVG(s_score)
FROM score
GROUP BY c_id
ORDER BY AVG(s_score) ASC,c_id DESC;
34.查询课程名称为"数学"，且分数低于60的学生姓名和分数
SELECT s_name,s_score
FROM student s
INNER JOIN score sc
ON s.`s_id`=sc.`s_id`
INNER JOIN course c
ON sc.`c_id`=c.`c_id`
WHERE c_name='数学'
AND s_score<60;
35. 查询所有学生的课程及分数情况（重点）
SELECT s.s_id,s_name,
MAX(CASE WHEN c_name='数学' THEN s_score ELSE NULL END) AS 数学成绩, #这里使用max的原理忘记了可以去看视频
MAX(CASE WHEN c_name='语文' THEN s_score ELSE NULL END ) AS 语文成绩,
MAX(CASE WHEN c_name='英语' THEN s_score ELSE NULL END) AS 英语成绩
FROM student st
LEFT JOIN score s
ON st.`s_id`=s.`s_id`
LEFT JOIN course c
ON c.`c_id`=s.`c_id`
GROUP BY s.s_id,s_name
36.查询任何一门课程成绩在70分以上的姓名、课程名称和分数（重点）
SELECT s_name,c_name,s_score
FROM student st
INNER JOIN score sc
ON st.`s_id`=sc.`s_id`
INNER JOIN course c
ON c.`c_id`=sc.`c_id`
WHERE s_score>70
37.查询不及格的课程并按课程号从大到小排列
select S_id,s_score,c_id
from score
where s_score<60
order by c_id desc;
38.查询课程编号为03且课程成绩在80分以上的学生的学号和姓名
select sc.s_id,st.s_name
from student st
inner join score sc
on st.`s_id`=sc.`s_id`
where c_id='03'
and s_score>80
39.求每门课程的学生人数
select c_id,count(*)
from score
group by c_id
40.查询选修“张三”老师所授课程的学生中成绩最高的学生姓名及其成绩（重要）
select s_name,s_score
from student st
inner join score sc
on st.`s_id`=sc.`s_id`
inner join course c
on sc.`c_id`=c.`c_id`
inner join teacher t
on c.`t_id`=t.`t_id`
where t_name='张三'
order by s_score desc
limit 1
41.查询不同课程成绩相同的学生的学生编号、课程编号、学生成绩 （重点）
SELECT DISTINCT a.`s_id`,a.`c_id`,a.`s_score`
FROM score a
INNER JOIN score b
ON a.`s_id`=b.`s_id`
WHERE a.`c_id`!=b.`c_id`
AND a.`s_score`=b.`s_score`
42.重复了
43.统计每门课程的学生选修人数（超过5人的课程才统计）。要求输出课程号和选修人数，查询结果按人数降序排列，若人数相同，按课程号升序排列
SELECT COUNT(s_id) AS 选修人数,c_id
FROM score
GROUP BY c_id
HAVING COUNT(s_id)>5
ORDER BY COUNT(s_id) DESC,c_id ASC
44. 检索至少选修两门课程的学生学号
select s_id,count(s_id)
from score
group by s_id
having count(c_id)>=2
45.查询选修了全部课程的学生信息(重点)
select *
from student
where s_id in(
SELECT s_id
FROM score
GROUP BY s_id
HAVING COUNT(c_id)=(
SELECT COUNT(c_id)
FROM course
)
)
46.查询各学生年龄
SELECT s_id,s_birth,DATEDIFF(NOW(),s_birth)/365
FROM student
47.查询没学过“张三”老师讲授的任一门课程的学生姓名
SELECT s_name
FROM student
WHERE s_id NOT IN(
SELECT DISTINCT st.s_id
FROM student st
LEFT JOIN score sc
ON st.`s_id`=sc.`s_id`
INNER JOIN course c
ON c.`c_id`=sc.`c_id`
INNER JOIN teacher t
ON c.`t_id`=t.`t_id`
WHERE t.`t_name`='张三'
)
48.查询两门以上不及格课程的同学的学号及其平均成绩
SELECT s_id,AVG(s_score)
FROM score
WHERE s_score<60
GROUP BY s_id
HAVING COUNT(c_id)>=2