1. 修改此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. 以下SQL列出每個入球的球員(來自goal表格)和場館名(來自game表格)
修改它來顯示每一個德國入球的球員名，隊伍名，場館和日期。
SELECT player,teamid, stadium, mdate
from game JOIN goal ON (id=matchid)
where teamid='GER'
4. 列出球員名字叫Mario (player LIKE 'Mario%')有入球的 隊伍1 team1, 隊伍2 team2 和 球員名 player
select team1, team2, player
from goal join game on (matchid=id)
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 eteam join game on (eteam.id=team1)
where coach='Fernando Santos'
7. 列出場館 'National Stadium, Warsaw'的入球球員。
select player
from goal join game on (matchid=id)
where 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
from game join goal on (id=matchid)
group by stadium
SELECT matchid, mdate, count(player)
FROM game JOIN goal ON (id=matchid)
WHERE (team1= 'POL' OR team2= 'POL')
group by matchid, mdate
from goal join game on (matchid=id)
where teamid='Ger'
group by matchid, mdate
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 JOIN goal ON (matchid = id)
group by mdate, matchid, team1, team2
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'的演員名單。
select name from actor
join casting on (id=actorid)
where movieid=27
8. 顯示電影異型'Alien' 的演員清單。
select name from actor
join casting on (actor.id=casting.actorid)
join movie on (casting.movieid=movie.id)
where title='Alien'
9. 列出演員夏里遜福 'Harrison Ford' 曾演出的電影。
select title from movie
join casting on (movie.id=casting.movieid)
join actor on (casting.actorid=actor.id)
where name= 'Harrison Ford'
10. 列出演員夏里遜福 'Harrison Ford' 曾演出的電影，但他不是第1主角。
select title from movie
join casting on (movie.id=casting.movieid)
join actor on (casting.actorid=actor.id)
where name='Harrison Ford' and ord!=1
11. 列出1962年首影的電影及它的第1主角。
select title from movie
join casting on (movie.id=casting.movieid)
join actor on (casting.actorid=actor.id)
where name='Harrison Ford' and ord!=1
12. 尊·特拉華達'John Travolta'最忙是哪一年? 顯示年份和該年的電影數目。
SELECT yr, COUNT(title) FROM movie
JOIN casting ON (movie.id=movieid)
JOIN actor ON (actorid=actor.id)
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 actorid=actor.id
where name='John Travolta'
GROUP BY yr) AS t)
13. 列出演員茱莉·安德絲'Julie Andrews'曾參與的電影名稱及其第1主角。
SELECT title, name FROM movie
join casting on (movie.id=casting.movieid)
join actor on (casting.actorid=actor.id)
WHERE movieid in (select movieid FROM movie
join casting on (movie.id=casting.movieid)
join actor on (casting.actorid=actor.id)
where actor.name='Julie Andrews') and ord=1
14. 列出按字母順序，列出哪一演員曾作30次第1主角。
select name from actor
join casting on (actor.id=casting.actorid)
where ord=1
group by name
having count(name)>=30
order by name
15. 列出1978年首影的電影名稱及角色數目，按此數目由多至少排列。
select title, count(ord) from movie
join casting on (movie.id=casting.movieid)
join actor on (casting.actorid=actor.id)
where yr=1978
group by title
order by count(ord) desc
16. 列出曾與演員亞特·葛芬柯'Art Garfunkel'合作過的演員姓名。
select name from actor
join casting on (actor.id=casting.actorid)
where name!='Art Garfunkel' and movieid in
(select movieid from movie join casting on (movie.id=casting.movieid)
join actor on (casting.actorid=actor.id)
where actor.name='Art Garfunkel')
Join Quiz 2
3 5 3 2 4 3 2
Self Join Edinburgh Buses
1. How many stops are in the database.
select count(name) from stops
2. Find the id value for the stop 'Craiglockhart'
select id from stops
where name='Craiglockhart'
3. Give the id and the name for the stops on the '4' 'LRT' service.
SELECT id, name FROM stops
JOIN route ON stops.id = route.stop
WHERE route.num=4 and route.company='LRT'
4. The query shown gives the number of routes that visit either London Road (149) or Craiglockhart (53). Run the query and notice the two services that link these stops have a count of 2. Add a HAVING clause to restrict the output to these two routes.
SELECT company, num, COUNT(*)
FROM route WHERE stop=149 OR stop=53
GROUP BY company, num
having count(*)=2
5. Execute the self join shown and observe that b.stop gives all the places you can get to from Craiglockhart, without changing routes. Change the query so that it shows the services from Craiglockhart to London Road.
SELECT a.company, a.num, a.stop, b.stop
FROM route a JOIN route b ON (a.company=b.company AND a.num=b.num)
WHERE a.stop=53 and b.stop=149
6. The query shown is similar to the previous one, however by joining two copies of the stops table we can refer to stops by name rather than by number. Change the query so that the services between 'Craiglockhart' and 'London Road' are shown. If you are tired of these places try 'Fairmilehead' against 'Tollcross'
SELECT a.company, a.num, stopa.name, stopb.name
FROM route a JOIN route b ON
(a.company=b.company AND a.num=b.num)
JOIN stops stopa ON (a.stop=stopa.id) JOIN stops stopb ON (b.stop=stopb.id)
WHERE stopa.name='Craiglockhart' and stopb.name='London Road'
7. Give a list of all the services which connect stops 115 and 137 ('Haymarket' and 'Leith')
select distinct a.company, a.num from route a join route b on (a.num=b.num)
where a.stop=115 and b.stop=137
8. Give a list of the services which connect the stops 'Craiglockhart' and 'Tollcross'
select distinct a.company, a.num from route a join route b on (a.num=b.num) join stops stopsa on (a.stop=stopsa.id) join stops stopsb on (b.stop=stopsb.id)
where stopsa.name ='Craiglockhart' and stopsb.name='Tollcross'
9. Give a distinct list of the stops which may be reached from 'Craiglockhart' by taking one bus, including 'Craiglockhart' itself, offered by the LRT company.
Include the company and bus no. of the relevant services.
select distinct stopsb.name, a.company, a.num from route a join route b on (a.num=b.num AND a.company = b.company) join stops stopsa on (a.stop=stopsa.id) join stops stopsb on (b.stop=stopsb.id)
where stopsa.name ='Craiglockhart'
Self Quiz
3 5 4
留言列表
