Lecture 01 · Fundamentals

Introduction to SQL & Setup

Beginner ~45 min

What is SQL?

SQL (Structured Query Language) is the standard language for managing and manipulating relational databases. It is used by virtually every major database system including MySQL, PostgreSQL, SQL Server, SQLite, and Oracle.

Why Learn SQL?

Essential skill for data analysis, backend development, and data science
High demand across industries
Works with almost every database system

Setup

Recommended: Install PostgreSQL or use SQLite (lightweight, no installation needed) + DBeaver or pgAdmin.

SQL Query

SELECT 'Hello, SQL Mastery!' AS greeting;

Output

greeting
Hello, SQL Mastery!

🎯 Exercise 1.1

Install PostgreSQL or SQLite. Create a database and run your first query to display the current date and time.

Lecture 02 · Fundamentals

Basic SELECT Queries

Beginner ~40 min

Retrieving Data

SELECT name, price FROM products;
SELECT * FROM employees; -- Select all columns

Aliases

SELECT first_name AS name FROM users;

Lecture 03 · Fundamentals

Filtering & Sorting

Beginner ~45 min

The WHERE Clause

SELECT * FROM products WHERE price > 100;
SELECT * FROM users WHERE country = 'USA' AND age >= 18;

Ordering Results

SELECT * FROM products ORDER BY price DESC;

Lecture 04 · Fundamentals

Joins

Beginner ~55 min

Combining Tables

SELECT orders.id, users.name
FROM orders
INNER JOIN users ON orders.user_id = users.id;

Lecture 05 · Fundamentals

Aggregation & GROUP BY

Beginner ~50 min

Summary Functions

SELECT COUNT(*), AVG(price) FROM products;
SELECT category, SUM(stock) FROM products GROUP BY category;

Lecture 06 · Core Concepts

Subqueries & CTEs

Intermediate ~60 min

Common Table Expressions (CTE)

WITH high_value_orders AS (
    SELECT * FROM orders WHERE total > 1000
)
SELECT * FROM high_value_orders;

Lecture 07 · Core Concepts

Data Modification

Intermediate ~45 min

INSERT, UPDATE, DELETE

INSERT INTO users (name, email) VALUES ('Bob', '[email protected]');
UPDATE products SET price = price * 1.1 WHERE category = 'Tech';
DELETE FROM logs WHERE created_at < '2023-01-01';

Lecture 08 · Core Concepts

Constraints & Indexes

Intermediate ~50 min

Ensuring Data Integrity

CREATE TABLE authors (
    id SERIAL PRIMARY KEY,
    name TEXT NOT NULL,
    email TEXT UNIQUE
);

Lecture 09 · Advanced

Transactions & ACID

Advanced ~50 min

All or Nothing

BEGIN;
UPDATE accounts SET balance = balance - 100 WHERE id = 1;
UPDATE accounts SET balance = balance + 100 WHERE id = 2;
COMMIT;

Lecture 10 · Advanced

Stored Procedures

Advanced ~60 min

Functions and procedures allow you to save SQL logic on the database server.

Lecture 11 · Advanced

Window Functions

Advanced ~65 min

Advanced Analytics

SELECT name, salary,
RANK() OVER (ORDER BY salary DESC) as ranking
FROM employees;

Lecture 12 · Capstone

Capstone Project: E-commerce DB

Advanced ~150 min

Design and implement a database schema for an e-commerce platform, including products, users, orders, and reviews.

-- Project goals:
-- 1. Create tables with proper constraints
-- 2. Populate with sample data
-- 3. Write complex queries for sales reports
-- 4. Optimize queries with indexes