SQLZOO练习记录

网址：https://sqlzoo.net/ 

1.查询内容里的嵌套，查询语句在select中

Germany (population 80 million) has the largest population of the countries in Europe. Austria (population 8.5 million) has 11% of the population of Germany.

Show the name and the population of each country in Europe. Show the population as a percentage of the population of Germany.

The format should be Name, Percentage for example:

select name,concat(round(population/(select population from world where name ='Germany')*100,0),'%')
 from world
 where continent  ='Europe'

2. 找到每个大陆中面积最大的国家

Find the largest country (by area) in each continent, show the continent, the name and the area:

The above example is known as a correlated or synchronized sub-query.

相关的子查询（子查询嵌套查询）一般用于“在.....其中找到最大的”

SELECT continent, name, area FROM world x
  WHERE area>= ALL
    (SELECT area FROM world y
        WHERE y.continent=x.continent
          AND area >0)

3. 每个大陆第一个国家（按名字排序）

First country of each continent (alphabetically)

solution1：all 可以对name进行排序并取第一位

select continent,name from world a
  where name <= all
    (select name from world b
       where a.continent =b.continent )

solution2：利用窗口函数

select continent,name from 
  ( select continent,name,
     row_number()over(partition by continent order by name) as num
       from world ) t           
         where num=1

4.Find the continents where all countries have a population <= 25000000. Then find the names of the countries associated with these continents. Show name, continent and population.

solution1：子查询

select name,continent,population from world
  where continent in ( SELECT continent from world
  group by continent 
  having max(population) <=25000000)

solution2： 相关的（关联的）子查询

select name,continent,population from world a 
  where 25000000 >=(
    select max(population) from world b 
       where a.continent =b.continent )

select name,continent,population from world a 
  where 25000000 >=all(
    select population from world b 
       where a.continent =b.continent )

5.Some countries have populations more than three times that of all of their neighbours (in the same continent). Give the countries and continents.

select name,continent from world a
   where a.population >= all(
     select population*3 from world b
        where a.continent =b.continent
          and a.name != b.name
          and population >0)

记录：SQL ALL运算符是一个逻辑运算符，它将单个值与子查询返回的单列值集进行比较。

6.For each continent show the continent and number of countries with populations of at least 10 million.

select continent,count(name )
from world
where population >= 10000000
group by continent

7.List the continents that have a total population of at least 100 million.

select continent
from world
group by continent 
having sum(population) >=100000000

8.List every match with the goals scored by each team as shown. This will use "CASE WHEN" which has not been explained in any previous exercises.

mdate	team1	score1	team2	score2
1 July 2012	ESP	4	ITA	0
10 June 2012	ESP	1	ITA	1
10 June 2012	IRL	1	CRO	3
...

Notice in the query given every goal is listed. If it was a team1 goal then a 1 appears in score1, otherwise there is a 0. You could SUM this column to get a count of the goals scored by team1. Sort your result by mdate, matchid, team1 and team2.

SELECT
  mdate,
  team1,
  sum(CASE WHEN teamid=team1 THEN 1 ELSE 0 END) score1,
  team2,
  sum(CASE WHEN teamid=team2 THEN 1 ELSE 0 END) score2
  FROM game 
 left JOIN goal ON matchid = id
 group by mdate,matchid ,team1,team2

9.List the film title and the leading actor for all of the films 'Julie Andrews' played in.

select title,name from movie a
join casting b on a.id =b.movieid
join actor c on b.actorid =c.id
  where ord =1
    group by movieid
      having movieid in
        (select x.id from movie x
           join casting y on x.id =y.movieid
           join actor z on y.actorid =z.id and name ='Julie Andrews' )

10.Obtain a list, in alphabetical order, of actors who've had at least 15 starring roles.

select name,movieid
from actor a
join casting b
on a.id =b.actorid
join  movie c
on c.id = b.movieid
where ord =1
group by a.id
having count(c.id) >= 15
order by name 

11.List all the people who have worked with 'Art Garfunkel'. 

select  name
from actor a
join casting b
on a.id = b.actorid
where movieid in (
select x.id  from movie x
join casting y
on y.movieid = x.id
join actor z
on z.id = y.actorid
where z.name ='Art Garfunkel')
and a.name != 'Art Garfunkel'

12.Use COALESCE to print the mobile number. Use the number '07986 444 2266' if there is no number given. Show teacher name and mobile number or '07986 444 2266'

COALESCE函数用法：

基本语法：coalesce（表达式1，表达式2，表达式3，...）

当字段为表达式1时，则返回表达式2，否则返回表达式3

若为空需用空"字符串，如 coalesce('')

select *
from table 
where COALESCE(userid,id2)  is not null  --若userid,id2不为空，则返回明细

select name,
coalesce(mobile,'07986 444 2266')
from teacher