Lazy loaded image
Technology
Lazy loaded imageSQL Q&A4 The JOIN operation
Words 862Read Time 3 min
May 8, 2020
Jun 13, 2025
type
status
date
slug
summary
tags
category
icon
password
notion image
notion image
 

1.JOIN and UEFA EURO 2012

The first example shows the goal scored by a player with the last name 'Bender'. The * says to list all the columns in the table - a shorter way of saying matchid, teamid, player, gtime
Modify it to show the matchid and player name for all goals scored by Germany. To identify German players, check for: teamid = 'GER'
matchid
player
1008
Mario Gómez
1010
Mario Gómez
1010
Mario Gómez
1012
Lukas Podolski
1012
Lars Bender
1026
Philipp Lahm
1026
Sami Khedira
1026
Miroslav Klose
1026
Marco Reus
1030
Mesut Özil
 

2.

From the previous query you can see that Lars Bender's scored a goal in game 1012. Now we want to know what teams were playing in that match.
Notice in the that the column matchid in the goal table corresponds to the id column in the game table. We can look up information about game 1012 by finding that row in the game table.
Show id, stadium, team1, team2 for just game 1012
id
stadium
team1
team2
1012
Arena Lviv
DEN
GER

3.

You can combine the two steps into a single query with a JOIN.
The FROM clause says to merge data from the goal table with that from the game table. The ON says how to figure out which rows in game go with which rows in goal - the matchid from goal must match id from game. (If we wanted to be more clear/specific we could say
ON (game.id=goal.matchid)
The code below shows the player (from the goal) and stadium name (from the game table) for every goal scored.
Modify it to show the player, teamid, stadium and mdate for every German goal.
yr
subject
1921
physics

4.

Use the same JOIN as in the previous question.
Show the team1, team2 and player for every goal scored by a player called Mario player LIKE 'Mario%'
team1
team2
player
GER
POR
Mario Gómez
NED
GER
Mario Gómez
NED
GER
Mario Gómez
IRL
CRO
Mario Mandžukic
IRL
CRO
Mario Mandžukic
ITA
CRO
Mario Mandžukic
ITA
IRL
Mario Balotelli
GER
ITA
Mario Balotelli
GER
ITA
Mario Balotelli

5.

The table eteam gives details of every national team including the coach. You can JOIN goal to eteam using the phrase goal JOIN eteam on teamid=id
Show playerteamidcoachgtime for all goals scored in the first 10 minutes gtime<=10
player
teamid
coach
gtime
Petr Jirácek
CZE
Michal Bílek
3
Václav Pilar
CZE
Michal Bílek
6
Mario Mandžukic
CRO
Slaven Bilic
3
Fernando Torres
ESP
Vicente del Bosque
4

6.

To JOIN game with eteam you could use either
game JOIN eteam ON (team1=eteam.id) or game JOIN eteam ON (team2=eteam.id)
Notice that because id is a column name in both game and eteam you must specify eteam.id instead of just id
List the dates of the matches and the name of the team in which 'Fernando Santos' was the team1 coach.
 
mdate
teamname
12 June 2012
Greece
16 June 2012
Greece

7.

List the player for every goal scored in a game where the stadium was 'National Stadium, Warsaw'

player

Robert Lewandowski

Dimitris Salpingidis

Alan Dzagoev

Jakub Blaszczykowski

Giorgos Karagounis

Cristiano Ronaldo

Mario Balotelli

Mario Balotelli

Mesut Özil

8.Chemistry and Physics from different years

The example query shows all goals scored in the Germany-Greece quarterfinal.
Instead show the name of all players who scored a goal against Germany.
player

Robin van Persie

Michael Krohn-Dehli

Georgios Samaras

Dimitris Salpingidis

Mario Balotelli

9.

Show teamname and the total number of goals scored. COUNT and GROUP BY You should COUNT(*) in the SELECT line and GROUP BY teamname
teamname
total_goals
Croatia
4
Czech Republic
4
Denmark
4
England
5
France
3
Germany
10
Greece
5
Italy
6
Netherlands
2
Poland
2
Portugal
6
Republic of Ireland
1
Russia
5
Spain
12
Sweden
5
Ukraine
2

10.

Show the stadium and the number of goals scored in each stadium.
stadium
total_goals
Arena Lviv
9
Donbass Arena
7
Metalist Stadium
7
National Stadium, Warsaw
9
Olimpiyskiy National Sports Complex
14
PGE Arena Gdansk
13
Stadion Miejski (Poznan)
8
Stadion Miejski (Wroclaw)
9

11.

For every match involving 'POL', show the matchid, date and the number of goals scored.
 
matchid
mdate
goals
1001
8 June 2012
2
1004
12 June 2012
2
1005
16 June 2012
1

12.Apostrophe

For every match where 'GER' scored, show matchid, match date and the number of goals scored by 'GER'
matchid
mdate
goals
1008
9 June 2012
1
1010
13 June 2012
2
1012
17 June 2012
2
1026
22 June 2012
4
1030
28 June 2012
1

13.Knights of the realm

Knights in order
List the winners, year and subject where the winner starts with Sir. Show the the most recent first, then by name order.
winner
yr
subject
Sir Peter Ratcliffe
2019
medicine
Sir Gregory Winter
2018
chemistry
Sir Fraser Stoddart
2016
chemistry
Sir John Gurdon
2012
medicine
Sir Martin Evans
2007
medicine
Sir Peter Mansfield
2003
medicine
Sir Paul Nurse
2001
medicine
Sir Harold Kroto
1996
chemistry
Sir James Black
1988
medicine
Sir Nevill Mott
1977
physics
Sir Bernard Katz
1970
medicine
Sir John Eccles
1963
medicine
Sir Frank Macfarlane Burnet
1960
medicine
Sir Cyril Hinshelwood
1956
chemistry
Sir Robert Robinson
1947
chemistry
Sir Alexander Fleming
1945
medicine
Sir Howard Florey
1945
medicine
Sir Henry Dale
1936
medicine
Sir Norman Angell
1933
peace
Sir Charles Sherrington
1932
medicine
Sir Chandrasekhara Venkata Raman
1930
physics
Sir Frederick Hopkins
1929
medicine
Sir Austen Chamberlain
1925
peace
Sir William Ramsay
1904
chemistry
Pattern
Meaning
Example Match
'John%'
Starts with John
John, Johnny, John Smith
'%John'
Ends with John
Sir John, Dr. John, is John
'%John%'
Contains John anywhere
Johnny, Sir John Smith, Dr. Johnny
'J%n'
Starts with J, ends with n, anything in between
John, Jason, Jordan

14.Chemistry and Physics last

The expression subject IN ('chemistry','physics') can be used as a value - it will be 0 or 1.
Show the 1984 winners and subject ordered by subject and winner name; but list chemistry and physics last.
Correct answer
winner
subject
Jaroslav Seifert
literature
César Milstein
medicine
GeorgesF. Köhler
medicine
Niels Jerne
medicine
Desmond Tutu
peace
Bruce Merrifield
chemistry
Carlo Rubbia
physics
Simon van der Meer
physics
SQL Keyword
Equivalent in Programming
CASE
Starts the if structure
WHEN
The if or elif condition
THEN
The result or block to return/execute
ELSE
The default case when no condition is met
END
Closes the CASE block
上一篇
SQL Q&A4 SUM and COUNT
下一篇
Weighted Euclidean Distance