SQL Zoo:The JOIN operation

PART-1:The JOIN operation

1. 第一個例子列出球員姓氏為’Bender’的入球數據。修改此SQL以列出 賽事編號matchid 和球員名 player ,該球員代表德國隊Germany入球的。要找出德國隊球員，要檢查: teamid = ‘GER’

SELECT matchid , player FROM goal
WHERE teamid=‘GER’

2. 只顯示賽事1012的 id, stadium, team1, team2

SELECT id,stadium,team1,team2 FROM game
where id=1012

3. 顯示每一個德國入球的球員名，隊伍名，場館和日期。

SELECT player,teamid,stadium,mdate
FROM game JOIN goal ON (id=matchid)
where teamid like ‘GER’

4. 列出球員名字叫Mario (player LIKE ‘Mario%’)有入球的 隊伍1 team1, 隊伍2 team2 和 球員名 player

select a.team1,a.team2,b.player
from game a join goal b on(a.id=b.matchid)
where player like ‘Mario%’

5. 列出每場球賽中首10分鐘gtime<=10有入球的球員 player, 隊伍teamid, 教練coach, 入球時間gtime

SELECT player, teamid,coach, gtime
FROM goal join eteam on (teamid=id)
WHERE gtime<=10

6. 列出’Fernando Santos’作為隊伍1 team1 的教練的賽事日期，和隊伍名。

select mdate,teamname
from game a join eteam b on (a.team1=b.id)
where b.coach=‘Fernando Santos’

7. 列出場館 'National Stadium, Warsaw’的入球球員。

select b.player
from game a join goal b on(a.id=b.matchid)
where a.stadium= ‘National Stadium, Warsaw’

8. 列出全部賽事，射入德國龍門的球員名字。

SELECT distinct player
FROM game JOIN goal ON matchid = id
WHERE (team1=‘GER’ or team2=‘GER’)
and teamid!=‘GER’

9. 列出隊伍名稱 teamname 和該隊入球總數

SELECT teamname, count(player)
FROM eteam JOIN goal ON id=teamid
group BY teamname

10. 列出場館名和在該場館的入球數字。

select a.stadium,count(b.player)
from game a join goal b on a.id=b.matchid
group by a.stadium

11. 每一場波蘭’POL’有參與的賽事中，列出賽事編號 matchid, 日期date 和入球數字

SELECT matchid,mdate, count(player)
FROM game JOIN goal ON matchid = id
WHERE (team1 = ‘POL’ OR team2 = ‘POL’)
group by matchid,mdate

12. 每一場德國’GER’有參與的賽事中，列出賽事編號 matchid, 日期date 和德國的入球數字。

select matchid,mdate,count(player)
from game a join goal b on a.id=b.matchid
where (a.team1=‘GER’ or a.team2=‘GER’)
and teamid=‘GER’
group by matchid,mdate

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.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,team1,team2
order by mdate, matchid, team1, team2

PART-2:More JOIN operations

1. 列出1962年首影的電影， [顯示 id, title]

SELECT id,title
FROM movie
WHERE yr=1962

2. 電影大國民 ‘Citizen Kane’ 的首影年份。

select yr from movie
where title=‘Citizen Kane’

3. 列出全部Star Trek星空奇遇記系列的電影，包括id, title 和 yr(此系統電影都以Star Trek為電影名稱的開首)。按年份順序排列。

select id,title,yr from movie
where title like ‘Star Trek%’
order by yr

4. id是 11768, 11955, 21191 的電影是什麼名稱?

select title from movie
where id in (11768 , 11955 , 21191 )

5. 女演員’Glenn Close’的編號 id是什麼?

select id from actor
where name=‘Glenn Close’

6. 電影北非諜影’Casablanca’ 的編號 id是什麼?

select id from movie
where title=‘Casablanca’

7. 列出電影北非諜影 'Casablanca’的演員名單,即電影中各演員的真實姓名清單。使用 movieid=11768, 這是上一題得到的結果。

select b.name
from casting a left join actor b on a.actorid=b.id
where a.movieid=11768

8. 顯示電影異型’Alien’ 的演員清單。

select name from actor
where id in
(select actorid
from casting a left join movie b on a.movieid=b.id
where b.title=‘Alien’)

9. 列出演員夏里遜福 ‘Harrison Ford’ 曾演出的電影。

select title from movie
where id in (select movieid from casting a left join actor b on a.actorid=b.id
where b.name=‘Harrison Ford’)

10. 列出演員夏里遜福 ‘Harrison Ford’ 曾演出的電影，但他不是第1主角。

select title from movie
where id in
(select movieid from casting a left join actor b on a.actorid=b.id
where b.name= ‘Harrison Ford’
and ord!=1)

11. 列出1962年首影的電影及它的第1主角。*****

select title,name
from movie a
join casting b on a.id=b.movieid
join actor y on b.actorid=y.id
where a.yr=1962 and b.ord=1

12. 尊·特拉華達’John Travolta’最忙是哪一年? 顯示年份和該年的電影數目。*****

select yr,count(distinct title)
from movie
join casting on id=movieid
join actor on actorid=actor.id
where name=‘John Travolta’
group by yr
having count(distinct title)=(select max(movie_num) from
(select yr,count(distinct title) as movie_num
from movie
join casting on id=movieid
join actor on actorid=actor.id
where name=‘John Travolta’
group by yr) b)

13. 列出演員茱莉·安德絲’Julie Andrews’曾參與的電影名稱及其第1主角。*****

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

14. 列出按字母順序，列出哪一演員曾作30次第1主角。

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

15. 列出1978年首影的電影名稱及角色數目，按此數目由多至少排列。

select title,count(actorid) as count
from movie join casting on id=movieid
where yr=1978
group by movieid,title
order by count desc

16. 列出曾與演員亞特·葛芬柯’Art Garfunkel’合作過的演員姓名。

select distinct name
from casting join actor on id=actorid
where movieid in
(select movieid
from casting join actor on id=actorid
where name=‘Art Garfunkel’)
and name!=‘Art Garfunkel’