Joining Data in SQL
  • AI Chat
  • Code
  • Report
    • Beta
    Spinner

    Joining Data with SQL

    👋 Welcome to your new workspace! Here, you can experiment with the world data you used in Joining Data in 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!

    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 counts the total number of languages spoken in each country.

    You can click the "Browse tables" button in the upper righthand corner of the cell below to view the available tables.

    Note: The databases from three different 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 world schema. To access each table, you will need to specify this schema in your queries (e.g., world.countries for the countries table, and world.languages for the languages table).

    Unknown integration
    DataFrameavailable as
    world_info
    variable
    SELECT 
	ctry.name AS country_name, 
    SUM(CASE WHEN official = 'True' THEN 1 ELSE 0 END) AS number_official_languages,
    COUNT(*) AS number_of_languages
FROM world.languages AS lang
LEFT JOIN world.countries_plus AS ctry USING(code)
GROUP BY ctry.name
ORDER BY country_name
    This query is taking long to finish...Consider adding a LIMIT clause or switching to Query mode to preview the result.

    2. Your Turn

    Now it's your turn to try out some challenge queries using the cells below. To start, update the sample query below to:

    • Combine the states and countries tables into one table.
    • The final result should have a name column, a continent column, and an indep_year column. Use the local_name column from the countries table as the name.
    • The final result should not include duplicate rows.

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

    Unknown integration
    DataFrameavailable as
    df
    variable
    SELECT * 
FROM world.states
LIMIT 5
    This query is taking long to finish...Consider adding a LIMIT clause or switching to Query mode to preview the result.

    3. Keep going!

    Continue to flex your SQL skills and update the sample query below to:

    • Return the country_code, fertility_rate, and life_expectancy from the populations table.
    • Only return countries which are categorized as 'large' in the countries_plus table.
    • Filter for data from 2015.
    Unknown integration
    DataFrameavailable as
    df
    variable
    SELECT * 
FROM world.populations
LIMIT 5
    This query is taking long to finish...Consider adding a LIMIT clause or switching to Query mode to preview the result.

    4. Final Challenge!

    Now let's throw one final challenge your way. Update the sample query below to:

    • Return the local_name aliased as country_name, continent, surface_area, the number of official languages, gdp_percapita, and unemployment_rate from the countries, economies, and languages tables.
    • Ensure that all records from the countries table are preserved, regardless whether a match is present in the economies or languages tables.
    • Only include economy data from 2015.
    • Sort your results by gdp_percapita in descending order.
    Unknown integration
    DataFrameavailable as
    df
    variable
    SELECT *
FROM world.countries
LIMIT 5
    This query is taking long to finish...Consider adding a LIMIT clause or switching to Query mode to preview the result.

    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:

    • Create a new blank workspace and connect to our sample integrations to further refine your SQL skills!
    • Check out Intermediate SQL. This course will teach you how to categorize information, enhance your subquery skills, and introduce you to window functions.