👋 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
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_stage
2. 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
countrytables 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 5
3. Almost there!
Keep building upon your SQL skills and update the sample query below to:
- Use the
countrytables 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 5
4. 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 5
5. 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: