JOIN quiz
game
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 |
... |
goal
matchid |
teamid |
player |
gtime |
1001 |
POL |
Robert Lewandowski |
17 |
1001 |
GRE |
Dimitris Salpingidis |
51 |
1002 |
RUS |
Alan Dzagoev |
15 |
1001 |
RUS |
Roman Pavlyuchenko |
82 |
... |
eteam
id |
teamname |
coach |
POL |
Poland |
Franciszek Smuda |
RUS |
Russia |
Dick Advocaat |
CZE |
Czech Republic |
Michal Bilek |
GRE |
Greece |
Fernando Santos |
... |
<quiz shuffle=none display=simple>
{You want to find the stadium where player 'Dimitris Salpingidis' scored. Select the JOIN condition to use:
|type="()"}
- eteam JOIN game ON (id=team1)
- eteam JOIN game ON (id=team2)
- eteam JOIN goal ON (teamid=id)
+ game JOIN goal ON (id=matchid)
- game JOIN goal ON (team1=teamid OR team2=teamid)
{You JOIN the tables goal and eteam in an SQL statement. Indicate the list of column names that may be used in the SELECT line:
|type="()"}
- gtime, mdate, stadium, match.id
- mdate, stadium, id
+ matchid, teamid, player, gtime, id, teamname, coach
- matchid, teamid, player, gtime, mdate, stadium, team1
- stadium, team1, team2
{Select the code which shows players, their team and the amount of goals they scored against Greece(GRE).
|type="()"}
+ SELECT player, COUNT(*), teamid FROM game JOIN goal ON matchid = id WHERE (team1 = "GRE" OR team2 = "GRE") AND teamid != 'GRE' GROUP BY player
- SELECT player, COUNT(*), teamid FROM game JOIN goal ON matchid = id WHERE (team1 = "GRE") AND teamid != 'GRE' GROUP BY player
- SELECT player, COUNT(*), teamid FROM game JOIN goal ON matchid = id WHERE (team1 = "POL" OR team2 = "POL") AND teamid != 'POL' GROUP BY player
- SELECT player, COUNT(*), teamid FROM game JOIN goal WITH matchid = id WHERE (team1 = "GRE" OR team2 = "GRE") AND teamid != 'GRE' GROUP BY player
- SELECT player, teamid FROM game JOIN goal ON matchid = id WHERE (team1 = "GRE" OR team2 = "GRE") AND teamid != 'GRE' GROUP BY player
{Select the result that would be obtained from this code: SELECT teamid, mdate FROM goal JOIN game on (matchid=id) WHERE mdate = '9 June 2012'
Table-ADEN | 9 June 2012 |
GER | 9 June 2012 |
Table-CGRE | 12 June 2012 |
CZE | 12 June 2012 |
POL | 12 June 2012 |
RUS | 12 June 2012 |
Table-EPOL | 8 June 2012 |
GRE | 8 June 2012 |
RUS | 8 June 2012 |
CZE | 8 June 2012 |
|type="()"}
+ Table-A
- Table-B
- Table-C
- Table-D
- Table-E
{Select the code which would show the player and their team for those who have scored against Poland(POL) in National Stadium, Warsaw.
|type="()"}
- SELECT DISTINCT player, teamid FROM game JOIN goal ON matchid = id WHERE stadium = 'National Stadium, Warsaw' AND mdate IN (SELECT mdate FROM game WHERE team1 = 'GER' OR team2 = 'GER')AND teamid != 'GER'
+ SELECT DISTINCT player, teamid FROM game JOIN goal ON matchid = id WHERE stadium = 'National Stadium, Warsaw' AND mdate IN (SELECT mdate FROM game WHERE team1 = 'POL' OR team2 = 'POL')AND teamid != 'POL'
- SELECT DISTINCT player, teamid FROM game JOIN goal ON matchid = id WHERE stadium = 'National Stadium, Warsaw' AND teamid != 'POL'
- SELECT DISTINCT player, teamid FROM game JOIN goal ON matchid = id WHERE stadium = 'Stadion Miejski (Wroclaw)' AND mdate IN (SELECT mdate FROM game WHERE team1 = 'POL' OR team2 = 'POL')AND teamid != 'POL'
- SELECT DISTINCT stadium, mdate FROM game JOIN goal ON matchid = id WHERE stadium = 'National Stadium, Warsaw' AND mdate IN (SELECT mdate FROM game WHERE team1 = 'POL' OR team2 = 'POL')AND teamid != 'POL'
{Select the code which shows the player, their team and the time they scored, for players who have played in Stadion Miejski (Wroclaw) but not against Italy(ITA).
|type="()"}
- SELECT DISTINCT player, teamid, gtime FROM game JOIN goal ON matchid = id WHERE stadium = 'National Stadium, Warsaw' AND (team1 != 'ITA' AND team2 !='ITA')
- SELECT DISTINCT player, teamid, gtime FROM game JOIN goal ON matchid = id WHERE stadium = 'Stadion Miejski (Wroclaw)' AND (team1 != 'ESP' AND team2 !='ESP')
+ SELECT DISTINCT player, teamid, gtime FROM game JOIN goal ON matchid = id WHERE stadium = 'Stadion Miejski (Wroclaw)' AND (team1 != 'ITA' AND team2 !='ITA')
- SELECT DISTINCT teamid, gtime FROM game JOIN goal ON matchid = id WHERE stadium = 'Stadion Miejski (Wroclaw)' AND (team1 != 'ITA' AND team2 !='ITA')
- SELECT DISTINCT player, teamid, gtime FROM game JOIN goal ON matchid = id WHERE team1 != 'ITA' AND team2 !='ITA'
{Select the result that would be obtained from this code: SELECT teamname, COUNT(*) FROM eteam JOIN goal ON teamid = id GROUP BY teamname HAVING COUNT(*) < 3
Table-BNetherlands | 2 |
Poland | 2 |
Republic of Ireland | 1 |
Ukraine | 2 |
Table-CNetherlands |
Poland |
Republic of Ireland |
Ukraine |
Table-ERepublic of Ireland | 1 |
|type="()"}
- Table-A
+ Table-B
- Table-C
- Table-D
- Table-E
</quiz>