目录

• 175 Combine Two Tables E
• 176 Second Highest Salary E
• 177 Nth Highest Salary M
• 178 Rank Scores M
• 180 Consecutive Numbers M
• 181 Employees Earning More Than Their Managers E
• 182 Duplicate Emails E
• 183 Customers Who Never Order E
• 184 Department Highest Salary M
• 185 Department Top Three Salaries H
• 196 Delete Duplicate Emails E
• 197 Rising Temperature E
• 262 Trips and Users H
• 595 Big Countries E
• 596 Classes More Than 5 Students E
• 620 Not Boring Movies E
• 626 Exchange Seats M
• 627 Swap Salary E

!!!共18个题， 2个Hard，5个Medium，11个Easy

175. Combine Two Tables

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

# Easy
SELECT
    a.FirstName,
    a.LastName,
    b.City,
    b.State
FROM
    Person a
LEFT JOIN Address b ON a.PersonID = b.PersonID

176. Second Highest Salary

Write a SQL query to getthe second highest salary from the Employee table.
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.

# Easy
SELECT
   ifnull(
       (
           SELECT DISTINCT
               Salary
           FROM
               Employee
           ORDER BY
               Salary DESC
           LIMIT 1 OFFSET 1
       ),
       null
   ) AS SecondHighestSalary

177. Nth Highest Salary

Write a SQL query to get the nth highest salary from the Employee table.
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.

# Medium
CREATE FUNCTION getNthHighestSalary (N INT) RETURNS INT
BEGIN
SET N = N - 1;
RETURN (
    # Write your MySQL query statement below.
    SELECT
        ifnull(
            (
                SELECT DISTINCT
                    Salary
                FROM
                    Employee
                ORDER BY
                    Salary DESC
                LIMIT 1 OFFSET N
            ),
            null
        )
);
END

178. Rank Scores

Write a SQL query to rank scores. If there is a tie between two scores, both should have the same ranking. Note that after a tie, the next ranking number should be the next consecutive integer value. In other words, there should be no "holes" between ranks.

# Medium
SELECT
    Score,
    (
        SELECT
            COUNT(DISTINCT Score)
        FROM
            Scores s1
        WHERE
            s1.Score >= s2.Score
    ) Rank
FROM
    Scores s2
ORDER BY
    Score DESC

180. Consecutive Numbers

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

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

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.
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.

# Easy
# Approach I:
SELECT
    a.Name AS Employee
FROM
    Employee AS a
JOIN Employee AS b ON a.ManagerId = b.Id
AND a.Salary > b.Salary;

# Approach II:
SELECT
    e1. NAME Employee
FROM
    Employee e1,
    Employee e2
WHERE
    e1.Salary > e2.Salary
AND e1.ManagerId = e2.Id

182. Duplicate Emails

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

# Easy
# Approach I:暴力法
SELECT DISTINCT
    a.Email
FROM
    Person a,
    Person b
WHERE
    a.Email = b.Email
AND a.Id != b.Id

# Approach II: Using GROUP BY and HAVING condition [Accepted]
SELECT
    Email
FROM
    Person
GROUP BY
    Email
HAVING
    count(Email) > 1;

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.

# Easy
# Approach: Using sub-query and NOT IN clause
SELECT
    Name Customers
FROM
    Customers
WHERE
    Id NOT IN (SELECT CustomerId FROM Orders)

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.
The Departmenttable holds all departments of the company.
Write a SQL query to find employees who have the highest salary in each of the departments.
Explanation:
Max and Jim both have the highest salary in the IT department.

# Medium
# Approach I:暴力法
SELECT DISTINCT
    b.Name Department,
    a1.Name Employee,
    a1.Salary
FROM
    Employee a1,
    Department b
WHERE
    a1.DepartmentId = b.Id
AND a1.Salary >= (
    SELECT
        max(a2.Salary)
    FROM
        Employee a2
    WHERE
        a1.DepartmentId = a2.DepartmentId
)

# ApproachII: Using JOIN and IN clause(more faster)
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
    );

185. Department Top Three Salaries

The Employee table holds all employees. Every employee has an Id, and there is also a column for the department Id.
The Department table holds all departments of the company.
Write a SQL query to find employees who earn the top three salaries in each of the department.
Explanation:
In IT department, Max earns the highest salary, both Randy and Joe earn the second highest salary, and Will earns the third highest salary. There are only two employees in the Sales department, Henry earns the highest salary while Sam earns the second highest salary.
+------------+----------+--------+
| Department | Employee | Salary |
+------------+----------+--------+
| IT | Max | 90000 |
| IT | Randy | 85000 |
| IT | Joe | 85000 |
| IT | Will | 70000 |
| Sales | Henry | 80000 |
| Sales | Sam | 60000 |
+------------+----------+--------+

# Hard
# 效率不高
SELECT
  b.Name Department,
  a1.Name Employee ,
  a1.Salary 
FROM
    Employee a1
JOIN Department b ON a1.DepartmentId = b.Id
WHERE
    (
        SELECT
            count(DISTINCT a2.Salary)
        FROM
            Employee a2
        WHERE
            a1.DepartmentId = a2.DepartmentId
        AND a2.Salary > a1.Salary
    ) < 3

196. Delete Duplicate Emails Easy

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

# Easy
# 注意把等号判断条件放前面！
DELETE a
FROM
    Person a,
    Person b
WHERE
    a.Email = b.Email
AND a.Id > b.Id

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.

# Easy
# Approach: Using JOIN and DATEDIFF() clause
# DATEDIFF(b,a) :b-a
SELECT
    b.Id
FROM
    Weather b
JOIN Weather a ON datediff(b.RecordDate, a.RecordDate) = 1
AND b.Temperature > a.Temperature

262. Trips and Users

The Trips table holds all taxi trips. Each trip has a unique Id, while Client_Id and Driver_Id are both foreign keys to the Users_Id at the Users table. Status is an ENUM type of (‘completed’, ‘cancelled_by_driver’, ‘cancelled_by_client’).
| Id | Client_Id | Driver_Id | City_Id | Status | Request_at|
The Users table holds all users. Each user has an unique Users_Id, and Role is an ENUM type of (‘client’, ‘driver’, ‘partner’).
| Users_Id | Banned | Role |
Write a SQL query to find the cancellation rate of requests made byunbanned users between Oct 1, 2013 and Oct 3, 2013. For the above tables, your SQL query should return the following rows with the cancellation rate being rounded to two decimal places.
| Day | Cancellation Rate |

# Hard !!!mark
SELECT
    Request_at AS Day,
    ROUND(
        COUNT(
            IF(Status!='completed',TRUE,NULL) 
        )/COUNT(*)
    ,2) AS 'Cancellation Rate'
    FROM Trips
    WHERE
        Client_Id IN 
        (
            SELECT Users_Id 
            FROM Users 
            WHERE Banned = 'No'
        )
        AND Driver_Id  IN 
        (
            SELECT Users_Id 
            FROM Users 
            WHERE Banned = 'No'
        )
        AND Request_at BETWEEN '2013-10-01' AND '2013-10-03'
    GROUP BY Request_at

595 Big Countries

这题，，就不再赘述了。。。

# Easy
SELECT
    name,population,area
FROM World
WHERE area > 3000000
OR population > 25000000

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.

# Easy 不过我不太会用having。。
SELECT
    class
FROM
    courses
GROUP BY class
HAVING COUNT(DISTINCT student) >= 5
;

620 Not Boring Movies

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.

# Easy
SELECT *
FROM cinema
WHERE
    id%2=1
    AND description!='boring'
ORDER BY rating DESC

626 Exchange Seats

Mary is a teacher in a middle school and she has a table seat storing students' names and their corresponding seat ids.
The column id is continuous increment.
Mary wants to change seats for the adjacent students.
Can you write a SQL query to output the result for Mary?

# Medium 
# 这个也太难啦八，竟然不是Hard
# # Approach I: Using lstatement IF (better)
SELECT
    IF (
        id < (SELECT count(*) FROM seat),
        IF (id MOD 2 = 0, id - 1, id + 1),
        IF (id MOD 2 = 0, id - 1, id)
    ) AS id, student
FROM seat
ORDER BY id;
# Approach II: Using flow control statement CASE
SELECT 
    (
        CASE
            WHEN MOD(id,2)=0 THEN id-1
            WHEN MOD(id,2)=1 
                AND id=(SELECT count(*)  FROM seat) 
                THEN id
            ELSE id+1
        END
    ) as id,student
FROM seat 
ORDER BY id

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 statement and no intermediate temp table.
Note that you must write a single update statement, DO NOT write any select statement for this problem.

# Easy
UPDATE salary
    SET sex = CASE sex 
        when 'f' then 'm'
        when 'm' then 'f'
    END;