Skip to content
SQL Course (a beginners guide)
- Lesson 0.1: What Problems Does SQL Solve?
- Application Focus: How do websites remember users? Where are products stored? How does an app track orders? Introduce the concept of persistent data storage.
- Introduce the role of a Database Management System (DBMS).
- Lesson 0.2: Thinking in Tables – Relational Databases Explained
- Application Focus: Analogy: Like interconnected spreadsheets but much more powerful. Show how data for an e-commerce site (users, products, orders) relates.
- Introduce basic terms: Tables, Rows (Records), Columns (Attributes), Keys (briefly).
- Lesson 0.3: Setting Up Your Practice Environment
- Application Focus: You need a playground! Recommend easy-to-install options (SQLite with a GUI tool like DB Browser for SQLite is often simplest, or Dockerised PostgreSQL/MySQL).
- Provide setup guides (links or embedded videos).
- Provide the SQL script to create and populate the sample E-commerce database (
users, products, categories, orders, order_items). This is crucial for hands-on practice.
- Lesson 1.1: Your First Query:
SELECT and FROM
- Application Focus: How to display a list of all products on a shop page? How to show all registered users in an admin panel?
- Syntax:
SELECT column1, column2 FROM tablename; and SELECT * FROM tablename;
- Lesson 1.2: Filtering Data:
WHERE Clause
- Application Focus: How to show only products in the ‘Electronics’ category? How to find a user by their email address (login)? How to show orders placed after a certain date?
- Syntax:
WHERE condition (using =, >, <, >=, <=, !=, LIKE, IN, BETWEEN, AND, OR).
- Lesson 1.3: Sorting Results:
ORDER BY
- Application Focus: How to display products sorted from lowest to highest price? How to show the newest user signups first?
- Syntax:
ORDER BY column ASC|DESC;
- Lesson 1.4: Limiting Results:
LIMIT and OFFSET
- Application Focus: How to implement pagination (showing only 10 products per page)? How to show just the top 5 most expensive products?
- Syntax:
LIMIT count; LIMIT count OFFSET offset; (Syntax might vary slightly between SQL dialects – mention this).
- Mini-Project 1: Write queries to:
- Find all users from ‘California’.
- List all products under $50, sorted by price (cheapest first).
- Show the 10 most recent orders.
- Lesson 2.1: Why Multiple Tables? The Power of Relationships
- Application Focus: Why don’t we store product and category information in one giant table? Introduce redundancy issues and the concept of normalization (very lightly). Explain Primary Keys and Foreign Keys visually using the sample schema.
- Lesson 2.2: Combining Tables:
INNER JOIN
- Application Focus: How to display each order along with the name of the user who placed it? How to show products listed with their category names?
- Syntax:
SELECT ... FROM tableA INNER JOIN tableB ON tableA.key = tableB.key;
- Lesson 2.3: More Join Types:
LEFT JOIN
- Application Focus: How to list all users and, if they have placed orders, show their order details? (Ensuring users without orders still appear). How to list all products and their categories, even if a product somehow doesn’t have a category assigned?
- Syntax:
SELECT ... FROM tableA LEFT JOIN tableB ON tableA.key = tableB.key;
- Lesson 2.4: Using Aliases
- Application Focus: Making complex queries (especially with joins) easier to read and write.
- Syntax:
FROMtablename AS t1 or SELECT column AS alias_name.
- Mini-Project 2: Write queries to:
- List all products with their category names.
- List all orders, including the user’s email and the order date.
- Show all users and the total number of orders they’ve placed (using a join and COUNT – foreshadowing Module 3).
- Lesson 3.1: Aggregate Functions:
COUNT, SUM, AVG, MAX, MIN
- Application Focus: How many users have registered? What’s the total revenue from all orders? What’s the average price of products in the ‘Books’ category? What’s the most expensive product?
- Syntax:
SELECT COUNT(*), AVG(price) FROM products;
- Lesson 3.2: Grouping Data:
GROUP BY
- Application Focus: How many products are in each category? What is the total quantity sold for each product (across all orders)? How many orders has each user placed?
- Syntax:
SELECT category_id, COUNT(*) FROM products GROUP BY category_id;
- Lesson 3.3: Filtering Groups:
HAVING
- Application Focus: Which categories have more than 10 products? Which users have placed 3 or more orders?
- Syntax:
... GROUP BY column HAVING condition; (Difference between WHERE and HAVING).
- Mini-Project 3: Write queries to:
- Calculate the total revenue for each category.
- Find the average order value.
- List users who have spent more than $1000 in total.
- Lesson 4.1: Adding Data:
INSERT
- Application Focus: What happens when a new user signs up? How is a new product added to the store via an admin panel?
- Syntax:
INSERT INTO tablename (col1, col2) VALUES (val1, val2);
- Lesson 4.2: Updating Data:
UPDATE
- Application Focus: How does a user update their profile information (e.g., shipping address)? How does an admin change the price of a product?
- Syntax:
UPDATE tablename SET col1 = val1 WHERE condition; (Emphasize the importance of WHERE!)
- Lesson 4.3: Deleting Data:
DELETE
- Application Focus: How is a user account removed? How is a product discontinued?
- Syntax:
DELETE FROM tablename WHERE condition; (Emphasize the importance of WHERE and the concept of soft deletes in real apps).
- Lesson 4.4: (Optional) Transactions
- Application Focus: What if placing an order involves updating the
orders table AND the product stock level? How to ensure both happen or neither happens? Introduce BEGIN, COMMIT, ROLLBACK conceptually.
- Mini-Project 4: Write SQL statements to:
- Add a new user.
- Add a new product category.
- Increase the price of all products in a specific category by 10%.
- Delete a specific order (by ID).
- Lesson 5.1: Subqueries (Queries within Queries)
- Application Focus: How to find products more expensive than the average product price? How to find users who have ordered a specific product (e.g., ‘Laptop’)?
- Syntax: Using subqueries in
WHERE, SELECT, and FROM clauses.
- Lesson 5.2: Common Table Expressions (CTEs):
WITH Clause
- Application Focus: How to make complex, multi-step queries (like detailed reports) more readable and organized.
- Syntax:
WITH cte_name AS (SELECT ...) SELECT ... FROM cte_name;
- Lesson 5.3: (Optional) Window Functions
- Application Focus: How to rank products by sales within each category? How to calculate running totals for monthly revenue? (More advanced analytics).
- Syntax:
RANK() OVER (PARTITION BY ... ORDER BY ...)
- Lesson 5.4: Working with Dates and Times
- Application Focus: Querying orders within specific date ranges, calculating user account age, grouping sales by month/year.
- Common date functions (
NOW(), DATE(), EXTRACT(), interval arithmetic – varies by SQL dialect).
- Lesson 6.1: Choosing Data Types
- Application Focus: Why use
INTEGER for IDs, VARCHAR for names, TEXT for descriptions, DECIMAL for prices, TIMESTAMP for dates, BOOLEAN for flags (e.g., is_active). Impact on storage and validation.
- Lesson 6.2: Keys Revisited: Primary & Foreign Keys
- Application Focus: Enforcing data integrity. Ensuring an
order_item always refers to a valid order and product. Preventing orphaned data.
- Lesson 6.3: Basic Normalization (Why We Split Tables)
- Application Focus: Briefly explain 1NF, 2NF, 3NF using the e-commerce example. How does it prevent data anomalies when updating/deleting? Trade-offs with query complexity (joins).
- Lesson 6.4: Indexes – Speeding Up Your Application
- Application Focus: Why does searching for a user by email need to be fast? Why does filtering products by category need to be efficient? Explain what an index is (like a book index) and when to create one (on foreign keys, frequently queried columns in
WHERE clauses). Mention trade-offs (slower writes).
- Lesson 7.1: Connecting from Code (Conceptual)
- Application Focus: Show brief pseudo-code or actual snippets (Python/PHP/Node.js/etc.) demonstrating how an application sends SQL and gets results. Introduce the concept of ORMs (Object-Relational Mappers) as an abstraction layer often used. This is NOT a coding lesson, just context.
- Lesson 7.2: Security Basics: SQL Injection
- Application Focus: Briefly explain the danger of building SQL strings directly from user input. Introduce parameterized queries/prepared statements as the fundamental defense.
- Lesson 7.3: Common Patterns & Anti-Patterns
- Application Focus: Soft deletes, using UUIDs, avoiding
SELECT * in production code, the N+1 query problem.
- Lesson 7.4: Further Learning
- Specific database features (PostgreSQL JSONB, MySQL GIS).
- Database administration basics.
- Data warehousing and ETL.
- Choosing the right database for the job (SQL vs NoSQL).
- Offer 1-3 projects where users apply everything they’ve learned.
- Project Idea 1: Build an Analytics Dashboard: Given the e-commerce schema, write a set of queries to populate a fictional dashboard (Total Revenue, New Users This Month, Top 5 Customers, Sales Per Category, Average Order Value).
- Project Idea 2: Design & Query a Simple API Backend: Design tables for a new feature (e.g., product reviews:
reviews table linked to users and products). Write the SQL queries needed for common API endpoints (Get reviews for product X, Add a review, Get reviews by user Y).
- Project Idea 3: Data Migration/Transformation: Write SQL to transform data from one simple structure to another (e.g., combining
first_name and last_name into full_name, categorizing users based on spending).
