文章目录
题目来源
最近上的数据库开发课程布置了一些sql题目，写到头秃……
题目及mysql语句如下
第一题
- 编写一个sql语句，查询累计工作时间超过1000的职工，结果返回职工工号eno。
select
eno
from
works
group by
eno
having
sum(hours) > 1000
- 编写一个sql语句，查询没有登记家属的职工，结果返回职工工号eno。
select
eno
from
employees
where
not exists (
select
*
from
relations
where
employees.eno = relations.eno
)
第二题
- 编写一个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
)
)
第三道
- 编写一个sql语句，查询各组进行的比赛场次，结果返回组号group_id及各组的场次数match_num。
SELECT
group_id,
COUNT(group_id) AS match_num
FROM
matches
GROUP BY
(group_id)
- 编写一个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
第四题
- 编写一个sql查询，查找订单数量最多的一天以及当天的订单数量，订单数相同时选择日期最小的一天，结果返回日期order_date和订单数量num。
select
order_date,
count(order_id) as num
from
orders
group by
order_date
order by
count(*) desc
limit
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
第五题
- 编写一个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
- 编写一个sql语句，查询发出过申请，但所有申请都未通过的用户，结果返回用户编号user_id。
select
distinct sender_id as user_id
from
friend_requests
where
sender_id not in(
select
requester_id
from
accepted_requests
)
第六题
- 编写一个sql语句，查询所有浏览过自己文章的作者，结果返回用户编号id，以id升序排列。
select
distinct author_id id
from
views
where
author_id = viewer_id
order by
id
- 编写一个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
- 编写一个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
第七题
- 编写一个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
第八题
- 编写一个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"
- 编写一个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
第九题
- 查找在 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
- 求每部电影的最高分，最低分以及平均分。结果字段包含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
- 查找用户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
第十题
- 查询金额最大的发票所对应的发票号，用户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
- 求拥有联系人的顾客的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