2020-08-21 easy 1-6

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.

+----+-------+--------+-----------+

+----+-------+--------+-----------+

| 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

;