Leetcode SQL 测试题简单篇

1. 组合两个表
   表1: Person
   +-------------+---------+
   | 列名 | 类型 |
   +-------------+---------+
   | PersonId | int |
   | FirstName | varchar |
   | LastName | varchar |
   +-------------+---------+
   PersonId 是上表主键
   表2: Address
   +-------------+---------+
   | 列名 | 类型 |
   +-------------+---------+
   | AddressId | int |
   | PersonId | int |
   | City | varchar |
   | State | varchar |
   +-------------+---------+
   AddressId 是上表主键

编写一个 SQL 查询，满足条件：无论 person 是否有地址信息，都需要基于上述两表提供 person 的以下信息：FirstName, LastName, City, State

/* Write your T-SQL query statement below */
SELECT FirstName, LastName, City, State
FROM Person LEFT JOIN Address
ON Person.PersonID=Address.PersonID;

output:

{"headers":["FirstName","LastName","City","State"],"values":[["Allen","Wang",null,null]]}

2. 第二高的薪水
   编写一个 SQL 查询，获取 Employee 表中第二高的薪水（Salary） 。
   +----+--------+
   | Id | Salary |
   +----+--------+
   | 1 | 100 |
   | 2 | 200 |
   | 3 | 300 |
   +----+--------+
   例如上述 Employee 表，SQL查询应该返回 200 作为第二高的薪水。如果不存在第二高的薪水，那么查询应返回 null。
   +---------------------+
   | SecondHighestSalary |
   +---------------------+
   | 200 |
   +---------------------+

# Write your MySQL query statement below
SELECT(
SELECT DISTINCT Salary
FROM Employee
ORDER BY Salary DESC
LIMIT 1 OFFSET 1
    )
AS SecondHighestSalary;

output:

{"headers":["SecondHighestSalary"],"values":[[200]]}

/* Write your T-SQL query statement below */
SELECT MAX(Salary) AS SecondHighestSalary
FROM Employee 
WHERE Salary < (SELECT MAX(Salary)
                FROM Employee);

3. 超过经理收入的员工
   Employee 表包含所有员工，他们的经理也属于员工。每个员工都有一个 Id，此外还有一列对应员工的经理的 Id。
   +----+-------+--------+-----------+
   | Id | Name | Salary | ManagerId |
   +----+-------+--------+-----------+
   | 1 | Joe | 70000 | 3 |
   | 2 | Henry | 80000 | 4 |
   | 3 | Sam | 60000 | NULL |
   | 4 | Max | 90000 | NULL |
   +----+-------+--------+-----------+

给定 Employee 表，编写一个 SQL 查询，该查询可以获取收入超过他们经理的员工的姓名。在上面的表格中，Joe 是唯一一个收入超过他的经理的员工。

/* Write your T-SQL query statement below */
SELECT NAME AS Employee
FROM Employee AS E1
WHERE Salary > (SELECT Salary
                FROM Employee AS E2
                WHERE E1.ManagerID = E2.ID
);

4. 查找重复的电子邮箱
   编写一个 SQL 查询，查找 Person 表中所有重复的电子邮箱。
   示例：
   +----+---------+
   | Id | Email |
   +----+---------+
   | 1 | a@b.com |
   | 2 | c@d.com |
   | 3 | a@b.com |
   +----+---------+

/* Write your T-SQL query statement below */
SELECT Email
FROM Person
GROUP BY Email
HAVING COUNT(*) > 1;

5. 从不订购的客户
   某网站包含两个表，Customers 表和 Orders 表。编写一个 SQL 查询，找出所有从不订购任何东西的客户。
   Customers 表：
   +----+-------+
   | Id | Name |
   +----+-------+
   | 1 | Joe |
   | 2 | Henry |
   | 3 | Sam |
   | 4 | Max |
   +----+-------+
   Orders 表：
   +----+------------+
   | Id | CustomerId |
   +----+------------+
   | 1 | 3 |
   | 2 | 1 |
   +----+------------+
   例如给定上述表格，你的查询应返回：
   +-----------+
   | Customers |
   +-----------+
   | Henry |
   | Max |
   +-----------+

/* Write your T-SQL query statement below */
SELECT NAME AS Customers
FROM Customers
WHERE Id NOT IN(
    SELECT CustomerID
    FROM Orders
)

output:

{"headers":["Customers"],"values":[["Henry"],["Max"]]}

6. 删除重复的电子邮箱
   编写一个 SQL 查询，来删除 Person 表中所有重复的电子邮箱，重复的邮箱里只保留 Id 最小 的那个。
   +----+------------------+
   | Id | Email |
   +----+------------------+
   | 1 | john@example.com |
   | 2 | bob@example.com |
   | 3 | john@example.com |
   +----+------------------+
   Id 是这个表的主键。
   例如，在运行你的查询语句之后，上面的 Person 表应返回以下几行:
   +----+------------------+
   | Id | Email |
   +----+------------------+
   | 1 | john@example.com |
   | 2 | bob@example.com |
   +----+------------------+

# Write your MySQL query statement below
DELETE P1
FROM Person P1, Person P2  
WHERE P1.Email = P2.Email AND P1.Id > P2.Id

output:

{"headers":["Id","Email"],"values":[[1,"john@example.com"],[2,"bob@example.com"]]}

7. 上升的温度
   给定一个 Weather 表，编写一个 SQL 查询，来查找与之前（昨天的）日期相比温度更高的所有日期的 Id。
   +---------+------------------+------------------+
   | Id(INT) | RecordDate(DATE) | Temperature(INT) |
   +---------+------------------+------------------+
   | 1 | 2015-01-01 | 10 |
   | 2 | 2015-01-02 | 25 |
   | 3 | 2015-01-03 | 20 |
   | 4 | 2015-01-04 | 30 |
   +---------+------------------+------------------+
   例如，根据上述给定的 Weather 表格，返回如下 Id:
   +----+
   | Id |
   +----+
   | 2 |
   | 4 |
   +----+

/* Write your T-SQL query statement below */
SELECT Id
FROM Weather AS W1
WHERE Temperature > (
    SELECT Temperature FROM Weather AS W2
    WHERE DATEDIFF(dd, W2.RecordDate,W1.RecordDate) = 1
)

output:

{"headers":["Id"],"values":[[2],[4]]}

8. 大的国家
   如果一个国家的面积超过300万平方公里，或者人口超过2500万，那么这个国家就是大国家。
   编写一个SQL查询，输出表中所有大国家的名称、人口和面积。

# Write your MySQL query statement below
SELECT name, population, area
FROM World
WHERE (area > 3000000) OR (population > 25000000);

output:

{"headers":["name","population","area"],"values":[["Afghanistan",25500100,652230],["Algeria",37100000,2381741]]}

9. 超过5名学生的课
   有一个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;

output:

{"headers":["class"],"values":[["Math"]]}

10. 有趣的电影
    某城市开了一家新的电影院，吸引了很多人过来看电影。该电影院特别注意用户体验，专门有个 LED显示板做电影推荐，上面公布着影评和相关电影描述。
    作为该电影院的信息部主管，您需要编写一个 SQL查询，找出所有影片描述为非 boring (不无聊) 的并且 id 为奇数 的影片，结果请按等级 rating 排列。
    例如，下表 cinema:
    +---------+-----------+--------------+-----------+
    | id | movie | description | rating |
    +---------+-----------+--------------+-----------+
    | 1 | War | great 3D | 8.9 |
    | 2 | Science | fiction | 8.5 |
    | 3 | irish | boring | 6.2 |
    | 4 | Ice song | Fantacy | 8.6 |
    | 5 | House card| Interesting| 9.1 |
    +---------+-----------+--------------+-----------+
    对于上面的例子，则正确的输出是为：
    +---------+-----------+--------------+-----------+
    | id | movie | description | rating |
    +---------+-----------+--------------+-----------+
    | 5 | House card| Interesting| 9.1 |
    | 1 | War | great 3D | 8.9 |
    +---------+-----------+--------------+-----------+

# Write your MySQL query statement below
SELECT id, movie, description, rating
FROM cinema
WHERE (description <> 'boring') AND (id % 2 = 1)
ORDER BY rating DESC;

output:

{"headers":["id","movie","description","rating"],"values":[[5,"House card","Interesting",9.1],[1,"War","great 3D",8.9]]}

11. 交换工资
    给定一个 salary表，如下所示，有m=男性 和 f=女性的值 。交换所有的 f 和 m 值(例如，将所有 f 值更改为 m，反之亦然)。要求使用一个更新查询，并且没有中间临时表。
    例如:

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'
          ELSE 'm'
          END)

output:

{"headers":["id","name","sex","salary"],"values":[[1,"A","f",2500],[2,"B","m",1500],[3,"C","f",5500],[4,"D","m",500]]}