JOIN Quiz - Revision history

Andr3w at 16:38, 29 September 2012

2012-09-29T16:38:30Z

Andr3w at 16:37, 29 September 2012

2012-09-29T16:37:01Z

Andr3w at 16:35, 29 September 2012

2012-09-29T16:35:20Z

Connor at 11:08, 7 August 2012

2012-08-07T11:08:37Z

Connor at 11:58, 3 August 2012

2012-08-03T11:58:53Z

Connor at 09:56, 3 August 2012

2012-08-03T09:56:18Z

Connor at 09:42, 3 August 2012

2012-08-03T09:42:22Z

Connor at 09:17, 3 August 2012

2012-08-03T09:17:08Z

Connor at 09:01, 3 August 2012

2012-08-03T09:01:37Z

Connor at 13:32, 1 August 2012

2012-08-01T13:32:33Z

@@ Line 125: / Line 125: @@
 - gtime, mdate, stadium, match.id
 - mdate, stadium, id
 - 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).

@@ Line 125: / Line 125: @@
 - gtime, mdate, stadium, match.id
 - mdate, stadium, id
 - 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).

@@ Line 113: / Line 113: @@
 </div>
 <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.
+{You want to find the stadium where player 'Dimitris Salpingidis' scored. Select the JOIN condition to use:
 |type="()"}
-- SELECT player, mdate FROM goal JOIN eteam ON (teamname=id) WHERE mdate IN ('9 June 2012','10 June 2012','11 June 2012')
+- eteam JOIN game ON (id=team1)
-- SELECT player, mdate FROM goal JOIN game ON (matchid=id) WHERE mdate IN ('9 July 1912','10 July 1912','11 July 1912')
+- eteam JOIN game ON (id=team2)
-+ SELECT player, mdate FROM goal JOIN game ON (matchid=id) WHERE mdate IN ('9 June 2012','10 June 2012','11 June 2012')
+- eteam JOIN goal ON (teamid=id)
-- SELECT player, mdate FROM goal JOIN game ON (matchid=id) WHERE mdate LIKE ('9 June 2012','10 June 2012','11 June 2012')
++ game  JOIN goal ON (id=matchid)
-- SELECT stadium, mdate FROM goal JOIN game ON (matchid=id) WHERE mdate IN ('9 June 2012','10 June 2012','11 June 2012')
+- game  JOIN goal ON (team1=teamid OR team2=teamid)
-{Select the code which shows '''player''', '''coach''' and '''COUNT(*)''' for players who scored in the first half of a match (0 - 45 minutes).
+{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="()"}
-- SELECT player, coach, COUNT(*) FROM goal JOIN eteam ON (id=teamid) WHERE gtime > 0 AND gtime < 45
+- gtime, mdate, stadium, match.id
-+ SELECT player, coach, COUNT(*) FROM goal JOIN eteam ON (id=teamid) WHERE gtime > 0 AND gtime < 45 GROUP BY player
+- mdate, stadium, id
-- SELECT player, coach, COUNT(*) FROM goal JOIN eteam ON (id=teamid) WHERE gtime < 0 AND gtime > 45 GROUP BY player
++ matchid, teamid, player, gtime, id, teamname, coach
-- SELECT player, coach, COUNT(*) FROM goal JOIN eteam ON (id=teamid) WHERE gtime > 0 AND gtime < 90 GROUP BY player
+- matchid, teamid, player, gtime, mdate, stadium, team1
-- SELECT player, stadium, COUNT(*) FROM goal JOIN game ON (id=matchid) WHERE gtime > 0 AND gtime < 45 GROUP BY player
+- stadium, team1, team2
 {Select the code which shows players, their team and the amount of goals they scored against Greece(GRE).

@@ Line 136: / Line 136: @@
 - 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.
@@ Line 152: / Line 165: @@
 - 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>
 [[Category:Quizzes]]

@@ Line 1: / Line 1: @@
 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.

@@ 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'
-{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="()"}
-- 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>
 [[Category:Quizzes]]

@@ Line 17: / Line 17: @@
 - SELECT player, stadium, COUNT(*) FROM goal JOIN game ON (id=matchid) WHERE gtime > 0 AND gtime < 45 GROUP BY player
-{Which of these shows the korean women's singles winners in order of medal achieved(Ascending order)?
+{Select the code which shows players, their team and the amount of goals they scored against Greece(GRE).
 |type="()"}
-- SELECT ttws.country, city, color FROM ttws JOIN games ON (ttws.games=games.yr) WHERE ttws.country = 'CHN' ORDER BY color ASC
++ SELECT player, COUNT(*), teamid FROM game JOIN goal ON matchid = id WHERE (team1 = "GRE" OR team2 = "GRE") AND teamid != 'GRE' GROUP BY player
-+ SELECT who, ttws.country, city, color FROM ttws JOIN games ON (ttws.games=games.yr) WHERE ttws.country = 'CHN' ORDER BY color ASC
+- SELECT player, COUNT(*), teamid FROM game JOIN goal ON matchid = id WHERE (team1 = "GRE") AND teamid != 'GRE' GROUP BY player
-- SELECT who, ttws.country, city, color FROM ttws JOIN games ON (ttws.games=games.yr) WHERE ttws.country = 'CHN' ORDER BY country ASC
+- SELECT player, COUNT(*), teamid FROM game JOIN goal ON matchid = id WHERE (team1 = "POL" OR team2 = "POL") AND teamid != 'POL' GROUP BY player
-- SELECT who, ttws.country, city, color FROM ttws JOIN games ON (ttws.games=games.yr) WHERE ttws.country = 'CHN' ORDER BY color DESC
+- SELECT player, COUNT(*), teamid FROM game JOIN goal WITH matchid = id WHERE (team1 = "GRE" OR team2 = "GRE") AND teamid != 'GRE' GROUP BY player
-- SELECT who, ttws.country, city, color FROM ttws JOIN games ON (ttws.games=games.yr) WHERE who = 'CHN' ORDER BY color ASC
+- SELECT player, teamid FROM game JOIN goal ON matchid = id WHERE (team1 = "GRE" OR team2 = "GRE") AND teamid != 'GRE' GROUP BY player
-{Which of these shows the gold medalists for the men's doubles from 1992 to 2000?
+{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 name, color, games FROM ttmd JOIN team ON team=team.id WHERE color='gold' AND games BETWEEN 1992 and 2000
+- 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 name, color, games FROM ttmd JOIN team ON team=team.id WHERE color='gold' AND games BETWEEN 2092 and 3000
++ 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 name, color, games FROM ttmd JOIN team ON team=team.id WHERE color='gold' AND WHERE games BETWEEN 1992 and 2000
+- SELECT DISTINCT player, teamid FROM game JOIN goal ON matchid = id WHERE stadium = 'National Stadium, Warsaw' AND teamid != 'POL'
-- SELECT name, color, games FROM ttmd JOIN team ON team=team.id WHERE color='silver' AND games BETWEEN 1992 and 2000
+- 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 name, color, games FROM ttmd JOIN team WITH team=team.id WHERE color='gold' AND games BETWEEN 1992 and 2000
+- 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?