# SQL语言 - SQL题目进阶
接下来，通过Leetcode上的SQL题目进行进阶吧。
# 相关题目
# 595. Big Countries
https://leetcode.com/problems/big-countries/description/ (opens new window)
# Description
+-----------------+------------+------------+--------------+---------------+
| 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 |
+-----------------+------------+------------+--------------+---------------+
1
2
3
4
5
6
7
8
9
2
3
4
5
6
7
8
9
查找面积超过 3,000,000 或者人口数超过 25,000,000 的国家。
+--------------+-------------+--------------+
| name | population | area |
+--------------+-------------+--------------+
| Afghanistan | 25500100 | 652230 |
| Algeria | 37100000 | 2381741 |
+--------------+-------------+--------------+
1
2
3
4
5
6
2
3
4
5
6
# SQL Schema
DROP TABLE
IF
EXISTS World;
CREATE TABLE World ( NAME VARCHAR ( 255 ), continent VARCHAR ( 255 ), area INT, population INT, gdp INT );
INSERT INTO World ( NAME, continent, area, population, gdp )
VALUES
( 'Afghanistan', 'Asia', '652230', '25500100', '203430000' ),
( 'Albania', 'Europe', '28748', '2831741', '129600000' ),
( 'Algeria', 'Africa', '2381741', '37100000', '1886810000' ),
( 'Andorra', 'Europe', '468', '78115', '37120000' ),
( 'Angola', 'Africa', '1246700', '20609294', '1009900000' );
1
2
3
4
5
6
7
8
9
10
11
2
3
4
5
6
7
8
9
10
11
# Solution
SELECT name,
population,
area
FROM
World
WHERE
area > 3000000
OR population > 25000000;
1
2
3
4
5
6
7
8
2
3
4
5
6
7
8
# 627. Swap Salary
https://leetcode.com/problems/swap-salary/description/ (opens new window)
# Description
| id | name | sex | salary |
|----|------|-----|--------|
| 1 | A | m | 2500 |
| 2 | B | f | 1500 |
| 3 | C | m | 5500 |
| 4 | D | f | 500 |
1
2
3
4
5
6
2
3
4
5
6
只用一个 SQL 查询，将 sex 字段反转。
| id | name | sex | salary |
|----|------|-----|--------|
| 1 | A | f | 2500 |
| 2 | B | m | 1500 |
| 3 | C | f | 5500 |
| 4 | D | m | 500 |
1
2
3
4
5
6
2
3
4
5
6
# SQL Schema
DROP TABLE
IF
EXISTS salary;
CREATE TABLE salary ( id INT, NAME VARCHAR ( 100 ), sex CHAR ( 1 ), salary INT );
INSERT INTO salary ( id, NAME, sex, salary )
VALUES
( '1', 'A', 'm', '2500' ),
( '2', 'B', 'f', '1500' ),
( '3', 'C', 'm', '5500' ),
( '4', 'D', 'f', '500' );
1
2
3
4
5
6
7
8
9
10
2
3
4
5
6
7
8
9
10
# Solution
UPDATE salary
SET sex = CHAR ( ASCII(sex) ^ ASCII( 'm' ) ^ ASCII( 'f' ) );
1
2
2
# 620. Not Boring Movies
https://leetcode.com/problems/not-boring-movies/description/ (opens new window)
# Description
+---------+-----------+--------------+-----------+
| 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 |
+---------+-----------+--------------+-----------+
1
2
3
4
5
6
7
8
9
2
3
4
5
6
7
8
9
查找 id 为奇数，并且 description 不是 boring 的电影，按 rating 降序。
+---------+-----------+--------------+-----------+
| id | movie | description | rating |
+---------+-----------+--------------+-----------+
| 5 | House card| Interesting| 9.1 |
| 1 | War | great 3D | 8.9 |
+---------+-----------+--------------+-----------+
1
2
3
4
5
6
2
3
4
5
6
# SQL Schema
DROP TABLE
IF
EXISTS cinema;
CREATE TABLE cinema ( id INT, movie VARCHAR ( 255 ), description VARCHAR ( 255 ), rating FLOAT ( 2, 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 );
1
2
3
4
5
6
7
8
9
10
11
2
3
4
5
6
7
8
9
10
11
# Solution
SELECT
*
FROM
cinema
WHERE
id % 2 = 1
AND description != 'boring'
ORDER BY
rating DESC;
1
2
3
4
5
6
7
8
9
2
3
4
5
6
7
8
9
# 596. Classes More Than 5 Students
https://leetcode.com/problems/classes-more-than-5-students/description/ (opens new window)
# Description
+---------+------------+
| student | class |
+---------+------------+
| A | Math |
| B | English |
| C | Math |
| D | Biology |
| E | Math |
| F | Computer |
| G | Math |
| H | Math |
| I | Math |
+---------+------------+
1
2
3
4
5
6
7
8
9
10
11
12
13
2
3
4
5
6
7
8
9
10
11
12
13
查找有五名及以上 student 的 class。
+---------+
| class |
+---------+
| Math |
+---------+
1
2
3
4
5
2
3
4
5
# SQL Schema
DROP TABLE
IF
EXISTS courses;
CREATE TABLE courses ( student VARCHAR ( 255 ), class VARCHAR ( 255 ) );
INSERT INTO courses ( student, class )
VALUES
( 'A', 'Math' ),
( 'B', 'English' ),
( 'C', 'Math' ),
( 'D', 'Biology' ),
( 'E', 'Math' ),
( 'F', 'Computer' ),
( 'G', 'Math' ),
( 'H', 'Math' ),
( 'I', 'Math' );
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
2
3
4
5
6
7
8
9
10
11
12
13
14
15
# Solution
SELECT
class
FROM
courses
GROUP BY
class
HAVING
count( DISTINCT student ) >= 5;
1
2
3
4
5
6
7
8
2
3
4
5
6
7
8