SQLZOO难题

11，问题：List the film title and the leading actor for all of the films 'Julie Andrews' played in.
回答：select title,name from movie left join casting on movie.id=movieid left join actor on actorid=actor.id where movie.id in
(select movieid from casting left join actor on actorid=actor.id where name='Julie Andrews') and ord=1

10：问题：Obtain a list, in alphabetical order, of actors who've had at least 15 starring roles.
回答：select name from casting join actor on actorid=actor.id where ord=1 group by name having count(movieid)>=15
9,问题：List the films released in the year 1978 ordered by the number of actors in the cast, then by title.
回答：select title,count(actorid) from movie join casting on movie.id=movieid where yr=1978 group by title order by count(actorid) desc,title
8,问题：List all the people who have worked with 'Art Garfunkel'.
回答：select name from actor join casting on actor.id=casting.actorid and casting.movieid in (select movieid from actor join casting on actor.id=casting.actorid and name='Art Garfunkel') and name!='Art Garfunkel'
7,问题：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.
答案：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 id=matchid group by mdate, matchid, team1,team2

6,问题：Instead show the name of all players who scored a goal against Germany
回答：select distinct player from game join goal on game.id=goal.matchid where (team1='GER' or team2='GER') and teamid !='GER'
5,问题：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
4,问题：Some countries have populations more than three times that of any of their neighbours (in the same continent). Give the countries and continents.
答案：select name,continent from world y where y.population/3 > all (
select population from world x where y.continent=x.continent and y.name !=x.name
)
3,问题：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.
答案：
select name,continent,population from world where continent in(
select continent from world group by continent having max(population)<=25000000
)
2，问题：List each continent and the name of the country that comes first alphabetically.
答案：select continent,min(name) from world group by continent

1,

image.png

问题：Find the largest country (by area) in each continent, show the continent, the name and the area:
答案：SELECT continent, name, area from world
where area in
(select max(area) from world group by continent)

image.png image.png image.png image.png
image.png
image.png image.png
image.png