牛客网数据库刷题答案

1、select * from employees where hire_date=(
    select max(hire_date) from employees
    );

2、select t.emp_no,t.birth_date,t.first_name,t.last_name,t.gender,t.hire_date from (select *,
       row_number() over (order by hire_date desc) as t_rank
from employees)as t where t.t_rank=3;


3、select s.emp_no,s.salary,s.from_date,s.to_date,dm.dept_no
from salaries s inner join dept_manager dm on s.emp_no = dm.emp_no order by s.emp_no;

4、select e.last_name, e.first_name, d.dept_no
from dept_emp d
         inner join employees e on d.emp_no = e.emp_no;
         
5、select e.last_name,e.first_name,de.dept_no
from employees e left join dept_emp de on e.emp_no = de.emp_no;

7、select distinct t.emp_no, t.count_sum as t
from (select *, count(emp_no) over (partition by emp_no) as count_sum
      from salaries) as t
where t.count_sum > 15;

8、select distinct salary
from salaries order by salary desc ;

10、select emp_no from employees where emp_no not in
(select emp_no from dept_manager);

11、select de.emp_no, dm.emp_no as manager
from dept_emp de
         inner join dept_manager dm on de.dept_no = dm.dept_no
where de.emp_no != dm.emp_no;


12、select t.dept_no, t.emp_no, t.max_sal as maxSalary
from (select de.dept_no                                   as dept_no,
             s.emp_no                                     as emp_no,
             s.salary                                     as salary,
             max(s.salary) over (partition by de.dept_no) as max_sal
      from dept_emp de
               inner join salaries s on de.emp_no = s.emp_no) as t
where t.salary = t.max_sal order by t.dept_no;

15、select * from employees where mod(emp_no,2)=1 and last_name!='Mary' order by hire_date desc ;


16、select * from (select distinct t.title,
       avg(s.salary) over (partition by t.title) as avg
from titles t,
     salaries s
where t.emp_no = s.emp_no) as t order by t.avg;


17、select t.emp_no, t.salary
from (select *,
             row_number() over (order by salary desc ) t_rank
      from salaries) as t
where t.t_rank = 2;



18、select e.emp_no,s1.salary,e.last_name,e.first_name
from employees e,
     salaries s1
where e.emp_no = s1.emp_no
  and 1 = (select count(*) from salaries s2 where s2.salary > s1.salary);


19、select e.last_name,e.first_name,d.dept_name
from employees e
         left join dept_emp de on e.emp_no = de.emp_no
         left join departments d on de.dept_no = d.dept_no;


21、select s1.emp_no, (s2.salary - s1.salary) as growth
from employees e,
     salaries s1,
     salaries s2
where e.emp_no = s1.emp_no
  and s1.emp_no = e.emp_no
  and s1.from_date = e.hire_date
  and s2.to_date = '9999-01-01'
  and s2.salary - s1.salary >= 0
  and s1.emp_no = s2.emp_no
order by growth;


22、select distinct de.dept_no,
                d.dept_name,
                count(s.salary) over (partition by de.dept_no) as sum
from dept_emp de
         inner join departments d on de.dept_no = d.dept_no
         inner join salaries s on de.emp_no = s.emp_no order by de.dept_no;

23、select emp_no,
       salary,
       dense_rank() over (order by salary desc) as t_rank
from salaries
order by salary desc, emp_no;



24、select de.dept_no,e.emp_no,s.salary
from employees e,
     dept_emp de,
     salaries s
where e.emp_no = de.emp_no
  and de.emp_no = s.emp_no
  and e.emp_no not in (select emp_no from dept_manager);



25、select t.emp_no, t.manager_emp, s1.salary, s2.salary
from salaries s1,
     salaries s2,
     (select de.emp_no as emp_no, dm.emp_no as manager_emp
      from dept_emp de,
           dept_manager dm,
           salaries s1,
           salaries s2
      where de.dept_no = dm.dept_no
        and s1.emp_no = de.emp_no
        and s2.emp_no = de.emp_no
        and de.emp_no != dm.emp_no) as t
where t.emp_no = s1.emp_no
  and t.manager_emp = s2.emp_no
  and s1.salary > s2.salary;


26、select distinct d.dept_no,
       d.dept_name,
       t.title,
       count(t.title) over (partition by d.dept_no,t.title) as count
from dept_emp de,
     departments d,
     titles t
where de.emp_no = t.emp_no
  and de.dept_no = d.dept_no order by d.dept_no;


28、select distinct b.name, b.count
from (select category_id, count(film_id) as a_count
      from film_category
      where category_id in (select c.category_id
                           from film_category fc,
                                film f,
                                category c
                           where fc.film_id = f.film_id
                             and fc.category_id = c.category_id
                             and f.description like '%robot%')
      group by category_id) as a,
     (select c.category_id as b_category_id, c.name as name, count(f.film_id) over (partition by c.category_id) as count
      from film_category fc,
           film f,
           category c
      where fc.category_id = c.category_id
        and fc.film_id = f.film_id
        and f.description like '%robot%') as b
where a.category_id = b.b_category_id
  and a.a_count >= 5;


29、select film_id, title
from film
where film_id not in (select fc.film_id
                      from category c
                               inner join film_category fc on c.category_id = fc.category_id);


30、select title, description
from film
where film_id in (select film_id
                  from film_category
                  where category_id = (select category_id
                                       from category
                                       where name = 'Action'));



32、select concat(last_name, ' ', first_name)
from employees;


33、create table actor(
    actor_id smallint(5) not null primary key comment '主键id',
    first_name varchar(45) not null comment '名字',
    last_name varchar(45) not null comment '姓氏',
    last_update date not null comment '日期'
)


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


35、insert into actor select * from actor where first_name='ED';

36、create table actor_name (
    first_name varchar(45) not null ,
    last_name varchar(45) not null
) select first_name,last_name from actor;

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


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

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


40、alter table actor add `create_date` datetime not null default '2020-10-01 00:00:00';

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


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


43、update titles_test set to_date=NULL ,from_date='2001-01-01' where to_date='9999-01-01';

44、update titles_test 
set emp_no = replace(emp_no, 10001, 10005)
where id = 5;


45、rename table `titles_test` to `titles_2017`

46、alter table audit add constraint emp_no foreign key(EMP_no) references employees_test(ID);

48、update salaries
set salary=salary*1.1
where emp_no in (select emp_no from emp_bonus) and to_date='9999-01-01';

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

51、select length('10,A,B')-length(replace('10,A,B',',',''));


52、select first_name from employees order by right(first_name, 2);

53、select dept_no, group_concat(emp_no separator ',')
from dept_emp
group by dept_no;


54、select avg(salary)
from salaries
where emp_no not in (select emp_no
                     from salaries
                     where salary = (select max(salary)
                                     from salaries
                                     where to_date = '9999-01-01')
                        or salary = (select min(salary)
                                     from salaries
                                     where to_date = '9999-01-01'))
  and to_date = '9999-01-01';



55、select * from employees limit 5,5;


57、select *
from employees e
where not exists(select * from dept_emp de where e.emp_no = de.emp_no);



59、select e.emp_no,
       e.first_name,
       e.last_name,
       eb.btype,
       s.salary,
       if(eb.btype = 1, s.salary * 0.1, if(eb.btype = 2, s.salary * 0.2, s.salary * 0.3)) as bonus
from employees e,
     salaries s,
     emp_bonus eb
where e.emp_no = eb.emp_no
  and eb.emp_no = s.emp_no
  and s.to_date = '9999-01-01';



60、select t.first_name
from (select *,
             row_number() over (order by first_name) as t_rank
      from employees) as t
where t.t_rank % 2 = 1 order by t.emp_no asc ;



61、select distinct t.number from (select *,
       count(number) over(partition by number) as count
       from grade) as t where t.count>=3;

62、select *,dense_rank() over(order by number desc)
from passing_number;

63、select p.id,p.name,t.content from person p left join task t on p.id=t.person_id;


64、select p.id,p.name,t.content from person p left join task t on p.id=t.person_id;


65、select a.date, round(b.count_no_completed / (a.count_completed + b.count_no_completed), 3) as p
from (select distinct e.date,
                      count(e.type) over (partition by e.date) as count_completed
      from email e,
           user u1,
           user u2
      where e.send_id = u1.id
        and e.receive_id = u2.id
        and u1.is_blacklist = 0
        and u2.is_blacklist = 0
        and e.type = 'completed') as a,
     (select distinct e.date,
                      count(e.type) over (partition by e.date) as count_no_completed
      from email e,
           user u1,
           user u2
      where e.send_id = u1.id
        and e.receive_id = u2.id
        and u1.is_blacklist = 0
        and u2.is_blacklist = 0
        and e.type = 'no_completed') as b
where a.date = b.date;


66、select t.user_id, t.date
from (select *,
             dense_rank() over (partition by user_id order by date desc ) as t_rank
      from login) as t
where t.t_rank = 1;


67、select t.name, t.c_name, t.date
from (select u.name                                                     as name,
             c.name                                                     as c_name,
             l.date                                                     as date,
             dense_rank() over (partition by u.id order by l.date desc) as t_rank
      from login l,
           user u,
           client c
      where l.user_id = u.id
        and l.client_id = c.id) as t
where t.t_rank = 1 order by t.name;


68、select round(count(distinct if(datediff(t2.date, t1.date) = 1, t2.user_id, null)) / count(distinct t1.user_id),3) as p
from (select * from login) as t1
         left join
         (select * from login) as t2
         on t1.user_id = t2.user_id;


69、select t.date, sum(if(t.t_rank = 1, 1, 0))
from (select *,
             row_number() over (partition by user_id order by date) as t_rank
      from login) as t group by t.date;


70、
select tab.a_date, round(if(tab.c2 = 0, 0, tab.c2 / tab.c1), 3)
from (select t.a_date,
             count(if(t.a_t_rank = 1, t.a_user_id, null))                   as c1,
             count(if(datediff(t.b_date, t.a_date) = 1, t.b_user_id, null)) as c2
      from (select a.user_id                                                  as a_user_id,
                   a.date                                                     as a_date,
                   b.date                                                     as b_date,
                   b.user_id                                                  as b_user_id,
                   row_number() over (partition by a.user_id order by a.date) as a_t_rank,
                   row_number() over (partition by b.user_id order by b.date) as b_t_rank
            from (select * from login) as a
                     left join (select * from login) as b on a.user_id = b.user_id) as t
      group by t.a_date) as tab;


71、select a.name, a.date, sum(a.number) over (partition by a.name order by a.date) as ps_num
from (select u.name, t.date, t.number
      from user u
               inner join (select distinct pm.date,
                                           pm.user_id,
                                           pm.number
                           from passing_number pm
                                    left join login l on pm.date = l.date) as t on u.id = t.user_id
      order by t.date, u.name) as a;


72、select t.job, round(t.avg,3)
from (select distinct job,
                      avg(score) over (partition by job) as avg
      from grade) as t
order by t.avg desc;



73、select *
from grade
where id in (select if(t.score > t.avg, t.id, null)
             from (select distinct *, avg(score) over (partition by job) as avg
                   from grade) as t) order by id;


74、select t.id, t.name, t.score
from (select g.id, l.name, g.score, dense_rank() over (partition by l.name order by g.score desc) as t_rak
      from grade g
               inner join language l on g.language_id = l.id
      order by l.name, g.score desc) as t
where t.t_rak <= 2;



75、select distinct t.job,
                if(t.t_count % 2 = 0, round(t.t_count / 2, 0), ceil(t.t_count / 2))     as start,
                if(t.t_count % 2 = 0, round(t.t_count / 2 + 1, 0), ceil(t.t_count / 2)) as end
from (select job, score, count(*) over (partition by job) as t_count
      from grade
      order by job) as t;


76、select a.id, a.job, a.score, a.t_rank
from (select *,
             if(t.t_count % 2 = 0, round(t.t_count / 2, 0), ceil(t.t_count / 2))     as start,
             if(t.t_count % 2 = 0, round(t.t_count / 2 + 1, 0), ceil(t.t_count / 2)) as end
      from (select *,
                   row_number() over (partition by job order by score desc ) as t_rank,
                   count(*) over (partition by job)                    as t_count
            from grade
            order by id) as t) as a
where a.t_rank between a.start and a.end;



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


78、select distinct t.user_id from (select *, count(product_name) over (partition by user_id) as count
from order_info
where product_name in ('C++', 'Java', 'Python')
  and date > '2025-10-15'
  and status = 'completed'
order by id) as t where t.count>=2 order by t.user_id;


79、select t.id, user_id, product_name, status, client_id, date
from (select *, count(product_name) over (partition by user_id) as count
      from order_info
      where product_name in ('C++', 'Java', 'Python')
        and date > '2025-10-15'
        and status = 'completed'
      order by id) as t where t.count>=2
order by t.id;


80、select a.user_id, a.date, a.cnt
from (select t.user_id,
             t.date,
             count(*) over (partition by t.user_id)                     as cnt,
             row_number() over (partition by t.user_id order by t.date) as t_rak
      from (select *, count(product_name) over (partition by user_id) as count
            from order_info
            where product_name in ('C++', 'Java', 'Python')
              and date > '2025-10-15'
              and status = 'completed'
            order by id) as t
      where t.count >= 2
      order by t.id) as a
where a.t_rak = 1 order by a.user_id;



81、select tb1.user_id, tb1.date, tb2.date, tb2.cnt
from (select a.*
      from (select t.user_id,
                   t.date,
                   count(*) over (partition by t.user_id)                     as cnt,
                   row_number() over (partition by t.user_id order by t.date) as t_rank
            from (select *, count(product_name) over (partition by user_id) as count
                  from order_info
                  where product_name in ('C++', 'Java', 'Python')
                    and date > '2025-10-15'
                    and status = 'completed'
                  order by id) as t
            where t.count >= 2
            order by t.id) as a
      where a.t_rank = 1) as tb1,
     (select a.*
      from (select t.user_id,
                   t.date,
                   count(*) over (partition by t.user_id)                     as cnt,
                   row_number() over (partition by t.user_id order by t.date) as t_rank
            from (select *, count(product_name) over (partition by user_id) as count
                  from order_info
                  where product_name in ('C++', 'Java', 'Python')
                    and date > '2025-10-15'
                    and status = 'completed'
                  order by id) as t
            where t.count >= 2
            order by t.id) as a
      where a.t_rank = 2) as tb2
where tb1.user_id = tb2.user_id order by tb1.user_id;


82、select t.id, t.is_group_buy, t.c_name
from (select o.*,
             c.id                                                as c_id,
             c.name                                              as c_name,
             count(o.product_name) over (partition by o.user_id) as cnt
      from order_info o
               left join client c on o.client_id = c.id
      where o.product_name in ('C++', 'Java', 'Python')
        and o.status = 'completed'
        and o.date > '2025-10-15') as t
where t.cnt >= 2
order by t.id;



83、select distinct tb.source, count(tb.source) over (partition by tb.source) as cnt
from (select t.id, t.is_group_buy, if(t.is_group_buy = 'No', t.c_name, 'GroupBuy') as source
      from (select o.*,
                   c.id                                                as c_id,
                   c.name                                              as c_name,
                   count(o.product_name) over (partition by o.user_id) as cnt
            from order_info o
                     left join client c on o.client_id = c.id
            where o.product_name in ('C++', 'Java', 'Python')
              and o.status = 'completed'
              and o.date > '2025-10-15') as t
      where t.cnt >= 2
      order by t.id) as tb
order by tb.source;



84、select distinct job, sum(num) over (partition by job) as sum_total
from resume_info
where year(date) = '2025' order by sum_total desc ;


85、select *
from (select distinct job,
                      left(date, 7)                                  as mon,
                      sum(num) over (partition by job,left(date, 7)) as cnt
      from resume_info
      where year(date) = '2025') as t
order by t.mon desc, t.cnt desc;


86、select distinct a.job,
                a.date as a_date,
                a.cnt  as a_cnt,
                b.date as b_date,
                b.cnt  as b_cnt
from (select distinct job,
                      left(date, 7)                                  as date,
                      sum(num) over (partition by job,left(date, 7)) as cnt
      from resume_info
      where year(date) = '2025') as a,
     (select distinct job,
                      left(date, 7)                                  as date,
                      sum(num) over (partition by job,left(date, 7)) as cnt
      from resume_info
      where year(date) = '2026') as b
where a.job = b.job
  and substr(a.date, 6) = substr(b.date, 6)
order by a.date
    desc, a.job desc;


87、select grade, sum(number) over (order by grade) as t_rank
from class_grade
order by grade;


88、select t.grade
from (select *,
             sum(number) over (order by grade) as count_grade,
             sum(number) over ()               as count
      from class_grade
      order by grade) as t
where if(t.count % 2 = 1, t.count_grade > t.count / 2 and t.count_grade < t.count / 2 + t.number,
         t.count_grade >= t.count / 2 and t.count_grade <= t.count / 2 + t.number
          );