Skip to content
iamnoobs

Beginner to advanced · 13 modules · 39 lessons

Learn SQL: Beginner to Advanced

Learn relational databases and SQL through structured lessons, practical queries, quizzes, checkpoints, and a database capstone.

Who this is for: Beginners, students, analysts, and developers who want to query, design, and manage relational databases.

Prerequisites: No prior SQL experience is required. Basic computer literacy is helpful. Examples use broadly familiar SQL with dialect differences noted where relevant.

Meet SQL and relational databases

Databases and database systems

A database stores organized data so applications can retrieve and update it reliably.

A database is an organized collection of information. A database management system (DBMS) provides facilities to define, query, protect, and maintain that information.

A relational database represents data in tables and uses relationships between tables. PostgreSQL, MySQL, SQLite, SQL Server, and Oracle are examples of relational database systems; their SQL dialects have differences.

Guided example

Read through this example; code examples are for learning and are not executed on this site.

-- A query asks the database for information
SELECT 'Hello, SQL!' AS message;

Relevance quest · +10 XP

Which component is responsible for managing stored data and executing SQL statements?

Choose your answer

Mark this lesson complete when you are ready.

Module checkpoint: Meet SQL and relational databases

Sketch a small relational database for a library. Name two tables, list several columns for each, and identify a value that could uniquely identify each row.

After this module, you can:

  • Explain the role of a database and a relational database management system.
  • Identify tables, rows, columns, and keys.
  • Recognize the main categories of SQL statements.

Full curriculum

What you will learn

1. Meet SQL and relational databases

Beginner

Understand what databases store, how relational tables are organized, and how SQL statements work.

  1. Databases and database systems

    A database stores organized data so applications can retrieve and update it reliably.

  2. Tables, rows, and columns

    A relational table organizes records into rows and attributes into columns.

  3. SQL statement families

    SQL includes commands for defining structures, changing data, querying data, and managing access or transactions.

Module checkpoint: Sketch a small relational database for a library. Name two tables, list several columns for each, and identify a value that could uniquely identify each row.

2. Design tables and choose data types

Beginner

Create table structures and use data types, primary keys, foreign keys, and constraints to protect data quality.

  1. Create and change tables

    CREATE TABLE defines a table, while ALTER TABLE changes its structure.

  2. Common SQL data types

    A column's data type controls which kinds of values it can store.

  3. Keys and integrity constraints

    Keys identify rows and connect tables; constraints enforce rules on stored data.

Module checkpoint: Create customers and orders tables. Give each a primary key, connect orders to customers with a foreign key, and add at least one NOT NULL and one CHECK constraint.

3. Retrieve data with SELECT

Beginner

Read records from one or more columns and shape result sets with aliases, expressions, and DISTINCT.

  1. Select columns and rows

    SELECT retrieves data from a table or other queryable source.

  2. Column aliases and expressions

    Aliases give result columns readable names, and expressions can calculate values in a query.

  3. Remove duplicate results with DISTINCT

    DISTINCT removes duplicate result rows from the selected columns.

Module checkpoint: Write three SELECT queries against a products table: one selecting named columns, one creating a calculated expression with an alias, and one listing distinct categories.

4. Filter records with WHERE

Beginner

Control which rows appear by applying comparisons, logical operators, ranges, patterns, and membership tests.

  1. Comparison operators and WHERE

    WHERE keeps only rows for which its condition evaluates to true.

  2. Combine conditions with AND, OR, and NOT

    Logical operators combine predicates to express more specific filters.

  3. IN, BETWEEN, LIKE, and NULL

    SQL predicates provide concise ways to test membership, ranges, patterns, and missing values.

Module checkpoint: Create filters for products in two selected categories, products within a price range, names beginning with a chosen letter, and rows where an optional column is NULL.

5. Sort, paginate, and handle NULL values

Beginner

Order query results predictably and return manageable subsets for reports and application screens.

  1. Sort results with ORDER BY

    ORDER BY controls the order of rows returned by a query.

  2. Limit results and paginate

    Pagination divides a large ordered result into smaller sections.

  3. Sort NULL values and use safe result ordering

    NULL sort placement and ordering expressions can vary across database systems.

Module checkpoint: Build a product listing query sorted by price and name. Return a page of 10 records and ensure the ordering includes a unique tie-breaker.

6. Summarize data with aggregate functions

Intermediate

Calculate counts, totals, averages, minimums, and maximums, then group and filter summarized results.

  1. COUNT, SUM, AVG, MIN, and MAX

    Aggregate functions calculate one summary value from a set of rows.

  2. Group rows with GROUP BY

    GROUP BY forms groups of rows that share the same grouping values.

  3. Filter groups with HAVING

    HAVING filters groups after grouping and aggregate calculations.

Module checkpoint: Report each customer's completed order count and total spending. Use WHERE for completed orders and HAVING to keep customers whose total spending exceeds a chosen threshold.

7. Combine data with JOINs

Intermediate

Retrieve related information from multiple tables using inner, outer, cross, and self joins.

  1. INNER JOIN related tables

    INNER JOIN returns rows where the join condition matches in both inputs.

  2. LEFT JOIN and unmatched records

    LEFT JOIN preserves every row from the left input and supplies NULLs for unmatched right-side columns.

  3. RIGHT, FULL, CROSS, and self joins

    Different join types express different matching and row-preservation requirements.

Module checkpoint: Create a report that lists every customer and their orders, including customers with no orders. Then create a second report that lists only customers with matching orders.

8. Use subqueries and set operations

Intermediate

Build queries inside queries and combine compatible result sets to express more complex data requirements.

  1. Scalar and multi-row subqueries

    A subquery is a SELECT statement nested inside another SQL statement.

  2. EXISTS and correlated subqueries

    EXISTS tests whether a subquery returns at least one row.

  3. UNION and other set operations

    Set operations combine compatible result sets vertically.

Module checkpoint: Find customers who have at least one order, customers who have no orders, and email addresses appearing in either of two source tables. Use EXISTS, NOT EXISTS, and UNION.

9. Insert, update, and delete data

Intermediate

Change table contents safely using DML statements, multi-row operations, and dialect-aware upsert patterns.

  1. Insert rows with INSERT

    INSERT adds new records to a table.

  2. Update existing rows

    UPDATE changes values in rows that satisfy its WHERE condition.

  3. Delete rows and understand upserts

    DELETE removes rows, while an upsert inserts or updates according to a conflict rule.

Module checkpoint: Practice inserting several products, updating one selected product, and deleting one test row. Before each destructive statement, write a SELECT with the same WHERE condition to verify its target.

10. Manage transactions and data consistency

Intermediate

Use transaction boundaries to group related changes and understand isolation, locking, and failure recovery.

  1. Understand ACID properties

    ACID describes important properties expected from reliable database transactions.

  2. Commit and roll back changes

    COMMIT makes a transaction's changes permanent, while ROLLBACK cancels uncommitted changes.

  3. Isolation levels and concurrent changes

    Isolation settings and locks help coordinate transactions that run at the same time.

Module checkpoint: Write a transaction for a bank transfer between two accounts. Check that the source account has sufficient funds, update both balances, and commit only when every step succeeds.

11. Use CTEs, views, and window functions

Advanced

Structure complex SQL with common table expressions, reusable views, and calculations across related rows.

  1. Common table expressions

    A CTE gives a named temporary result that can be referenced by the main statement.

  2. Create and use views

    A view stores a query definition that can be queried like a table.

  3. Calculate values with window functions

    Window functions calculate over related rows without collapsing them into one row per group.

Module checkpoint: Create a CTE for customer spending totals, expose a reusable active-customer view, and rank each customer's orders from newest to oldest with ROW_NUMBER.

12. Improve database design and query performance

Advanced

Apply normalization principles, understand indexes and query plans, and diagnose inefficient queries.

  1. Normalize relational data

    Normalization organizes data to reduce unnecessary duplication and update anomalies.

  2. Indexes and access paths

    An index can help the database find rows without scanning every row in a table.

  3. Inspect query plans and tune queries

    A query plan shows how the database intends to access and combine data.

Module checkpoint: Choose two frequently used queries in your sample application. Identify their filters, joins, and sort order, propose suitable indexes, and compare query plans before and after.

13. Use SQL safely in real applications

Advanced

Apply least-privilege access, parameterized queries, backup awareness, and reliable database integration practices.

  1. Users, roles, and permissions

    Database permissions control which operations a user or role can perform.

  2. Prevent SQL injection

    Parameterized queries keep user-supplied values separate from SQL command text.

  3. Migrations, backups, and database testing

    Reliable database work includes controlled schema changes, recoverable backups, and repeatable tests.

Module checkpoint: Design a secure database access plan for a small application. Define separate application and reporting roles, parameterize user-driven queries, describe a migration, and document how a backup restore would be tested.

Put it together

SQL capstone: Personal study tracker database

Design a relational database for a personal study tracker. Store learners, subjects, study sessions, goals, and progress; write queries for summaries and reports; and demonstrate safe updates, transactions, constraints, and indexing.

  1. 1Identify the entities and relationships, then draw a simple relational schema for learners, subjects, study sessions, and goals.
  2. 2Create tables with appropriate data types, primary keys, foreign keys, NOT NULL, UNIQUE, and CHECK constraints.
  3. 3Insert representative sample data and write SELECT queries with filtering, sorting, and pagination.
  4. 4Write JOIN and aggregate queries to report study time by learner, subject, and date range.
  5. 5Use a CTE, a view, and a window function to build useful progress reports.
  6. 6Demonstrate a transaction for a multi-step update and explain how errors are handled.
  7. 7Add indexes based on chosen query patterns, inspect query plans, and document database-specific syntax used.
  8. 8Create least-privilege roles, use parameterized application queries, and document a tested backup and restore approach.

Progress is saved in this browser when local storage is available. You can continue without an account; changing browsers or clearing browser data may remove saved progress. Examples are not executed by the site.