文章目录

• • 题目来源
  • 题目及mysql语句如下
  • • 第一题
    • 第二题
    • 第三道
    • 第四题
    • 第五题
    • 第六题
    • 第七题
    • 第八题
    • 第九题
    • 第十题

题目来源

最近上的数据库开发课程布置了一些sql题目，写到头秃……

题目及mysql语句如下

第一题

1. 编写一个sql语句，查询累计工作时间超过1000的职工，结果返回职工工号eno。

select
  eno
from
  works
group by
  eno
having
  sum(hours) > 1000

2. 编写一个sql语句，查询没有登记家属的职工，结果返回职工工号eno。

select
  eno
from
  employees
where
  not exists (
    select
      *
    from
      relations
    where
      employees.eno = relations.eno
  )

第二题

1. 编写一个sql语句，找出预定了所有船的水手，结果返回水手姓名sname。

select
  sname
from
  sailors
where
  not exists (
    select
      *
    from
      boats
    where
      not exists (
        select
          *
        from
          reserves
        where
          sailors.sid = reserves.sid
          and boats.bid = reserves.bid
      )
  )

第三道

1. 编写一个sql语句，查询各组进行的比赛场次，结果返回组号group_id及各组的场次数match_num。

SELECT
  group_id,
  COUNT(group_id) AS match_num
FROM
  matches
GROUP BY
  (group_id)

2. 编写一个sql语句，查询分差最大的一场比赛，若分差相同，选择编号match_id较小的比赛，结果返回比赛编号match_id和分差sub（大于0）。

SELECT
  match_id,
  abs(first_score - second_score) as sub
FROM
  matches
ORDER BY
  abs(first_score - second_score) DESC
LIMIT
  1

第四题

1. 编写一个sql查询，查找订单数量最多的一天以及当天的订单数量，订单数相同时选择日期最小的一天，结果返回日期order_date和订单数量num。

select
  order_date,
  count(order_id) as num
from
  orders
group by
  order_date
order by
  count(*) desc
limit
  1

2. 编写一个sql语句，查找买到过自己最喜欢的商品的用户，结果返回用户编号user_id。

select
  users.user_id
from
  users,
  orders,
  items
where
  users.user_id = orders.buyer_id
  and orders.item_id = items.item_id
  and users.favorite_brand = items.item_brand

第五题

1. 编写一个sql语句，求出好友申请的总通过率accept_rate，用2位小数表示。通过率计算公式为接受好友申请的数目除以申请总数（申请和接收可能会有重复，此时均视作一次）。

select
  round(
    ifnull(
      (
        select
          count(*)
        from
          (
            select
              distinct requester_id,
              accepter_id
            from
              accepted_requests
          ) as b
      ) /(
        select
          count(*)
        from
          (
            select
              distinct sender_id,
              send_to_id
            from
              friend_requests
          ) as a
      ),
      0
    ),
    2
  ) as accept_rate

2. 编写一个sql语句，查询发出过申请，但所有申请都未通过的用户，结果返回用户编号user_id。

select
  distinct sender_id as user_id
from
  friend_requests
where
  sender_id not in(
    select
      requester_id
    from
      accepted_requests
  )

第六题

1. 编写一个sql语句，查询所有浏览过自己文章的作者，结果返回用户编号id，以id升序排列。

select
  distinct author_id id
from
  views
where
  author_id = viewer_id
order by
  id

2. 编写一个sql语句，找出曾在一天内阅读至少两篇文章的人，结果返回用户编号viewer_id，以viewer_id升序排列。

select
  distinct viewer_id
from
  views
group by
  view_date,
  viewer_id
having
  count(distinct article_id) >= 2
ORDER BY
  viewer_id

3. 编写一个sql语句，找出阅读文章总数最多的用户，阅读数量一样时选择view_id较小的用户，结果返回用户编号viewer_id和阅读文章数量article_num。

select
  viewer_id,
  count(article_id) as article_num
from
  views
group by
  viewer_id
order by
  count(*) desc
limit
  1

第七题

1. 编写一个sql语句，查询每个用户最近一天登录的日子，结果返回用户编号user_id和登录日期date，以user_id升序排列。

select
  user_id,
  login_date as date
from
  logins a
where
  not exists(
    select
      1
    from
      logins
    where
      user_id = a.user_id
      and login_date > a.login_date
  )
order by
  user_id asc

第八题

1. 编写一个sql语句，查找Technology部门工资的平均值，结果返回部门名称department和平均工资avg_salary。

select
  departments.department_name as department,
  avg(employees.salary) as avg_salary
from
  departments,
  employees
where
  employees.department_id = departments.department_id
  and department_name = "Technology"

2. 编写一个sql语句，查找各部门最高工资与最低工资的差值，结果返回部门名称department和差值sub。

select
  department_name as department,(max(salary) - min(salary)) as sub
from
  employees,
  departments
where
  departments.department_id = employees.department_id
group by
  employees.department_id

第九题

1. 查找在 2020 年 2 月 平均评分最高 的电影名称。 如果有相同的，返回字典序较小的电影名称。结果字段：title

select
  title
from
  movie_rating r
  left join movies m on r.movie_id = m.movie_id
where
  date_format(created_at, '%Y-%m') = '2020-02'
group by
  r.movie_id
order by
  avg(rating) desc,
  title
limit
  1

2. 求每部电影的最高分，最低分以及平均分。结果字段包含movie_id, title, avg_rating, max_rating, min_rating。

select
  movies.movie_id,
  movies.title,
  avg(movie_rating.rating) as avg_rating,
  max(movie_rating.rating) as max_rating,
  min(movie_rating.rating) as min_rating
from
  movie_rating,
  movies
where
  movie_rating.movie_id = movies.movie_id
group by
  movie_rating.movie_id

3. 查找用户id为1的用户看过的电影以及所给的评分。结果字段包含user_id，name，title，rating。

select
  users.user_id,
  users.name,
  movies.title,
  movie_rating.rating
from
  users,
  movies,
  movie_rating
where
  users.user_id = movie_rating.user_id
  and users.user_id = 1
  and movies.movie_id = movie_rating.movie_id

第十题

1. 查询金额最大的发票所对应的发票号，用户ID，用户姓名。结果字段：invoice_id，price, user_id，customer_name

select
  i1.invoice_id,
  i1.price,
  i1.user_id,
  customers.customer_name
from
  customers,
  invoices as i1,(
    select
      max(price) as maxprice
    from
      invoices
  ) as i2
where
  i1.price = i2.maxprice
  and i1.user_id = customers.customer_id

2. 求拥有联系人的顾客的id和姓名以及联系人姓名。结果字段customer_id，customer_name，contact_name

select
  customers.customer_id,
  customers.customer_name,
  contacts.contact_name
from
  customers,
  contacts
where
  customers.customer_id = user_id