Learning Objectives

Understand relational database concepts
Write SQL queries for CRUD operations
Use PostgreSQL-specific features
Work with joins and relationships
Query optimization basics

1. Introduction to Relational Databases

What is a Relational Database?

A relational database organizes data into tables (relations) with rows (records) and columns (fields). Tables can be linked through relationships using keys.

Key Concepts

Understanding these fundamental concepts is essential before writing any SQL queries. Each concept plays a vital role in how data is organized and accessed in a relational database.

Concept	Description
Table	Collection of related data organized in rows and columns
Row	Single record in a table
Column	Field/attribute of a record
Primary Key	Unique identifier for each row
Foreign Key	Reference to primary key in another table
Index	Data structure to speed up queries

Why PostgreSQL?

PostgreSQL is a powerful, open-source relational database that offers many advantages for modern web applications:

ACID Compliant - Reliable transactions
JSON Support - Store and query JSON data
Extensions - Rich ecosystem (PostGIS, pgvector, etc.)
Scalable - Handles large datasets efficiently
Supabase - Built on PostgreSQL

2. Basic SQL Syntax

Data Types in PostgreSQL

PostgreSQL provides a rich set of data types to accurately represent your data. Choosing the right data type for each column ensures data integrity and optimal storage efficiency. The following example demonstrates commonly used types in a typical application.

-- Common PostgreSQL data types
CREATE TABLE example (
  -- Numeric types
  id SERIAL PRIMARY KEY,           -- Auto-incrementing integer
  price DECIMAL(10, 2),            -- Exact decimal (10 digits, 2 after decimal)
  quantity INTEGER,                -- Whole number
  rating REAL,                     -- Floating point

  -- Text types
  name VARCHAR(100),               -- Variable-length string (max 100 chars)
  description TEXT,                -- Unlimited length text
  status CHAR(1),                  -- Fixed-length string

  -- Date/Time types
  created_at TIMESTAMP,            -- Date and time
  created_at_tz TIMESTAMPTZ,       -- Date/time with timezone
  birth_date DATE,                 -- Date only
  duration INTERVAL,               -- Time interval

  -- Boolean
  is_active BOOLEAN DEFAULT true,

  -- UUID
  uuid UUID DEFAULT gen_random_uuid(),

  -- JSON
  metadata JSONB                   -- Binary JSON (faster queries)
);

Creating Tables

Tables are the foundation of any relational database. When creating tables, you define the structure of your data including column names, data types, and constraints. The following examples show how to create tables for a user management and wallet system with proper relationships.

-- Create a users table for RizPay
CREATE TABLE users (
  id UUID PRIMARY KEY DEFAULT gen_random_uuid(),
  email VARCHAR(255) UNIQUE NOT NULL,
  phone VARCHAR(20) UNIQUE,
  full_name VARCHAR(100) NOT NULL,
  password_hash VARCHAR(255) NOT NULL,
  is_verified BOOLEAN DEFAULT false,
  created_at TIMESTAMPTZ DEFAULT NOW(),
  updated_at TIMESTAMPTZ DEFAULT NOW()
);

-- Create a wallets table
CREATE TABLE wallets (
  id UUID PRIMARY KEY DEFAULT gen_random_uuid(),
  user_id UUID REFERENCES users(id) ON DELETE CASCADE,
  balance DECIMAL(12, 2) DEFAULT 0.00,
  currency VARCHAR(3) DEFAULT 'NGN',
  is_active BOOLEAN DEFAULT true,
  created_at TIMESTAMPTZ DEFAULT NOW(),
  updated_at TIMESTAMPTZ DEFAULT NOW(),
  CONSTRAINT positive_balance CHECK (balance >= 0)
);

3. CRUD Operations

CRUD stands for Create, Read, Update, and Delete - the four basic operations you can perform on data in a database. Mastering these operations is essential for any application that works with persistent data.

CREATE - Inserting Data

The INSERT statement adds new records to a table. You can insert single rows, multiple rows at once, or use RETURNING to get back the created data immediately.

-- Insert a single row
INSERT INTO users (email, phone, full_name, password_hash)
VALUES ('john@example.com', '+2348012345678', 'John Doe', 'hashed_password_here');

-- Insert and return the created row
INSERT INTO users (email, phone, full_name, password_hash)
VALUES ('jane@example.com', '+2348087654321', 'Jane Smith', 'hashed_password_here')
RETURNING id, email, created_at;

-- Insert multiple rows
INSERT INTO users (email, full_name, password_hash)
VALUES
  ('user1@example.com', 'User One', 'hash1'),
  ('user2@example.com', 'User Two', 'hash2'),
  ('user3@example.com', 'User Three', 'hash3');

READ - Selecting Data

The SELECT statement retrieves data from tables. It supports filtering with WHERE clauses, sorting with ORDER BY, pagination with LIMIT and OFFSET, and aggregation with functions like COUNT and SUM.

-- Select all columns
SELECT * FROM users;

-- Select specific columns
SELECT id, email, full_name FROM users;

-- Filter with WHERE
SELECT * FROM users WHERE is_verified = true;

-- Multiple conditions
SELECT * FROM users
WHERE is_verified = true
  AND created_at > '2024-01-01';

-- Pattern matching
SELECT * FROM users WHERE email LIKE '%@gmail.com';

-- Case-insensitive pattern matching (PostgreSQL)
SELECT * FROM users WHERE email ILIKE '%@gmail.com';

-- Ordering results
SELECT * FROM users ORDER BY created_at DESC;

-- Limit and offset (pagination)
SELECT * FROM users
ORDER BY created_at DESC
LIMIT 10 OFFSET 0;  -- First page

SELECT * FROM users
ORDER BY created_at DESC
LIMIT 10 OFFSET 10;  -- Second page

-- Count rows
SELECT COUNT(*) FROM users WHERE is_verified = true;

-- Aggregate functions
SELECT
  COUNT(*) as total_users,
  COUNT(*) FILTER (WHERE is_verified) as verified_users,
  MIN(created_at) as first_user,
  MAX(created_at) as latest_user
FROM users;

UPDATE - Modifying Data

The UPDATE statement modifies existing records. Always use a WHERE clause to target specific rows, otherwise all rows in the table will be updated. The RETURNING clause is useful for confirming what was changed.

-- Update a single row
UPDATE users
SET is_verified = true, updated_at = NOW()
WHERE id = 'user-uuid-here';

-- Update and return the modified row
UPDATE users
SET full_name = 'John Smith'
WHERE email = 'john@example.com'
RETURNING *;

-- Update multiple rows
UPDATE users
SET is_verified = true
WHERE email LIKE '%@company.com';

-- Conditional update
UPDATE wallets
SET balance = balance + 1000
WHERE user_id = 'user-uuid-here'
  AND is_active = true;

DELETE - Removing Data

The DELETE statement removes records from a table. Be extremely careful with DELETE - always use a WHERE clause unless you intend to remove all data. Consider using soft deletes (setting a deleted_at timestamp) for important data.

-- Delete specific rows
DELETE FROM users WHERE id = 'user-uuid-here';

-- Delete with condition
DELETE FROM users WHERE is_verified = false AND created_at < '2024-01-01';

-- Delete and return deleted rows
DELETE FROM users
WHERE email = 'test@example.com'
RETURNING id, email;

-- Delete all rows (use with caution!)
DELETE FROM users;

-- Truncate table (faster for deleting all rows)
TRUNCATE TABLE users CASCADE;

4. Filtering and Operators

SQL provides powerful operators for filtering data. Understanding these operators allows you to write precise queries that return exactly the data you need.

Comparison Operators

Comparison operators evaluate conditions and return matching rows. These operators form the basis of most WHERE clauses.

-- Equality
SELECT * FROM users WHERE status = 'active';

-- Not equal
SELECT * FROM users WHERE status != 'suspended';
SELECT * FROM users WHERE status <> 'suspended';

-- Comparison
SELECT * FROM transactions WHERE amount > 1000;
SELECT * FROM transactions WHERE amount >= 1000;
SELECT * FROM transactions WHERE amount < 500;
SELECT * FROM transactions WHERE amount <= 500;

-- Between (inclusive)
SELECT * FROM transactions
WHERE amount BETWEEN 1000 AND 5000;

-- IN operator
SELECT * FROM users WHERE status IN ('active', 'pending');

-- NOT IN
SELECT * FROM users WHERE status NOT IN ('suspended', 'deleted');

-- NULL checks
SELECT * FROM users WHERE phone IS NULL;
SELECT * FROM users WHERE phone IS NOT NULL;

Logical Operators

Logical operators combine multiple conditions. Use parentheses to control the order of evaluation and make complex queries readable.

-- AND
SELECT * FROM users
WHERE is_verified = true AND is_active = true;

-- OR
SELECT * FROM transactions
WHERE type = 'airtime' OR type = 'data';

-- NOT
SELECT * FROM users WHERE NOT is_verified;

-- Complex conditions with parentheses
SELECT * FROM transactions
WHERE (type = 'airtime' OR type = 'data')
  AND amount > 500
  AND status = 'completed';

Text Operators

Text operators enable pattern matching and string comparisons. PostgreSQL offers both standard SQL LIKE and the case-insensitive ILIKE extension.

-- LIKE (case-sensitive)
SELECT * FROM users WHERE email LIKE '%@gmail.com';
SELECT * FROM users WHERE full_name LIKE 'John%';  -- Starts with John
SELECT * FROM users WHERE phone LIKE '%1234%';     -- Contains 1234

-- ILIKE (case-insensitive, PostgreSQL)
SELECT * FROM users WHERE full_name ILIKE '%john%';

-- Regular expressions (PostgreSQL)
SELECT * FROM users WHERE email ~ '^[a-z]+@';      -- Regex match
SELECT * FROM users WHERE email ~* '^[a-z]+@';     -- Case-insensitive regex

5. Joins

Joins are essential for working with relational data spread across multiple tables. They allow you to combine rows from two or more tables based on related columns.

Understanding Joins

Joins combine rows from two or more tables based on related columns. The type of join determines which rows are included in the result.

Types of Joins

Each join type serves a different purpose. INNER JOIN returns only matching rows, while LEFT JOIN includes all rows from the left table even when there is no match in the right table.

-- Sample tables for examples
-- users: id, email, full_name
-- wallets: id, user_id, balance
-- transactions: id, wallet_id, amount, type

-- INNER JOIN: Only matching rows from both tables
SELECT
  u.full_name,
  u.email,
  w.balance
FROM users u
INNER JOIN wallets w ON u.id = w.user_id;

-- LEFT JOIN: All rows from left table, matching from right
SELECT
  u.full_name,
  u.email,
  COALESCE(w.balance, 0) as balance
FROM users u
LEFT JOIN wallets w ON u.id = w.user_id;

-- RIGHT JOIN: All rows from right table, matching from left
SELECT
  u.full_name,
  w.balance
FROM users u
RIGHT JOIN wallets w ON u.id = w.user_id;

-- FULL OUTER JOIN: All rows from both tables
SELECT
  u.full_name,
  w.balance
FROM users u
FULL OUTER JOIN wallets w ON u.id = w.user_id;

Multi-Table Joins

Real-world queries often need to join more than two tables. You can chain multiple JOINs together to traverse relationships through your data model.

-- Join multiple tables
SELECT
  u.full_name,
  u.email,
  w.balance,
  t.amount,
  t.type,
  t.created_at as transaction_date
FROM users u
INNER JOIN wallets w ON u.id = w.user_id
INNER JOIN transactions t ON w.id = t.wallet_id
WHERE t.status = 'completed'
ORDER BY t.created_at DESC
LIMIT 10;

-- Self join (e.g., referral system)
SELECT
  u.full_name as user_name,
  r.full_name as referred_by
FROM users u
LEFT JOIN users r ON u.referred_by = r.id;

6. Grouping and Aggregation

Aggregation functions compute values across sets of rows. Combined with GROUP BY, they enable powerful analytical queries for reporting and dashboards.

GROUP BY

GROUP BY collects rows into groups based on column values, then applies aggregate functions to each group. This is fundamental for creating summary reports.

-- Count transactions by type
SELECT
  type,
  COUNT(*) as transaction_count,
  SUM(amount) as total_amount
FROM transactions
GROUP BY type;

-- Count users by verification status
SELECT
  is_verified,
  COUNT(*) as user_count
FROM users
GROUP BY is_verified;

-- Group by multiple columns
SELECT
  type,
  status,
  COUNT(*) as count,
  SUM(amount) as total
FROM transactions
GROUP BY type, status
ORDER BY type, status;

HAVING (Filter Groups)

HAVING filters groups after aggregation, similar to how WHERE filters rows before grouping. Use HAVING when you need to filter based on aggregate function results.

-- Find transaction types with total > 10000
SELECT
  type,
  SUM(amount) as total_amount
FROM transactions
GROUP BY type
HAVING SUM(amount) > 10000;

-- Find users with multiple wallets
SELECT
  user_id,
  COUNT(*) as wallet_count
FROM wallets
GROUP BY user_id
HAVING COUNT(*) > 1;

Common Aggregate Functions

These aggregate functions perform calculations across a set of rows. The ROUND function is often used with AVG to control decimal precision in results.

SELECT
  COUNT(*) as total_rows,
  COUNT(DISTINCT user_id) as unique_users,
  SUM(amount) as total_amount,
  AVG(amount) as average_amount,
  MIN(amount) as min_amount,
  MAX(amount) as max_amount,
  ROUND(AVG(amount), 2) as rounded_avg
FROM transactions
WHERE status = 'completed';

7. Subqueries

Subqueries are queries nested inside other queries. They enable complex data retrieval patterns that would be difficult or impossible with simple queries.

Subquery in WHERE

Subqueries in WHERE clauses filter results based on data from another query. The subquery executes first, and its results are used by the outer query.

-- Find users who have made transactions
SELECT * FROM users
WHERE id IN (
  SELECT DISTINCT user_id
  FROM wallets w
  INNER JOIN transactions t ON w.id = t.wallet_id
);

-- Find users with balance above average
SELECT * FROM wallets
WHERE balance > (SELECT AVG(balance) FROM wallets);

Subquery in FROM

Subqueries in the FROM clause create derived tables that can be queried like regular tables. This pattern is useful for pre-aggregating data before joining with other tables.

-- Get transaction statistics per user
SELECT
  user_stats.user_id,
  u.full_name,
  user_stats.total_transactions,
  user_stats.total_amount
FROM (
  SELECT
    w.user_id,
    COUNT(*) as total_transactions,
    SUM(t.amount) as total_amount
  FROM wallets w
  INNER JOIN transactions t ON w.id = t.wallet_id
  GROUP BY w.user_id
) user_stats
INNER JOIN users u ON user_stats.user_id = u.id;

Common Table Expressions (CTE)

CTEs provide a more readable alternative to subqueries by naming intermediate result sets. They make complex queries easier to understand and maintain.

-- More readable than subqueries
WITH user_transactions AS (
  SELECT
    w.user_id,
    COUNT(*) as tx_count,
    SUM(t.amount) as total_amount
  FROM wallets w
  INNER JOIN transactions t ON w.id = t.wallet_id
  WHERE t.status = 'completed'
  GROUP BY w.user_id
)
SELECT
  u.full_name,
  u.email,
  ut.tx_count,
  ut.total_amount
FROM users u
INNER JOIN user_transactions ut ON u.id = ut.user_id
ORDER BY ut.total_amount DESC
LIMIT 10;

8. PostgreSQL-Specific Features

PostgreSQL extends standard SQL with powerful features like JSONB, arrays, and advanced date/time functions. These features make it ideal for modern applications.

JSONB Operations

JSONB (binary JSON) allows you to store and query semi-structured data directly in PostgreSQL. This is useful for flexible schemas and storing metadata.

-- Create table with JSONB column
CREATE TABLE products (
  id UUID PRIMARY KEY DEFAULT gen_random_uuid(),
  name VARCHAR(100),
  metadata JSONB
);

-- Insert JSON data
INSERT INTO products (name, metadata)
VALUES (
  'MTN Airtime',
  '{"provider": "MTN", "category": "airtime", "denominations": [100, 200, 500, 1000]}'
);

-- Query JSON fields
SELECT
  name,
  metadata->>'provider' as provider,
  metadata->>'category' as category
FROM products;

-- Filter by JSON field
SELECT * FROM products
WHERE metadata->>'provider' = 'MTN';

-- Check if JSON array contains value
SELECT * FROM products
WHERE metadata->'denominations' @> '[500]';

-- Update JSON field
UPDATE products
SET metadata = jsonb_set(metadata, '{category}', '"telecom"')
WHERE name = 'MTN Airtime';

Array Operations

PostgreSQL supports native array types, allowing you to store multiple values in a single column. This is useful for tags, categories, or any multi-valued attribute.

-- Create table with array column
CREATE TABLE user_preferences (
  user_id UUID REFERENCES users(id),
  favorite_services TEXT[]
);

-- Insert array data
INSERT INTO user_preferences (user_id, favorite_services)
VALUES ('user-uuid', ARRAY['airtime', 'data', 'electricity']);

-- Query array contains
SELECT * FROM user_preferences
WHERE 'airtime' = ANY(favorite_services);

-- Array operators
SELECT * FROM user_preferences
WHERE favorite_services @> ARRAY['airtime', 'data'];  -- Contains all

Date/Time Functions

PostgreSQL provides comprehensive date/time functions for working with temporal data. These functions are essential for reporting, scheduling, and time-based queries.

-- Current timestamp
SELECT NOW(), CURRENT_TIMESTAMP, CURRENT_DATE;

-- Date arithmetic
SELECT
  created_at,
  created_at + INTERVAL '30 days' as expires_at,
  created_at - INTERVAL '1 hour' as one_hour_before
FROM users;

-- Extract parts
SELECT
  created_at,
  EXTRACT(YEAR FROM created_at) as year,
  EXTRACT(MONTH FROM created_at) as month,
  EXTRACT(DOW FROM created_at) as day_of_week
FROM transactions;

-- Date formatting
SELECT
  created_at,
  TO_CHAR(created_at, 'YYYY-MM-DD') as date_str,
  TO_CHAR(created_at, 'HH24:MI:SS') as time_str
FROM transactions;

-- Date truncation (for grouping)
SELECT
  DATE_TRUNC('month', created_at) as month,
  COUNT(*) as transaction_count
FROM transactions
GROUP BY DATE_TRUNC('month', created_at)
ORDER BY month;

9. Indexes for Performance

Indexes are database structures that speed up data retrieval. They are essential for query performance but come with trade-offs in storage space and write performance.

Creating Indexes

Indexes should be created on columns frequently used in WHERE clauses, JOIN conditions, and ORDER BY clauses. The following examples show different types of indexes for various use cases.

-- Simple index
CREATE INDEX idx_users_email ON users(email);

-- Unique index
CREATE UNIQUE INDEX idx_users_phone ON users(phone);

-- Composite index
CREATE INDEX idx_transactions_wallet_date
ON transactions(wallet_id, created_at DESC);

-- Partial index (only index certain rows)
CREATE INDEX idx_active_users
ON users(email)
WHERE is_active = true;

-- Index for JSONB queries
CREATE INDEX idx_products_provider
ON products USING GIN (metadata);

When to Use Indexes

Not all columns need indexes. Consider indexing based on your query patterns and the trade-off between read and write performance.

-- Good candidates for indexing:
-- 1. Primary keys (automatic)
-- 2. Foreign keys
-- 3. Columns used in WHERE clauses
-- 4. Columns used in JOIN conditions
-- 5. Columns used in ORDER BY

-- Check if index is being used
EXPLAIN ANALYZE
SELECT * FROM users WHERE email = 'john@example.com';

10. Transactions

Database transactions ensure data integrity by grouping multiple operations into a single unit of work. Understanding transactions is crucial for building reliable applications.

ACID Properties

ACID properties guarantee reliable processing of database transactions:

Atomicity - All operations succeed or all fail
Consistency - Database remains in valid state
Isolation - Concurrent transactions don't interfere
Durability - Committed changes are permanent

Using Transactions

Transactions wrap multiple SQL statements so they either all succeed or all fail together. This is essential for operations that modify multiple related tables, such as transferring money between wallets.

-- Begin a transaction
BEGIN;

-- Debit from one wallet
UPDATE wallets
SET balance = balance - 1000
WHERE user_id = 'sender-uuid'
  AND balance >= 1000;

-- Credit to another wallet
UPDATE wallets
SET balance = balance + 1000
WHERE user_id = 'receiver-uuid';

-- Record the transaction
INSERT INTO transactions (wallet_id, amount, type, status)
VALUES ('wallet-uuid', 1000, 'transfer', 'completed');

-- If all successful, commit
COMMIT;

-- If any error, rollback
ROLLBACK;

Additional Resources

Next Lesson: Supabase Setup & Configuration