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 

Naive Way : "regardless if there is an address for" means address could be null. So use left outer join to combine two tables. Left join ensures that the left table cannot be null, right table may use null value to match left table.

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

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

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


Naive Way: My naive way is to first find the max(salary), exclude it from all salary, and then select the max from the rest. It nested two sub-queries.

 SELECT MAX(Salary)  
 FROM  
 (SELECT Salary   
 FROM Employee  
 WHERE Salary <>  
 (SELECT MAX(Salary)   
 FROM Employee)) E;  

Improved Way: Select the max from those with salary less than max(Salary)

 SELECT MAX(Salary)  
 FROM Employee  
 WHERE Salary <  
 (SELECT MAX(Salary)   
 FROM Employee);  

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

The Department table holds all departments of the company.

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

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

Naive Way: 做数据库的题真是比做算法题快多了。但还不是很熟练。

 SELECT D.name as Department, E1.name as Employee, E1.Salary as Salary  
 FROM Employee E1 join Department D  
 WHERE E1.DepartmentId = D.Id   
 AND E1.Salary >= (SELECT MAX(Salary) from Employee E2  
 WHERE E1.DepartmentId = E2.DepartmentId);  

Improved Way: 

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. For the above table, Joe is the only employee who earns more than his manager.

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

Naive Way: 这回一次就过了。

 SELECT E1.Name AS Employee  
 FROM Employee E1 join Employee E2  
 WHERE E1.ManagerId = E2.Id AND E1.Salary > E2.Salary;  

Improved Way:还有用left join 的，来自kkamkou

 SELECT e1.`Name`  
  FROM `Employee` e1  
   LEFT JOIN `Employee` e2 ON(e2.`Id` = e1.`ManagerId`)  
   WHERE e1.`ManagerId` IS NOT NULL AND e1.`Salary` > e2.`Salary`  

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

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

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

Note: All emails are in lowercase.

 Naive Way: 我先建一个Table(Email, count(*)), 然后在这个Table里找count(*) > 1的。

 SELECT Email  
 FROM   
 (SELECT Email, count(*) AS num  
 FROM Person  
 FROUP BY Email) E  
 WHERE num > 1;  

Improved Way: 不需要使用多余的subquery。

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

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

Using the above tables as example, return the following:

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

值得纪念的一次，第一次做出一道数据库的题，我没有任何数据库基础可言，全是自学youtube Standford 那个教程的（https://www.youtube.com/watch?v=D-k-h0GuFmE&list=PL6hGtHedy2Z4EkgY76QOcueU8lAC4o6c3），觉得这道题好简单啊。

 SELECT Name AS Customers  
 FROM Customers  
 WHERE Id NOT IN  
 (SELECT CustomerId AS Id FROM Orders);  

发图一张，特此纪念。