Difference between revisions of "JOIN Quiz"

From SQLZOO
Jump to: navigation, search
Line 33: Line 33:
 
- 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 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'
  
{Which of the following shows the men's doubles chinese gold medalists who have names beginning with w?
+
{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="()"}
 
|type="()"}
- SELECT FROM ttmd JOIN team ON team=team.id WHERE country = 'CHN' AND color = 'gold' AND name LIKE 'W%'
+
- SELECT DISTINCT player, teamid, gtime FROM game JOIN goal ON matchid = id WHERE stadium = 'National Stadium, Warsaw' AND (team1 != 'ITA' AND team2 !='ITA')
- SELECT name, color, country FROM ttmd JOIN team ON team=team.id WHERE country = 'CHN' AND color = 'gold' AND name IN 'W%'
+
- SELECT DISTINCT player, teamid, gtime FROM game JOIN goal ON matchid = id WHERE stadium = 'Stadion Miejski (Wroclaw)' AND (team1 != 'ESP' AND team2 !='ESP')
- SELECT name, color, country FROM ttmd JOIN team ON team=team.id WHERE country = 'CHN' AND color = 'gold' AND name LIKE '%W'
+
+ SELECT DISTINCT player, teamid, gtime FROM game JOIN goal ON matchid = id WHERE stadium = 'Stadion Miejski (Wroclaw)' AND (team1 != 'ITA' AND team2 !='ITA')
+ SELECT name, color, country FROM ttmd JOIN team ON team=team.id WHERE country = 'CHN' AND color = 'gold' AND name LIKE 'W%'
+
- SELECT DISTINCT teamid, gtime FROM game JOIN goal ON matchid = id WHERE stadium = 'Stadion Miejski (Wroclaw)' AND (team1 != 'ITA' AND team2 !='ITA')
- SELECT name, color, country FROM ttmd JOIN team ON team=team.id WHERE country = 'CHN' AND color = 'silver' AND name LIKE 'W%'
+
- SELECT DISTINCT player, teamid, gtime FROM game JOIN goal ON matchid = id WHERE team1 != 'ITA' AND team2 !='ITA'
 +
 
 
</quiz>
 
</quiz>
  
 
[[Category:Quizzes]]
 
[[Category:Quizzes]]

Revision as of 09:56, 3 August 2012

JOIN quiz <quiz shuffle=none display=simple> {Select the code which shows player and mdate for players which scored during 9th June 2012 to 11th June 2012. |type="()"} - SELECT player, mdate FROM goal JOIN eteam ON (teamname=id) WHERE mdate IN ('9 June 2012','10 June 2012','11 June 2012') - SELECT player, mdate FROM goal JOIN game ON (matchid=id) WHERE mdate IN ('9 July 1912','10 July 1912','11 July 1912') + SELECT player, mdate FROM goal JOIN game ON (matchid=id) WHERE mdate IN ('9 June 2012','10 June 2012','11 June 2012') - SELECT player, mdate FROM goal JOIN game ON (matchid=id) WHERE mdate LIKE ('9 June 2012','10 June 2012','11 June 2012') - SELECT stadium, mdate FROM goal JOIN game ON (matchid=id) WHERE mdate IN ('9 June 2012','10 June 2012','11 June 2012')

{Select the code which shows player, coach and COUNT(*) for players who scored in the first half of a match (0 - 45 minutes). |type="()"} - SELECT player, coach, COUNT(*) FROM goal JOIN eteam ON (id=teamid) WHERE gtime > 0 AND gtime < 45 + SELECT player, coach, COUNT(*) FROM goal JOIN eteam ON (id=teamid) WHERE gtime > 0 AND gtime < 45 GROUP BY player - SELECT player, coach, COUNT(*) FROM goal JOIN eteam ON (id=teamid) WHERE gtime < 0 AND gtime > 45 GROUP BY player - SELECT player, coach, COUNT(*) FROM goal JOIN eteam ON (id=teamid) WHERE gtime > 0 AND gtime < 90 GROUP BY player - SELECT player, stadium, COUNT(*) FROM goal JOIN game ON (id=matchid) WHERE gtime > 0 AND gtime < 45 GROUP BY player

{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 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'

</quiz>