Basic SQL Course

Lesson 1 • Understanding Database Schema

Explains schema as the blueprint defining tables, data types, and constraints. Prepares learners to read and interpret schema diagrams used in later chapters.

Lesson 2 • What Is a Relational Database

Defines relational databases, tables, rows, and columns as core structures. Establishes the conceptual model learners will query throughout the course.

Lesson 3 • Setting Up a Practice Environment

Guides learners through installing or accessing a SQL environment for hands-on practice. Ensures every learner can execute queries before moving forward.

Lesson 1 • Basic SELECT Statement Structure

Covers SELECT and FROM clauses as the minimum viable query. Learners write queries that return all or specific columns from a table.

Lesson 2 • Filtering Rows with WHERE

Introduces the WHERE clause to restrict rows returned by a query. Learners apply comparison operators to target specific records.

Lesson 3 • Limiting Output with LIMIT and OFFSET

Explains row-count limiting and pagination for large result sets. Learners retrieve manageable data slices for analysis and performance.

Lesson 1 • Pattern Matching with LIKE

Introduces wildcard characters for partial string matching in WHERE clauses. Learners filter text data using flexible patterns relevant to search scenarios.

Lesson 2 • Handling NULL Values

Explains NULL semantics and why standard comparisons fail with NULL. Learners write queries that correctly identify and manage missing data.

Lesson 3 • Range and List Filtering

Covers BETWEEN and IN operators for concise multi-value filtering. Learners replace verbose OR chains with cleaner, readable expressions.

Lesson 1 • Grouping Data with GROUP BY

Explains how GROUP BY partitions rows before aggregation is applied. Learners generate per-category summaries from raw transactional data.

Lesson 2 • Counting Distinct Values

Covers DISTINCT within aggregates to count unique occurrences. Learners measure cardinality and deduplicate summary metrics accurately.

Lesson 3 • Core Aggregate Functions

Introduces COUNT, SUM, AVG, MIN, and MAX for column-level summarisation. Learners apply each function to numeric and non-numeric columns appropriately.

Lesson 1 • INNER JOIN for Matched Rows

Teaches INNER JOIN to return only rows with matches in both tables. Learners retrieve complete records by linking related entities.

Lesson 2 • Joining Three or More Tables

Extends join skills to multi-table chains common in normalised schemas. Learners write readable multi-join queries that retrieve fully assembled records.

Lesson 3 • Outer Joins for Unmatched Rows

Covers LEFT, RIGHT, and FULL OUTER JOIN to include unmatched rows. Learners identify gaps in data such as customers with no orders.

Lesson 4 • Self Joins and Cross Joins

Introduces joining a table to itself and generating all row combinations. Learners solve hierarchical and combinatorial problems using these specialised joins.

Lesson 1 • Subqueries in FROM Clauses

Teaches derived tables as inline views within the FROM clause. Learners pre-aggregate or pre-filter data before the outer query processes it.

Lesson 2 • Scalar and Row Subqueries

Covers subqueries that return a single value or row for use in comparisons. Learners embed subqueries in SELECT, WHERE, and HAVING clauses.

Lesson 3 • EXISTS and NOT EXISTS

Explains EXISTS as a semi-join test for row presence in a subquery. Learners filter rows based on related data existence without returning joined columns.

Lesson 1 • Transactions and Data Safety

Introduces BEGIN, COMMIT, and ROLLBACK to wrap DML in atomic units. Learners protect data integrity by reversing mistakes before committing changes.

Lesson 2 • Updating Existing Records

Teaches UPDATE with WHERE to modify specific rows safely. Learners avoid full-table updates by verifying filter conditions before execution.

Lesson 3 • Inserting Rows into Tables

Covers single-row and multi-row INSERT syntax for adding new records. Learners match column lists to values and insert from SELECT results.

Lesson 1 • Indexes for Query Performance

Introduces indexes as structures that accelerate data retrieval on large tables. Learners create and drop indexes and understand when indexing improves performance.

Lesson 2 • Defining Constraints

Teaches PRIMARY KEY, FOREIGN KEY, UNIQUE, NOT NULL, and CHECK constraints. Learners enforce business rules at the database level to prevent invalid data.

Lesson 3 • Creating Tables with CREATE TABLE

Covers CREATE TABLE syntax including column definitions and data types. Learners build tables that accurately represent entity attributes.

Lesson 4 • Modifying Tables with ALTER TABLE

Explains how to add, modify, and drop columns and constraints on existing tables. Learners adapt schema to evolving requirements without recreating tables.