Skip to content
Intermediate SQL
  • AI Chat
  • Code
  • Report

    • 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.

    Unknown integration
    DataFrameavailable as
    df
    variable
    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 match, league, and country tables 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".
    Unknown integration
    DataFrameavailable as
    df
    variable
    SELECT * 
FROM soccer.league
LIMIT 5

    3. Almost there!

    Keep building upon your SQL skills and update the sample query below to:

    • Use the match and country tables 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.
    Unknown integration
    DataFrameavailable as
    df
    variable
    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 match table 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!

    Unknown integration
    DataFrameavailable as
    df
    variable
    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: