Data Definition Language (DDL)

DDL statements create, modify, and delete database structures (tables, indexes, views).

CREATE TABLE

sql

ALTER TABLE

sql

DROP TABLE

sql

Data Types

SQL supports various data types to store different kinds of information.

VARCHAR(n) โ€” Text, variable length (max n characters)
CHAR(n) โ€” Text, fixed length
TEXT โ€” Unlimited text
INTEGER / INT โ€” Whole numbers
SMALLINT โ€” Smaller whole numbers
BIGINT โ€” Larger whole numbers
DECIMAL(m,n) โ€” Exact decimal, m total digits, n after decimal
NUMERIC โ€” Alias for DECIMAL
FLOAT / REAL โ€” Approximate decimal
BOOLEAN โ€” TRUE / FALSE
DATE โ€” YYYY-MM-DD
TIMESTAMP โ€” Date and time
TIME โ€” Time only
SERIAL โ€” Auto-incrementing integer
JSONB โ€” JSON (PostgreSQL)
UUID โ€” Unique identifier (PostgreSQL)

Constraints

Constraints enforce rules on data to maintain integrity.

Common Constraints

sql

Constraint Types

  • PRIMARY KEY โ€” Uniquely identifies each row (NOT NULL + UNIQUE)
  • FOREIGN KEY โ€” Links to another table's primary key
  • NOT NULL โ€” Column must have a value
  • UNIQUE โ€” All values in column must be different
  • CHECK โ€” Custom condition must be true
  • DEFAULT โ€” Default value if none provided

Data Manipulation Language (DML)

DML statements insert, update, and delete data.

INSERT

sql

UPDATE

sql

DELETE

sql

UPSERT (ON CONFLICT)

sql

SELECT & Filtering

Query data with filtering, sorting, and limiting.

Basic SELECT

sql

WHERE Clause

sql

ORDER BY & LIMIT

sql

DISTINCT

sql

Filtering Operators

Advanced filtering with operators and conditions.

Comparison & Logic Operators

sql

NULL Checks

sql

Logical Operators

sql

Aggregate Functions & GROUP BY

Summarize data with aggregates and group results.

Basic Aggregates

sql

GROUP BY & HAVING

sql

Joins

Combine data from multiple tables.

INNER JOIN

sql

LEFT JOIN (LEFT OUTER JOIN)

sql

FULL OUTER JOIN

sql

Self-Join

sql

Subqueries & Common Table Expressions (CTEs)

Use subqueries and CTEs to write complex queries clearly.

Scalar Subquery

sql

EXISTS Subquery

sql

WITH (CTE)

sql

Recursive CTE

sql

Window Functions

Perform calculations across rows without collapsing results.

ROW_NUMBER & RANK

sql

LAG & LEAD

sql

SUM OVER (Running Total)

sql

NTILE (Percentiles)

sql

Indexes

Improve query performance by indexing frequently searched columns.

Create Index

sql

Partial Index & Expression Index

sql

Drop Index

sql

Views

Create virtual tables from queries for reusability.

CREATE VIEW

sql

Use View

sql

Materialized View (PostgreSQL)

sql

Transactions

Group statements together and roll back if anything fails.

Transaction Basics

sql

Savepoint

sql

Permissions & Roles (PostgreSQL)

Control who can access and modify data.

Create Role & Grant Permissions

sql

Useful Functions

Common functions for data manipulation and formatting.

NULL Handling

sql

Type Casting

sql

Date & Time

sql

String Functions

sql

Math Functions

sql

PostgreSQL-Specific Features

Advanced features unique to PostgreSQL.

JSONB Operators

sql

EXPLAIN & ANALYZE

sql

COPY (Bulk Import)

sql

GENERATE_SERIES

sql
Africa & India's Best AI Academy
๐Ÿค– AI-first ๐ŸŽ“ Placement Assistance

Master SQL and build data-driven applications.

Join the Best AI Program: The AI-Era Data Engineer โ€” 5 months, fully online, with placement assistance. Week 1 is free.

Explore The AI-Era Data Engineer โ†’