
Already know some of this? A 2-minute test skips the lessons you know.
Take the placement testA free 30-lesson course built from Sangam Mukherjee's 4h 07m of YouTube video. 3 days at 90 minutes a day, with study notes and a quiz in every lesson. A 2-minute placement test skips what you already know.
Taught by Sangam Mukherjee on YouTube. Videos play in YouTube's own player, so the creator keeps every view.
Install PostgreSQL on macOS using the official installer, configure the superuser password, and launch pgAdmin 4 to verify the server is running.
Distinguish relational databases (structured tables with relationships) from non-relational databases (documents or key-value pairs) and identify when to choose PostgreSQL for a project.
Use CREATE DATABASE and DROP DATABASE IF EXISTS in a .sql file, then execute it via psql -U postgres -d postgres -f to manage databases from the command line.
Run common psql meta-commands (SELECT current_database, current_user, version, /l, /dt, /q) to inspect the connected database, list databases and tables, and exit the shell.
Create a custom schema with CREATE SCHEMA IF NOT EXISTS, enable the pgcrypto extension for UUID generation, and query information_schema.schemata to list schemas.
Define a table using serial primary key, varchar, text, integer, bigint, numeric(10,2), boolean, and timestamp with defaults and CHECK constraints.
Use uuid_generate_v4() for UUID primary keys, store flexible metadata in JSONB columns, and extract nested values with the ->> operator.
Differentiate NULL (missing), empty string (known but zero-length), and numeric zero; query with IS NULL / IS NOT NULL instead of = NULL.
Apply column-level constraints (NOT NULL, UNIQUE, CHECK, DEFAULT) to enforce data integrity at the database level.
Create tables with serial, UUID, and composite primary keys; understand how PRIMARY KEY enforces uniqueness and NOT NULL together.
Build a reusable products table with all core columns, insert seed data, and verify contents with SELECT * FROM products.
Insert one row with explicit column list and VALUES, then use RETURNING id, name, sku to immediately retrieve generated columns.
Insert several rows in one statement by providing multiple comma-separated VALUE tuples and return the inserted rows with RETURNING.
Select only needed columns to reduce payload, and use column aliases (AS) to rename output columns for clarity.
Filter rows with WHERE using =, !=, >, <, >=, <= on numeric, text, and date columns.
Build compound conditions using AND, OR, NOT with parentheses to control precedence.
Use LIKE (case-sensitive) and ILIKE (case-insensitive) with % and _ wildcards; search across multiple columns with OR.
Match a column against a list with IN / NOT IN, and test inclusive numeric ranges with BETWEEN.
Correctly test for missing values with IS NULL and IS NOT NULL; avoid the common mistake of using = NULL.
Sort results ascending (ASC) or descending (DESC) on one or multiple columns.
Implement pagination by combining LIMIT (rows to return) and OFFSET (rows to skip); calculate OFFSET as (page-1)*LIMIT.
Update one row with WHERE on primary key, or many rows with a broader condition; use RETURNING to confirm changed columns.
Delete rows with WHERE, optionally return deleted columns via RETURNING, and verify removal with a follow-up SELECT.
Create users, posts, comments, tags, and post_tags tables with foreign keys and composite primary keys; insert seed data for relationship queries.
Define REFERENCES constraints to link posts→users and comments→posts; understand one-to-many cardinality and referential integrity.
Join two tables on matching columns; INNER JOIN returns only rows with matches in both tables.
Preserve all rows from the left table; non-matching right-side columns become NULL—useful for optional relationships like posts without comments.
Model many-to-many via a post_tags junction table with composite primary key (post_id, tag_id) and two foreign keys; query across three tables with two INNER JOINs.
Shorten table names with aliases (p, u, c, t) in SELECT, JOIN, and WHERE clauses to reduce verbosity and avoid ambiguity.
Compute single-value summaries over a column or group; combine with WHERE to filter before aggregation.