Summary
This tutorial provides a thorough introduction to SQL for data analytics, covering its importance in the industry and practical application. It progresses from fundamental concepts like databases, CRUD operations, and query execution environments to advanced techniques including CTEs, subqueries, and constructing a Capstone project. The course emphasizes hands-on practice with real-world data, explaining SQL syntax, comparison and logical operators, wildcards, aliases, arithmetic operations, aggregation functions (SUM, COUNT, AVG, MIN, MAX), `GROUP BY`, `HAVING`, joins (LEFT, RIGHT, INNER, FULL OUTER), and the order of SQL query execution. It aims to equip learners with the skills to analyze job postings and build a portfolio project.
Key Insights
SELECT, FROM, and LIMIT are fundamental SQL commands for data retrieval.
SELECT specifies columns to retrieve, FROM specifies the table, and LIMIT restricts the number of rows returned. The asterisk (*) is used with SELECT to retrieve all columns.
WHERE clauses filter data based on specified conditions.
The WHERE clause allows filtering rows based on specific criteria, applicable to text (strings need quotes) and numerical data (using operators like >, <, =). It directly follows the FROM clause.
Logical operators (AND, OR, BETWEEN, IN) combine multiple filter conditions.
Logical operators allow for complex filtering: AND requires all conditions to be true, OR requires at least one condition to be true, BETWEEN checks if a value falls within a range (inclusive), and IN checks if a value matches any in a list.
Arithmetic operations (+, -, *, /) can be performed directly within SQL queries, often in SELECT or WHERE clauses.
SQL supports basic arithmetic operations that can be used within queries to calculate new values. These include addition, subtraction, multiplication, and division, useful for creating derived columns or applying conditions based on calculations.
Aggregation functions (SUM, COUNT, AVG, MIN, MAX) perform calculations across multiple rows.
Aggregation functions summarize data: SUM adds values, COUNT counts rows or non-null values, AVG calculates the average, MIN finds the minimum, and MAX finds the maximum. These are often used with GROUP BY.
Joins combine rows from two or more tables based on related columns.
Joins are essential for combining data from multiple tables. The primary types covered are LEFT JOIN (all rows from table A, matched from B), INNER JOIN (rows matching in both tables), and implicitly RIGHT JOIN (opposite of LEFT).
Sections
Introduction and Course Overview
SQL is a highly sought-after skill for data analysts, ranking first in data engineer and data analyst job postings.
SQL (Structured Query Language) is presented as a critical skill in data science, ranked as the number one required skill in nearly half of data analyst and data engineer job postings, and second only to Python for data scientists. The course aims to provide a solid foundation in SQL basics.
The course is structured into three chapters: Basics, Advanced Techniques, and a Capstone Project.
The tutorial is divided into three main parts: a basics chapter covering fundamental concepts and query practice, an advanced techniques chapter focusing on local database setup and complex analysis using CTEs and subqueries, and a final chapter to build a real-world Capstone project analyzing data science job postings.
The course is free, with optional paid resources offering additional practice problems, solutions, and notes.
The course is offered for free, with all necessary resources included. An optional support tier provides extra perks like multiple practice problems with solutions (easy, medium, hard), detailed course notes, and a certificate of completion, aiming to accelerate learning.
Course content is co-created with Kelly Adams, a full-time data analyst.
Shout-out to Kelly Adams, a full-time data analyst, who collaborated on the course content. Her LinkedIn and portfolio will be linked below.
Basics: Understanding SQL and Databases
SQL queries extract data; databases store vast amounts of structured or unstructured data.
A database is a collection of data designed to hold massive amounts of information, far exceeding the capacity of tools like Excel. SQL is the programming language used to query these databases.
Databases are either relational (structured, tables) or non-relational (unstructured).
There are two main types of databases: relational, which store data in structured tables (rows and columns), and non-relational (NoSQL), which handle unstructured data. The course primarily focuses on relational databases.
SQL commands include Create, Read, Update, and Delete (CRUD) operations.
SQL supports CRUD operations: CREATE and INSERT into add records, SELECT retrieves data, UPDATE modifies existing records, and DELETE removes records. These commands will be covered throughout the course.
Databases can be hosted locally on your computer or on servers (on-premise or cloud-based).
Databases can be run locally for cost savings and learning, or on servers. Server options include on-premise hosting (company-controlled) or cloud providers (AWS, Google Cloud, Azure), often referred to as 'serverless' as the provider manages the infrastructure.
Popular databases include relational (PostgreSQL, SQLite) and non-relational (NoSQL).
The course will primarily focus on PostgreSQL and SQLite, both popular relational databases. Among non-relational databases (NoSQL), popular options exist but are not the focus here. These databases are used in a wide array of applications.
SQL queries are written and executed in editors like VS Code, database-specific applications, or cloud provider interfaces.
Queries can be written in database-provided editors (e.g., for PostgreSQL, MySQL), cloud provider web interfaces, or general code editors like VS Code. For beginners, an in-browser editor like SQLite Viz will be used.
The course utilizes a dataset of data science job postings from dataNerd.tech.
The course uses a dataset aggregated from dataNerd.tech, containing 2023 data science job postings worldwide. This dataset includes job titles, required skills, pay requirements, and company information.
Data is structured into fact tables (core data) and dimension tables (descriptive attributes).
The dataset is organized using a star schema with a fact table (job postings) containing transactional data and dimension tables (skills, companies) providing descriptive attributes. This structure supports efficient filtering and grouping.
Beginner chapters use SQLite; advanced chapters use PostgreSQL for its broader capabilities.
The initial beginner sections will utilize SQLite due to its lightweight, browser-based nature. For more complex analyses in later chapters, PostgreSQL, a more robust open-source relational database, will be used. Some minor syntax differences exist but are generally compatible.
SELECT, FROM, and LIMIT are fundamental SQL commands for data retrieval.
SELECT specifies columns to retrieve, FROM specifies the table, and LIMIT restricts the number of rows returned. The asterisk (*) is used with SELECT to retrieve all columns.
DISTINCT retrieves unique values from a column, often used for categorical data analysis.
The DISTINCT keyword, used with SELECT, filters out duplicate values in a specified column, returning only unique entries. This can be resource-intensive as it requires scanning and aggregating all values.
WHERE clauses filter data based on specified conditions.
The WHERE clause allows filtering rows based on specific criteria, applicable to text (strings need quotes) and numerical data (using operators like >, <, =). It directly follows the FROM clause.
Comments (single-line with --, multi-line with /* */) improve code readability and documentation.
Comments are used to explain SQL code, making it easier to understand, debug, and maintain. Single-line comments start with '--', and multi-line comments are enclosed in '/* */'. They are ignored by the database.
ORDER BY sorts results based on specified columns, ascending (ASC) or descending (DESC).
The ORDER BY clause sorts the result set based on one or more columns. ASC (ascending) is the default, while DESC sorts in descending order. NULL values typically appear first in ascending sorts.
Comparison operators (=, !=, >, <, >=, <=) filter data based on equality or inequality.
Comparison operators are used within WHERE clauses to filter data. Examples include checking for equality (=), inequality (!= or <>), greater than (>), less than (<), greater than or equal to (>=), and less than or equal to (<=).
Logical operators (AND, OR, BETWEEN, IN) combine multiple filter conditions.
Logical operators allow for complex filtering: AND requires all conditions to be true, OR requires at least one condition to be true, BETWEEN checks if a value falls within a range (inclusive), and IN checks if a value matches any in a list.
Combining conditions uses parentheses to control the order of operations, ensuring logical flow.
When using multiple conditions with AND and OR, parentheses clarify the order of evaluation, similar to mathematical order of operations. This ensures the database filters data according to the intended logic.
Wildcards like '%' (zero or more characters) and '_' (single character) are used with the LIKE operator for pattern matching.
The LIKE operator, used with the WHERE clause, enables pattern matching in string data. The '%' wildcard matches any sequence of zero or more characters, while the '_' wildcard matches any single character. These are useful for partial string searches.
Aliases (AS) rename columns or tables for clarity, especially in complex queries or presentations.
Aliases, specified using the AS keyword, provide temporary, more readable names for columns or tables. This is particularly useful for simplifying convoluted column names or when joining multiple tables to avoid ambiguity.
Arithmetic operations (+, -, *, /) can be performed directly within SQL queries, often in SELECT or WHERE clauses.
SQL supports basic arithmetic operations that can be used within queries to calculate new values. These include addition, subtraction, multiplication, and division, useful for creating derived columns or applying conditions based on calculations.
The modulus operator (%) returns the remainder of a division.
The modulus operator (%) calculates the remainder after division. It's useful for tasks like identifying patterns or distributing items evenly, such as finding projects that took a specific number of hours past a full day's work.
Aggregation functions (SUM, COUNT, AVG, MIN, MAX) perform calculations across multiple rows.
Aggregation functions summarize data: SUM adds values, COUNT counts rows or non-null values, AVG calculates the average, MIN finds the minimum, and MAX finds the maximum. These are often used with GROUP BY.
GROUP BY aggregates rows with identical values in specified columns.
The GROUP BY clause groups rows that have the same values in one or more columns. Aggregation functions are then applied to each group, allowing for summarized analysis per category (e.g., average salary per job title).
HAVING filters groups based on aggregation results, unlike WHERE which filters individual rows.
The HAVING clause filters groups created by GROUP BY based on specified conditions involving aggregate functions. This is distinct from the WHERE clause, which filters individual rows before aggregation.
NULL values represent missing or unknown data and require specific handling (IS NULL, IS NOT NULL).
NULL indicates the absence of a value, differentiating it from zero or empty strings. Specific operators, IS NULL and IS NOT NULL, are used in WHERE or HAVING clauses to filter for or exclude rows with NULL values.
Joins combine rows from two or more tables based on related columns.
Joins are essential for combining data from multiple tables. The primary types covered are LEFT JOIN (all rows from table A, matched from B), INNER JOIN (rows matching in both tables), and implicitly RIGHT JOIN (opposite of LEFT).
The order of SQL query execution (parser, optimizer, executor) impacts performance.
SQL queries are processed sequentially: parsing checks syntax, optimization determines the most efficient execution plan, and execution retrieves the data. Understanding this order helps in optimizing complex queries for faster results, especially with large datasets.
Ask a Question
*Uses 1 Wisdom coin from your coin balance
![Learn C Programming and OOP with Dr. Chuck [feat. classic book by Kernighan and Ritchie]](https://i.ytimg.com/vi/PaPN51Mm5qQ/hqdefault.jpg)
