第一题
- 编写一个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
第二题
- 编写一个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'
)
- 编写一个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
- 编写一个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
)
第三题
- 编写一个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
- 编写一个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
第四题
- 编写一个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
- 编写一个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
第五题
- 编写一个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
- 编写一个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
第六题
- 编写一个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
- 编写一个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;
第七题
- 编写一个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
);
- 编写一个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
- 编写一个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
第八题
- 编写一个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
- 编写一个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的用户的联系人名字及电子邮件。结果字段: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
- 为每张发票编写一个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
第十题
- 查询各科成绩最高分,最低分,不及格率,中等率和优秀率。结果字段包含: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