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

利用查詢(xún)嵌套

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

先利用分組找出每個(gè)部門(mén)最高的工資，將工資和部門(mén)Id存到一個(gè)表中乃秀，然后將三個(gè)表進(jìn)行聯(lián)結(jié)等限。

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

聯(lián)結(jié)的嵌套峦树，使用自聯(lián)結(jié)找出表中同個(gè)部門(mén)最高的三個(gè)工資（利用計(jì)數(shù)函數(shù)在同一表中查找比自己工資高的人數(shù)小于3的人）政供，利用內(nèi)部聯(lián)結(jié)聯(lián)結(jié)表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

使用自聯(lián)結(jié)

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

利用日期函數(shù)對(duì)兩個(gè)日期進(jìn)行計(jì)算投剥。

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;