PostgreSQL
From database design and DDL to complex SELECT queries, CTEs, and transactions.
Foundations + DDL
(25 lessons)Installation & Setup
Installing PostgreSQL and DBeaver, configuring the development environment.
RDBMS Introduction
Introduction to relational database management systems and core concepts.
Database Components
Tables, columns, rows, data types, and the structure of a relational database.
Data Types
PostgreSQL data types: numeric, text, date/time, boolean, and choosing the right type.
Normalization
Database normalization: 1NF, 2NF, 3NF and eliminating data redundancy.
Database Modeling & ERD
Entity-relationship diagrams and database modeling fundamentals.
ERD Advanced
Advanced ERD techniques: cardinality, participation, and complex relationships.
Modeling Practice (A1)
Hands-on practice designing database models for assignment 1.
CREATE DATABASE & SCHEMA
Creating databases and schemas using DDL statements.
CREATE TABLE
Creating tables with columns, data types, and primary keys.
INSERT INTO Basics
Inserting data into tables using INSERT INTO statements.
Foreign Keys
Defining foreign key constraints and establishing table relationships.
Foreign Keys Practice
Practice exercises working with foreign key constraints.
ALTER TABLE
Modifying table structure: adding, dropping, and renaming columns.
ALTER TABLE Constraints
Adding and modifying constraints using ALTER TABLE.
ALTER TABLE Practice
Practice exercises for ALTER TABLE operations.
Database Creation Practice
End-to-end practice creating a complete database from a model.
Database Creation & ALTER Practice
Combined practice with CREATE and ALTER statements.
Rerunable Scripts
Writing idempotent SQL scripts that can be executed multiple times safely.
Defense A1
Defense and evaluation of assignment 1: database modeling project.
Defense A1 (Continued)
Continuation of assignment 1 defense sessions.
A2 Given & Rerunable Review
Assignment 2 task given, review of rerunable script concepts.
A2 Work Session
In-class work session for assignment 2.
A2 Work Session & Review
Continued work on assignment 2 with peer review.
Defense A2
Defense and evaluation of assignment 2: DDL project.
SELECT Queries
(3 lessons)Full SELECT with DVD Rental
Complete SELECT syntax using the DVD Rental database as a real-world context.
Advanced Filtering
Complex WHERE conditions: AND/OR/NOT, LIKE, IN, BETWEEN, IS NULL, COALESCE.
SELECT Practice
Hands-on practice session: SELECT queries with filtering, sorting, and NULL handling.
Data Aggregation
(2 lessons)Joins
(3 lessons)JOINs: All Types
INNER, LEFT, RIGHT, FULL OUTER, SELF JOIN, CROSS JOIN, and UNION — complete join reference.
JOIN Practice 1
Guided practice with INNER JOIN, LEFT JOIN, and multi-table joins.
JOIN Practice 2
Advanced join practice: FULL OUTER JOIN, SELF JOIN, multi-table chains, and UNION.
Subqueries & CTE
(4 lessons)Subqueries
Writing subqueries in WHERE, FROM, and SELECT clauses; correlated subqueries and EXISTS.
Subqueries Practice
Practice session: correlated subqueries, EXISTS, derived tables, and scalar subqueries.
Common Table Expressions (CTEs)
WITH clause syntax, multiple CTEs, recursive CTEs, and CTE vs subquery trade-offs.
CTEs Practice
Practice session: building and chaining CTEs, rewriting subqueries, recursive patterns.
Data Insertion
(4 lessons)INSERT via CTE and Subqueries
Dynamic INSERT using INSERT...SELECT, subqueries, and CTEs to avoid hardcoded values.
INSERT Task Release (A3)
Task release for Assignment 3: rerunable INSERT scripts. Requirements and grading criteria explained.
INSERT Practice (Session 1)
First guided practice session for A3: building rerunable INSERT scripts.
INSERT Practice (Session 2)
Second guided practice session for A3: completing and polishing rerunable INSERT scripts.
Project
(6 lessons)Project: Task Briefing
Project kickoff: task requirements, schema, deliverables, and work plan for the rerunable INSERT project.
Project Work Session 1
First independent work session: building the project's base inserts and FK resolution.
Project Work Session 2
Second work session: completing child table inserts and initial idempotency testing.
Project Work Session 3
Third work session: finalising scripts, edge cases, and peer testing.
Project Wrap-Up
Final project session: script review, submission, and preparation for defense.
Project Defense
Graded defense of the rerunable INSERT project — live presentation and Q&A with instructor.
Database Security
(2 lessons)Transactions
(1 lessons)Final Assessment
(8 lessons)Final SQL Assignment (Task Release)
Final assignment release: comprehensive SQL task covering all course topics.
Final Work Session 1
First independent work session for the final SQL assignment.
Final Work Session 2
Second independent work session: DML and INSERT section of the final assignment.
Final Work Session 3
Third independent work session: SELECT queries section of the final assignment.
Final Work Session 4
Fourth work session: DCL section and final polishing of all assignment parts.
Final Work Session 5
Last work session: submission deadline, final review, and defence preparation.
Final Defense (Session 1)
First session of the final oral defense — students present and explain their SQL assignments.
Final Defense (Session 2)
Second and final defense session — remaining students present; grades finalised.