Databases with SQL Server

From zero to stored procedures, functions and triggers: design relational databases, query them with T-SQL and automate business rules, with labs that run right in your browser.

A 10-chapter program for complete beginners. Every topic is explained with simple databases (School, Store, Company) whose schema and entity-relationship diagram you see in the lesson and in the Schema tab of each lab. You write real T-SQL—CREATE TABLE, JOIN, GROUP BY, subqueries, transactions, TRY/CATCH, procedures, functions and triggers—and run it instantly, with the same messages and errors SQL Server Management Studio would show. Capstone project: the car dealership (imports, vehicles, repairs, sales, financing and payments). Includes two midterm exams and a final practice exam.

10 chapters · 87 labs · ~36 h · US$8.99, one-time payment. The first chapter is free.

What you'll be able to do

  • Design normalized relational databases with primary keys, foreign keys and constraints.
  • Query data with SELECT, filters, aggregations, JOINs, subqueries and CTEs.
  • Modify data safely with transactions and error handling.
  • Program business rules with variables, IF, WHILE, stored procedures, functions and triggers.
  • Build and present a capstone project: the database for a car dealership.

Syllabus

  1. Chapter 1 · Fundamentals and setup (free)

    What a relational database is, how to install SQL Server on Windows and Mac, SSMS, data types, and your first tables with CREATE TABLE, INSERT, and SELECT.

    1. Lesson: What is a relational database? (20 min)
    2. Lesson: Install SQL Server on Windows and Mac, step by step (30 min)
    3. Lesson: SQL Server and SSMS: install, connect, and run (20 min)
    4. Lesson: SQL Server data types (20 min)
    5. Lesson: CREATE TABLE, INSERT, and your first SELECT (25 min)
    6. Lab: Fill in the blanks: the Teachers table (6 min)
    7. Lab: Your first query: all the courses (6 min)
    8. Lab: Create the Teachers table (12 min)
    9. Lab: Insert three teachers (10 min)
    10. Lab: Columns with aliases (6 min)
    11. Lab: Count the students (5 min)
    12. Lab: Add the Email column (8 min)
    13. Lab: A new student (10 min)
    14. Lab: Drop the Scratch table (5 min)
    15. Quiz: Quiz · Fundamentals and setup (10 min)

  2. Chapter 2 · Entity-relationship design and real tables (in the full course)

    Entity-relationship modeling, primary and foreign keys, constraints, normalization up to 3NF, and junction tables for many-to-many relationships.

    1. Lesson: Entity-relationship modeling: entities, attributes, and relationships (20 min)
    2. Lesson: Primary and foreign keys (20 min)
    3. Lab: Fill in the blank: relate two tables (6 min)
    4. Lesson: Constraints: NOT NULL, UNIQUE, CHECK, and DEFAULT (20 min)
    5. Lesson: Normalization: from the denormalized invoice to 3NF (25 min)
    6. Lesson: Many-to-many relationships: junction tables (15 min)
    7. Lab: Create Courses and Enrollments with foreign keys (15 min)
    8. Lab: Insert while respecting the foreign keys (10 min)
    9. Lab: Cascading delete: authors and books (12 min)
    10. Lab: Named constraints: the Classrooms table (15 min)
    11. Lab: Data that follows the rules (10 min)
    12. Lab: Normalize a flat table (15 min)
    13. Lab: Junction table: students and clubs (12 min)
    14. Lab: Query the relationship: Robotics members (8 min)
    15. Quiz: Quiz · Entity-relationship design (12 min)

  3. Chapter 3 · Basic queries (in the full course)

    SELECT and WHERE in depth: logical operators, LIKE, IN, BETWEEN, NULL, ORDER BY, TOP, DISTINCT, OFFSET/FETCH, and text, date and conversion functions.

    1. Lesson: Filtering rows with WHERE: comparisons and logical operators (20 min)
    2. Lab: Fill in: only the ones from Santiago (5 min)
    3. Lesson: Patterns and ranges: LIKE, IN and BETWEEN (20 min)
    4. Lesson: NULL: the unknown value (15 min)
    5. Lesson: Sorting and limiting: ORDER BY, TOP, DISTINCT and OFFSET/FETCH (20 min)
    6. Lab: Fill in: students in alphabetical order (5 min)
    7. Lesson: Useful functions: text, dates and conversions (20 min)
    8. Lab: Students from Santiago (6 min)
    9. Lab: Good averages in two cities (8 min)
    10. Lab: Products that mention “laptop” (6 min)
    11. Lab: Price range in two categories (8 min)
    12. Lab: Customers with incomplete data (10 min)
    13. Lab: The 5 most expensive products in stock (8 min)
    14. Lab: Page 2 of the catalog (8 min)
    15. Lab: Student profile with functions (12 min)
    16. Quiz: Quiz · Basic queries (12 min)

  4. Chapter 4 · Aggregation and reports (in the full course)

    COUNT, SUM, AVG, MIN and MAX; GROUP BY and HAVING; the logical order of execution; ISNULL, ROUND and conditional counts with CASE to build reports on the STORE database.

    1. Lesson: Aggregate functions: summarizing many rows into one value (25 min)
    2. Lab: Build the query: how many students are there? (5 min)
    3. Lab: Fact sheet for the Products table (8 min)
    4. Lab: Count customers three ways (8 min)
    5. Lab: Averages without losing decimals (10 min)
    6. Lesson: GROUP BY and HAVING: grouping to summarize (25 min)
    7. Lab: Customers per city (10 min)
    8. Lab: Inventory by category (12 min)
    9. Lab: Frequent customers (12 min)
    10. Lesson: Reports: CASE inside aggregations, dates and percentages (20 min)
    11. Lab: Orders per month (15 min)
    12. Lab: The three largest orders (15 min)
    13. Quiz: Quiz · Chapter 4 (15 min)

  5. Chapter 5 · JOINs (in the full course)

    Joining related tables: INNER JOIN, LEFT/RIGHT/FULL JOIN and anti-join, self-join, many-to-many relationships, JOIN with GROUP BY, CROSS JOIN and UNION. Includes the First Midterm (chapters 1-5).

    1. Lesson: INNER JOIN: joining related tables (25 min)
    2. Lab: Fill in: each product with its category (6 min)
    3. Lab: Products with their category (10 min)
    4. Lab: Details of Ana's orders (12 min)
    5. Lesson: LEFT JOIN and orphan rows (20 min)
    6. Lab: Customers without orders (10 min)
    7. Lab: All customers and their orders (12 min)
    8. Lesson: Self-joins and many-to-many relationships (20 min)
    9. Lab: Employees and their managers (12 min)
    10. Lab: Members of each project (15 min)
    11. Lesson: JOIN + GROUP BY, CROSS JOIN and UNION (25 min)
    12. Lab: Total purchased per customer (15 min)
    13. Lab: Sales by month (15 min)
    14. Quiz: Quiz · Chapter 5 (10 min)
    15. Exam: First Midterm · Chapters 1-5 (60 min)

  6. Chapter 6 · Subqueries and CTEs (in the full course)

    Scalar, IN/NOT IN, EXISTS and correlated subqueries; derived tables and chained CTEs; temporary tables and rankings with ROW_NUMBER on the STORE database.

    1. Lesson: Subqueries: scalar, IN, EXISTS and correlated (25 min)
    2. Lab: Fill in: products more expensive than the average (6 min)
    3. Lab: Products cheaper than the average (10 min)
    4. Lab: Customers who bought computers (12 min)
    5. Lab: Customers with no deliveries (12 min)
    6. Lab: The one with the most stock in each category (12 min)
    7. Lesson: Derived tables and CTEs (20 min)
    8. Lab: Average ticket per customer (15 min)
    9. Lab: VIP customers with a CTE (15 min)
    10. Lesson: Temporary tables and ranking with ROW_NUMBER (20 min)
    11. Lab: Temporary table of totals (15 min)
    12. Lab: The best seller in each category (18 min)
    13. Quiz: Quiz · Chapter 6 (15 min)

  7. Chapter 7 · Dealership design and safe modification (in the full course)

    The normalized DEALERSHIP model, safe INSERT/UPDATE/DELETE, referential integrity, @@ROWCOUNT and transactions.

    1. Lesson: The dealership data model (25 min)
    2. Lesson: INSERT: recording new data (20 min)
    3. Lab: A new brand with its first model (10 min)
    4. Lab: An imported vehicle in transit (12 min)
    5. Lab: A batch of three vehicles (10 min)
    6. Lesson: UPDATE and DELETE: modifying with care (25 min)
    7. Lab: Fill in: discounting the peripherals (6 min)
    8. Quiz: Check: create, relate and modify (5 min)
    9. Lab: The RAV4 clears customs (10 min)
    10. Lab: Into the shop (10 min)
    11. Lab: Raising the price of available SUVs (12 min)
    12. Lab: Canceling an import in transit (10 min)
    13. Lesson: Transactions: all or nothing (20 min)
    14. Lab: A financed sale in a transaction (15 min)
    15. Quiz: Quiz · Chapter 7 (15 min)

  8. Chapter 8 · Essential T-SQL programming (in the full course)

    Variables, IF/ELSE, CASE, WHILE, TRY/CATCH, THROW and safe transactions. Includes the Second Midterm (chapters 6-8).

    1. Lesson: Variables, types and PRINT (20 min)
    2. Lab: Discount with variables (12 min)
    3. Lesson: Decisions: IF/ELSE, IF EXISTS and CASE (25 min)
    4. Lab: Sell only if it's available (15 min)
    5. Lab: Each vehicle's tier (CASE) (10 min)
    6. Lab: Commissions with CASE in UPDATE (12 min)
    7. Lesson: Repeating: WHILE, BREAK and CONTINUE (20 min)
    8. Lab: Amortization table with WHILE (15 min)
    9. Lesson: Errors and transactions: TRY/CATCH, THROW and the “validate → execute → commit” pattern (25 min)
    10. Lab: Catch an error with TRY/CATCH (12 min)
    11. Lab: Validate, execute, commit (18 min)
    12. Lab: All or nothing: the transaction is rolled back (15 min)
    13. Quiz: Quiz · Chapter 8 (15 min)
    14. Exam: Second Midterm · Chapters 6-8 (60 min)

  9. Chapter 9 · Stored procedures and functions (in the full course)

    CREATE PROCEDURE with parameters, default values, OUTPUT, and RETURN; scalar functions; the dealership's business rules; and an introduction to triggers.

    1. Lesson: Stored procedures (25 min)
    2. Lab: sp_VehiclesByStatus (12 min)
    3. Lab: sp_RegisterRepair (12 min)
    4. Lesson: OUTPUT parameters, RETURN, and business rules (25 min)
    5. Lab: sp_RegisterSale with validation and OUTPUT (20 min)
    6. Lesson: Scalar functions (25 min)
    7. Lab: fn_CalculateItbis (10 min)
    8. Lab: fn_TotalRepairs (12 min)
    9. Lab: fn_LoanBalance (12 min)
    10. Lab: sp_ApplyPayment with an OUTPUT balance (18 min)
    11. Lesson: Triggers: automatic reactions (introduction) (20 min)
    12. Lab: Status audit trigger (15 min)
    13. Quiz: Quiz · Chapter 9 (15 min)

  10. Chapter 10 · Integration and final presentation (in the full course)

    The dealership capstone project, report views, indexes, capstone labs, a full course review with a practice exam, and preparation for the defense.

    1. Lesson: Capstone project: Dealership management system (20 min)
    2. Lesson: Views: saved queries (20 min)
    3. Lab: View: sales by month (12 min)
    4. Lab: View: available inventory by brand (12 min)
    5. Lab: View: outstanding balance by customer (15 min)
    6. Lesson: Indexes: speeding up queries (20 min)
    7. Lab: Indexes for the quarterly report (12 min)
    8. Lab: Capstone: receive an import (20 min)
    9. Lab: Capstone: close a repair (20 min)
    10. Lab: Capstone: a complete financed sale (25 min)
    11. Lesson: Course review (25 min)
    12. Quiz: Final exam practice test (30 min)
    13. Lesson: Project defense and presentation (20 min)

Before you start

  • No prior experience with databases is needed.
  • A modern browser (Chrome, Edge or Firefox). Optional: SQL Server 2025 Express, free, to practice on your own computer (Windows or Mac; step-by-step guide in Chapter 1).

Try the first lesson for free, no account needed

Also in: Español