Leetcode中的SQL题目练习（一）

595. Big Countries

https://leetcode.com/problems/big-countries/description/

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

A country is big if it has an area of bigger than 3 million square km or a population of more than 25 million.
Write a SQL solution to output big countries’ name, population and area.(查找面积超过 3,000,000 或者人口数超过 25,000,000 的国家。)
For example, according to the above table, we should output:

name	population	area
Afghanistan	25500100	652230
Algeria	37100000	2381741

Solution

SELECT name,
    population,
    area
FROM
    World
WHERE
    area > 3000000
    OR population > 25000000;

627. Swap Salary

https://leetcode.com/problems/swap-salary/description/

Description

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

Given a table salary, such as the one below, that has m=male and f=female values. Swap all f and m values (i.e., change all f values to m and vice versa) with a single update query and no intermediate temp table.(只用一个 SQL 查询，将 sex 字段反转。)
After running your query, the above salary table should have the following rows:

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

Solution：

update salary
set sex = 
    case sex
        when 'm'
        then 'f'
        else 'm'
    end;

620. Not Boring Movies

https://leetcode.com/problems/not-boring-movies/description/

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

查找 id 为奇数，并且 description 不是 boring 的电影，按 rating 降序。

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

Solution：

SELECT
    *
FROM
    cinema
WHERE
    id % 2 = 1
    AND description != 'boring'
ORDER BY
    rating DESC;

596. Classes More Than 5 Students

Description

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

查找有五名及以上 student 的 class。

class

Math

Solution：

SELECT
    class
FROM
    courses
GROUP BY
    class
HAVING
    count( DISTINCT student ) >= 5;

182. Duplicate Emails

https://leetcode.com/problems/duplicate-emails/description/

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

查找重复的邮件地址：

Email

a@b.com

Solution：

select Email
from Person 
group by Email 
having count(Email)>=2

196. Delete Duplicate Emails ?

https://leetcode.com/problems/delete-duplicate-emails/description/

Description:

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

删除重复的邮件地址

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

Solution:

(1)

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

(2)

DELETE
FROM
    Person
WHERE
    id NOT IN ( SELECT id FROM ( SELECT min( id ) AS id FROM Person GROUP BY email ) AS m );