w3resource
Soccer Database Exercises

SQL Joins exercises on soccer Database: Find the team against which the penalty shot number 26 had been taken

SQL soccer Database: Joins Exercise-38 with Solution

38. Write a query in SQL to find the team against which the penalty shot number 26 had been taken.

Sample table: penalty_shootout


Sample table: soccer_country


Sample Solution:

SQL Code:

SELECT match_no,
       country_name
FROM penalty_shootout a
JOIN soccer_country c ON a.team_id=c.country_id
WHERE match_no=
    (SELECT match_no
     FROM penalty_shootout
     WHERE kick_id=26)
  AND country_name<>
    (SELECT country_name
     FROM soccer_country
     WHERE country_id=
         (SELECT team_id
          FROM penalty_shootout
          WHERE kick_id=26))
GROUP BY match_no,
         country_name;

Sample Output:

 match_no | country_name
----------+--------------
       47 | Germany
(1 row)

Practice Online


Sample Database: soccer

soccer database relationship structure

Have another way to solve this solution? Contribute your code (and comments) through Disqus.

Previous: Write a query in SQL to find the player along with his country who taken the penalty shot number 26.
Next: Write a query in SQL to find the captain who was also the goalkeeper.

What is the difficulty level of this exercise?



New Content: Composer: Dependency manager for PHP, R Programming