数据库题目汇总（下）

第一题

1. 编写一个sql语句，查询每一个部门中薪水最高的职工，结果返回部门编号dno，薪水最高的职工工号eno和薪水salary，以dno升序排列。

select
  b.dno,
  a.eno,
  b.max_salary as salary
from
  employees as a,(
    select
      dno,
      max(salary) as max_salary
    from
      employees
    group by
      dno
  ) as b
where
  a.dno = b.dno
  and a.salary = b.max_salary
order by
  b.dno asc

第二题

1. 编写一个sql语句，找出年龄在35以上的并且在2020-09-01至2020-09-30期间没有预定红色（RED）船只的水手，结果返回水手姓名sname。

select
  s.sname
from
  sailors s
where
  s.age > 35
  and not exists(
    select
      *
    from
      reserves r,
      boats b
    where
      r.sid = s.sid
      and r.bid = b.bid
      and b.color = "RED"
      and r.reserve_date between '2020-09-01'
      and '2020-09-30'
  )

2. 编写一个sql语句，找出2020-05-01至2020-05-31期间预定过绿色船（GREEN）的等级最高的水手，结果返回水手姓名sname。

select sname from(select
  s.sname,
  s.rating
from
  sailors s,
  reserves r,
  boats b
where
  r.sid = s.sid
  and r.bid = b.bid
  and b.color = "GREEN"
  and r.reserve_date between '2020-05-01'
  and '2020-05-31'
order by
  s.rating desc) as t1
limit
  1

3. 编写一个sql语句，找出年龄在35岁以上，并且在2020-08-01至2020-08-31期间同时预定了红色船（RED）和绿色船（GREEN）的水手，结果返回水手姓名sname。

select
  sname
from(
    select
    distinct  s.sname
    from
      sailors s,
      reserves r,
      boats b
    where
      r.sid = s.sid
      and r.bid = b.bid
      and b.color = "GREEN"
      and r.reserve_date between '2020-08-01'
      and '2020-08-31'
      and s.age > 35
  ) as t1
where
  exists(
    select
    distinct  s.sname
    from
      sailors s,
      reserves r,
      boats b
    where
      r.sid = s.sid
      and r.bid = b.bid
      and b.color = "RED"
      and r.reserve_date between '2020-08-01'
      and '2020-08-31'
      and s.age > 35
  )

第三题

1. 编写一个sql语句，找出2020-08-01至2020-08-31期间订单数量最多的客户，数量相同时选择customer_id较小的用户，结果返回用户编号customer_id和购买数量order_num。

select
  customer_id,
  count(order_id) as order_num
from
  orders
where
  order_date between "2020-08-01"
  and "2020-08-31"
group by
  customer_id
order by
  count(*) desc
limit
  1

2. 编写一个sql语句，找到每个用户最近三笔订单。若用户订单少于3笔，则返回该用户的全部订单，结果返回用户名customer_name，订单编号order_id和订单日期order_date，以custromer_name升序，order_date降序排列。

select
  name customer_name,
  order_id,
  order_date
from
  (
    select
      o1.customer_id,
      o1.order_id,
      o1.order_date,
      (
        select
          1 + count(*)
        from
          orders o7
        where
          o7.customer_id = o1.customer_id
          and o7.order_date > o1.order_date
      ) as rnk
    from
      orders o1
  ) t
  left join customers using(customer_id)
where
  rnk <= 3
order by
  name asc,
  order_date desc

第四题

1. 编写一个sql语句，查询参加比赛场次最多的选手，若参与比赛场次相同，选择用户编号player_id较小的选手，结果返回用户编号player_id和参与的比赛数量match_num。

select
  player_id,
  count(match_id) as match_num
from(
    select
      first_player as player_id,
      match_id
    from
      matches
    union all
    select
      second_player as player_id,
      match_id
    from
      matches
  ) as ps
group by
  player_id
order by
  match_num desc
limit
  1

2. 编写一个sql语句，查找每组中的获胜者。每组的获胜者是在组内累积得分最高的选手。如果有得分相同的情况，则认为player_id 最小的选手获胜，结果返回组号group_id和选手编号player_id，以group_id升序排列。

select
  group_id,
  player_id
from(
    select
      p.group_id,
      p.player_id,
      sum(ps.score) as score
    from
      players as p
      inner join(
        select
          first_player as player_id,
          first_score as score
        from
          matches
        union all
        select
          second_player as player_id,
          second_score as score
        from
          matches
      ) as ps on p.player_id = ps.player_id
    group by
      player_id
    order by
      group_id,
      score desc,
      player_id
  ) as top_scores
group by
  group_id

第五题

1. 编写一个sql语句，查询每个用户的注册日期以及在2019年作为买家的订单总数，结果返回用户编号user_id，注册日期join_date和订单数量orders_in_2019，以user_id升序排列。

select
  u.user_id,
  join_date,
  count(o.order_id) as orders_in_2019
from
  users u
  left join orders o on u.user_id = o.buyer_id
  and year(o.order_date) = '2019'
group by
  u.user_id

2. 编写一个sql语句，查询每一个用户按顺序卖出的第二件商品是否是他们最喜爱的品牌。结果返回卖家编号seller_id和是否是最喜爱品牌的情况if_fav_brand（取值为’yes’，‘no’，售出小于2件时返回 ‘no’），以seller_id升序排列。

select
  users.user_id as seller_id,
  if(
    O3.item_id is not null
    and users.favorite_brand = items.item_brand,
    'yes',
    'no'
  ) as 'if_fav_brand'
from
  users
  left join (
    select
      seller_id,(
        select
          item_id
        from
          orders O2
        where
          O2.seller_id = O1.seller_id
        order by
          order_date
        limit
          1, 1
      ) as item_id
    from
      orders O1
    group by
      seller_id
  ) O3 on users.user_id = O3.seller_id
  left join items on O3.item_id = items.item_id

第六题

1. 编写一个sql语句，查询每个月的通过率，结果返回月份mon及通过率accept_rate（不包括通过率为0的月份），以mon升序排列。

select
  a_m1.mon,
  a_m2.count2 / a_m1.count1 as accept_rate
from
  (
    select
      mon,
      count(*) as count1
    from(
        select
          distinct sender_id,
          send_to_id,
          month(request_date) as mon
        from
          friend_requests
      ) as t1
    group by
      mon
  ) as a_m1,(
    select
      mon,
      count(*) as count2
    from(
        select
          distinct requester_id,
          accepter_id,
          month(accept_date) as mon
        from
          accepted_requests
      ) as t1
    group by
      mon
  ) as a_m2
where
  a_m1.mon = a_m2.mon
  and a_m1.count1 / a_m2.count2 != 0
group by
  a_m1.mon asc

2. 编写一个sql语句，找出拥有最多的好友的用户以及他拥有的好友数目，好友数相同时选择user_id较小的用户，结果返回用户编号user_id和好友数目friend_num。

select
  user_id,
  count(*) friend_num
from
  (
    select
      requester_id user_id
    from
      accepted_requests
    union all
    select
      accepter_id user_id
    from
      accepted_requests
  ) as t1
group by
  user_id
order by
  friend_num desc
limit
  1;

第七题

1. 编写一个sql语句，查询新登录用户的留存率，即新用户第1天登陆之后，第2天再次登陆的概率，结果返回留存率rate。

select
  round(
    count(distinct user_id) * 1.0 /(
      select
        count(distinct user_id)
      from
        logins
    ),
    3
  ) as rate
from
  logins
where
  (user_id, login_date) in (
    select
      user_id,
      DATE_ADD(min(login_date), INTERVAL 1 DAY)
    from
      logins
    group by
      user_id
  );

2. 编写一个sql语句，查询登录新用户个数不少于2个的日期，结果返回日期login_date和登录新用户个数new_user_num，以login_date升序排序。

select
  t1.login_date,
  count(t2.user_id) as new_user_num
from
  (
    select
      distinct login_date
    from
      logins
  ) t1
  left join (
    select
      user_id,
      min(login_date) first_date
    from
      logins
    group by
      user_id
  ) t2 on t1.login_date = t2.first_date
group by
  t1.login_date
having
  new_user_num >= 2
order by
  t1.login_date asc

3. 编写一个sql语句，查询每个日期新用户次日留存率（包括留存率为0的日期），即该日登录的新用户第二日仍然登录的概率，返回日期date和留存率rate，保留小数点后3位，以date升序排列。

select
  t1.login_date as date,
  round(
    count(distinct logins.user_id) / count(t1.user_id),
    3
  ) as rate
from
  (
    select
      user_id,
      min(login_date) as login_date
    from
      logins
    group by
      user_id
  ) as t1
  left join logins on logins.user_id = t1.user_id
  and logins.login_date = DATE_ADD(t1.login_date, INTERVAL 1 DAY)
group by
  t1.login_date
union
select
  login_date as date,
  0.000 as rate
from
  logins
where
  login_date not in(
    select
      min(login_date)
    from
      logins
    group by
      user_id
  )
order by
  date

第八题

1. 编写一个sql语句，获取各个部门第二高的薪水，结果返回部门名称department，员工姓名name和工资salary，以department升序排列。

select
  d.department_name as department,
  e.name,
  e.salary
from
  employees e,
  departments d
where
  e.department_id = d.department_id
  and salary = IFNULL(
    (
      select
        distinct e1.salary
      from
        employees as e1
      where
        e.department_id = e1.department_id
      order by
        e1.salary desc
      limit
        1, 1
    ), 0
  )
order by
  department

2. 编写一个sql语句，找出每个部门获得前三高工资的所有员工，结果返回部门名称department，员工姓名name和工资salary，以department升序，salary降序排列。

select
  d.department_name as department,
  e1.name as name,
  e1.salary
from
  employees e1
  join departments d on e1.department_id = d.department_id
where
  3 > (
    select
      count(distinct e2.salary)
    from
      employees e2
    where
      e2.salary > e1.salary
      and e1.department_id = e2.department_id
  )
order by
  department asc,
  e1.salary desc

第九题

1. 求拥有发票数大于1的用户的联系人名字及电子邮件。结果字段：user_id，contact_name，contact_email

select
  contacts.user_id,
  contacts.contact_name,
  contacts.contact_email
from
  contacts,(
    select
      user_id
    from
      invoices
    group by
      user_id
    having
      count(invoice_id) > 1
  ) as t1
where
  contacts.user_id = t1.user_id

2. 为每张发票编写一个SQL查询，结果字段包含：invoice_id，customer_name，price，contacts_cnt（该顾客的联系人数量），trusted_contacts_cnt（可信联系人的数量）。查询的结果按照 invoice_id 排序。

select
  invoices.invoice_id,
  t1.customer_name,
  invoices.price,
  count(contacts.user_id) as contacts_cnt,
  count(t2.email) as trusted_contacts_cnt
from
  invoices
  join customers t1 on invoices.user_id = t1.customer_id
  left join contacts on t1.customer_id = contacts.user_id
  left join customers t2 on contacts.contact_email = t2.email
group by
  invoices.invoice_id

第十题

1. 查询各科成绩最高分，最低分，不及格率，中等率和优秀率。结果字段包含：c_id，c_name，max_score，min_score，‘不及格率’，‘中等率’和’优秀率’（及格率、中等率和优秀率结果在0到1之间，保留两位小数）。注：不及格<60，中等>=60且<90，优秀>=90。

select t1.c_id,courses.c_name,t1.max_score,t1.min_score,t1.不及格率 as '不及格率',t1.中等率 as '中等率',t1.优秀率 as '优秀率' from courses,(select c_id,max(s_score) as max_score,min(s_score) as min_score,round((sum(case when s_score < 60 then 1 else 0 end)/ count(*)),2) as 不及格率,round((sum(case when s_score >= 60 and s_score <90 then 1 else 0 end)/ count(*)),2) as 中等率,round((sum(case when s_score >= 90 then 1 else 0 end)/ count(*)),2) as 优秀率 from scores group by c_id) as t1 where t1.c_id=courses.c_id