文章目录
主要内容
- LeetCode–高频·SQL50题(基础版)11-20
一.SQL练习题
1.577-员工奖金
代码如下(示例):
# Write your MySQL query statement below
select e.name,b.bonus
from employee e
left join bonus b
on e.empId = b.empId
where b.bonus<1000 or b.bonus is null;
2.1280-学生们参加各科测试的次数
代码如下(示例):
# Write your MySQL query statement below
select s.student_id,s.student_name,su.subject_name,
count(e.subject_name) as attended_exams
from students s
cross join subjects su
left join examinations e
on e.student_id = s.student_id
and e.subject_name = su.subject_name
group by s.student_id,su.subject_name
order by s.student_id,su.subject_name;
3.570-至少有5名直接下属的经理
代码如下(示例):
# Write your MySQL query statement below
select name
from employee
where id in (
select managerId from employee
group by managerId
having count(*) >=5
);
4.1934-确认率
代码如下(示例):
# Write your MySQL query statement below
select s.user_id,
round(avg(if(c.action = 'confirmed',1,0)),2)confirmation_rate
from signups s
left join Confirmations c
on s.user_id = c.user_id
group by s.user_id;
#confirmation_rate 列使用了 ROUND 函数将平均值保留两位小数。
#IF 函数用于判断 Confirmations 表中的 action 列是否为'confirmed',如果是则返回1,否则返回0。
5.620-有趣的电影
代码如下(示例):
# Write your MySQL query statement below
select id,movie,description,rating
from cinema
where description != 'boring' and mod(id,2) = 1
order by rating desc;
6. 1251-平均售价
代码如下(示例):
# Write your MySQL query statement below
#select p.product_id,round(sum(u.units * p.price)/sum(u.units),2) average_price
#from prices p
#left join UnitsSold u
#on p.product_id = u.product_id
#where u.purchase_date between p.start_date and p.end_date
#group by p.product_id;
SELECT
product_id,
IFNULL(Round(SUM(sales) / SUM(units), 2), 0) AS average_price
FROM (
SELECT
Prices.product_id AS product_id,
Prices.price * UnitsSold.units AS sales,
UnitsSold.units AS units
FROM Prices
LEFT JOIN UnitsSold ON Prices.product_id = UnitsSold.product_id
AND (UnitsSold.purchase_date BETWEEN Prices.start_date AND Prices.end_date)
) T
GROUP BY product_id
7.1075-项目员工
代码如下(示例):
# Write your MySQL query statement below
select project_id,round(avg(experience_years),2) average_years
from project p
left join employee e
on p.employee_id=e.employee_id
group by project_id;
8.1633-各赛事的用户注册率
代码如下(示例):
# Write your MySQL query statement below
select contest_id,
round(count(distinct user_id)/(select count(*) from users)*100,2) as percentage
from register
group by contest_id
order by percentage desc,contest_id;
9.1211-查询结果的质量和占比
代码如下(示例):
# Write your MySQL query statement below
SELECT
query_name,
ROUND(AVG(rating/position), 2) quality,
ROUND(SUM(IF(rating < 3, 1, 0)) * 100 / COUNT(*), 2) poor_query_percentage
FROM Queries
GROUP BY query_name
10.1193-每月交易
代码如下(示例):
# Write your MySQL query statement below
select
left(trans_date,7) month,
country,
count(state) trans_count,
count(if(state='approved',1,null)) approved_count,
sum(amount) trans_total_amount,
sum(if(state='approved',amount,0)) approved_total_amount
from transactions
group by month,country
总结
以上是今天要讲的内容,练习了一些 高频SQL题。