1. 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 |
+----+--------+
SELECT
(
SELECT DISTINCT `salary`
FROM employee
ORDER BY `salary` DESC
LIMIT 1
OFFSET 1
) AS sec_highest_salary
;
2. Combine Two Tables
Table: Person
+-------------+---------+
| Column Name | Type |
+-------------+---------+
| PersonId | int |
| FirstName | varchar |
| LastName | varchar |
+-------------+---------+
PersonId is the primary key column for this table.
Table: Address
+-------------+---------+
| Column Name | Type |
+-------------+---------+
| AddressId | int |
| PersonId | int |
| City | varchar |
| State | varchar |
+-------------+---------+
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
SELECT p.firstname,
p.lastname,
a.city,
a.state
FROM person AS p
LEFT JOIN address AS a
ON p.personid = a.personid
GROUP BY p.firstname, p.lastname, a.city, a.state
;
这个题address里面的perosnid并不是primary key， 所以存在一种情况是相同的personid有不同的address，所以我们join以后，其实personid有可能是有重复值的，为了避免这种情况发生，我们把最后要select的这些column全部group by以后再选，去掉所有重复值。
3. 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 |
+----+-------+--------+-----------+
Given the Employee table, write a SQL query that finds out employees who earn more than their managers.
SELETC a.name
FROM employee AS a #员工表和员工salary
INNER JOIN employee AS b #manager表和manager的salary
ON a.managerid = b.id
WHERE a.salary > b.salary
;
join时要明确每个表代表的不同立场，可以通过注释来防止遗忘和混乱。
4. Reformat Department Table
Table: Department
+---------------+---------+
| Column Name | Type |
+---------------+---------+
| id | int |
| revenue | int |
| month | varchar |
+---------------+---------+
(id, month) is the primary key of this table.
The table has information about the revenue of each department per month.
The month has values in ["Jan","Feb","Mar","Apr","May","Jun","Jul","Aug","Sep","Oct","Nov","Dec"].
Write an SQL query to reformat the table such that there is a department id column and a revenue column for each month.
Department table:
+------+---------+-------+
| id | revenue | month |
+------+---------+-------+
| 1 | 8000 | Jan |
| 2 | 9000 | Jan |
| 3 | 10000 | Feb |
| 1 | 7000 | Feb |
| 1 | 6000 | Mar |
+------+---------+-------+
SELECT
id,
SUM(CASE WHEN month = 'jan' THEN revenue ELSE NULL END) AS jan_revenue,
SUM(CASE WHEN month = 'feb' THEN revenue ELSE NULL END) AS feb_revenue,
SUM(CASE WHEN month = 'Mar' THEN revenue ELSE NULL END) AS mar_revenue,
SUM(CASE WHEN month = 'Apr' THEN revenue ELSE NULL END) AS apr_revenue,
SUM(CASE WHEN month = 'may' THEN revenue ELSE NULL END) AS may_revenue,
SUM(CASE WHEN month = 'jun' THEN revenue ELSE NULL END) AS jun_revenue,
SUM(CASE WHEN month = 'Jul' THEN revenue ELSE NULL END) AS jul_revenue,
SUM(CASE WHEN month = 'aug' THEN revenue ELSE NULL END) AS aug_revenue,
SUM(CASE WHEN month = 'sep' THEN revenue ELSE NULL END) AS sep_revenue,
SUM(CASE WHEN month = 'oct' THEN revenue ELSE NULL END) AS oct_revenue,
SUM(CASE WHEN month = 'nov' THEN revenue ELSE NULL END) AS nov_revenue,
SUM(CASE WHEN month = 'dec' THEN revenue ELSE NULL END) AS dec_revenue
FROM department
GROUP BY id
;
该题有两点需要注意的地方：一是当用group by和case when的时候，注意聚合函数应作用于整个case到end的语句，而不是放在then的后面；二是如果case when只想表达如果那么的关系，并没有涉及到else的话，这时候else也是可以加的（后面跟null），效果没有差别。
5. 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 |
+----+-------+
Table: Orders.
+----+------------+
| Id | CustomerId |
+----+------------+
| 1 | 3 |
| 2 | 1 |
+----+------------+
SELECT name
FROM customers
WHERE id NOR IN (SELECT DISTINCT customerid FROM orders)
;
这道题有个需要注意的地方：当表中含有多个id的时候，千万要注意id跟id之间是不是指同一个id，比如这道题中很容易就把两个表中的id当是一个id。
6. 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 |
+----+---------+
SELECT a.email
FROM
(SELECT email, count(email) as num
FROM person
GROUP BY email) AS a
WHERE num >1
;
