牛客网SQL面试题练习答案（全部通过）

1 年前 · 来自专栏数据分析

缨络

潜水者

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

select *
from employees
order by hire_date desc 
LIMIT 0,1;

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

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

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

select a.*,b.dept_no
from salaries a
join dept_manager b 
on a.emp_no=b.emp_no
where b.to_date='9999-01-01'
order by a.emp_no asc;

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

select a.last_name,a.first_name,b.dept_no
from employees a 
join dept_emp b 
on a.emp_no=b.emp_no;

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

select a.last_name,a.first_name,b.dept_no
from employees a 
left join dept_emp b 
on a.emp_no=b.emp_no;

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

select emp_no,count(*)
from salaries
group by emp_no
having count(*)>=15;

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

select distinct salary
from salaries
order by salary desc;

10、获取所有非manager的员工emp_no

select a.emp_no
from employees a 
left join dept_manager b
on a.emp_no=b.emp_no
where b.emp_no is null;

11、获取所有员工当前的manager

SELECT a.emp_no,b.emp_no
from dept_emp a 
left join dept_manager b 
on a.dept_no=b.dept_no
where a.emp_no!=b.emp_no;

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

select a.dept_no,b.emp_no,b.salary
from dept_emp a 
join salaries b on a.emp_no=b.emp_no
(select a.dept_no,max(b.salary) max_salary
from dept_emp a 
join salaries b 
on a.emp_no=b.emp_no
group by a.dept_no) c on a.dept_no=c.dept_no
where b.salary=c.max_salary
order by a.dept_no asc;

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

select *
from employees
where emp_no%2=1 and last_name !='Mary'
order




    
 by hire_date desc;

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

select a.title,avg(b.salary) avg_salary
from titles a 
join salaries b
on a.emp_no=b.emp_no
group by a.title
order by avg_salary asc;

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

SELECT emp_no,salary
from salaries
order by salary desc 
limit 1,1;

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

select b.emp_no,b.salary,a.last_name,a.first_name
from employees a
join salaries b
on a.emp_no=b.emp_no
where salary=(select max(salary)
             from salaries
             where salary !=(select max(salary)
                            from salaries));

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

select a.last_name,a.first_name,c.dept_name
from employees a
left join dept_emp b on a.emp_no=b.emp_no
left join departments c on b.dept_no=c.dept_no;

21、查找在职员工自入职以来的薪水涨幅情况

select x.emp_no,(y.last_salary-x.first_salary) growth
from (select a.emp_no, b.salary first_salary
     from employees a 
     join salaries b on a.emp_no=b.emp_no
     where a.hire_date=b.from_date)  x 
join (select a.emp_no,b.salary last_salary
     from employees a 
     join salaries b on a.emp_no=b.emp_no
     where b.to_date='9999-01-01') y 
on x.emp_no=y.emp_no
order by growth asc;

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

SELECT c.dept_no,c.dept_name,count(*) sum 
from dept_emp a
join salaries b on a.emp_no=b.emp_no
join departments c on a.dept_no=c.dept_no
group by c.dept_no
order by c.dept_no asc;

23、对所有员工的薪水按照salary降序进行1-N的排名

select emp_no,salary,DENSE_RANK() over(order by salary desc)
from salaries
order by salary desc,emp_no asc;

24、获取所有非manager员工当前的薪水情况

select a.dept_no,b.emp_no,d.salary
from dept_emp a 
join employees b on a.emp_no=b.emp_no
join dept_manager c on a.dept_no=c.dept_no
join salaries d on b.emp_no=d.emp_no
where b.emp_no!=c.emp_no;

25、获取员工其当前的薪水比其manager当前薪水还高的相关信息

select a.emp_no,c.emp_no manager_no,b.salary emp_salary,d.salary manager_salary
from dept_emp a 
join salaries b on a.emp_no=b.emp_no
join dept_manager c on a.dept_no=c.dept_no
join salaries d on c.emp_no=d.emp_no
where b.salary>d.salary;

26、汇总各个部门当前员工的title类型的分配数目

select a.dept_no,b.dept_name,c.title,count(*) cnt
from dept_emp a 
join departments b on a.dept_no=b.dept_no
join titles c on a.emp_no=c.emp_no
group by a.dept_no,b.dept_name,c.title
order by a.dept_no asc;

28、查找描述信息中包含robot的电影对应的分类名称以及电影数目，而且还需要该分类对应电影数量>=5部

SELECT c.name,count(*)
from film_category a 
join film b on a.film_id=b.film_id
join category c on a.category_id=c.category_id
join (select category_id,count(film_id) cnt
      from film_category
      group by category_id
      having count(film_id)>=5) d on c.category_id=d.category_id
where b.description like '%robot%'
group by c.name;

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

select a.film_id,a.title
from film a 
left join film_category b on a.film_id=b.film_id
left join category c




    
 on b.category_id=c.category_id
where b.category_id is null;

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

select title,description
from film
where film_id in (select a.film_id
                  from film_category a 
                  join film b on a.film_id=b.film_id
                  join category c on a.category_id=c.category_id
                  where c.name='Action');

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

select concat(last_name,' ',first_name) Name
from employees;

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

create table if not exists actor (actor_id smallint(5) primary key,
                                 first_name varchar(45) Not Null,
                                 last_name varchar(45) Not Null,
                                 last_update date Not Null);

34、批量插入数据

  insert into actor values('1','PENELOPE','GUINESS','2006-02-15 12:34:33'),
                                       ('2','NICK','WAHLBERG','2006-02-15 12:34:33');

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

insert ignore actor values('3','ED','CHASE','2006-02-15 12:34:33');

36、创建一个actor_name表

create table actor_name 
select first_name,last_name
from actor;

37、对first_name创建唯一索引uniq_idx_firstname

alter table actor add unique uniq_idx_firstname(first_name);
alter table actor add index idx_lastname(last_name);

38、针对actor表创建视图actor_name_view

create view actor_name_view
select first_name first_name_v,last_name last_name_v
from actor;

39、针对上面的salaries表emp_no字段创建索引idx_emp_no

select *
from salaries
force index (idx_emp_no)
where emp_no='10005';

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

alter table actor add column create_date datetime NOT NULL default '2020-10-01 00:00:00';

41、构造一个触发器audit_log

create trigger audit_log 
after insert on employees_test
for each row 
begin 
 insert into audit values (new.id,new.name);
end

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

delete from titles_test 
where id not in (select * from (select min(id) min_id
                                from titles_test
                                group by emp_no) a );

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

update titles_test set to_date=null,from_date='2001-01-01' 
                   where to_date='9999-01-01';

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

replace into titles_test values('5','10005','Senior Engineer','1986-06-26','9999-01-01');

45、将titles_test表名修改为titles_2017

alter table titles_test rename titles_2017;

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

alter table audit add foreign key(emp_no) references employees_test(id);

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

update salaries a 
join emp_bonus b
on a.emp_no=b.emp_no
set a.salary=a.salary*(1+0.1)
where a.to_date='9999-01-01';

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

select concat(last_name,"'",first_name)
from employees;

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

select length('10,A,B')-length('10AB') cnt;

52、获取Employees中的first_name

select first_name
from employees
order by substr(first_name,-2) asc;

53、按照dept_no进行汇总

select dept_no,group_concat(emp_no)
from dept_emp
group by dept_no;

54、平均工资

 select avg(salary) avg_salary
from salaries
where to_date='9999-01-01' 
and salary !=(select max(salary)
             from salaries
             where to_date='9999-01-01')
and salary !=(select min(salary)
             from salaries
             where to_date='9999-01-01');

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

select *
from employees
limit 5,5;

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

select a.*
from employees a
where not exists (select b.emp_no
                 from dept_emp b
                 where a.emp_no=b.emp_no




    
);

59、获取有奖金的员工相关信息。

select a.emp_no,a.first_name,a.last_name,b.btype,c.salary,
       case when b.btype=1 then c.salary*0.1
            when b.btype=2 then c.salary*0.2
            else salary*0.3
            end bonus
from employees a 
join emp_bonus b on a.emp_no=b.emp_no
join salaries c on a.emp_no=c.emp_no
where c.to_date='9999-01-01';

60、统计salary的累计和running_total

select emp_no,salary,sum(salary) over(order by emp_no asc) running_total
from salaries
where to_date='9999-01-01';

61、对于employees表中，给出奇数行的first_name

select a.first_name
from employees a 
join(select first_name,row_number() over(order by first_name asc) t_rank
     from employees) b
on a.first_name=b.first_name
where b.t_rank%2=1;

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

select number 
from grade
group by number
having count(*)>=3;

63、刷题通过的题目排名

select *,DENSE_RANK() over(order by number desc) t_rank
from passing_number
order by t_rank asc,id asc;

64、找到每个人的任务

select a.id,a.name,b.content
from person a 
left join task b 
on a.id=b.person_id
order by a.id asc;

65、异常的邮件概率

select x.date,round(x.cnt/y.cnt,3) p
from(select a.date,count(*) cnt
     from email a 
     left join user b on a.send_id=b.id
     left join user c on a.receive_id=c.id
     where b.is_blacklist=c.is_blacklist
     and a.type='no_completed'
     group by a.date) x 
join (select a.date,count(*) cnt
      from email a 
      left join user b on a.send_id=b.id
      left join user c on a.receive_id=c.id
      where b.is_blacklist=c.is_blacklist
      group by a.date) y 
on x.date=y.date
order by x.date asc;

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

select user_id,max(date)
from login
group by user_id
order by user_id asc;

67、牛客每个人最近的登录日期(二)

select b.name u_n,c.name c_n,d.max_date date
from login a 
join user b on a.user_id=b.id
join client c on a.client_id=c.id
join (select user_id,max(date) max_date
     from login
     group by user_id) d on a.user_id=d.user_id
where a.date=d.max_date
order by u_n asc;

68、牛客每个人最近的登录日期(三)

select round((select count(*)
              from login a 
              join (select user_id,date_add(min(date),interval 1 day) second_date
                    from login 
                    group by user_id) b 
              on a.user_id=b.user_id
              where a.date=b.second_date)/
            (select count(distinct user_id)
             from login),3) p

69、牛客每个人最近的登录日期(四)

select x.date,ifnull(y.new,0) new
from login x 
left join (select a.first_date,count(*) new
           from (select user_id,min(date) first_date
                 from login 
                 group by user_id) a 
           group by a.first_date) y 
on x.date=y.first_date
group by x.date
order by x.date;

70、牛客每个人最近的登录日期(五)

select




    
 distinct x.date,round(ifnull(z.second_cnt/y.first_cnt,0),3) p
from login x
left join (select a.first_date,count(*) first_cnt
           from (select user_id,min(date) first_date
                 from login 
                 group by user_id) a 
           group by a.first_date) y 
on x.date=y.first_date
left join (select a.second_date,count(*) second_cnt
           from(select user_id,date second_date
                from login
                where (user_id,date) in (select user_id,date_add(min(date),interval 1 day)                        
                                         from login 
                                         group by user_id)) a
           group by a.second_date) z 
on x.date=date_add(z.second_date,interval -1 day)
order by x.date asc;

71、牛客每个人最近的登录日期(六)

select b.name u_n,a.date,sum(a.number) over(partition by b.name order by date asc) ps_sum
from passing_number a 
join user b on a.user_id=b.id
group by b.name,a.date
order by a.date asc,b.name;

72、考试分数(一)

select job,round(avg(score),3) avg
from grade 
group by job
order by avg(score) desc;

73、考试分数(二)

select a.*
from grade a 
join (select job,avg(score) avg 
      from grade 
      group by job) b 
on a.job=b.job
where a.score>b.avg
order by id asc;

74、考试分数(三)

select a.id,b.name,a.score
from (select *,dense_rank() over(partition by language_id order by score desc) t_rank
      from grade ) a 
join language b 
on a.language_id=b.id
where a.t_rank<=2
order by b.name asc,a.score desc, a.id asc;

75、考试分数(四)

select job, floor((count(*)+1)/2) start,ceil((count(*)+1)/2) end
from grade
group by job
order by job asc;

76、考试分数(五)

select a.*
(select  *,row_number() over(partition by job order by score desc) t_rank
from grade) a
(select job, floor((count(*)+1)/2) start,ceil((count(*)+1)/2) end
from grade
group by job) b
on a.job=b.job
where a.t_rank=b.start or a.t_rank=b.end
order by a.id asc;

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

select *
from order_info
where date>'2025-10-15'
and status='completed'
and product_name in('C++','Java','Python')
order by id asc;

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

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(*)>=2
order by user_id asc;

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

select *
from order_info
where 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(*)>=2)
and date>'2025-10-15'
and status='completed'
and product_name in('C++','Java','Python')
order by id asc;

80、牛客的课程订单分析(四)

select user_id,min(date) first_buy_date




    
,count(user_id) cnt
from order_info
where date > '2025-10-15'
and product_name in ('C++','Java','Python')
and status = 'completed'
group by user_id
having count(user_id)>=2
order by user_id;

81、牛客的课程订单分析(五)

select a.user_id,min(a.date) first_buy_date,max(a.date) second_buy_date,b.cnt
from (select user_id,date,row_number() over(partition by user_id order by date asc) t_rank
      from order_info
      where 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(*)>=2)
      and date>'2025-10-15'
      and status='completed'
      and product_name in('C++','Java','Python')) a 
join(select user_id,count(*) cnt
     from order_info
     where date>'2025-10-15'
     and status='completed'
     and product_name in('C++','Java','Python')
     group by user_id 
     having count(*)>=2) b 
on a.user_id=b.user_id
where a.t_rank<=2
group by a.user_id
order by a.user_id asc;

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

select a.id,a.is_group_buy,case when a.is_group_buy='YES' then NULL else b.name end client_name
from order_info a 
left join client b 
on a.client_id=b.id
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(*)>=2)
order by a.id asc;

83、牛客的课程订单分析(七)

select case when a.client_id=0 then 'GroupBuy' else b.name end source,a.cnt
(select client_id,counT(*) cnt
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(*)>=2)
group by client_id) a 
left join client b 
on a.client_id=b.id
order by source asc;

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

select job,sum(num) cnt 
from resume_info
where year(date)=2025
group by job
order by cnt desc;

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

select job,substr(date,1,7) mon,sum(num) cnt
from resume_info
where year(date)=2025
group by job,substr(date,1,7)
order by mon desc,cnt desc;

86、实习广场投递简历分析(三)

select a.job,a.first_year_mon,a.first_year_cnt,b.second_year_mon,b.second_year_cnt
from (select job,substr(date,1,7) first_year_mon,sum(num) first_year_cnt
      from resume_info
      where year(date)=2025
      group by job,substr(date,1,7)) a
join (select job,substr(date,1,7) second_year_mon,sum(num) second_year_cnt
      from resume_info
      where year(date)=2026
      group by job,substr(date,1,7)) b 
on a.job=b.job and substr(a.first_year_mon,-2)=substr(b.second_year_mon,-2)
order by a.first_year_mon desc,a.job desc;

87、最差是第几名(一)

select grade,sum(number) over(order by grade asc) t_rank
from class_grade
order by grade asc;

88、最差是第几名(二)

select a.grade
from (select grade,sum(number) over(order by grade asc) asc_total,sum(number) over(order by grade desc) desc_total,
      (select sum(number)  from class_grade) total
      from class_grade) a 
      where a.asc_total>=a.total/2 and a.desc_total>=a.total/2
order by a.grade asc;

89、获得积分最多的人(一)

select b.name,sum(a.grade_num) cnt
from grade_info a 
join user b 
on a.user_id=b.id
where a.type='add'
group by b.name
order by cnt desc 
limit 0,1;

90、获得积分最多的人(二)

select b.id,b.name,sum(a.grade_num) grade_sum
from grade_info a 
join user b 
on a.user_id=b.id
where a.type='add' 
group by b.id
having sum(a.grade_num)=(select a.cnt
                         from (select user_id,sum(grade_num) cnt 
                               from grade_info
                               where type='add'
                               group by user_id
                               order by cnt desc 
                               limit 0,1) a )
order by b.id asc;

91、获得积分最多的人(三)

select y.id,y.name,x.sum grade_sum
(select a.user_id,sum(a.grade_num*a.new_type) sum,dense_rank() over(order by sum(a.grade_num*a.new_type )desc) t_rank
from (select user_id,grade_num,case when type='add' then 1 else -1 end new_type