183. Customers Who Never Order

Problem

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

Answer

利用查询嵌套

select Customers.Name as Customers 
from Customers
where Customers.Id not in(
select CustomerId from Orders);

184. Department Highest Salary

Problem

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

Answer

先利用分组找出每个部门最高的工资，将工资和部门Id存到一个表中，然后将三个表进行联结。

select Department.Name AS Department,Employee.Name as Employee,tmp.Salary
from Employee,
(select DepartmentId,max(Salary) as Salary 
from Employee
group by DepartmentId) tmp,Department
where Employee.DepartmentId=tmp.DepartmentId
and Employee.Salary=tmp.Salary
and Department.Id=tmp.DepartmentId;

185. Department Top Three Salaries

Problem

The Employee table holds all employees. Every employee has an Id, 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            |
| 5  | Janet | 69000  | 1            |
| 6  | Randy | 85000  | 1            |
+----+-------+--------+--------------+

The Department table holds all departments of the company.

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

Write a SQL query to find employees who earn the top three salaries in each of the department. For the above tables, your SQL query should return the following rows.

+------------+----------+--------+
| Department | Employee | Salary |
+------------+----------+--------+
| IT         | Max      | 90000  |
| IT         | Randy    | 85000  |
| IT         | Joe      | 70000  |
| Sales      | Henry    | 80000  |
| Sales      | Sam      | 60000  |
+------------+----------+--------+

Answer

联结的嵌套，使用自联结找出表中同个部门最高的三个工资（利用计数函数在同一表中查找比自己工资高的人数小于3的人），利用内部联结联结表Employee和表Department。

select d.Name Department, e1.Name Employee, e1.Salary 
from Employee e1
join Department d on e1.DepartmentId = d.Id
where 3 > (select count(distinct(e2.Salary))
           from Employee e2 
           where e2.Salary > e1.Salary and e1.DepartmentId = e2.DepartmentId );

196. Delete Duplicate Emails

Problem

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

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

Answer

使用自联结

Delete p1 from Person p1,Person p2 
where p1.Email=p2.Email and p1.Id>p2.Id;

197. Rising Temperature

Problem

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

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

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

Answer

利用日期函数对两个日期进行计算。

select w1.Id from Weather w1,Weather w2
where w1.Date=adddate(w2.Date,1) and w1.Temperature>w2.Temperature;

262. Trips and Users

Problem

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|
+----+-----------+-----------+---------+--------------------+----------+
| 1  |     1     |     10    |    1    |     completed      |2013-10-01|
| 2  |     2     |     11    |    1    | cancelled_by_driver|2013-10-01|
| 3  |     3     |     12    |    6    |     completed      |2013-10-01|
| 4  |     4     |     13    |    6    | cancelled_by_client|2013-10-01|
| 5  |     1     |     10    |    1    |     completed      |2013-10-02|
| 6  |     2     |     11    |    6    |     completed      |2013-10-02|
| 7  |     3     |     12    |    6    |     completed      |2013-10-02|
| 8  |     2     |     12    |    12   |     completed      |2013-10-03|
| 9  |     3     |     10    |    12   |     completed      |2013-10-03|
| 10 |     4     |     13    |    12   | cancelled_by_driver|2013-10-03|
+----+-----------+-----------+---------+--------------------+----------+

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  |
+----------+--------+--------+
|    1     |   No   | client |
|    2     |   Yes  | client |
|    3     |   No   | client |
|    4     |   No   | client |
|    10    |   No   | driver |
|    11    |   No   | driver |
|    12    |   No   | driver |
|    13    |   No   | driver |
+----------+--------+--------+

Write a SQL query to find the cancellation rate of requests made by unbanned clients 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 |
+------------+-------------------+
| 2013-10-01 |        0.33       |
| 2013-10-02 |        0.00       |
| 2013-10-03 |        0.50       |
+------------+-------------------+

Answer

select Request_at as Day,
round(count(Status!='completed' or NULL)/count(*),2) as 'Cancellation Rate'
from Trips
inner join Users
on Trips.Client_Id=Users.Users_Id and Banned='No'
and Request_at between '2013-10-01' and '2013-10-03'
group by Request_at;