Difference between revisions of "JOIN Quiz"

From SQLZOO
Jump to: navigation, search
Line 1: Line 1:
 
JOIN quiz
 
JOIN quiz
 
<quiz shuffle=none display=simple>
 
<quiz shuffle=none display=simple>
{SELECT the code which shows the players and their teams who have scored goals against Poland before.
+
{Select the code which shows '''player''' and '''mdate''' for players which scored during 9th June 2012 to 11th June 2012.
 
|type="()"}
 
|type="()"}
- SELECT country.name, color FROM ttms JOIN country ON (ttms.country=country.id) WHERE color = 'gold'
+
- SELECT player, mdate FROM goal JOIN eteam ON (teamname=id) WHERE mdate IN ('9 June 2012','10 June 2012','11 June 2012')
- SELECT who, country.name, color FROM ttms JOIN country (ttms.country=country.id) WHERE color = 'gold'
+
- SELECT player, mdate FROM goal JOIN game ON (matchid=id) WHERE mdate IN ('9 July 1912','10 July 1912','11 July 1912')
+ SELECT who, country.name, color FROM ttms JOIN country ON (ttms.country=country.id) WHERE color = 'gold'
+
+ SELECT player, mdate FROM goal JOIN game ON (matchid=id) WHERE mdate IN ('9 June 2012','10 June 2012','11 June 2012')
- SELECT who, country.name, color FROM ttms JOIN country ON (ttms.country=country.id) WHERE color = 'silver'
+
- SELECT player, mdate FROM goal JOIN game ON (matchid=id) WHERE mdate LIKE ('9 June 2012','10 June 2012','11 June 2012')
- SELECT who, country.name, color FROM ttms JOIN country WITH (ttms.country=country.id) WHERE color = 'gold'
+
- SELECT stadium, mdate FROM goal JOIN game ON (matchid=id) WHERE mdate IN ('9 June 2012','10 June 2012','11 June 2012')
  
 
{Which of the following shows the chinese woman's singles winners in the Seoul and Barcelona games?
 
{Which of the following shows the chinese woman's singles winners in the Seoul and Barcelona games?
Line 16: Line 16:
 
+ SELECT who, city FROM ttws JOIN games ON (ttws.games=games.yr) WHERE city IN ('Barcelona', 'Seoul') AND ttws.country = 'CHN'
 
+ SELECT who, city FROM ttws JOIN games ON (ttws.games=games.yr) WHERE city IN ('Barcelona', 'Seoul') AND ttws.country = 'CHN'
 
- SELECT who, city FROM ttws JOIN games ON (ttws.games=games.yr) WHERE city IN ('Beijing', 'Seoul') AND ttws.country = 'CHN'
 
- SELECT who, city FROM ttws JOIN games ON (ttws.games=games.yr) WHERE city IN ('Beijing', 'Seoul') AND ttws.country = 'CHN'
 
  
 
{Which of these shows the korean women's singles winners in order of medal achieved(Ascending order)?
 
{Which of these shows the korean women's singles winners in order of medal achieved(Ascending order)?

Revision as of 09:01, 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')

{Which of the following shows the chinese woman's singles winners in the Seoul and Barcelona games? |type="()"} - SELECT city FROM ttws JOIN games ON (ttws.games=games.yr) WHERE city IN ('Barcelona', 'Seoul') AND ttws.country = 'CHN' - SELECT who, city FROM ttws JOIN games IN (ttws.games=games.yr) WHERE city IN ('Barcelona', 'Seoul') AND ttws.country = 'CHN' - SELECT who, city FROM ttws JOIN games ON (ttws.games=games.yr) WHERE city IN ('Barcelona', 'Atlanta') AND ttws.country = 'CHN' + SELECT who, city FROM ttws JOIN games ON (ttws.games=games.yr) WHERE city IN ('Barcelona', 'Seoul') AND ttws.country = 'CHN' - SELECT who, city FROM ttws JOIN games ON (ttws.games=games.yr) WHERE city IN ('Beijing', 'Seoul') AND ttws.country = 'CHN'

{Which of these shows the korean women's singles winners in order of medal achieved(Ascending order)? |type="()"} - SELECT ttws.country, city, color FROM ttws JOIN games ON (ttws.games=games.yr) WHERE ttws.country = 'CHN' ORDER BY color ASC + SELECT who, ttws.country, city, color FROM ttws JOIN games ON (ttws.games=games.yr) WHERE ttws.country = 'CHN' ORDER BY color ASC - SELECT who, ttws.country, city, color FROM ttws JOIN games ON (ttws.games=games.yr) WHERE ttws.country = 'CHN' ORDER BY country ASC - SELECT who, ttws.country, city, color FROM ttws JOIN games ON (ttws.games=games.yr) WHERE ttws.country = 'CHN' ORDER BY color DESC - SELECT who, ttws.country, city, color FROM ttws JOIN games ON (ttws.games=games.yr) WHERE who = 'CHN' ORDER BY color ASC

{Which of these shows the gold medalists for the men's doubles from 1992 to 2000? |type="()"} + SELECT name, color, games FROM ttmd JOIN team ON team=team.id WHERE color='gold' AND games BETWEEN 1992 and 2000 - SELECT name, color, games FROM ttmd JOIN team ON team=team.id WHERE color='gold' AND games BETWEEN 2092 and 3000 - SELECT name, color, games FROM ttmd JOIN team ON team=team.id WHERE color='gold' AND WHERE games BETWEEN 1992 and 2000 - SELECT name, color, games FROM ttmd JOIN team ON team=team.id WHERE color='silver' AND games BETWEEN 1992 and 2000 - SELECT name, color, games FROM ttmd JOIN team WITH team=team.id WHERE color='gold' AND games BETWEEN 1992 and 2000

{Which of the following shows the men's doubles chinese gold medalists who have names beginning with w? |type="()"} - SELECT FROM ttmd JOIN team ON team=team.id WHERE country = 'CHN' AND color = 'gold' AND name LIKE 'W%' - SELECT name, color, country FROM ttmd JOIN team ON team=team.id WHERE country = 'CHN' AND color = 'gold' AND name IN 'W%' - SELECT name, color, country FROM ttmd JOIN team ON team=team.id WHERE country = 'CHN' AND color = 'gold' AND name LIKE '%W' + SELECT name, color, country FROM ttmd JOIN team ON team=team.id WHERE country = 'CHN' AND color = 'gold' AND name LIKE 'W%' - SELECT name, color, country FROM ttmd JOIN team ON team=team.id WHERE country = 'CHN' AND color = 'silver' AND name LIKE 'W%' </quiz>