leetcode sql练习

数据库太菜，怕笔试面试考sql语句，练几天

 

给定表 customer ，里面保存了所有客户信息和他们的推荐人。

+------+------+-----------+
| id | name | referee_id|
+------+------+-----------+
| 1 | Will | NULL |
| 2 | Jane | NULL |
| 3 | Alex | 2 |
| 4 | Bill | NULL |
| 5 | Zack | 1 |
| 6 | Mark | 2 |
+------+------+-----------+
写一个查询语句，返回一个编号列表，列表中编号的推荐人的编号都 不是 2。

对于上面的示例数据，结果为：

+------+
| name |
+------+
| Will |
| Jane |
| Bill |
| Zack |
+------+

# Write your MySQL query statement below
select name from customer where referee_id is null or referee_id<>2;

 

 

 

在表 orders 中找到订单数最多客户对应的 customer_number 。

数据保证订单数最多的顾客恰好只有一位。

表 orders 定义如下：

| Column | Type |
|-------------------|-----------|
| order_number (PK) | int |
| customer_number | int |
| order_date | date |
| required_date | date |
| shipped_date | date |
| status | char(15) |
| comment | char(200) |
样例输入

| order_number | customer_number | order_date | required_date | shipped_date | status | comment |
|--------------|-----------------|------------|---------------|--------------|--------|---------|
| 1 | 1 | 2017-04-09 | 2017-04-13 | 2017-04-12 | Closed | |
| 2 | 2 | 2017-04-15 | 2017-04-20 | 2017-04-18 | Closed | |
| 3 | 3 | 2017-04-16 | 2017-04-25 | 2017-04-20 | Closed | |
| 4 | 3 | 2017-04-18 | 2017-04-28 | 2017-04-25 | Closed | |
样例输出

| customer_number |
|-----------------|
| 3 |
解释

customer_number 为 '3' 的顾客有两个订单，比顾客 '1' 或者 '2' 都要多，因为他们只有一个订单
所以结果是该顾客的 customer_number ，也就是 3 。
进阶： 如果有多位顾客订单数并列最多，你能找到他们所有的 customer_number 吗？

# Write your MySQL query statement below
select customer_number from orders group by customer_number order by count(*) desc limit 1;
# Write your MySQL query statement below
select customer_number from orders group by customer_number having count(*)=(select max(a) from (select count(*) as a from orders group by customer_number) as fuckyou);

 

 

 

有一个courses 表 ，有: student (学生) 和 class (课程)。

请列出所有超过或等于5名学生的课。

例如,表:

+---------+------------+
| student | class      |
+---------+------------+
| A       | Math       |
| B       | English    |
| C       | Math       |
| D       | Biology    |
| E       | Math       |
| F       | Computer   |
| G       | Math       |
| H       | Math       |
| I       | Math       |
+---------+------------+

应该输出:

+---------+
| class   |
+---------+
| Math    |
+---------+

Note:
学生在每个课中不应被重复计算。

# Write your MySQL query statement below
select class from courses group by class having count(distinct student)>=5;
# Write your MySQL query statement below
select class 
from 
    (select class,count(distinct student) as x 
    from courses  
    group by class
    ) as fuck
where x>=5;

 

 

 

在 Facebook 或者 Twitter 这样的社交应用中，人们经常会发好友申请也会收到其他人的好友申请。现在给如下两个表：

 

表： friend_request

| sender_id | send_to_id |request_date|
|-----------|------------|------------|
| 1 | 2 | 2016_06-01 |
| 1 | 3 | 2016_06-01 |
| 1 | 4 | 2016_06-01 |
| 2 | 3 | 2016_06-02 |
| 3 | 4 | 2016-06-09 |
 

表： request_accepted

| requester_id | accepter_id |accept_date |
|--------------|-------------|------------|
| 1 | 2 | 2016_06-03 |
| 1 | 3 | 2016-06-08 |
| 2 | 3 | 2016-06-08 |
| 3 | 4 | 2016-06-09 |
| 3 | 4 | 2016-06-10 |
 

写一个查询语句，求出好友申请的通过率，用 2 位小数表示。通过率由接受好友申请的数目除以申请总数。

 

对于上面的样例数据，你的查询语句应该返回如下结果。

 

|accept_rate|
|-----------|
| 0.80|
 

注意:

通过的好友申请不一定都在表 friend_request 中。在这种情况下，你只需要统计总的被通过的申请数（不管它们在不在原来的申请中），并将它除以申请总数，得到通过率
一个好友申请发送者有可能会给接受者发几条好友申请，也有可能一个好友申请会被通过好几次。这种情况下，重复的好友申请只统计一次。
如果一个好友申请都没有，通过率为 0.00 。
 

解释： 总共有 5 个申请，其中 4 个是不重复且被通过的好友申请，所以成功率是 0.80 。

 

进阶:

你能写一个查询语句得到每个月的通过率吗？
你能求出每一天的累计通过率吗？

select round(ifnull((select count(distinct requester_id ,accepter_id ) from request_accepted)/(select count(distinct sender_id ,send_to_id) from friend_request),0),2) as accept_rate

学习了两个新函数：ifnull(x,y)，如果x不为null/0则x，否则为y。round(x,2)，对x保留2位小数，round(x,3)，对x保留3位小数。

 

 

 

几个朋友来到电影院的售票处，准备预约连续空余座位。

你能利用表 cinema ，帮他们写一个查询语句，获取所有空余座位，并将它们按照 seat_id 排序后返回吗？

| seat_id | free |
|---------|------|
| 1 | 1 |
| 2 | 0 |
| 3 | 1 |
| 4 | 1 |
| 5 | 1 |
 

对于如上样例，你的查询语句应该返回如下结果。

 

| seat_id |
|---------|
| 3 |
| 4 |
| 5 |
注意：

seat_id 字段是一个自增的整数，free 字段是布尔类型（'1' 表示空余， '0' 表示已被占据）。
连续空余座位的定义是大于等于 2 个连续空余的座位。

# Write your MySQL query statement below
select distinct a.seat_id from cinema a join cinema b
on abs(a.seat_id-b.seat_id)=1 and a.free=1 and b.free=1 order by a.seat_id;

 

一个小学生 Tim 的作业是判断三条线段是否能形成一个三角形。

 

然而，这个作业非常繁重，因为有几百组线段需要判断。

 

假设表 triangle 保存了所有三条线段的三元组 x, y, z ，你能帮 Tim 写一个查询语句，来判断每个三元组是否可以组成一个三角形吗？

 

| x | y | z |
|----|----|----|
| 13 | 15 | 30 |
| 10 | 20 | 15 |
对于如上样例数据，你的查询语句应该返回如下结果：

| x | y | z | triangle |
|----|----|----|----------|
| 13 | 15 | 30 | No |
| 10 | 20 | 15 | Yes |

select *,
if((x + y <= z or x + z <= y or y + z <= x), "No", "Yes") as triangle
from triangle;

select的属性里可以用if语句 if(condition,x,y)，表示如果condition成立则为x，否则为y。

# Write your MySQL query statement below
select x,y,z,(case when x+y>z and x+z>y and y+z>x then  'Yes' else 'No' end) as triangle from triangle;

还可以在select的属性里用case when then else end语句。

用法：

CASE column
    WHEN  value1 THEN  result1 
    WHEN  value2 THEN  result2
    ......
    ELSE resultN

 

 

 

表 point 保存了一些点在 x 轴上的坐标，这些坐标都是整数。

写一个查询语句，找到这些点中最近两个点之间的距离。

 

| x |
|-----|
| -1 |
| 0 |
| 2 |
 

最近距离显然是 '1' ，是点 '-1' 和 '0' 之间的距离。所以输出应该如下：

 

| shortest|
|---------|
| 1 |
 

注意：每个点都与其他点坐标不同，表 table 不会有重复坐标出现。

# Write your MySQL query statement below
select min(qqq) as shortest from (select abs(a.x-b.x) as qqq from point a join point b on a.x<b.x ) as fuckyou;

 

 

 

表 my_numbers 的 num 字段包含很多数字，其中包括很多重复的数字。

你能写一个 SQL 查询语句，找到只出现过一次的数字中，最大的一个数字吗？

+---+
|num|
+---+
| 8 |
| 8 |
| 3 |
| 3 |
| 1 |
| 4 |
| 5 |
| 6 |
对于上面给出的样例数据，你的查询语句应该返回如下结果：

+---+
|num|
+---+
| 6 |
注意：

如果没有只出现一次的数字，输出 null 。

# Write your MySQL query statement below
select ifnull((select num from my_numbers group by num having count(*)=1 order by num desc limit 1),null) as num;

注意ifnull用法，用在select的属性项上。

评论区又看到一个方法：在外层再次select一次，就可以查找出null值并输出。

# Write your MySQL query statement below
select (select num from my_numbers group by num having count(*)=1 order by num desc limit 1) as num;

 

 

 

给定一个 salary 表，如下所示，有 m = 男性 和 f = 女性 的值。交换所有的 f 和 m 值（例如，将所有 f 值更改为 m，反之亦然）。要求只使用一个更新（Update）语句，并且没有中间的临时表。

注意，您必只能写一个 Update 语句，请不要编写任何 Select 语句。

例如：

| id | name | sex | salary |
|----|------|-----|--------|
| 1 | A | m | 2500 |
| 2 | B | f | 1500 |
| 3 | C | m | 5500 |
| 4 | D | f | 500 |
运行你所编写的更新语句之后，将会得到以下表:

| id | name | sex | salary |
|----|------|-----|--------|
| 1 | A | f | 2500 |
| 2 | B | m | 1500 |
| 3 | C | f | 5500 |
| 4 | D | m | 500 |

# Write your MySQL query statement below
 update salary set sex=
 (case sex when 'm' then 'f' when 'f' then 'm' else null end);
# Write your MySQL query statement below
update salary set sex=if(sex='f','m','f');

 

 

 

ActorDirector 表：

+-------------+---------+
| Column Name | Type |
+-------------+---------+
| actor_id | int |
| director_id | int |
| timestamp | int |
+-------------+---------+
timestamp 是这张表的主键.
 

写一条SQL查询语句获取合作过至少三次的演员和导演的 id 对 (actor_id, director_id)

示例：

ActorDirector 表：
+-------------+-------------+-------------+
| actor_id | director_id | timestamp |
+-------------+-------------+-------------+
| 1 | 1 | 0 |
| 1 | 1 | 1 |
| 1 | 1 | 2 |
| 1 | 2 | 3 |
| 1 | 2 | 4 |
| 2 | 1 | 5 |
| 2 | 1 | 6 |
+-------------+-------------+-------------+

Result 表：
+-------------+-------------+
| actor_id | director_id |
+-------------+-------------+
| 1 | 1 |
+-------------+-------------+
唯一的 id 对是 (1, 1)，他们恰好合作了 3 次。

# Write your MySQL query statement below
select actor_id,director_id from actordirector group by actor_id,director_id having count(*)>=3;

 

 

销售表 Sales：

+-------------+-------+
| Column Name | Type |
+-------------+-------+
| sale_id | int |
| product_id | int |
| year | int |
| quantity | int |
| price | int |
+-------------+-------+
(sale_id, year) 是销售表 Sales 的主键.
product_id 是产品表 Product 的外键.
注意: price 表示每单位价格
产品表 Product：

+--------------+---------+
| Column Name | Type |
+--------------+---------+
| product_id | int |
| product_name | varchar |
+--------------+---------+
product_id 是表的主键.
写一条SQL 查询语句获取产品表 Product 中所有的 产品名称 product name 以及 该产品在 Sales 表中相对应的 上市年份 year 和 价格 price。

示例：

Sales 表：
+---------+------------+------+----------+-------+
| sale_id | product_id | year | quantity | price |
+---------+------------+------+----------+-------+
| 1 | 100 | 2008 | 10 | 5000 |
| 2 | 100 | 2009 | 12 | 5000 |
| 7 | 200 | 2011 | 15 | 9000 |
+---------+------------+------+----------+-------+

Product 表：
+------------+--------------+
| product_id | product_name |
+------------+--------------+
| 100 | Nokia |
| 200 | Apple |
| 300 | Samsung |
+------------+--------------+

Result 表：
+--------------+-------+-------+
| product_name | year | price |
+--------------+-------+-------+
| Nokia | 2008 | 5000 |
| Nokia | 2009 | 5000 |
| Apple | 2011 | 9000 |
+--------------+-------+-------+

# Write your MySQL query statement below
select product_name,year,price from product a join sales b on a.product_id=b.product_id

 

 

Table: Project

+-------------+---------+
| Column Name | Type |
+-------------+---------+
| project_id | int |
| employee_id | int |
+-------------+---------+
主键为 (project_id, employee_id)。
employee_id 是员工表 Employee 表的外键。
Table: Employee

+------------------+---------+
| Column Name | Type |
+------------------+---------+
| employee_id | int |
| name | varchar |
| experience_years | int |
+------------------+---------+
主键是 employee_id。

先筛选出最大的雇员数（分组count+max）

再重新分组count+having条件

# Write your MySQL query statement below
select project_id from project group by project_id having count(*)>=(select max(f) from (select count(*) as f from project group by project_id) as fuckyou);

先分组+count，降序排序只取第一个，即count最大的。

再重新分组count+having条件

# Write your MySQL query statement below
select project_id from project group by project_id having count(*)>=(select count(*) as f from project group by project_id order by f desc limit 1);

分组count，>=all的方式取最大count

# Write your MySQL query statement below
select project_id from project group by project_id having count(*)>=all(select count(*) as f from project group by project_id);

 

查询表 Queries： 

+-------------+---------+
| Column Name | Type |
+-------------+---------+
| query_name | varchar |
| result | varchar |
| position | int |
| rating | int |
+-------------+---------+
此表没有主键，并可能有重复的行。
此表包含了一些从数据库中收集的查询信息。
“位置”（position）列的值为 1 到 500 。
“评分”（rating）列的值为 1 到 5 。评分小于 3 的查询被定义为质量很差的查询。
 

将查询结果的质量 quality 定义为：

各查询结果的评分与其位置之间比率的平均值。

将劣质查询百分比 poor_query_percentage 为：

评分小于 3 的查询结果占全部查询结果的百分比。

编写一组 SQL 来查找每次查询的名称(query_name)、质量(quality) 和 劣质查询百分比(poor_query_percentage)。

质量(quality) 和劣质查询百分比(poor_query_percentage) 都应四舍五入到小数点后两位。

查询结果格式如下所示：

Queries table:
+------------+-------------------+----------+--------+
| query_name | result | position | rating |
+------------+-------------------+----------+--------+
| Dog | Golden Retriever | 1 | 5 |
| Dog | German Shepherd | 2 | 5 |
| Dog | Mule | 200 | 1 |
| Cat | Shirazi | 5 | 2 |
| Cat | Siamese | 3 | 3 |
| Cat | Sphynx | 7 | 4 |
+------------+-------------------+----------+--------+

Result table:
+------------+---------+-----------------------+
| query_name | quality | poor_query_percentage |
+------------+---------+-----------------------+
| Dog | 2.50 | 33.33 |
| Cat | 0.66 | 33.33 |
+------------+---------+-----------------------+

Dog 查询结果的质量为 ((5 / 1) + (5 / 2) + (1 / 200)) / 3 = 2.50
Dog 查询结果的劣质查询百分比为 (1 / 3) * 100 = 33.33

Cat 查询结果的质量为 ((2 / 5) + (3 / 3) + (4 / 7)) / 3 = 0.66
Cat 查询结果的劣质查询百分比为 (1 / 3) * 100 = 33.33

# Write your MySQL query statement below
select query_name,
round(avg(rating/position),2) as quality,
round(100*(sum(case when rating<3 then 1 else 0 end))/count(*),2) as poor_query_percentage
from queries group by query_name;

case when语句：

CASE WHEN condition THEN result
 
[WHEN...THEN...]
 
ELSE result
 
END

 

 

posted @ 2020-03-28 02:56  NeoZy  阅读(223)  评论(0)    收藏  举报