LeetCode

最近刷LeetCode DataBase，顺便把答案贴出来，供以后复习参考。

Contents

[TOC]

175. Combine Two Tables

Table: Person

+-------------+--------+
| Column Name | Type    |
+-------------+--------+
| PersonId    | int     |
| FirstName   | varchar |
| LastName    | varchar |
+-------------+--------+

1
2
3
4
5
6
7

PersonId is the primary key column for this table. Table: Address

+-------------+--------+
| Column Name | Type    |
+-------------+--------+
| AddressId   | int     |
| PersonId    | int     |
| City        | varchar |
| State       | varchar |
+-------------+--------+

1
2
3
4
5
6
7
8

AddressId is the primary key column for this table.

Write a SQL query for a report that provides the following information for each person in the Person table, regardless if there is an address for each of those people:

FirstName, LastName, City, State

Solutions

SELECT
	FirstName, LastName, City, State
FROM
	Person LEFT JOIN Address
ON
	Person.PersonId = Address.PersonId
;

1
2
3
4
5
6
7

176. Second Highest Salary

Write a SQL query to get the second highest salary from the Employee table.

+----+--------+
| Id | Salary |
+----+--------+
| 1  | 100    |
| 2  | 200    |
| 3  | 300    |
+----+--------+

1
2
3
4
5
6
7

For example, given the above Employee table, the query should return 200 as the second highest salary. If there is no second highest salary, then the query should return null.

+---------------------+
| SecondHighestSalary |
+---------------------+
| 200                 |
+---------------------+

1
2
3
4
5

Solutions

SELECT DISTINCT
    Salary AS SecondHighestSalary
FROM
    Employee
ORDER BY Salary DESC
LIMIT 1 OFFSET 1
;

1
2
3
4
5
6
7

SELECT
    (SELECT DISTINCT
            Salary
        FROM
            Employee
        ORDER BY Salary DESC
        LIMIT 1 OFFSET 1) AS SecondHighestSalary
;

1
2
3
4
5
6
7
8

SELECT
    IFNULL(
      (SELECT DISTINCT Salary
       FROM Employee
       ORDER BY Salary DESC
        LIMIT 1 OFFSET 1),
    NULL) AS SecondHighestSalary
;

1
2
3
4
5
6
7
8

177. Nth Highest Salary

Write a SQL query to get the nth highest salary from the Employee table.

+----+--------+
| Id | Salary |
+----+--------+
| 1  | 100    |
| 2  | 200    |
| 3  | 300    |
+----+--------+

1
2
3
4
5
6
7

For example, given the above Employee table, the nth highest salary where n = 2 is 200. If there is no nth highest salary, then the query should return null.

+------------------------+
| getNthHighestSalary(2) |
+------------------------+
| 200                    |
+------------------------+

1
2
3
4
5

Solutions

CREATE FUNCTION getNthHighestSalary(N INT) RETURNS INT
BEGIN
DECLARE M INT;
SET M = N - 1;
  RETURN (
      # Write your MySQL query statement below.
      select IFNULL((select distinct Salary from Employee order by Salary desc limit 1 offset M), null)
  );
END

1
2
3
4
5
6
7
8
9

180. Consecutive Numbers

Write a SQL query to find all numbers that appear at least three times consecutively.

+----+-----+
| Id | Num |
+----+-----+
| 1  |  1  |
| 2  |  1  |
| 3  |  1  |
| 4  |  2  |
| 5  |  1  |
| 6  |  2  |
| 7  |  2  |
+----+-----+

1
2
3
4
5
6
7
8
9
10
11

For example, given the above Logs table, 1 is the only number that appears consecutively for at least three times.

+-----------------+
| ConsecutiveNums |
+-----------------+
| 1               |
+-----------------+

1
2
3
4
5

Solutions

SELECT *
FROM
    Logs l1,
    Logs l2,
    Logs l3
WHERE
    l1.Id = l2.Id - 1
    AND l2.Id = l3.Id - 1
    AND l1.Num = l2.Num
    AND l2.Num = l3.Num
;

1
2
3
4
5
6
7
8
9
10
11

SELECT DISTINCT
    l1.Num AS ConsecutiveNums
FROM
    Logs l1,
    Logs l2,
    Logs l3
WHERE
    l1.Id = l2.Id - 1
    AND l2.Id = l3.Id - 1
    AND l1.Num = l2.Num
    AND l2.Num = l3.Num
;

1
2
3
4
5
6
7
8
9
10
11
12

181. Employees Earning More Than Their Managers

The Employee table holds all employees including their managers. Every employee has an Id, and there is also a column for the manager Id.

+----+-------+--------+-----------+
| Id | Name  | Salary | ManagerId |
+----+-------+--------+-----------+
| 1  | Joe   | 70000  | 3         |
| 2  | Henry | 80000  | 4         |
| 3  | Sam   | 60000  | NULL      |
| 4  | Max   | 90000  | NULL      |
+----+-------+--------+-----------+

1
2
3
4
5
6
7
8

Given the Employee table, write a SQL query that finds out employees who earn more than their managers. For the above table, Joe is the only employee who earns more than his manager.

+----------+
| Employee |
+----------+
| Joe      |
+----------+

1
2
3
4
5

Solutions

SELECT
    a.Name AS 'Employee'
FROM
    Employee AS a,
    Employee AS b
WHERE
    a.ManagerId = b.Id
        AND a.Salary > b.Salary
;

1
2
3
4
5
6
7
8
9

SELECT
     a.NAME AS Employee
FROM Employee AS a JOIN Employee AS b
     ON a.ManagerId = b.Id
     AND a.Salary > b.Salary
;

1
2
3
4
5
6

182. Duplicate Emails

Write a SQL query to find all duplicate emails in a table named Person.

+----+---------+
| Id | Email   |
+----+---------+
| 1  | a@b.com |
| 2  | c@d.com |
| 3  | a@b.com |
+----+---------+

1
2
3
4
5
6
7

For example, your query should return the following for the above table:

+---------+
| Email   |
+---------+
| a@b.com |
+---------+

1
2
3
4
5

Note: All emails are in lowercase.

Solutions

SELECT
	Email
FROM
	(
		SELECT Email, count(Email) AS num
		FROM Person
		GROUP BY Email
	) AS statistic
	WHERE num > 1
;

1
2
3
4
5
6
7
8
9
10

SELECT
	Email
FROM
	Person
GROUP BY
	Email
HAVING 
	count(Email) > 1
;

1
2
3
4
5
6
7
8
9

183. Customers Who Never Order

Suppose that a website contains two tables, the Customers table and the Orders table. Write a SQL query to find all customers who never order anything. Table: Customers.

+----+-------+
| Id | Name  |
+----+-------+
| 1  | Joe   |
| 2  | Henry |
| 3  | Sam   |
| 4  | Max   |
+----+-------+

1
2
3
4
5
6
7
8

Table: Orders.

+----+------------+
| Id | CustomerId |
+----+------------+
| 1  | 3          |
| 2  | 1          |
+----+------------+

1
2
3
4
5
6

Using the above tables as example, return the following:

+-----------+
| Customers |
+-----------+
| Henry     |
| Max       |
+-----------+

1
2
3
4
5
6

Solutions

SELECT
	customers.name AS 'Customers'
FROM
	customers
WHERE
	customers.id NOT IN
(
    SELECT customerid FROM orders
);

1
2
3
4
5
6
7
8
9

SELECT
    Name AS Customers
FROM 
    Customers 
LEFT JOIN 
    Orders 
ON
    Customers.Id = Orders.CustomerId
WHERE
    Orders.CustomerId IS NULL

1
2
3
4
5
6
7
8
9
10

184. Department Highest Salary

The Employee table holds all employees. Every employee has an Id, a salary, and there is also a column for the department Id.

+----+-------+--------+--------------+
| Id | Name  | Salary | DepartmentId |
+----+-------+--------+--------------+
| 1  | Joe   | 70000  | 1            |
| 2  | Henry | 80000  | 2            |
| 3  | Sam   | 60000  | 2            |
| 4  | Max   | 90000  | 1            |
+----+-------+--------+--------------+

1
2
3
4
5
6
7
8

The Department table holds all departments of the company.

+----+----------+
| Id | Name     |
+----+----------+
| 1  | IT       |
| 2  | Sales    |
+----+----------+

1
2
3
4
5
6

Write a SQL query to find employees who have the highest salary in each of the departments. For the above tables, Max has the highest salary in the IT department and Henry has the highest salary in the Sales department.

+------------+----------+--------+
| Department | Employee | Salary |
+------------+----------+--------+
| IT         | Max      | 90000  |
| Sales      | Henry    | 80000  |
+------------+----------+--------+

1
2
3
4
5
6

Solutions

SELECT
    Department.name AS 'Department',
    Employee.name AS 'Employee',
    Salary
FROM
    Employee
        JOIN
    Department ON Employee.DepartmentId = Department.Id
WHERE
    (Employee.DepartmentId , Salary) IN
    (   SELECT
            DepartmentId, MAX(Salary)
        FROM
            Employee
        GROUP BY DepartmentId
    )
;

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17

SELECT
    D.Name AS Department, E.Name AS Employee, E.Salary AS Salary
FROM
    Employee E, Department D,
    (SELECT DepartmentId, MAX(Salary) AS maxS FROM Employee GROUP BY DepartmentId) T
WHERE
    E.Salary = T.maxS AND
    E.DepartmentId = T.DepartmentId AND
    E.DepartmentId = D.Id
 ;

1
2
3
4
5
6
7
8
9
10

196. Delete Duplicate Emails

Write a SQL query to delete all duplicate email entries in a table named Person, keeping only unique emails based on its smallest Id.

+----+------------------+
| Id | Email            |
+----+------------------+
| 1  | john@example.com |
| 2  | bob@example.com  |
| 3  | john@example.com |
+----+------------------+

1
2
3
4
5
6
7

Id is the primary key column for this table. For example, after running your query, the above Person table should have the following rows:

+----+------------------+
| Id | Email            |
+----+------------------+
| 1  | john@example.com |
| 2  | bob@example.com  |
+----+------------------+

1
2
3
4
5
6

Solutions

SELECT p1.*
FROM Person p1,
    Person p2
WHERE
    p1.Email = p2.Email AND p1.Id > p2.Id
;

1
2
3
4
5
6

DELETE p1 FROM Person p1,
    Person p2
WHERE
    p1.Email = p2.Email AND p1.Id > p2.Id
;

1
2
3
4
5

197. Rising Temperature

Given a Weather table, write a SQL query to find all dates' Ids with higher temperature compared to its previous (yesterday's) dates.

+---------+------------+------------------+
| Id(INT) | Date(DATE) | Temperature(INT) |
+---------+------------+------------------+
|       1 | 2015-01-01 |               10 |
|       2 | 2015-01-02 |               25 |
|       3 | 2015-01-03 |               20 |
|       4 | 2015-01-04 |               30 |
+---------+------------+------------------+

1
2
3
4
5
6
7
8

For example, return the following Ids for the above Weather table:

+----+
| Id |
+----+
|  2 |
|  4 |
+----+

1
2
3
4
5
6

Solutions

SELECT
    weather.id AS 'Id'
FROM
    weather
        JOIN
    weather w ON DATEDIFF(weather.date, w.date) = 1
        AND weather.Temperature > w.Temperature
;

1
2
3
4
5
6
7
8

SELECT
    t2.Id
FROM
    Weather t1, Weather t2
WHERE
    t2.Date = DATE_ADD(t1.Date, INTERVAL 1 day) AND t2.Temperature > t1.Temperature

1
2
3
4
5
6

595. Big Countries

There is a table World

+-----------------+------------+------------+--------------+---------------+
| name            | continent  | area       | population   | gdp           |
+-----------------+------------+------------+--------------+---------------+
| Afghanistan     | Asia       | 652230     | 25500100     | 20343000      |
| Albania         | Europe     | 28748      | 2831741      | 12960000      |
| Algeria         | Africa     | 2381741    | 37100000     | 188681000     |
| Andorra         | Europe     | 468        | 78115        | 3712000       |
| Angola          | Africa     | 1246700    | 20609294     | 100990000     |
+-----------------+------------+------------+--------------+---------------+

1
2
3
4
5
6
7
8
9

A country is big if it has an area of bigger than 3 million square km or a population of more than 25 million. Write a SQL solution to output big countries' name, population and area. For example, according to the above table, we should output:

+--------------+-------------+--------------+
| name         | population  | area         |
+--------------+-------------+--------------+
| Afghanistan  | 25500100    | 652230       |
| Algeria      | 37100000    | 2381741      |
+--------------+-------------+--------------+

1
2
3
4
5
6

Solutions

SELECT
    name, population, area
FROM
    world
WHERE
    area > 3000000

UNION

SELECT
    name, population, area
FROM
    world
WHERE
    population > 25000000
;

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16

SELECT
    name, population, area
FROM
    world
WHERE
    area > 3000000 OR population > 25000000
;

1
2
3
4
5
6
7

620. Not Boring Movies

X city opened a new cinema, many people would like to go to this cinema. The cinema also gives out a poster indicating the movies’ ratings and descriptions. Please write a SQL query to output movies with an odd numbered ID and a description that is not 'boring'. Order the result by rating.

For example, table 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     |
+---------+-----------+--------------+-----------+

1
2
3
4
5
6
7
8
9

For the example above, the output should be:

+---------+-----------+--------------+-----------+
|   id    | movie     |  description |  rating   |
+---------+-----------+--------------+-----------+
|   5     | House card|   Interesting|   9.1     |
|   1     | War       |   great 3D   |   8.9     |
+---------+-----------+--------------+-----------+

1
2
3
4
5
6

Solutions

SELECT *
FROM cinema
WHERE mod(id, 2) = 1 AND description != 'boring'
ORDER BY RATING DESC
;

1
2
3
4
5

627. Swap Salary

Given a table salary, such as the one below, that has m=male and f=female values. Swap all f and m values (i.e., change all f values to m and vice versa) with a single update query and no intermediate temp table. For example:

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

1
2
3
4
5
6

After running your query, the above salary table should have the following rows:

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

1
2
3
4
5
6

Solutions

UPDATE salary
SET
    sex = CASE sex
        WHEN 'm' THEN 'f'
        ELSE 'm'
    END;

1
2
3
4
5
6

596. Classes More Than 5 Students

There is a table courses with columns: student and class

Please list out all classes which have more than or equal to 5 students.

For example, the table:

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

1
2
3
4
5
6
7
8
9
10
11
12
13
14

Should output:

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

1
2
3
4
5
6

Note: The students should not be counted duplicate in each course.

Solutions

SELECT
    class
FROM
    (SELECT
        class, COUNT(DISTINCT student) AS num
    FROM
        courses
    GROUP BY class) AS temp_table
WHERE
    num >= 5
;

1
2
3
4
5
6
7
8
9
10
11

SELECT
    class
FROM
    courses
GROUP BY class
HAVING COUNT(DISTINCT student) >= 5
;

1
2
3
4
5
6
7

# LeetCode

# 175. Combine Two Tables

# Solutions

# 176. Second Highest Salary

# Solutions

# 177. Nth Highest Salary

# Solutions

# 180. Consecutive Numbers

# Solutions

# 181. Employees Earning More Than Their Managers

# Solutions

# 182. Duplicate Emails

# Solutions

# 183. Customers Who Never Order

# Solutions

# 184. Department Highest Salary

# Solutions

# 196. Delete Duplicate Emails

# Solutions

# 197. Rising Temperature

# Solutions

# 595. Big Countries

# Solutions

# 620. Not Boring Movies

# Solutions

# 627. Swap Salary

# Solutions

# 596. Classes More Than 5 Students

# Solutions

LeetCode

175. Combine Two Tables

Solutions

176. Second Highest Salary

Solutions

177. Nth Highest Salary

Solutions

180. Consecutive Numbers

Solutions

181. Employees Earning More Than Their Managers

Solutions

182. Duplicate Emails

Solutions

183. Customers Who Never Order

Solutions

184. Department Highest Salary

Solutions

196. Delete Duplicate Emails

Solutions

197. Rising Temperature

Solutions

595. Big Countries

Solutions

620. Not Boring Movies

Solutions

627. Swap Salary

Solutions

596. Classes More Than 5 Students

Solutions