SQL Q&A4 The JOIN operation

type

status

date

slug

summary

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 player, teamid, coach, gtime 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

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