数据库基本操作习题

题⽬⼀：

编写⼀个 SQL 查询，查找Person 表中所有重复的电⼦邮箱。
示例：
+----+---------+
| Id | Email   |
+----+---------+
| 1  | a@b.com |
| 2  | c@d.com |
| 3  | a@b.com |
+----+---------+
根据以上输⼊，你的查询应返回以下结果:

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

CREATE TABLE Person(
	Id INT,
	Email VARCHAR(10)
);

INSERT INTO Person(Id,Email) 
VALUES 
(1,'a@b.com'),
(2,'c@d.com'),
(3,'a@b.com');

SELECT email FROM Person GROUP BY email 
HAVING COUNT(Email)>1;

题目二：

这⾥有张World 表
+-----------------+------------+------------+--------------+---------------+
| name            | continent  | area       | population   |  gdp          |
+-----------------+------------+------------+--------------+---------------+
| Afghanistan     | Asia       | 652230     | 25500100     |20343000       |
| Albania         | Europe     | 28748      | 2831741      |12960000       |
| Algeria         | Africa     | 2381741    | 37100000     |188681000      |
| Andorra         | Europe     | 468        | 78115        |3712000        |
| Angola          | Africa     | 1246700    | 20609294     |100990000      |
+-----------------+------------+------------+--------------+---------------+
如果⼀个国家的⾯积超过300万平⽅公⾥，或者⼈⼝超过2500万，那么这个国家就是
⼤国家。
编写⼀个SQL查询，输出表中所有⼤国家的名称、⼈⼝和⾯积。
例如，根据上表，我们应该输出:
+--------------+-------------+--------------+
| name         | population  | area         |
+--------------+-------------+--------------+
| Afghanistan  | 25500100    | 652230       |
| Algeria      | 37100000    | 2381741      |
+--------------+-------------+--------------+

CREATE TABLE World(
	NAME VARCHAR(20),
	continent VARCHAR(10),
	AREA INT,
	population INT,
	gdp INT
);

INSERT INTO World(NAME,continent,AREA,population,gdp) 
VALUE
('Afghanistan','Asia',652230,25500100,20343000),
('Albania','Europe',28748,2831741,12960000),
('Albania','Africa',2381741,37100000,188681000),
('Andorra','Europe',468,78115,3712000),
('Angola','Africa',1246700,20609294,100990000);

SELECT NAME,population,AREA FROM world 
WHERE population>25000000 OR AREA>3000000;

题目三：

表 point 保存了⼀些点在 x 轴上的坐标，这些坐标都是整数。
写⼀个查询语句，找到这些点中最近两个点之间的距离。
+-----+
| x   |
+-----+
| -1  |
| 0   |
| 2   |
+-----+
最近距离显然是 '1' ，是点 '-1' 和 '0' 之间的距离。所以输出应该如下：
+---------+
| shortest|
+---------+
| 1       |
+---------+
注意：每个点都与其他点坐标不同，表 table 不会有重复坐标出现。
进阶：如果这些点在 x 轴上从左到右都有⼀个编号，输出结果时需要输出最近点对的
编号呢？

CREATE TABLE points(
	X INT
);

INSERT INTO points(X) VALUE (-1),(0),(2);

SELECT ABS(p1.x-p2.x) AS shortest FROM points p1,points p2 
WHERE p1.x<>p2.x ORDER BY shortest LIMIT 1;

题目四：

某城市开了⼀家新的电影院，吸引了很多⼈过来看电影。该电影院特别注意⽤户体验，专⻔有个 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   	 |
+---------+-----------+--------------+-----------+

CREATE TABLE cinema(
	id INT,
	movie VARCHAR(10),
	description VARCHAR(10),
	rating DOUBLE(3,1)
);

INSERT INTO cinema(id,movie,description,rating) 
VALUES
(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);

SELECT * FROM cinema WHERE MOD (id,2)=1 AND description != 'boring'
ORDER BY rating DESC;

题目五：

在表 orders 中找到订单数最多客户对应的 customer_number 。

数据保证订单数最多的顾客恰好只有⼀位。

表 orders 定义如下：
+-------------------+-----------+
| Column 			| Type	    |
+-------------------+-----------+
| order_number 	    | int 		|
| customer_number   | int	    |
| order_date 	    | date 		|
| required_date	    | date 		|
| shipped_date 		| date 		|
| status 			| char(15)  |
| comment 			| char(200) |
+-------------------+-----------+

样例输⼊

+--------------+-----------------+------------+---------------+-------------+--------+---------+
| order_number | customer_number | order_date | required_date |shipped_date | status | comment |
+--------------+-----------------+------------+---------------+-------------+--------+---------+
| 1 		   | 1 				 | 2017-04-09 | 2017-04-13 	  | 2017-04-12  | Closed | 		   |
| 2 		   | 2 				 | 2017-04-15 | 2017-04-20 	  | 2017-04-18  | Closed |         |
| 3 		   | 3 				 | 2017-04-16 | 2017-04-25 	  | 2017