JOIN Quiz
JOIN quiz
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 | |
1001 | RUS | Roman Pavlyuchenko | 82 | |
... |
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'
DEN | 9 June 2012 |
GER | 9 June 2012 |
DEN |
GER |
GRE | 12 June 2012 |
CZE | 12 June 2012 |
POL | 12 June 2012 |
RUS | 12 June 2012 |
GRE |
CZE |
POL |
RUS |
POL | 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.
Query-A
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'
Query-B
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'
Query-C
SELECT DISTINCT player, teamid
FROM game JOIN goal ON matchid = id
WHERE stadium = 'National Stadium, Warsaw' AND teamid != 'POL'
Query-D
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'
Query-E
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'
|type="()"} - Query-A + Query-B - Query-C - Query-D - Query-E
{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
2 |
2 |
1 |
2 |
Netherlands | 2 |
Poland | 2 |
Republic of Ireland | 1 |
Ukraine | 2 |
Netherlands |
Poland |
Republic of Ireland |
Ukraine |
Poland | 76 |
Republic of Ireland | 1 |
|type="()"} - Table-A + Table-B - Table-C - Table-D - Table-E
</quiz>