数据表:game-gaol-eteam
id | mdate | stadium | team1 | team2 |
---|---|---|---|---|
1001 | 8 June 2012 | National Stadium, Warsaw | POL | GRE |
1002 | 8 June 2012 | Stadion Miejski (Wroclaw) | RUS | CZE |
1003 | 12 June 2012 | Stadion Miejski (Wroclaw) | GRE | CZE |
1004 | 12 June 2012 | National Stadium, Warsaw | POL | RUS |
... |
matchid | teamid | player | gtime | |
---|---|---|---|---|
1001 | POL | Robert Lewandowski | 17 | |
1001 | GRE | Dimitris Salpingidis | 51 | |
1002 | RUS | Alan Dzagoev | 15 | |
1002 | RUS | Roman Pavlyuchenko | 82 | |
... |
id | teamname | coach | ||
---|---|---|---|---|
POL | Poland | Franciszek Smuda | ||
RUS | Russia | Dick Advocaat | ||
CZE | Czech Republic | Michal Bilek | ||
GRE | Greece | Fernando Santos | ||
... |
Q1
Show the matchid and player name for all goals scored by Germany. To identify German players, check for: teamid = 'GER'
SELECT matchid,player FROM goal
WHERE teamid='GER'
Q2
Show id, stadium, team1, team2 for just game 1012
SELECT id,stadium,team1,team2 FROM game
WHERE id='1012'
Q3
Show the player, teamid, stadium and mdate for every German goal.
SELECT player,teamid,stadium,mdate
FROM game JOIN goal ON (id=matchid)
WHERE teamid='GER'
JOIN
SQL JOIN 子句用于把来自两个或多个表的行基于共同字段结合
SQL INNER JOIN: 从多个表中返回满足 JOIN 条件的所有行
SELECT column1, column2, ... FROM table1 JOIN table2 ON condition
- INNER JOIN:如果表中有至少一个匹配,则返回行
- LEFT JOIN:即使右表中没有匹配,也从左表返回所有的行
- RIGHT JOIN:即使左表中没有匹配,也从右表返回所有的行
- FULL JOIN:只要其中一个表中存在匹配,则返回行
Q4
Show the team1, team2 and player for every goal scored by a player called Mario player LIKE 'Mario%'
SELECT team1,team2,player FROM game
JOIN goal ON id=matchid
WHERE player LIKE 'Mario%'
Q5
Show player
, teamid
, coach
, gtime
for all goals scored in the first 10 minutes gtime<=10
SELECT player, teamid, coach,gtime
FROM goal JOIN eteam ON id=teamid
WHERE gtime<=10
Q6
List the dates of the matches and the name of the team in which 'Fernando Santos' was the team1 coach.
SELECT mdate,teamname FROM game
JOIN eteam ON team1=eteam.id
WHERE COACH='Fernando Santos'
Q7
List the player for every goal scored in a game where the stadium was 'National Stadium, Warsaw'
SELECT player FROM game
JOIN goal ON id=matchid
WHERE stadium='National Stadium, Warsaw'
Q8
The example query shows all goals scored in the Germany-Greece quarterfinal.
Instead show the name of all players who scored a goal against Germany.
SELECT DISTINCT player FROM game
JOIN goal ON id=matchid
WHERE teamid<>'GER'
AND (team1='GER' OR team2='GER')
Q9
Show teamname and the total number of goals scored.
SELECT teamname, COUNT(player) FROM eteam
JOIN goal ON id=teamid
GROUP BY teamname
Q10
Show the stadium and the number of goals scored in each stadium.
SELECT stadium, COUNT(player) FROM game
JOIN goal ON id=matchid
GROUP BY stadium
Q11
For every match involving 'POL', show the matchid, date and the number of goals scored.
SELECT matchid,mdate,COUNT(teamid)
FROM game JOIN goal ON matchid = id
WHERE (team1 = 'POL' OR team2 = 'POL')
GROUP BY matchid
Q12
For every match where 'GER' scored, show matchid, match date and the number of goals scored by 'GER'
SELECT matchid,mdate,COUNT(teamid)
FROM goal JOIN game ON id=matchid
WHERE teamid='GER'
GROUP BY matchid
Q13
List every match with the goals scored by each team as shown. This will use "CASE WHEN"
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 LEFT JOIN goal ON id=matchid
GROUP BY mdate,matchid,team1,team2
CASE WHEN
CASE WHEN条件表达式函数类似IF ELSE语句
CASE WHEN condition THEN result
[WHEN...THEN...]
ELSE result
END
外连接分为左连接和右连接
左连接
select staff.name,deptname from staff left join deptno onstaff.name=deptno.name;
包含左边表中所有的记录,右边表中没有匹配的记录显示为 NULL
右连接
select deptname,deptno.name from staff right join deptno ondeptno.name=staff.name;
包含右边表中所有的记录,左边表中没有匹配的记录显示为 NULL