数据库题目汇总(上)

文章目录

题目来源

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

题目及mysql语句如下

第一题

数据库题目汇总(上)

  1. 编写一个sql语句,查询累计工作时间超过1000的职工,结果返回职工工号eno。
select
  eno
from
  works
group by
  eno
having
  sum(hours) > 1000
  1. 编写一个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)
  1. 编写一个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
  1. 编写一个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
  1. 编写一个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
  1. 编写一个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
  1. 编写一个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"
  1. 编写一个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
  1. 求每部电影的最高分,最低分以及平均分。结果字段包含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
  1. 查找用户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
  1. 求拥有联系人的顾客的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
上一篇:python个性化电影推荐第三版源码+论文


下一篇:Hive -e时的双引号和反斜杠问题