Beginner
Core SQL fundamentals: tables, queries, filtering, aggregates, and constraints.
Progress: 0 / 41 topics completed
PostgreSQL Introduction
What is PostgreSQL?
An open-source object-relational database known for standards compliance, extensibility, and advanced features.
psql The PostgreSQL Command-Line Tool
Connect to and interact with PostgreSQL databases using the psql CLI tool.
PostgreSQL Data Types
Choose the right data type for each column: integers, text, booleans, dates, and more.
Database & Table Creation
CREATE DATABASE
Create, configure, and drop PostgreSQL databases.
CREATE TABLE
Define the structure of a table with columns, data types, and inline constraints.
ALTER TABLE
Modify an existing table: add/remove columns, change types, rename, and manage constraints.
DROP TABLE
Permanently remove a table and all its data from the database.
Schemas
Organise database objects into namespaces called schemas for better structure and access control.
Data Manipulation
INSERT INTO
Add new rows to a table with INSERT INTO, including multi-row inserts and conflict handling.
SELECT Statement
Retrieve data from tables using SELECT, choosing specific columns and all rows.
UPDATE
Modify existing rows in a table using the UPDATE statement.
DELETE
Remove rows from a table with DELETE, including safe patterns and RETURNING.
TRUNCATE
Instantly remove all rows from a table using TRUNCATE faster than DELETE with no WHERE.
Transactions: BEGIN, COMMIT, ROLLBACK
Group SQL statements into atomic units with BEGIN, COMMIT, and ROLLBACK.
Filtering & Sorting
WHERE Clause
Filter rows in SELECT, UPDATE, and DELETE using the WHERE clause.
Comparison Operators
Compare values using =, <>, <, >, <=, >= and SQL-specific operators.
LIKE and Pattern Matching
Filter text columns using wildcard patterns with LIKE and ILIKE.
ORDER BY
Sort query results by one or more columns in ascending or descending order.
LIMIT and OFFSET
Paginate query results using LIMIT (max rows) and OFFSET (skip rows).
DISTINCT
Eliminate duplicate rows from query results using SELECT DISTINCT.
IN and NOT IN Operators
Filter rows against a list of values with IN and NOT IN.
Mathematical Functions and Operators
Perform calculations using arithmetic operators and PostgreSQL math functions.
Logical Operators: AND, OR, NOT
Combine multiple conditions with AND, OR, and NOT in WHERE clauses.
Introduction to Subqueries
Use a SELECT inside another query to compute values or filter rows.
BETWEEN, Column Aliases, and Table Aliases
Use BETWEEN for range checks and AS aliases for readable queries.
NULL & Conditional Handling
IS NULL and IS NOT NULL
Check for NULL values correctly using IS NULL and IS NOT NULL.
COALESCE
Return the first non-NULL value from a list of expressions using COALESCE.
CASE Expression
Write conditional logic inline in SQL using CASE WHEN ... THEN ... ELSE ... END.
Aggregate Functions
Grouping
Constraints
PRIMARY KEY
Uniquely identify each row with a PRIMARY KEY constraint.
FOREIGN KEY
Enforce referential integrity between tables with FOREIGN KEY constraints.
UNIQUE Constraint
Ensure no two rows have the same value in a column (or combination of columns).
NOT NULL Constraint
Prevent NULL values from being stored in a column with the NOT NULL constraint.
CHECK Constraint
Validate column values against a boolean expression using CHECK constraints.
DEFAULT Values
Set automatic values for columns when INSERT omits them.
SERIAL and IDENTITY (Auto-increment)
Generate auto-incrementing integer primary keys using SERIAL or GENERATED ALWAYS AS IDENTITY.
CAST and Type Conversion
Convert values between data types using CAST and the :: shorthand.
String Functions
Manipulate text with PostgreSQL's built-in string functions.
Date and Time Functions
Work with dates and times: current time, extraction, arithmetic, and formatting.