1.組合兩個表

基本信息

表1: Person

列名	類型
PersonId	int
FirstName	varchar
LastName	varchar

• PersonId 是上表主鍵
  表2: Address

列名	類型
AddressId	int
PersonId	int
City	varchar
State	varchar

• AddressId 是上表主鍵

要求

編寫一個 SQL 查詢，滿足條件：無論 person 是否有地址信息，都需要基于上述兩表提供 person 的以下信息：

FirstName, LastName, City, State

答案

select Person.FirstName,Person.LastName,Address.City,Address.State  
from Person left join Address on  Person.PersonId = Address.PersonId;

2.第二高的薪水

基本信息

編寫一個 SQL 查詢，獲取 Employee 表中第二高的薪水（Salary） 。

Id	Salary
1	100
2	200
3	300

要求

例如上述 Employee 表，SQL查詢應該返回 200 作為第二高的薪水。如果不存在第二高的薪水，那么查詢應返回 null。

SecondHighestSalary
200

答案

select (select DISTINCT  Salary from Employee ORDER BY Salary DESC limit 1 offset 1
) as SecondHighestSalary;

3.查找重復的電子郵箱

基本信息

編寫一個 SQL 查詢，查找 Person 表中所有重復的電子郵箱。

示例：

Id	Email
1	a@b.com
2	c@d.com
3	a@b.com

要求

根據以上輸入，你的查詢應返回以下結果：

Email
a@b.com

說明：所有電子郵箱都是小寫字母。

答案

select Email from Person group by Email having count(Email)>1;

4.超過經理收入的員工

基本信息

Employee 表包含所有員工，他們的經理也屬于員工。每個員工都有一個 Id，此外還有一列對應員工的經理的 Id。

Id	Name	Salary	ManagerId
1	Joe	70000	3
2	Henry	80000	4
3	Sam	60000	NULL
4	Max	90000	NULL

要求

給定 Employee 表，編寫一個 SQL 查詢，該查詢可以獲取收入超過他們經理的員工的姓名。在上面的表格中，Joe 是唯一一個收入超過他的經理的員工。

Employee
Joe

答案：

Select e1.Name as Employee 
from Employee e1 join Employee e2  on e1.ManagerId = e2.Id and e1.Salary > e2.Salary

5.超過5名學生的課

信息：

有一個courses 表 ，有: student (學生) 和 class (課程)。

請列出所有超過或等于5名學生的課。

例如,表:

student	class
A	Math
B	English
C	Math
D	Biology
E	Math
F	Computer
G	Math
H	Math
I	Math

要求

應該輸出:

class
Math

• Note:
  學生在每個課中不應被重復計算。

答案

select class
from courses
group by class
having count(DISTINCT student) >= 5;

6.有趣的電影

信息

某城市開了一家新的電影院，吸引了很多人過來看電影。該電影院特別注意用戶體驗，專門有個 LED顯示板做電影推薦，上面公布著影評和相關電影描述。

作為該電影院的信息部主管，您需要編寫一個 SQL查詢，找出所有影片描述為非 boring (不無聊) 的并且 id 為奇數 的影片，結果請按等級 rating 排列。

例如，下表 cinema:

id	movie	description	rating
1	War	great 3D	8.9
2	Science	fiction	8.5
3	irish	boring	6.2
4	Ice song	Fantacy	8.6
5	House card	Interesting	9.1

要求

對于上面的例子，則正確的輸出是為：

id	movie	description	rating
5	House card	Interesting	9.1
1	War	great 3D	8.9

答案

select id,movie, description, rating
from cinema
where description  !='boring' and id %2=1
order by rating desc;

7.交換工資（updeta,條件判斷）

基本信息

給定一個 salary 表，如下所示，有 m = 男性 和 f = 女性 的值。交換所有的 f 和 m 值（例如，將所有 f 值更改為 m，反之亦然）。要求只使用一個更新（Update）語句，并且沒有中間的臨時表。

注意，您必只能寫一個 Update 語句，請不要編寫任何 Select 語句。

例如：

id	name	sex	salary
1	A	m	2500
2	B	f	1500
3	C	m	5500
4	D	f	500

要求

運行你所編寫的更新語句之后，將會得到以下表:

id	name	sex	salary
1	A	f	2500
2	B	m	1500
3	C	f	5500
4	D	m	500

答案

UPDATE salary 
SET sex=IF(sex='f','m','f');

8從不訂購的客戶

信息

某網站包含兩個表，Customers 表和 Orders 表。編寫一個 SQL 查詢，找出所有從不訂購任何東西的客戶。

Customers 表：

Id	Name
1	Joe
2	Henry
3	Sam
4	Max

Orders 表：

Id	CustomerId
1	3
2	1

要求

例如給定上述表格，你的查詢應返回：

Customers
Henry
Max

答案

使用的not in

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

使用的not exists

select Name as Customers
from Customers
where  not exists (select * from Orders where Customers.Id = Orders.CustomerId);

9.上升的溫度(mysql函數to_days)

信息

給定一個 Weather 表，編寫一個 SQL 查詢，來查找與之前（昨天的）日期相比溫度更高的所有日期的 Id。

Id(INT)	RecordDate(DATE)	Temperature(INT)
1	2015-01-01	10
2	2015-01-02	25
3	2015-01-03	20
4	2015-01-04	30

例如，根據上述給定的 Weather 表格，返回如下 Id:

要求

Id
2
4

答案

select w1.Id 
from weather as w1,weather as w2
where w1.Temperature > w2.Temperature and to_days(w1.RecordDate)-to_days(w2.RecordDate)=1;

10.刪除重復的郵箱

信息

編寫一個 SQL 查詢，來刪除 Person 表中所有重復的電子郵箱，重復的郵箱里只保留 Id 最小 的那個。

Id	Email
1	john@example.com
2	bob@example.com
3	john@example.com

• Id 是這個表的主鍵。

要求

例如，在運行你的查詢語句之后，上面的 Person 表應返回以下幾行:

Id	Email
1	john@example.com
2	bob@example.com

答案

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

11.員工獎金(null的應用)

信息

select all employee’s name and bonus whose bonus is < 1000.

Table:Employee

empId	name	supervisor	salary
1	John	3	1000
2	Dan	3	2000
3	Brad	null	4000
4	Thomas	3	4000

empId is the primary key column for this table.
Table: Bonus

empId	bonus
2	500
4	2000

• empId is the primary key column for this table.

要求

Example ouput:

name	bonus
John	null
Dan	500
Brad	null

選出所有獎金<1000元的雇員姓名及獎金數額

答案：

select name, bonus
from Employee e left join Bonus b
on e.empId = b.empId
where bonus < 1000 or bonus is null

12.查找訂單最多的用戶