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/

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/

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

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/

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 );