Module 1: SQL Basics with PostgreSQL - The Foundation
Theoretical Concepts:
INSERT INTOSELECTUPDATEDELETE FROMPRIMARY KEY: Uniquely identifies each row.FOREIGN KEY: Establishes relationships between tables.UNIQUE: Ensures unique values in a column.CHECK: Defines a condition that must be true for data in a column.NOT NULL: Ensures a column cannot contain NULL values.WHERE clause, comparison operators, logical operators (AND, OR, NOT), IN, BETWEEN, LIKE.ORDER BY (ASC/DESC).COUNT, SUM, AVG, MIN, MAX).INNER JOIN: Returns rows when there is a match in both tables.
LEFT JOIN (LEFT OUTER JOIN): Returns all rows from the left table and matching rows from the right table.
1
RIGHT JOIN (RIGHT OUTER JOIN): Returns all rows from the right table and matching rows from the left table.
FULL JOIN (FULL OUTER JOIN): Returns all rows when there is a match in either left or right table.
GROUP BY: Groups rows with the same values in one or more columns into a summary row.HAVING: Filters the results of a GROUP BY clause based on specified conditions.Code Samples:
SQL
- Creating a table
CREATE TABLE users ( user_id SERIAL PRIMARY KEY, username VARCHAR(50) UNIQUE NOT NULL, email VARCHAR(100) NOT NULL, created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP
);
-- Inserting data
INSERT INTO users (username, email) VALUES ('john_doe', '[email protected]');
INSERT INTO users (username, email) VALUES ('jane_smith', '[email protected]');
-- Reading data
SELECT * FROM users;
SELECT username, email FROM users WHERE user_id = 1;
-- Updating data
UPDATE users SET email = '[email protected]' WHERE user_id = 1;
-- Deleting data
DELETE FROM users WHERE user_id = 2;
-- Using constraints
CREATE TABLE orders ( order_id SERIAL PRIMARY KEY, user_id INTEGER REFERENCES users(user_id), -- Foreign key constraint order_date DATE NOT NULL, total_amount DECIMAL(10, 2) CHECK (total_amount > 0)
);
-- Joining tables
SELECT o.order_id, u.username, o.order_date
FROM orders o
INNER JOIN users u ON o.user_id = u.user_id;
-- Aggregation and grouping
SELECT COUNT(*) AS total_users FROM users;
SELECT user_id, SUM(total_amount) AS total_spent FROM orders GROUP BY user_id HAVING SUM(total_amount) > 100;
-- Subquery
SELECT username FROM users WHERE user_id IN (SELECT user_id FROM orders WHERE total_amount > 50);
Exercises & Use-Case Challenges:
GROUP BY, HAVING, and subqueries.Checkpoint 1: You should be comfortable with fundamental SQL concepts in the context of PostgreSQL, including data types, table manipulation, CRUD operations, constraints, filtering, sorting, aggregation, and different types of joins.
Module 2: Schema Design & Normalization - Structuring Your Data
WHERE clauses or joins).Module 3: Advanced SQL & PostgreSQL Features - Unleashing the Power
Theoretical Concepts:
INSERT, UPDATE, DELETE).ROW_NUMBER(), RANK(), LAG(), LEAD(), SUM() OVER (...).JSONB is the preferred type for performance.Code Samples:
SQL
- Creating a view
CREATE VIEW active_users AS
SELECT user_id, username, email FROM users WHERE created_at >= CURRENT_DATE - INTERVAL '30 days';
-- Creating a materialized view
CREATE MATERIALIZED VIEW monthly_order_summary AS
SELECT DATE_TRUNC('month', order_date) AS month, COUNT(*) AS total_orders, SUM(total_amount) AS total_revenue
FROM orders
GROUP BY DATE_TRUNC('month', order_date);
-- Creating a trigger
CREATE OR REPLACE FUNCTION update_order_timestamp()
RETURNS TRIGGER AS $$
BEGIN NEW.updated_at = NOW(); RETURN NEW;
END;
$$ LANGUAGE plpgsql;
CREATE TRIGGER orders_before_update
BEFORE UPDATE ON orders
FOR EACH ROW
EXECUTE FUNCTION update_order_timestamp();
-- Creating a function
CREATE OR REPLACE FUNCTION get_user_order_count(user_id_param INTEGER)
RETURNS INTEGER AS $$
DECLARE order_count INTEGER;
BEGIN SELECT COUNT(*) INTO order_count FROM orders WHERE user_id = user_id_param; RETURN order_count;
END;
$$ LANGUAGE plpgsql;
SELECT get_user_order_count(1);
-- Using a window function
SELECT order_id, order_date, total_amount, RANK() OVER (ORDER BY total_amount DESC) AS amount_rank
FROM orders;
-- Using a CTE
WITH recent_orders AS ( SELECT user_id, order_date, total_amount FROM orders WHERE order_date >= CURRENT_DATE - INTERVAL '7 days'
)
SELECT u.username, COUNT(ro.order_id) AS recent_order_count
FROM users u
LEFT JOIN recent_orders ro ON u.user_id = ro.user_id
GROUP BY u.username;
-- Using JSONB
ALTER TABLE products ADD COLUMN details JSONB;
UPDATE products SET details = '{"color": "red", "size": "M"}' WHERE product_id = 1;
SELECT product_id, details->>'color' AS color FROM products WHERE details->>'size' = 'M';
-- Full-text search (requires creating an index)
ALTER TABLE products ADD COLUMN search_vector TSVECTOR;
UPDATE products SET search_vector = to_tsvector('english', name || ' ' || description);
CREATE INDEX products_search_idx ON products USING GIN (search_vector);
SELECT name FROM products WHERE search_vector @@ to_tsquery('english', 'keyword');
Exercises & Use-Case Challenges:
Checkpoint 3: You should be proficient in using advanced SQL features in PostgreSQL, including views, materialized views, triggers, stored procedures/functions, window functions, CTEs, recursive queries, arrays, JSON/JSONB support, and full-text search.
Module 4: Database Performance Optimization - Making Your Queries Fly
EXPLAIN and EXPLAIN ANALYZE to understand how PostgreSQL executes your queries and identify potential bottlenecks.pg_stat_statements).