SQL Exercise: Find the position of the player who scored an own goal
58. From the following table, write a SQL query to find out which players scored against his own team by accident. Return player name, jersey number, country name, age, position to play, and playing club.
Sample table: goal_details
goal_id | match_no | player_id | team_id | goal_time | goal_type | play_stage | goal_schedule | goal_half
---------+----------+-----------+---------+-----------+-----------+------------+---------------+-----------
1 | 1 | 160159 | 1207 | 57 | N | G | NT | 2
2 | 1 | 160368 | 1216 | 65 | P | G | NT | 2
3 | 1 | 160154 | 1207 | 89 | N | G | NT | 2
4 | 2 | 160470 | 1221 | 5 | N | G | NT | 1
5 | 3 | 160547 | 1224 | 10 | N | G | NT | 1
6 | 3 | 160403 | 1218 | 61 | N | G | NT | 2
7 | 3 | 160550 | 1224 | 81 | N | G | NT | 2
8 | 4 | 160128 | 1206 | 73 | N | G | NT | 2
9 | 4 | 160373 | 1217 | 93 | N | G | ST | 2
.........
108 | 51 | 160319 | 1214 | 109 | N | F | ET | 2
Sample table: player_mast
player_id | team_id | jersey_no | player_name | posi_to_play | dt_of_bir | age | playing_club
-----------+---------+-----------+-------------------------+--------------+------------+-----+---------------------
160001 | 1201 | 1 | Etrit Berisha | GK | 1989-03-10 | 27 | Lazio
160008 | 1201 | 2 | Andi Lila | DF | 1986-02-12 | 30 | Giannina
160016 | 1201 | 3 | Ermir Lenjani | MF | 1989-08-05 | 26 | Nantes
160007 | 1201 | 4 | Elseid Hysaj | DF | 1994-02-20 | 22 | Napoli
160013 | 1201 | 5 | Lorik Cana | MF | 1983-07-27 | 32 | Nantes
160010 | 1201 | 6 | Frederic Veseli | DF | 1992-11-20 | 23 | Lugano
160004 | 1201 | 7 | Ansi Agolli | DF | 1982-10-11 | 33 | Qarabag
160012 | 1201 | 8 | Migjen Basha | MF | 1987-01-05 | 29 | Como
160017 | 1201 | 9 | Ledian Memushaj | MF | 1986-12-17 | 29 | Pescara
.........
160548 | 1224 | 23 | Simon Church | FD | 1988-12-10 | 27 | MK Dons
Sample table: soccer_country
country_id | country_abbr | country_name
------------+--------------+---------------------
1201 | ALB | Albania
1202 | AUT | Austria
1203 | BEL | Belgium
1204 | CRO | Croatia
1205 | CZE | Czech Republic
1206 | ENG | England
1207 | FRA | France
1208 | GER | Germany
1209 | HUN | Hungary
.......
1229 | NOR | Norway
Sample Solution:
SQL Code:
-- Selecting player information including name, jersey number, country name, age, position to play, and playing club
SELECT
player_name, -- Selecting the name of the player
jersey_no, -- Selecting the jersey number of the player
country_name, -- Selecting the name of the country
age, -- Selecting the age of the player
posi_to_play, -- Selecting the position to play of the player
playing_club -- Selecting the club the player is currently playing for
FROM
goal_details a -- Selecting from the goal_details table with alias 'a'
JOIN
player_mast b ON a.player_id = b.player_id -- Joining with the player_mast table with alias 'b' based on player ID
JOIN
soccer_country c ON a.team_id = c.country_id -- Joining with the soccer_country table with alias 'c' based on team ID
WHERE
goal_type = 'O' -- Filtering records where the goal type is 'O' (open play)
-- Ordering the results by player name
ORDER BY
player_name;
Sample Output:
player_name | jersey_no | country_name | age | posi_to_play | playing_club
--------------------+-----------+---------------------+-----+--------------+--------------
Birkir Saevarsson | 2 | Iceland | 31 | DF | Hammarby
Ciaran Clark | 3 | Republic of Ireland | 26 | DF | Aston Villa
Gareth McAuley | 4 | Northern Ireland | 36 | DF | West Brom
(3 rows)
Code Explanation:
The said query in SQL that selects a list of players who have scored own goals, along with their relevant information such as their jersey number, country, age, position, and playing club.
The JOIN clause combines the goal_details and player_mast tables are based on the player_id column, and the goal_details and soccer_country tables are joined based on the team_id and country_id columns.
The WHERE clause limits the results to only include goals that are of type 'O', which means own goals.
The results are sorted by player name in alphabetical order.
Alternative Solutions:
Using a Subquery with IN Clause:
-- Selecting player information including name, jersey number, country name, age, position to play, and playing club
SELECT
player_name, -- Selecting the name of the player
jersey_no, -- Selecting the jersey number of the player
country_name, -- Selecting the name of the country
age, -- Selecting the age of the player
posi_to_play, -- Selecting the position to play of the player
playing_club -- Selecting the club the player is currently playing for
FROM
goal_details a -- Selecting from the goal_details table with alias 'a'
JOIN
player_mast b ON a.player_id = b.player_id -- Joining with the player_mast table with alias 'b' based on player ID
JOIN
soccer_country c ON a.team_id = c.country_id -- Joining with the soccer_country table with alias 'c' based on team ID
WHERE
goal_type = 'O' -- Filtering records where the goal type is 'O' (open play)
AND b.team_id IN ( -- Filtering records where the player's team ID is in the list of country IDs
SELECT
country_id -- Subquery to select the country IDs
FROM
soccer_country -- Subquery selecting from the soccer_country table
)
-- Ordering the results by player name
ORDER BY
b.player_name;
Explanation:
This query uses a subquery with the IN clause to filter the results based on the country_id. It ensures that the country_id is present in the soccer_country table.
Using EXISTS Clause:
-- Selecting player information including name, jersey number, country name, age, position to play, and playing club
SELECT
player_name, -- Selecting the name of the player
jersey_no, -- Selecting the jersey number of the player
country_name, -- Selecting the name of the country
age, -- Selecting the age of the player
posi_to_play, -- Selecting the position to play of the player
playing_club -- Selecting the club the player is currently playing for
FROM
goal_details a -- Selecting from the goal_details table with alias 'a'
JOIN
player_mast b ON a.player_id = b.player_id -- Joining with the player_mast table with alias 'b' based on player ID
JOIN
soccer_country c ON a.team_id = c.country_id -- Joining with the soccer_country table with alias 'c' based on team ID
WHERE
goal_type = 'O' -- Filtering records where the goal type is 'O' (open play)
AND EXISTS ( -- Checking if there exists a record in the soccer_country table
SELECT
1 -- Dummy column to indicate existence
FROM
soccer_country d -- Subquery selecting from the soccer_country table with alias 'd'
WHERE
b.team_id = d.country_id -- Matching the team ID from player_mast with the country ID from soccer_country
)
-- Ordering the results by player name
ORDER BY
b.player_name;
Explanation:
This query uses the EXISTS clause to check for the existence of a matching country_id in the soccer_country table.
Relational Algebra Expression:
Relational Algebra Tree:
Go to:
PREV : Find the defender who scored goal for his team.
NEXT : Find the results of penalty shootout matches.
Practice Online
Sample Database: soccer
Have another way to solve this solution? Contribute your code (and comments) through Disqus.
What is the difficulty level of this exercise?
Test your Programming skills with w3resource's quiz.