Intermediate SQL
👋 Welcome to your new workspace! Here, you can experiment with the soccer data you used in Intermediate SQL and practice your newly learned skills with some challenges. You can find out more about DataCamp Workspace here.
This workspace takes about 20 minutes to complete, but you are free to experiment as long as you like!
Note: The databases from different PostgreSQL courses are available, which you can preview using the dropdown menu to the left. The data you will want to use for this workspace is contained in the soccer schema. To access each table, you will need to specify this schema in your queries (e.g., soccer.match for the match table, and soccer.league for the league table).
1. Get Started
Below is a SQL cell. It is used to execute SQL queries. There is already a pre-written query to get you started that calculates the home-team win percentage based on how far they are in the tournament (i.e., later stages).
🏃 To execute a query, click inside the cell to select it and click "Run" or the ► icon. You can also use Shift-Enter to run a selected cell and automatically navigate to the next cell. You can click the "Browse tables" button in the upper righthand corner of the cell below to view the available tables.
SELECT
-- Categorize matches at higher and lower stages
CASE WHEN stage > 20 THEN 'Higher stage'
ELSE 'Lower stage' END AS match_stage,
-- Calculate home team win percentage
AVG(CASE WHEN home_goal > away_goal THEN 1
ELSE 0 END) AS home_win_pct
FROM soccer.match
GROUP BY match_stage2. Keep going!
Now it's your turn to try out some challenge queries using the cells below. Continue to flex your SQL skills and update the sample query below to:
- Use the
match,league, andcountrytables to return the number of matches played in Great Britain versus elsewhere in the world. - "England", "Scotland", and "Wales" should be categorized as "Great Britain"
- All other leagues will need to be categorized as "World".
SELECT *
FROM soccer.league
LIMIT 53. Almost there!
Keep building upon your SQL skills and update the sample query below to:
- Use the
matchandcountrytables to return the countries in which the average number of goals (home and away goals) scored are greater than the average number of goals of all matches.
SELECT *
FROM soccer.country
LIMIT 54. Final Challenge!
Now let's throw one final challenge your way. In a soccer league, points are assigned to teams based on the result of a game. Here, let's assume that 3 points are awarded for a win, 1 for a tie, and 0 for a defeat. Update the sample query below to:
- Use the
matchtable to calculate the running total of points earned by the team "Chelsea" (team id 8455) in the season "2014/2015". - The final output should have the match date, the points earned by Chelsea, and the running total.
Hint: You may want to use a common table expression to break apart the problem!
SELECT *
FROM soccer.match
LIMIT 55. Next Steps
Feel free to continue to experiment with these tables by creating a new SQL cell below, or if you're interested in more, try the following options:
- Create a new blank workspace and connect to our sample integrations to further refine your SQL skills!
- Check out PostgreSQL Summary Stats and Window Functions. This course will teach you how to solve real-world business problems with window functions!
SELECT
-- Select the team long name and team API id
team_long_name,
team_api_id
FROM soccer.team
-- Only include FC Schalke 04 and FC Bayern Munich
WHERE team_long_name IN ('FC Schalke 04', 'FC Bayern Munich');Create a CASE statement that identifies whether a match in Germany included FC Bayern Munich, FC Schalke 04, or neither as the home team. Group the query by the CASE statement alias, home_team.
-- Identify the home team as Bayern Munich, Schalke 04, or neither
SELECT
CASE WHEN hometeam_id = 10189 THEN 'FC Schalke 04'
WHEN hometeam_id = 9823 THEN 'FC Bayern Munich'
ELSE 'Other' END AS home_team,
COUNT(id) AS total_matches
FROM soccer.match
-- Group by the CASE statement alias
GROUP BY home_team; CASE statements comparing column values Barcelona is considered one of the strongest teams in Spain's soccer league.
In this exercise, you will be creating a list of matches in the 2011/2012 season where Barcelona was the home team. You will do this using a CASE statement that compares the values of two columns to create a new group -- wins, losses, and ties.
In 3 steps, you will build a query that identifies a match's winner, identifies the identity of the opponent, and finally filters for Barcelona as the home team. Completing a query in this order will allow you to watch your results take shape with each new piece of information.
The matches_spain table currently contains Barcelona's matches from the 2011/2012 season, and has two key columns, hometeam_id and awayteam_id, that can be joined with the teams_spain table. However, you can only join teams_spain to one column at a time.