一、足球数据库

1.

select matchid,player
from goal 
where teamid = 'GER'

2.

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 = 'GER'

4.

select team1,team2,player
from game join goal on id=matchid
where player like 'Mario%'

5.

SELECT player, teamid, coach,gtime
FROM goal as x
JOIN eteam as y
on x.teamid = y.id
 WHERE gtime<=10

6.

select mdate,teamname
from eteam as x
join game as y
on x.id = y.team1
where coach = 'Fernando Santos'

7.

select player
from goal as x
join game as y
on x.matchid = y.id
where stadium = 'National Stadium, Warsaw'

8.

题目要求：列出面对德国队有进球记录的球员姓名
思路拆解：

1. 列出球员姓名：select name
2. 面对德国队：
   1. 德国队的对手：teamid != ‘GER’
   2. 双方其中一队是德国队：team1 = ‘GER’ or team2 = ‘GER’

9.

SELECT teamname, count(*)
  FROM eteam JOIN goal ON id=teamid
GROUP BY teamname

10.

11.

12.

SELECT matchid,mdate,count(*)
FROM goal JOIN game ON id = matchid
WHERE teamid = 'GER'
GROUP BY matchid,mdate

13.

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

二、音乐数据库

三、more JOINT

6.

select name
from actor as x 
join casting as y on x.id= y.actorid
join movie as z on z.id = y.movieid
where title = 'Casablanca'

7.

select name
from actor as x
join casting as y on x.id = y.actorid
join movie as z on z.id = y.movieid
where z.title = 'Alien'

8.

select title 
from movie as x
join casting as y on x.id = y.movieid
join actor as z on z.id = y.actorid
where name in ('Harrison Ford')

9.

select title
from movie as x
join casting as y on x.id = y.movieid
join actor as z on z.id = y.actorid
where z.name = 'Harrison Ford' and y.ord != 1

10.

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

11.

SELECT yr,COUNT(title) FROM
  movie JOIN casting ON movie.id=movieid
        JOIN actor   ON actorid=actor.id
WHERE name='Rock Hudson'
GROUP BY yr
HAVING COUNT(title) > 2

12.

SELECT title,name
FROM movie as x
join casting as y on x.id = y.movieid
join actor as z on z.id = y.actorid
WHERE movieid IN (
  SELECT movieid FROM actor
  join casting on id=actorid
  WHERE name='Julie Andrews') and ord = 1

13.

select name
from actor as x
join casting as y  on x.id = y.actorid
join movie as z  on z.id = y.movieid
where ord = 1
group by name
having count(*) >= 15
order by name

14.

select title,count(name)
from movie as x
join casting as y  on x.id = y.movieid
join actor as z  on z.id = y.actorid
where yr = 1978
group by title
order by count(name) desc,title

15.

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