sqlzoo练习答案3

最新推荐文章于 2025-01-13 18:02:07 发布

原创最新推荐文章于 2025-01-13 18:02:07 发布 · 755 阅读

0 ·

CC 4.0 BY-SA版权

本文通过SQLzoo中的练习题目，详细介绍了JOIN操作在处理数据库关联查询中的应用。内容涵盖LEFT JOIN的使用，以及如何查找特定年份中某演员的繁忙程度和主演电影的情况，同时展示了获取主演超过30部电影的演员名单的方法。

四.The JOIN

13.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
...

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 game.id=matchid

group by mdate,matchid,team1,team2.

五.More JOIN

11.Which were the busiest years for 'John Travolta', show the year and the number of movies he made each year for any year in which he made more than 2 movies.

SELECT yr,count(title) FROM movie
join casting on movie.id=movieid
join actor on actor.id=actorid
WHERE name='John Travolta'
GROUP BY yr
HAVING count(title)=(SELECT max(c) FROM (
SELECT yr,count(title) as c FROM movie
join casting on movie.id=movieid
join actor on actor.id=actorid
WHERE name='John Travolta'

GROUP BY yr) AS s )

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

select title,name from movie
join casting on movie.id=movieid
join actor on actorid=actor.id
and ord=1
where movieid in (select movieid from casting
join actor on actor.id=actorid

where name='Julie Andrews')

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

select name from actor
join casting on actor.id=actorid
where ord=1
group by name
having count(movieid)>=30
order by name

14.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

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

select name from actor
join casting on actor.id=actorid
where movieid in (select movieid from casting
join actor on actorid=actor.id
where name='Art Garfunkel')
and name!='Art Garfunkel'