<p class='imper'>Show '''matchid''' and '''player''' name for all goals scored by English players.

+

<p class='imper'>Show '''matchid''' and '''player''' name for all goals scored by Germany.

−

<code>teamid = 'ENG'</code></p>

+

<code>teamid = 'GER'</code></p>

<source lang='sql' class='def'>

<source lang='sql' class='def'>

SELECT * FROM goal

SELECT * FROM goal

−

WHERE player LIKE '%Rooney'

+

WHERE player LIKE '%Bender'

</source>

</source>

Line 139:

Line 139:

SELECT matchid, player

SELECT matchid, player

FROM goal

FROM goal

−

WHERE teamid LIKE 'ENG'

+

WHERE teamid LIKE 'GER'

</source>

</source>

</div>

</div>

<div class='qu'>

<div class='qu'>

−

From the previous query you can see that Wayne Rooney's goal was scored in game 1023.

+

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.

−

<p class='imper'>Show id, stadium, team1, team2 for game 1023</p>

+

<p class='imper'>Show id, stadium, team1, team2 for game 1012</p>

<source lang='sql' class='def'>

<source lang='sql' class='def'>

Line 163:

Line 163:

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 join. You will get all the '''game''' details and all the '''goal''' details if you use

SELECT * FROM game JOIN goal ON (game.id=goal.matchid)

SELECT * FROM game JOIN goal ON (game.id=goal.matchid)

−

<p class='imper'>Show the player and the stadium for every England goal. <code>teamid</code></p>

+

<p class='imper'>Show the player and the stadium for every German goal. <code>teamid='GER'</code></p>

<source lang='sql' class='def'>

<source lang='sql' class='def'>

−

SELECT coach from eteam

+

SELECT *

−

WHERE teamname = 'Germany'

+

FROM game JOIN goal ON (game.id=goal.matchid)

−

OR teamname = 'England'

+

</source>

</source>

<source lang='sql' class='ans'>

<source lang='sql' class='ans'>

−

SELECT teamname, coach from eteam

+

SELECT *

−

WHERE coach LIKE 'M%'

+

FROM game JOIN goal ON (game.id=goal.matchid)

+

WHERE teamid='GER'

</source>

</source>

</div>

</div>

Revision as of 23:44, 28 September 2012

game

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

...

goal

matchid

teamid

player

gtime

1001

POL

Robert Lewandowski

17

1001

GRE

Dimitris Salpingidis

51

1002

RUS

Alan Dzagoev

15

1001

RUS

Roman Pavlyuchenko

82

...

eteam

id

teamname

coach

POL

Poland

Franciszek Smuda

RUS

Russia

Dick Advocaat

CZE

Czech Republic

Michal Bilek

GRE

Greece

Fernando Santos

...

JOIN and UEFA EURO 2012

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.

Summary

The first example shows the goal scored by 'Wayne Rooney'.

Show matchid and player name for all goals scored by Germany.
teamid = 'GER'

SELECT*FROM goal
WHERE player LIKE'%Bender'

SELECT matchid, player
FROM goal
WHERE teamid LIKE'GER'

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.

Show id, stadium, team1, team2 for game 1012

SELECT id,stadium,team1,team2
FROM game
WHERE stadium LIKE'%Warsaw%'

SELECT id,stadium,team1,team2
FROM game
WHERE id=1023

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

SELECT * FROM game JOIN goal ON (game.id=goal.matchid)

Show the player and the stadium for every German goal. teamid='GER'

SELECT*FROM game JOIN goal ON(game.id=goal.matchid)

SELECT*FROM game JOIN goal ON(game.id=goal.matchid)WHERE teamid='GER'

The following query shows coaches of teams that played on 9 June 2012. JOIN has been used to make relation between two tables. Because of this we can select coach from eteam table and mdate from game table.

Show stadium and mdate of matches played by the team having 'Vicente del Bosque' as the coach.