Skip to content

Data Manipulation in SQL

Here you can access every table used in the course. To access each table, you will need to specify the soccer schema in your queries (e.g., soccer.match for the match table, and soccer.league for the league table).

Note: When using sample integrations such as those that contain course data, you have read-only access. You can run queries, but cannot make any changes such as adding, deleting, or modifying the data (e.g., creating tables, views, etc.).

Take Notes

Add notes about the concepts you've learned and SQL cells with queries you want to keep.

Add your notes here

Spinner
DataFrameas
df
variable
-- Add your own queries here
SELECT *
FROM soccer.match
LIMIT 5

Order of SQL

  1. FROM and JOIN(S)
  2. WHERE
  3. GROUP BY
  4. HAVING
  5. SELECT
  6. DISTINCT
  7. ORDER BY
  8. LIMIT / OFFSET

WHY we have to use subqueries ?

  1. Comparing groups to SUMMARIZED VALUES ( How did Liverpool compare to the English Premier League's average performance for that year?)
  2. Reshaping data ( what is the highest monthly average of goals scored in the Bundesliga?)
  3. Combining data that cannot be joined ( How do you get both the home and away team names into a table of match results?)

SUBQUERIES IN WHERE AND FROM IN WHERE: JUST RETURN A SINGLE COLUMN FROM Restructure and tranform data: + Transforming data from long to wide before selecting + Prefiltering data Calculating aggreagates of aggregates + Which 3 teams has the highest average of home goals scored?

SELECTing what?

  • Returns a single value Include aggregate values to compare to individual values
  • Used in mathematical calculations Deviation from the average Subqueries in SELECT
  • Calculate the total matches across all seasons SELECT season, COUNT(id) AS matches, (SELECT COUNT(id) FROM match) as total_matches FROM match GROUP BY season;

Different use cases Joins

  • 2+ tables( What is the total sales per employee) Correlated Subqueries
  • Who does each empoyee report to in a company ? Multiple/Nested Subqueries
  • What is the average deal size closed by each sales representavtive in the quarter? Common Table Expressions
  • How did the marketing, sales, growth, & engineering teams perform on key mertrics ?