The JOIN operation - Revision history

Battlestar: added sorting instructions

2013-02-13T08:51:03Z

added sorting instructions

Bminaji: Changed "matchid date" to "matchid, date"

2013-01-15T19:01:57Z

Changed "matchid date" to "matchid, date"

Bminaji: Changed "For every match where 'GER' scored, show the number of goals scored by 'GER'" to "For every match where 'GER' scored, show the matchid, match date and the number of goals scored by 'GER'"

2013-01-15T19:00:25Z

Changed "For every match where 'GER' scored, show the number of goals scored by 'GER'" to "For every match where 'GER' scored, show the matchid, match date and the number of goals scored by 'GER'"

Bminaji: Changed the order of the columns asked for to match the answer.

2013-01-15T18:16:16Z

Changed the order of the columns asked for to match the answer.

JesseTG: Clarified a question

2012-12-29T22:20:20Z

Clarified a question

JesseTG: Added some `tags`


2012-12-29T19:32:13Z
Added some <code> tags

				
				
				
				
			
			← Older revision
			Revision as of 19:32, 29 December 2012
			
Line 114:
Line 114:
    
     <h2>JOIN and UEFA EURO 2012</h2>      <h2>JOIN and UEFA EURO 2012</h2>
−    <p>This tutorial introduces JOIN which allows you to use data from two or more tables. The tables contain all matches and goals from UEFA EURO 2012 Football Championship in Poland and Ukraine.</p> +    <p>This tutorial introduces <code>JOIN</code> which allows you to use data from two or more tables. The tables contain all matches and goals from UEFA EURO 2012 Football Championship in Poland and Ukraine.</p>
    
  <div class="progress_panel"><div>   <div class="progress_panel"><div>
Line 145:
Line 145:
  <div class='qu'>   <div class='qu'>
  From the previous query you can see that Lars Bender's goal was scored in game 1012.   From the previous query you can see that Lars Bender's goal was scored in game 1012.
− Notice that the column '''matchid''' in the '''goal''' table corresponds to the '''id''' column in the '''game''' table. + Notice that the column <code>matchid</code> in the <code>goal</code> table corresponds to the <code>id</code> column in the <code>game</code> table.
  <p class='imper'>Show id, stadium, team1, team2 for game 1012</p>   <p class='imper'>Show id, stadium, team1, team2 for game 1012</p>
    
Line 161:
Line 161:
    
  <div class='qu'>   <div class='qu'>
− You can combine the two steps into a single query with a join. You will get all the '''game''' details and all the '''goal''' details if you use + You can combine the two steps into a single query with a <code>JOIN</code>. You will get all the <code>game</code> details and all the <code>goal</code> details if you use
    SELECT *     SELECT *
      FROM game JOIN goal ON (id=matchid)       FROM game JOIN goal ON (id=matchid)
Line 179:
Line 179:
    
  <div class='qu'>   <div class='qu'>
− Use the same join as in the previous question. + Use the same <code>JOIN</code> as in the previous question.
  <p class='imper'>Show the team1, team2 and player for every goal scored by a player called Mario <code>player LIKE 'Mario%'</code></p>   <p class='imper'>Show the team1, team2 and player for every goal scored by a player called Mario <code>player LIKE 'Mario%'</code></p>
    
Line 193:
Line 193:
    
  <div class='qu'>   <div class='qu'>
− The table '''eteam''' gives details of every national team including the coach. You can JOIN '''goal''' to '''eteam''' using the phrase <code>goal JOIN eteam on teamid=id</code> + The table <code>eteam</code> gives details of every national team including the coach. You can <code>JOIN</code> <code>goal</code> to <code>eteam</code> using the phrase <code>goal JOIN eteam on teamid=id</code>
− <p class='imper'>Show player, teamid, coach, gtime for all goals scored in the first 10 minutes <code>gtime&lt;=10</code></p> + <p class='imper'>Show <code>player</code>, <code>teamid</code>, <code>coach</code>, <code>gtime</code> for all goals scored in the first 10 minutes <code>gtime&lt;=10</code></p>
    
  <source lang='sql' class='def'>   <source lang='sql' class='def'>
Line 210:
Line 210:
    
  <div class='qu'>   <div class='qu'>
− To JOIN '''game''' with '''eteam''' you could use either<br/> <code>game JOIN eteam ON (team1=eteam.id)</code> or <code>game JOIN eteam ON (team2=eteam.id)</code> + To <code>JOIN</code> <code>game</code> with <code>eteam</code> you could use either<br/> <code>game JOIN eteam ON (team1=eteam.id)</code> or <code>game JOIN eteam ON (team2=eteam.id)</code>
    
− Notice that because '''id''' is a column name in both '''game''' and '''eteam''' you must specify '''eteam.id''' instead of just '''id''' + Notice that because <code>id</code> is a column name in both <code>game</code> and <code>eteam</code> you must specify <code>eteam.id</code> instead of just <code>id</code>
  <p class='imper'>List the dates of the matches in which 'Fernando Santos' was the team1 coach.</p>   <p class='imper'>List the dates of the matches in which 'Fernando Santos' was the team1 coach.</p>



40053530: Q2: "for game 1012" in question, "WHERE id=1023"
2012-10-01T12:31:12Z
Q2: "for game 1012" in question, "WHERE id=1023"

				
				
				
				
			
			← Older revision
			Revision as of 12:31, 1 October 2012
			
Line 157:
Line 157:
  SELECT id,stadium,team1,team2   SELECT id,stadium,team1,team2
    FROM game     FROM game
− WHERE id=1023</source> + WHERE id=1012</source>
  </div>   </div>
    



Andr3w at 14:55, 29 September 2012
2012-09-29T14:55:41Z


				
				
				
				
			
			← Older revision
			Revision as of 14:55, 29 September 2012
			
Line 341:
Line 341:
  <tr><td colspan=5>...</td></tr>   <tr><td colspan=5>...</td></tr>
  </table>   </table>
− Notice in the query given every goal is listed. If it was a team1 goal then a 1 appears in score1, otherwise there is a 0. + Notice in the query given every goal is listed. If it was a team1 goal then a 1 appears in score1, otherwise there is a 0. You could SUM this column to get a count of the goals scored by team1.
  <source lang='sql' class='def'>   <source lang='sql' class='def'>
  SELECT mdate,   SELECT mdate,



Andr3w at 14:52, 29 September 2012
2012-09-29T14:52:45Z


				
				
				
				
			
			← Older revision
			Revision as of 14:52, 29 September 2012
			
Line 343:
Line 343:
  Notice in the query given every goal is listed. If it was a team1 goal then a 1 appears in score1, otherwise there is a 0.   Notice in the query given every goal is listed. If it was a team1 goal then a 1 appears in score1, otherwise there is a 0.
  <source lang='sql' class='def'>   <source lang='sql' class='def'>
− SELECT matchid, + SELECT mdate,
     team1,      team1,
−    CASE(WHEN teamid=team1) THEN 1 ELSE 0 END score1, +    CASE WHEN teamid=team1 THEN 1 ELSE 0 END score1
−   team2 +    FROM game JOIN goal ON matchid = id 
−   CASE(WHEN teamid=team2) THEN 1 ELSE 0 END score2, + 
−    FROM goal + 
−  WHERE (teamid='GER') + 
  </source>   </source>
    



Andr3w at 14:49, 29 September 2012
2012-09-29T14:49:22Z


				
				
				
				
			
			← Older revision
			Revision as of 14:49, 29 September 2012
			
Line 320:
Line 320:
    
  <div class='imper'>For every match where 'GER' scored, show the number of goals scored by 'GER'</div>   <div class='imper'>For every match where 'GER' scored, show the number of goals scored by 'GER'</div>
  + <source lang='sql' class='def'>
  + </source>
  <source lang='sql' class='ans'>   <source lang='sql' class='ans'>
−   + SELECT matchid,mdate,COUNT(teamid)
  +   FROM game JOIN goal ON matchid = id 
  +  WHERE (teamid='GER')
  + GROUP BY matchid,mdate
  </source>   </source>
  + </div>
  + <div>
    
  + <div class='qu'>
  + 
  + <div class='imper'>List every match with the goals scored by each team as shown.</div>
  + <table class="sqlmine">
  + <tr><th>mdate</th><th>team1</th><th>score1</th><th>team2</th><th>score2</th></tr>
  + <tr><td>1 July 2012</td><td>ESP</td><td class="r">4</td><td>ITA </td><td class="r">0</td></tr>
  + <tr><td>10 June 2012</td><td>ESP</td><td class="r">1</td><td>ITA</td><td class="r">1</td></tr>
  + <tr><td>10 June 2012</td><td>IRL</td><td class="r">1</td><td>CRO</td><td class="r">3</td></tr>
  + <tr><td colspan=5>...</td></tr>
  + </table>
  + Notice in the query given every goal is listed. If it was a team1 goal then a 1 appears in score1, otherwise there is a 0.
  <source lang='sql' class='def'>   <source lang='sql' class='def'>
− SELECT matchid,mdate,COUNT(teamid) + SELECT matchid,
−    FROM game JOIN goal ON matchid = id +   team1,
  +   CASE(WHEN teamid=team1) THEN 1 ELSE 0 END score1,
  +   team2
  +   CASE(WHEN teamid=team2) THEN 1 ELSE 0 END score2,
  +    FROM goal
    WHERE (teamid='GER')     WHERE (teamid='GER')
− GROUP BY matchid,mdate</source> + </source>
  +  
  + <source lang='sql' class='ans'>
  + SELECT mdate,
  +   team1,
  +   SUM(CASE WHEN teamid=team1 THEN 1 ELSE 0 END) score1,
  +   team2,
  +   SUM(CASE WHEN teamid=team2 THEN 1 ELSE 0 END) score2
  +   FROM game JOIN goal ON matchid = id 
  + GROUP BY mdate,matchid,team1,team2
  + </source>
  +  
  </div>   </div>
  <div>   <div>

@@ Line 302: / Line 302: @@
 <div class='qu'>
-<div class='imper'>For every match involving 'POL', show the matchid date and the number of goals scored.</div>
+<div class='imper'>For every match involving 'POL', show the matchid, date and the number of goals scored.</div>
 <source lang='sql' class='def'>
 SELECT matchid,mdate, team1, team2,teamid

@@ Line 213: / Line 213: @@
 Notice that because <code>id</code> is a column name in both <code>game</code> and <code>eteam</code> you must specify <code>eteam.id</code> instead of just <code>id</code>
-<p class='imper'>List the name of the team and the dates of the matches in which 'Fernando Santos' was the team1 coach.</p>
+<p class='imper'>List the the dates of the matches and the name of the team in which 'Fernando Santos' was the team1 coach.</p>
 <source lang='sql' class='def'>

The JOIN operation - Revision history

Battlestar: added sorting instructions

Bminaji: Changed "matchid date" to "matchid, date"

Bminaji: Changed "For every match where 'GER' scored, show the number of goals scored by 'GER'" to "For every match where 'GER' scored, show the matchid, match date and the number of goals scored by 'GER'"

Bminaji: Changed the order of the columns asked for to match the answer.

JesseTG: Clarified a question

JesseTG: Added some tags

40053530: Q2: "for game 1012" in question, "WHERE id=1023"

Andr3w at 14:55, 29 September 2012

Andr3w at 14:52, 29 September 2012

Andr3w at 14:49, 29 September 2012

JesseTG: Added some `tags`