Pre-Reqs

I'll be using PostgreSQL through all the examples.

In addition, I'll use pgcli for working with the database.

-- Create the database
CREATE DATABASE bookstore;

-- Connect to the database (only pgcli)
USE bookstore;

-- Create the authors table
CREATE TABLE authors (
    author_id SERIAL PRIMARY KEY,
    author_name VARCHAR(100) NOT NULL
);

-- Create the genres table
CREATE TABLE genres (
    genre_id SERIAL PRIMARY KEY,
    genre_name VARCHAR(50) NOT NULL
);

-- Create the publishers table
CREATE TABLE publishers (
    publisher_id SERIAL PRIMARY KEY,
    publisher_name VARCHAR(100) NOT NULL
);

-- Create the languages table
CREATE TABLE languages (
    language_id SERIAL PRIMARY KEY,
    language_name VARCHAR(50) NOT NULL
);

-- Create the formats table
CREATE TABLE formats (
    format_id SERIAL PRIMARY KEY,
    format_name VARCHAR(50) NOT NULL
);

-- Create the books table
CREATE TABLE books (
    book_id SERIAL PRIMARY KEY,
    title VARCHAR(255) NOT NULL,
    author_id INT REFERENCES authors(author_id),
    genre_id INT REFERENCES genres(genre_id),
    publisher_id INT REFERENCES publishers(publisher_id),
    language_id INT REFERENCES languages(language_id),
    format_id INT REFERENCES formats(format_id),
    publication_year INT,
    price DECIMAL(10, 2) NOT NULL
);

-- Create the customers table
CREATE TABLE customers (
    customer_id SERIAL PRIMARY KEY,
    customer_name VARCHAR(100) NOT NULL,
    email VARCHAR(100) UNIQUE NOT NULL,
    address VARCHAR(255) NOT NULL
);

-- Create the orders table
CREATE TABLE orders (
    order_id SERIAL PRIMARY KEY,
    customer_id INT REFERENCES customers(customer_id),
    order_date DATE NOT NULL
);

-- Create the order_items table
CREATE TABLE order_items (
    order_item_id SERIAL PRIMARY KEY,
    order_id INT REFERENCES orders(order_id),
    book_id INT REFERENCES books(book_id),
    quantity INT NOT NULL,
    total_price DECIMAL(10, 2) NOT NULL
);

-- Create the authors table
CREATE TABLE authors (
    author_id INTEGER PRIMARY KEY,
    author_name TEXT NOT NULL
);

-- Create the genres table
CREATE TABLE genres (
    genre_id INTEGER PRIMARY KEY,
    genre_name TEXT NOT NULL
);

-- Create the publishers table
CREATE TABLE publishers (
    publisher_id INTEGER PRIMARY KEY,
    publisher_name TEXT NOT NULL
);

-- Create the languages table
CREATE TABLE languages (
    language_id INTEGER PRIMARY KEY,
    language_name TEXT NOT NULL
);

-- Create the formats table
CREATE TABLE formats (
    format_id INTEGER PRIMARY KEY,
    format_name TEXT NOT NULL
);

-- Create the books table
CREATE TABLE books (
    book_id INTEGER PRIMARY KEY,
    title TEXT NOT NULL,
    author_id INTEGER REFERENCES authors(author_id),
    genre_id INTEGER REFERENCES genres(genre_id),
    publisher_id INTEGER REFERENCES publishers(publisher_id),
    language_id INTEGER REFERENCES languages(language_id),
    format_id INTEGER REFERENCES formats(format_id),
    publication_year INTEGER,
    price DECIMAL(10, 2) NOT NULL
);

-- Create the customers table
CREATE TABLE customers (
    customer_id INTEGER PRIMARY KEY,
    customer_name TEXT NOT NULL,
    email TEXT UNIQUE NOT NULL,
    address TEXT NOT NULL
);

-- Create the orders table
CREATE TABLE orders (
    order_id INTEGER PRIMARY KEY,
    customer_id INTEGER REFERENCES customers(customer_id),
    order_date DATE NOT NULL
);

-- Create the order_items table
CREATE TABLE order_items (
    order_item_id INTEGER PRIMARY KEY,
    order_id INTEGER REFERENCES orders(order_id),
    book_id INTEGER REFERENCES books(book_id),
    quantity INTEGER NOT NULL,
    total_price DECIMAL(10, 2) NOT NULL
);

INSERT INTO authors (author_name) VALUES
    ('John Doe'),
    ('Jane Smith'),
    ('Michael Brown'),
    ('Emily Johnson'),
    ('Robert Davis'),
    ('Sarah White'),
    ('David Miller'),
    ('Olivia Wilson'),
    ('William Taylor'),
    ('Emma Anderson');

INSERT INTO genres (genre_name) VALUES
    ('Fiction'),
    ('Non-fiction'),
    ('Mystery'),
    ('Science Fiction'),
    ('Romance'),
    ('Thriller'),
    ('Biography'),
    ('Fantasy'),
    ('History'),
    ('Self-Help');

INSERT INTO publishers (publisher_name) VALUES
    ('ABC Publications'),
    ('XYZ Books'),
    ('123 Publishers'),
    ('Bestseller Press'),
    ('Epic Publishing'),
    ('Infinite Books'),
    ('Global Print'),
    ('New Horizon Publishers'),
    ('Silverleaf Publishing'),
    ('Golden Words');

INSERT INTO languages (language_name) VALUES
    ('English'),
    ('Spanish'),
    ('French'),
    ('German'),
    ('Italian'),
    ('Chinese'),
    ('Japanese'),
    ('Ukraine'),
    ('Arabic'),
    ('Portuguese');

INSERT INTO formats (format_name) VALUES
    ('Hardcover'),
    ('Paperback'),
    ('E-book'),
    ('Audiobook'),
    ('Magazine'),
    ('Audio CD'),
    ('Kindle'),
    ('Nook'),
    ('Large Print'),
    ('Interactive E-book');

INSERT INTO books (title, author_id, genre_id, publisher_id, language_id, format_id, publication_year, price) VALUES
   ('Book 1', 1, 1, 1, 1, 1, 2019, 24.99),
   ('Book 2', 2, 2, 2, 2, 2, 2020, 19.99),
   ('Book 3', 3, 3, 3, 3, 3, 2018, 29.99),
   ('The Adventure Begins', 4, 4, 4, 4, 1, 2021, 35.99),
   ('Echoes of Eternity', 5, 5, 5, 5, 2, 2017, 18.99),
   ('Whispers in the Wind', 6, 6, 6, 6, 3, 2022, 27.99),
   ('Hidden Secrets', 7, 7, 7, 7, 1, 2016, 22.99),
   ('Beyond the Stars', 8, 8, 8, 8, 2, 2015, 31.99),
   ('The Enchanted Garden', 9, 9, 9, 9, 3, 2023, 26.99),
   ('Mysteries Unveiled', 10, 10, 10, 10, 1, 2020, 29.99);

INSERT INTO customers (customer_name, email, address) VALUES
   ('Alice Johnson', 'alice@example.com', '123 Main St, Cityville'),
   ('Bob Williams', 'bob@example.com', '456 Oak St, Townsville'),
   ('Charlie Davis', 'charlie@example.com', '789 Pine St, Villagetown'),
   ('Diana Smith', 'diana@example.com', '567 Elm St, Hamletville'),
   ('Edward Brown', 'edward@example.com', '890 Birch St, Countryside'),
   ('Fiona Miller', 'fiona@example.com', '234 Cedar St, Suburbia'),
   ('George Wilson', 'george@example.com', '678 Maple St, Riverside'),
   ('Helen Taylor', 'helen@example.com', '901 Walnut St, Lakeside'),
   ('Ivan Martinez', 'ivan@example.com', '345 Pine St, Mountainside'),
   ('Jennifer Lee', 'jennifer@example.com', '678 Oak St, Hilltop');

INSERT INTO orders (customer_id, order_date) VALUES
   (1, '2024-01-09'),
   (2, '2024-01-10'),
   (3, '2024-01-11'),
   (4, '2024-01-12'),
   (5, '2024-01-13'),
   (6, '2024-01-14'),
   (7, '2024-01-15'),
   (8, '2024-01-16'),
   (9, '2024-01-17'),
   (10, '2024-01-18');

INSERT INTO order_items (order_id, book_id, quantity, total_price) VALUES
   (1, 1, 2, 49.98),
   (2, 2, 1, 19.99),
   (3, 3, 3, 89.97),
   (4, 4, 1, 35.99),
   (5, 5, 2, 37.98),
   (6, 6, 1, 27.99),
   (7, 7, 3, 68.97),
   (8, 8, 2, 63.98),
   (9, 9, 1, 26.99),
   (10, 10, 4, 119.96);

Database

Create database:

CREATE DATABASE bookstore;

Use database:

USE bookstore;

Drop database:

DROP DATABASE bookstore;

Comments

One line comment:

-- comment

Multi-line comment:

/* More
comments.
*/

Data Types

Numeric Types

INTEGER / INT:

CREATE TABLE example (
    id INTEGER,
    count INT
);

SERIAL (Auto-incrementing integer in PostgreSQL):

CREATE TABLE example (
    id SERIAL PRIMARY KEY
);

DECIMAL / NUMERIC:

CREATE TABLE example (
    price DECIMAL(10, 2),  -- 10 digits total, 2 after decimal
    amount NUMERIC(8, 4)
);

REAL / DOUBLE PRECISION:

CREATE TABLE example (
    temperature REAL,
    precision_value DOUBLE PRECISION
);

String Types

VARCHAR(n):

CREATE TABLE example (
    name VARCHAR(100)  -- Variable length, max 100 characters
);

CHAR(n):

CREATE TABLE example (
    code CHAR(5)  -- Fixed length, always 5 characters
);

TEXT:

CREATE TABLE example (
    description TEXT  -- Unlimited length
);

Date and Time Types

DATE:

CREATE TABLE example (
    birth_date DATE
);

TIME:

CREATE TABLE example (
    start_time TIME
);

TIMESTAMP:

CREATE TABLE example (
    created_at TIMESTAMP,
    updated_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP
);

TIMESTAMPTZ (PostgreSQL - with timezone):

CREATE TABLE example (
    event_time TIMESTAMPTZ
);

INTERVAL:

SELECT CURRENT_TIMESTAMP + INTERVAL '1 day' AS tomorrow;

Boolean Type

CREATE TABLE example (
    is_active BOOLEAN,
    is_published BOOLEAN DEFAULT TRUE
);

Array Type (PostgreSQL)

CREATE TABLE example (
    tags TEXT[],
    numbers INTEGER[]
);

INSERT INTO example VALUES 
    (ARRAY['fiction', 'adventure'], ARRAY[1, 2, 3]);

Querying the data

Retrieve the data

Column Aliases

Use aliases to rename columns in the result set using the AS keyword (or without it):

SELECT author_name AS name FROM authors;

Or without AS:

SELECT author_name name FROM authors;

+-------------+
| name        |
|-------------|
| John Doe    |
| Jane Smith  |
| Michael Brown |
+-------------+

Retrieve individual column:

SELECT author_name
FROM authors;

+----------------+
| author_name    |
|----------------|
| John Doe       |
| Jane Smith     |
| Michael Brown  |
| Emily Johnson  |
| Robert Davis   |
| Sarah White    |
| David Miller   |
| Olivia Wilson  |
| William Taylor |
| Emma Anderson  |
+----------------+

Retrieve multiple columns:

SELECT book_id, title, publication_year, price
FROM books;

+---------+----------------------+------------------+-------+
| book_id | title                | publication_year | price |
|---------+----------------------+------------------+-------|
| 1       | Book 1               | 2019             | 24.99 |
| 2       | Book 2               | 2020             | 19.99 |
| 3       | Book 3               | 2018             | 29.99 |
| 4       | The Adventure Begins | 2021             | 35.99 |
| 5       | Echoes of Eternity   | 2017             | 18.99 |
| 6       | Whispers in the Wind | 2022             | 27.99 |
| 7       | Hidden Secrets       | 2016             | 22.99 |
| 8       | Beyond the Stars     | 2015             | 31.99 |
| 9       | The Enchanted Garden | 2023             | 26.99 |
| 10      | Mysteries Unveiled   | 2020             | 29.99 |
+---------+----------------------+------------------+-------+

Retrieve all columns:

SELECT *
FROM books;

+---------+----------------------+-----------+----------+--------------+-------------+-----------+------------------+-------+
| book_id | title                | author_id | genre_id | publisher_id | language_id | format_id | publication_year | price |
|---------+----------------------+-----------+----------+--------------+-------------+-----------+------------------+-------|
| 1       | Book 1               | 1         | 1        | 1            | 1           | 1         | 2019             | 24.99 |
| 2       | Book 2               | 2         | 2        | 2            | 2           | 2         | 2020             | 19.99 |
| 3       | Book 3               | 3         | 3        | 3            | 3           | 3         | 2018             | 29.99 |
| 4       | The Adventure Begins | 4         | 4        | 4            | 4           | 1         | 2021             | 35.99 |
| 5       | Echoes of Eternity   | 5         | 5        | 5            | 5           | 2         | 2017             | 18.99 |
| 6       | Whispers in the Wind | 6         | 6        | 6            | 6           | 3         | 2022             | 27.99 |
| 7       | Hidden Secrets       | 7         | 7        | 7            | 7           | 1         | 2016             | 22.99 |
| 8       | Beyond the Stars     | 8         | 8        | 8            | 8           | 2         | 2015             | 31.99 |
| 9       | The Enchanted Garden | 9         | 9        | 9            | 9           | 3         | 2023             | 26.99 |
| 10      | Mysteries Unveiled   | 10        | 10       | 10           | 10          | 1         | 2020             | 29.99 |
+---------+----------------------+-----------+----------+--------------+-------------+-----------+------------------+-------+

Distinct rows

To retrieve distinct rows, use the DISTINCT keyword. This is useful when you want to eliminate duplicate values from the result set.

SELECT DISTINCT title
FROM books;

+----------------------+
| title                |
|----------------------|
| Echoes of Eternity   |
| The Adventure Begins |
| Beyond the Stars     |
| Mysteries Unveiled   |
| Book 1               |
| Book 3               |
| Hidden Secrets       |
| Whispers in the Wind |
| The Enchanted Garden |
| Book 2               |
+----------------------+

Note: DISTINCT can't be partial. It applies to all columns, not just to one it precedes.

Limit Rows

Limit results:

SELECT author_id, author_name
FROM authors
LIMIT 5;

+-----------+---------------+
| author_id | author_name   |
|-----------+---------------|
| 1         | John Doe      |
| 2         | Jane Smith    |
| 3         | Michael Brown |
| 4         | Emily Johnson |
| 5         | Robert Davis  |
+-----------+---------------+

Note: Other databases can use this syntax:

SELECT TOP 5 author_id, author_name,
FROM authors;

Offset

To skip a certain number of rows before starting to return rows from the query, you can use the OFFSET clause. This is useful for pagination.

SELECT author_id, author_name
FROM authors
LIMIT 3
OFFSET 3;

+-----------+---------------+
| author_id | author_name   |
|-----------+---------------|
| 4         | Emily Johnson |
| 5         | Robert Davis  |
| 6         | Sarah White   |
+-----------+---------------+

Sorting

To sort in ascending order, use the ASC keyword. By default, the sorting is in ascending order.

SELECT author_name
FROM authors
ORDER BY author_name;

+----------------+
| author_name    |
|----------------|
| David Miller   |
| Emily Johnson  |
| Emma Anderson  |
| Jane Smith     |
| John Doe       |
| Michael Brown  |
| Olivia Wilson  |
| Robert Davis   |
| Sarah White    |
| William Taylor |
+----------------+

To sort in descending order, use the DESC keyword.

SELECT author_name
FROM authors
ORDER BY author_name DESC;

+----------------+
| author_name    |
|----------------|
| William Taylor |
| Sarah White    |
| Robert Davis   |
| Olivia Wilson  |
| Michael Brown  |
| John Doe       |
| Jane Smith     |
| Emma Anderson  |
| Emily Johnson  |
| David Miller   |
+----------------+

Note: Important to note that the ORDER BY clause must be the last clause in the SQL statement. Also, you can use multiple columns in the ORDER BY clause to sort by multiple criteria.

Filtering

To filter the results based on specific conditions, you can use the WHERE clause. This allows you to specify criteria that must be met for a row to be included in the result set.

SELECT *
FROM authors
WHERE author_id = 2;

+-----------+-------------+
| author_id | author_name |
|-----------+-------------|
| 2         | Jane Smith  |
+-----------+-------------+

WHERE operators

You can use various operators in the WHERE clause to filter the results. Here is the list of operators you can use:

Examples:

Equal to (=):

SELECT * FROM books WHERE price = 24.99;

+---------+--------+-----------+----------+--------------+-------------+-----------+------------------+-------+
| book_id | title  | author_id | genre_id | publisher_id | language_id | format_id | publication_year | price |
|---------+--------+-----------+----------+--------------+-------------+-----------+------------------+-------|
| 1       | Book 1 | 1         | 1        | 1            | 1           | 1         | 2019             | 24.99 |
+---------+--------+-----------+----------+--------------+-------------+-----------+------------------+-------+

Not equal to (<> or !=):

SELECT * FROM books WHERE price <> 24.99;

Greater than (>):

SELECT title, price FROM books WHERE price > 25.00;

+----------------------+-------+
| title                | price |
|----------------------+-------+
| The Adventure Begins | 35.99 |
| Beyond the Stars     | 31.99 |
| Whispers in the Wind | 27.99 |
| Mysteries Unveiled   | 29.99 |
+----------------------+-------+

Less than (<):

SELECT title, price FROM books WHERE price < 25.00;

+-------------------+-------+
| title             | price |
|-------------------+-------+
| Book 2            | 19.99 |
| Echoes of Eternity| 18.99 |
| Book 1            | 24.99 |
| Hidden Secrets    | 22.99 |
+-------------------+-------+

BETWEEN:

SELECT title, price FROM books WHERE price BETWEEN 20.00 AND 30.00;

+----------------------+-------+
| title                | price |
|----------------------+-------+
| Book 1               | 24.99 |
| Book 3               | 29.99 |
| Whispers in the Wind | 27.99 |
| Hidden Secrets       | 22.99 |
| The Enchanted Garden | 26.99 |
| Mysteries Unveiled   | 29.99 |
+----------------------+-------+

LIKE (pattern matching):

Wildcards:

• % - matches any sequence of characters (zero or more)
• _ - matches any single character

SELECT title FROM books WHERE title LIKE 'Book%';

+--------+
| title  |
|--------+
| Book 1 |
| Book 2 |
| Book 3 |
+--------+

More LIKE examples:

-- Match titles starting with 'The'
SELECT title FROM books WHERE title LIKE 'The%';

+----------------------+
| title                |
|----------------------|
| The Adventure Begins |
| The Enchanted Garden |
+----------------------+

-- Match titles with exactly 5 characters followed by space and number
SELECT title FROM books WHERE title LIKE '_____ %';

-- Match titles containing 'Secret'
SELECT title FROM books WHERE title LIKE '%Secret%';

+---------------+
| title         |
|---------------|
| Hidden Secrets |
+---------------+

ILIKE (case-insensitive LIKE in PostgreSQL):

SELECT title FROM books WHERE title ILIKE 'the%';

IN (matches any value in a list):

SELECT title, price FROM books WHERE price IN (24.99, 29.99, 35.99);

+----------------------+-------+
| title                | price |
|----------------------+-------+
| Book 1               | 24.99 |
| Book 3               | 29.99 |
| The Adventure Begins | 35.99 |
| Mysteries Unveiled   | 29.99 |
+----------------------+-------+

IS NULL:

SELECT title, publication_year FROM books WHERE publication_year IS NULL;

IS NOT NULL:

SELECT title, publication_year FROM books WHERE publication_year IS NOT NULL;

AND (combines multiple conditions):

SELECT title, price, publication_year 
FROM books 
WHERE price > 25.00 AND publication_year > 2020;

+----------------------+-------+------------------+
| title                | price | publication_year |
|----------------------+-------+------------------+
| The Adventure Begins | 35.99 | 2021             |
| Whispers in the Wind | 27.99 | 2022             |
| The Enchanted Garden | 26.99 | 2023             |
+----------------------+-------+------------------+

OR (combines multiple conditions):

SELECT title, price FROM books WHERE price < 20.00 OR price > 30.00;

+----------------------+-------+
| title                | price |
|----------------------+-------+
| Book 2               | 19.99 |
| Echoes of Eternity   | 18.99 |
| The Adventure Begins | 35.99 |
| Beyond the Stars     | 31.99 |
+----------------------+-------+

NOT (negates a condition):

SELECT title, price FROM books WHERE NOT price > 25.00;

Data Manipulation Language (DML)

INSERT

Insert a single row:

INSERT INTO authors (author_name) VALUES ('New Author');

Insert multiple rows:

INSERT INTO genres (genre_name) VALUES
    ('Horror'),
    ('Comedy'),
    ('Drama');

Insert with SELECT:

INSERT INTO authors (author_name)
SELECT DISTINCT author_name FROM authors WHERE author_id > 5;

UPDATE

Update a single row:

UPDATE books
SET price = 30.00
WHERE book_id = 1;

Update multiple columns:

UPDATE books
SET price = 30.00, publication_year = 2024
WHERE book_id = 1;

Update with subquery:

UPDATE books
SET price = price * 1.1
WHERE author_id IN (SELECT author_id FROM authors WHERE author_name = 'John Doe');

DELETE

Delete specific rows:

DELETE FROM books WHERE book_id = 10;

Delete with subquery:

DELETE FROM books
WHERE author_id IN (
    SELECT author_id FROM authors WHERE author_name = 'John Doe'
);

Delete all rows (use with caution):

DELETE FROM books;

TRUNCATE

Removes all rows from a table (faster than DELETE, cannot be rolled back in some databases):

TRUNCATE TABLE books;

MERGE / UPSERT

Insert or update a row based on a condition. PostgreSQL uses ON CONFLICT:

INSERT INTO books (book_id, title, price)
VALUES (1, 'Updated Book Title', 29.99)
ON CONFLICT (book_id) 
DO UPDATE SET 
    title = EXCLUDED.title,
    price = EXCLUDED.price;

Insert or do nothing on conflict:

INSERT INTO customers (customer_id, customer_name, email, address)
VALUES (11, 'New Customer', 'new@example.com', '123 Street')
ON CONFLICT (customer_id) DO NOTHING;

Aggregate Functions

Aggregate functions perform calculations on a set of rows and return a single value.

COUNT

Counts the number of rows.

SELECT COUNT(*) AS total_books FROM books;

+-------------+
| total_books |
|-------------|
| 10          |
+-------------+

Count distinct values:

SELECT COUNT(DISTINCT author_id) AS unique_authors FROM books;

+----------------+
| unique_authors |
|----------------|
| 10             |
+----------------+

SUM

Calculates the sum of values in a column.

SELECT SUM(total_price) AS total_revenue FROM order_items;

+---------------+
| total_revenue |
|---------------|
| 599.78        |
+---------------+

AVG

Calculates the average of values in a column.

SELECT AVG(price) AS average_price FROM books;

+---------------+
| average_price |
|---------------|
| 26.99         |
+---------------+

MIN

Finds the minimum value in a column.

SELECT MIN(price) AS cheapest_book FROM books;

+---------------+
| cheapest_book |
|---------------|
| 18.99         |
+---------------+

MAX

Finds the maximum value in a column.

SELECT MAX(price) AS most_expensive_book FROM books;

+----------------------+
| most_expensive_book  |
|----------------------|
| 35.99                |
+----------------------+

GROUP BY

Groups rows that have the same values in specified columns.

SELECT genre_id, COUNT(*) AS book_count
FROM books
GROUP BY genre_id;

+----------+------------+
| genre_id | book_count |
|----------+------------|
| 1        | 1          |
| 2        | 1          |
| 3        | 1          |
| 4        | 1          |
| 5        | 1          |
| 6        | 1          |
| 7        | 1          |
| 8        | 1          |
| 9        | 1          |
| 10       | 1          |
+----------+------------+

Multiple columns:

SELECT author_id, genre_id, COUNT(*) AS book_count
FROM books
GROUP BY author_id, genre_id;

HAVING

Filters groups after GROUP BY. Similar to WHERE but for aggregated data.

SELECT genre_id, COUNT(*) AS book_count
FROM books
GROUP BY genre_id
HAVING COUNT(*) > 1;

+----------+------------+
| genre_id | book_count |
|----------+------------|
(empty result)

Example with aggregate function:

SELECT author_id, AVG(price) AS avg_price
FROM books
GROUP BY author_id
HAVING AVG(price) > 25.00;

+-----------+-----------+
| author_id | avg_price |
|-----------+-----------+
| 1        | 24.99     |
| 3        | 29.99     |
| 4        | 35.99     |
| 6        | 27.99     |
| 8        | 31.99     |
| 9        | 26.99     |
| 10       | 29.99     |
+-----------+-----------+

Joins

Joins allow you to combine rows from two or more tables based on a related column between them.

INNER JOIN

Returns only the rows that have matching values in both tables.

SELECT b.title, a.author_name, g.genre_name
FROM books b
INNER JOIN authors a ON b.author_id = a.author_id
INNER JOIN genres g ON b.genre_id = g.genre_id;

+----------------------+----------------+-------------+
| title                | author_name    | genre_name  |
|----------------------+----------------+-------------+
| Book 1               | John Doe       | Fiction     |
| Book 2               | Jane Smith     | Non-fiction |
| Book 3               | Michael Brown  | Mystery     |
| The Adventure Begins | Emily Johnson  | Science Fiction |
| Echoes of Eternity   | Robert Davis   | Romance     |
| Whispers in the Wind | Sarah White    | Thriller    |
| Hidden Secrets       | David Miller   | Biography   |
| Beyond the Stars     | Olivia Wilson  | Fantasy     |
| The Enchanted Garden | William Taylor | History     |
| Mysteries Unveiled   | Emma Anderson  | Self-Help   |
+----------------------+----------------+-------------+

LEFT JOIN (LEFT OUTER JOIN)

Returns all rows from the left table and the matched rows from the right table. If no match, NULL values are returned.

SELECT a.author_name, b.title
FROM authors a
LEFT JOIN books b ON a.author_id = b.author_id;

+----------------+----------------------+
| author_name    | title                |
|----------------+----------------------|
| John Doe       | Book 1               |
| Jane Smith     | Book 2               |
| Michael Brown  | Book 3               |
| Emily Johnson  | The Adventure Begins |
| Robert Davis   | Echoes of Eternity   |
| Sarah White    | Whispers in the Wind |
| David Miller   | Hidden Secrets       |
| Olivia Wilson  | Beyond the Stars    |
| William Taylor | The Enchanted Garden |
| Emma Anderson  | Mysteries Unveiled   |
+----------------+----------------------+

RIGHT JOIN (RIGHT OUTER JOIN)

Returns all rows from the right table and the matched rows from the left table. If no match, NULL values are returned.

SELECT b.title, c.customer_name
FROM books b
RIGHT JOIN order_items oi ON b.book_id = oi.book_id
RIGHT JOIN orders o ON oi.order_id = o.order_id
RIGHT JOIN customers c ON o.customer_id = c.customer_id;

FULL OUTER JOIN

Returns all rows when there is a match in either left or right table. If no match, NULL values are returned.

SELECT a.author_name, b.title
FROM authors a
FULL OUTER JOIN books b ON a.author_id = b.author_id;

CROSS JOIN

Returns the Cartesian product of the two tables (all possible combinations).

SELECT a.author_name, g.genre_name
FROM authors a
CROSS JOIN genres g
LIMIT 10;

+-------------+-------------+
| author_name | genre_name  |
|-------------+-------------+
| John Doe    | Fiction     |
| John Doe    | Non-fiction |
| John Doe    | Mystery     |
| John Doe    | Science Fiction |
| John Doe    | Romance     |
| John Doe    | Thriller    |
| John Doe    | Biography   |
| John Doe    | Fantasy     |
| John Doe    | History     |
| John Doe    | Self-Help   |
+-------------+-------------+

Self Join

A self join is a regular join where a table is joined with itself.

Table Aliases

Table aliases simplify queries and are required for self-joins:

SELECT a.author_name, b.title
FROM authors a
INNER JOIN books b ON a.author_id = b.author_id;

+-------------+----------------------+
| author_name | title                 |
|-------------+----------------------+
| John Doe    | Book 1               |
| Jane Smith  | Book 2               |
| Michael Brown | Book 3             |
+-------------+----------------------+

SELECT a1.author_name AS author1, a2.author_name AS author2
FROM authors a1
INNER JOIN authors a2 ON a1.author_id < a2.author_id
LIMIT 5;

+-------------+-------------+
| author1     | author2     |
|-------------+-------------+
| John Doe    | Jane Smith  |
| John Doe    | Michael Brown |
| John Doe    | Emily Johnson |
| John Doe    | Robert Davis |
| Jane Smith  | Michael Brown |
+-------------+-------------+

Subqueries

A subquery is a query nested inside another query.

Scalar Subquery

Returns a single value.

SELECT title, price,
       (SELECT AVG(price) FROM books) AS average_price
FROM books
WHERE price > (SELECT AVG(price) FROM books);

+----------------------+-------+---------------+
| title                | price | average_price |
|----------------------+-------+---------------+
| The Adventure Begins | 35.99 | 26.99         |
| Beyond the Stars     | 31.99 | 26.99         |
| Whispers in the Wind | 27.99 | 26.99         |
| Mysteries Unveiled   | 29.99 | 26.99         |
+----------------------+-------+---------------+

Correlated Subquery

A subquery that references columns from the outer query.

SELECT b.title, b.price,
       (SELECT COUNT(*) FROM order_items oi WHERE oi.book_id = b.book_id) AS order_count
FROM books b;

+----------------------+-------+-------------+
| title                | price | order_count |
|----------------------+-------+-------------+
| Book 1               | 24.99 | 1           |
| Book 2               | 19.99 | 1           |
| Book 3               | 29.99 | 1           |
| The Adventure Begins | 35.99 | 1           |
| Echoes of Eternity   | 18.99 | 1           |
| Whispers in the Wind | 27.99 | 1           |
| Hidden Secrets       | 22.99 | 1           |
| Beyond the Stars     | 31.99 | 1           |
| The Enchanted Garden | 26.99 | 1           |
| Mysteries Unveiled   | 29.99 | 1           |
+----------------------+-------+-------------+

Subquery with IN

SELECT title, price
FROM books
WHERE author_id IN (SELECT author_id FROM authors WHERE author_name LIKE '%Smith%');

+--------+-------+
| title  | price |
|--------+-------+
| Book 2 | 19.99 |
+--------+-------+

Subquery with EXISTS

SELECT a.author_name
FROM authors a
WHERE EXISTS (
    SELECT 1 FROM books b WHERE b.author_id = a.author_id
);

+----------------+
| author_name    |
|----------------|
| John Doe       |
| Jane Smith     |
| Michael Brown  |
| Emily Johnson  |
| Robert Davis   |
| Sarah White    |
| David Miller   |
| Olivia Wilson  |
| William Taylor |
| Emma Anderson  |
+----------------+

Set Operations

UNION

Combines results from two or more SELECT statements, removing duplicates.

SELECT author_name AS name FROM authors
UNION
SELECT customer_name AS name FROM customers;

+----------------+
| name           |
|----------------|
| Alice Johnson  |
| Bob Williams   |
| Charlie Davis  |
| David Miller   |
| Diana Smith    |
| Edward Brown   |
| Emily Johnson  |
| Emma Anderson  |
| Fiona Miller   |
| George Wilson  |
| Helen Taylor   |
| Ivan Martinez  |
| Jane Smith     |
| Jennifer Lee   |
| John Doe       |
| Michael Brown  |
| Olivia Wilson  |
| Robert Davis   |
| Sarah White    |
| William Taylor |
+----------------+

UNION ALL

Combines results from two or more SELECT statements, keeping all duplicates.

SELECT author_name AS name FROM authors
UNION ALL
SELECT customer_name AS name FROM customers;

INTERSECT

Returns rows that are common to both SELECT statements.

SELECT author_name FROM authors
WHERE author_name LIKE '%Smith%'
INTERSECT
SELECT customer_name FROM customers
WHERE customer_name LIKE '%Smith%';

+-------------+
| author_name |
|-------------|
| Jane Smith  |
+-------------+

EXCEPT

Returns rows from the first SELECT that are not in the second SELECT.

SELECT author_name FROM authors
EXCEPT
SELECT customer_name FROM customers;

Database Relationships

Database relationships define how tables are connected to each other. Understanding these relationships is crucial for designing effective databases.

One-to-One Relationship

A one-to-one relationship means each record in one table relates to exactly one record in another table.

Example: Each customer has one profile (extended customer information).

-- Create customer_profiles table for one-to-one relationship
CREATE TABLE customer_profiles (
    profile_id SERIAL PRIMARY KEY,
    customer_id INT UNIQUE NOT NULL REFERENCES customers(customer_id) ON DELETE CASCADE,
    phone_number VARCHAR(20),
    date_of_birth DATE,
    preferred_language VARCHAR(50)
);

Key characteristics:

• The foreign key has a UNIQUE constraint
• Each customer can have only one profile
• Each profile belongs to only one customer

Query example:

SELECT c.customer_name, cp.phone_number, cp.preferred_language
FROM customers c
INNER JOIN customer_profiles cp ON c.customer_id = cp.customer_id;

One-to-Many Relationship

A one-to-many relationship means one record in a table can relate to many records in another table.

Example: One author can write many books, but each book has one author.

-- This relationship already exists in our bookstore database
-- One author (authors table) can have many books (books table)
-- The books table has author_id as a foreign key

Key characteristics:

• The "many" side (books) contains the foreign key (author_id)
• The "one" side (authors) has the primary key
• No UNIQUE constraint on the foreign key

Query example:

SELECT a.author_name, COUNT(b.book_id) AS book_count
FROM authors a
LEFT JOIN books b ON a.author_id = b.author_id
GROUP BY a.author_id, a.author_name
ORDER BY book_count DESC;

+----------------+------------+
| author_name    | book_count |
|----------------+------------+
| John Doe       | 1          |
| Jane Smith     | 1          |
| Michael Brown  | 1          |
| Emily Johnson  | 1          |
| Robert Davis   | 1          |
| Sarah White    | 1          |
| David Miller   | 1          |
| Olivia Wilson  | 1          |
| William Taylor | 1          |
| Emma Anderson  | 1          |
+----------------+------------+

More examples from bookstore database:

• One customer can have many orders
• One order can have many order_items
• One genre can have many books
• One publisher can publish many books

-- One customer to many orders
SELECT c.customer_name, COUNT(o.order_id) AS order_count
FROM customers c
LEFT JOIN orders o ON c.customer_id = o.customer_id
GROUP BY c.customer_id, c.customer_name;

+----------------+-------------+
| customer_name  | order_count |
|----------------+-------------+
| Alice Johnson | 1           |
| Bob Williams  | 1           |
| Charlie Davis | 1           |
| Diana Smith   | 1           |
| Edward Brown  | 1           |
| Fiona Miller  | 1           |
| George Wilson | 1           |
| Helen Taylor  | 1           |
| Ivan Martinez | 1           |
| Jennifer Lee  | 1           |
+----------------+-------------+

Many-to-Many Relationship

A many-to-many relationship means records in one table can relate to many records in another table, and vice versa.

Example: Books can have multiple authors, and authors can write multiple books. This requires a junction/bridge table.

Creating a many-to-many relationship:

-- Create a junction table for books and authors (many-to-many)
CREATE TABLE book_authors (
    book_id INT REFERENCES books(book_id) ON DELETE CASCADE,
    author_id INT REFERENCES authors(author_id) ON DELETE CASCADE,
    PRIMARY KEY (book_id, author_id),
    contribution_type VARCHAR(50) -- e.g., 'Primary Author', 'Co-Author', 'Editor'
);

Key characteristics:

• Requires a junction table (book_authors)
• The junction table contains foreign keys to both tables
• Composite primary key on both foreign keys
• Can include additional columns specific to the relationship

Query example:

SELECT b.title, a.author_name, ba.contribution_type
FROM books b
INNER JOIN book_authors ba ON b.book_id = ba.book_id
INNER JOIN authors a ON ba.author_id = a.author_id
ORDER BY b.title;

Another example: Books and Tags (many-to-many):

-- Create tags table
CREATE TABLE tags (
    tag_id SERIAL PRIMARY KEY,
    tag_name VARCHAR(50) UNIQUE NOT NULL
);

-- Create junction table for books and tags
CREATE TABLE book_tags (
    book_id INT REFERENCES books(book_id) ON DELETE CASCADE,
    tag_id INT REFERENCES tags(tag_id) ON DELETE CASCADE,
    PRIMARY KEY (book_id, tag_id)
);

-- Insert sample tags
INSERT INTO tags (tag_name) VALUES
    ('Bestseller'),
    ('Award Winner'),
    ('New Release'),
    ('Classic'),
    ('Recommended');

-- Query books with their tags
SELECT b.title, t.tag_name
FROM books b
INNER JOIN book_tags bt ON b.book_id = bt.book_id
INNER JOIN tags t ON bt.tag_id = t.tag_id;

Self-Referencing Relationship

A self-referencing relationship is when a table references itself. This is useful for hierarchical data.

Example: Employees and their managers, or categories with subcategories.

-- Create a categories table with self-referencing relationship
CREATE TABLE categories (
    category_id SERIAL PRIMARY KEY,
    category_name VARCHAR(100) NOT NULL,
    parent_category_id INT REFERENCES categories(category_id) ON DELETE SET NULL
);

-- Insert sample data
INSERT INTO categories (category_name, parent_category_id) VALUES
    ('Fiction', NULL),
    ('Non-Fiction', NULL),
    ('Science Fiction', 1),  -- parent: Fiction
    ('Fantasy', 1),         -- parent: Fiction
    ('Biography', 2),       -- parent: Non-Fiction
    ('History', 2);         -- parent: Non-Fiction

-- Query with self-join to show hierarchy
SELECT 
    c.category_name AS category,
    p.category_name AS parent_category
FROM categories c
LEFT JOIN categories p ON c.parent_category_id = p.category_id;

+------------------+------------------+
| category         | parent_category  |
|------------------+------------------+
| Fiction          | NULL             |
| Non-Fiction      | NULL             |
| Science Fiction  | Fiction          |
| Fantasy          | Fiction          |
| Biography        | Non-Fiction      |
| History          | Non-Fiction      |
+------------------+------------------+

Foreign Key Constraints

Foreign keys enforce referential integrity. You can specify what happens when the referenced record is deleted or updated.

ON DELETE options:

-- CASCADE: Delete related records when parent is deleted
CREATE TABLE order_items (
    order_item_id SERIAL PRIMARY KEY,
    order_id INT REFERENCES orders(order_id) ON DELETE CASCADE,
    book_id INT REFERENCES books(book_id) ON DELETE CASCADE
);

-- SET NULL: Set foreign key to NULL when parent is deleted
CREATE TABLE book_reviews (
    review_id SERIAL PRIMARY KEY,
    book_id INT REFERENCES books(book_id) ON DELETE SET NULL,
    review_text TEXT
);

-- RESTRICT: Prevent deletion if related records exist (default)
CREATE TABLE book_editions (
    edition_id SERIAL PRIMARY KEY,
    book_id INT REFERENCES books(book_id) ON DELETE RESTRICT
);

-- NO ACTION: Similar to RESTRICT, but checked at end of transaction
CREATE TABLE book_copies (
    copy_id SERIAL PRIMARY KEY,
    book_id INT REFERENCES books(book_id) ON DELETE NO ACTION
);

ON UPDATE options:

-- CASCADE: Update foreign key when parent key is updated
CREATE TABLE book_chapters (
    chapter_id SERIAL PRIMARY KEY,
    book_id INT REFERENCES books(book_id) ON UPDATE CASCADE
);

Practical Examples from Bookstore Database

Current relationships in bookstore database:

1. One-to-Many:

   • authors → books (one author, many books)
   • genres → books (one genre, many books)
   • publishers → books (one publisher, many books)
   • customers → orders (one customer, many orders)
   • orders → order_items (one order, many items)
   • books → order_items (one book, many order items)

2. Many-to-Many (potential):

   • Books ↔ Authors (if a book can have multiple authors)
   • Books ↔ Tags (if books can have multiple tags)

Query to see all relationships:

-- Find all books with their related data (demonstrating multiple one-to-many relationships)
SELECT 
    b.title,
    a.author_name,
    g.genre_name,
    p.publisher_name,
    l.language_name,
    f.format_name,
    COUNT(oi.order_item_id) AS times_ordered
FROM books b
INNER JOIN authors a ON b.author_id = a.author_id
INNER JOIN genres g ON b.genre_id = g.genre_id
INNER JOIN publishers p ON b.publisher_id = p.publisher_id
INNER JOIN languages l ON b.language_id = l.language_id
INNER JOIN formats f ON b.format_id = f.format_id
LEFT JOIN order_items oi ON b.book_id = oi.book_id
GROUP BY b.book_id, b.title, a.author_name, g.genre_name, 
         p.publisher_name, l.language_name, f.format_name;

Constraints

PRIMARY KEY

CREATE TABLE products (
    product_id SERIAL PRIMARY KEY,
    product_name VARCHAR(255)
);

FOREIGN KEY

CREATE TABLE order_items (
    order_item_id SERIAL PRIMARY KEY,
    order_id INT REFERENCES orders(order_id),
    book_id INT REFERENCES books(book_id)
);

UNIQUE

CREATE TABLE customers (
    customer_id SERIAL PRIMARY KEY,
    email VARCHAR(100) UNIQUE NOT NULL
);

CHECK

CREATE TABLE books (
    book_id SERIAL PRIMARY KEY,
    price DECIMAL(10, 2) CHECK (price > 0)
);

NOT NULL

CREATE TABLE books (
    book_id SERIAL PRIMARY KEY,
    title VARCHAR(255) NOT NULL
);

DEFAULT

CREATE TABLE orders (
    order_id SERIAL PRIMARY KEY,
    order_date DATE DEFAULT CURRENT_DATE
);

Data Definition Language (DDL)

CREATE TABLE

CREATE TABLE products (
    product_id SERIAL PRIMARY KEY,
    product_name VARCHAR(255) NOT NULL,
    price DECIMAL(10, 2) NOT NULL,
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP
);

ALTER TABLE

Add a column:

ALTER TABLE books ADD COLUMN isbn VARCHAR(13);

Modify a column:

ALTER TABLE books ALTER COLUMN price TYPE DECIMAL(12, 2);

Drop a column:

ALTER TABLE books DROP COLUMN isbn;

Add a constraint:

ALTER TABLE books ADD CONSTRAINT check_price CHECK (price > 0);

DROP TABLE

DROP TABLE products;

Drop if exists:

DROP TABLE IF EXISTS products;

CREATE INDEX

Create a simple index:

CREATE INDEX idx_book_title ON books(title);

Create a unique index:

CREATE UNIQUE INDEX idx_customer_email ON customers(email);

Create a composite index:

CREATE INDEX idx_book_author_genre ON books(author_id, genre_id);

DROP INDEX

DROP INDEX idx_book_title;

CREATE VIEW

CREATE VIEW book_details AS
SELECT 
    b.title,
    a.author_name,
    g.genre_name,
    b.price,
    b.publication_year
FROM books b
INNER JOIN authors a ON b.author_id = a.author_id
INNER JOIN genres g ON b.genre_id = g.genre_id;

Query the view:

SELECT * FROM book_details WHERE price > 25.00;

DROP VIEW

DROP VIEW book_details;

ALTER VIEW

Replace a view definition:

CREATE OR REPLACE VIEW book_details AS
SELECT 
    b.title,
    a.author_name,
    g.genre_name,
    b.price,
    b.publication_year,
    COUNT(oi.order_item_id) AS times_ordered
FROM books b
INNER JOIN authors a ON b.author_id = a.author_id
INNER JOIN genres g ON b.genre_id = g.genre_id
LEFT JOIN order_items oi ON b.book_id = oi.book_id
GROUP BY b.book_id, b.title, a.author_name, g.genre_name, b.price, b.publication_year;

Materialized Views

Materialized views store the query result physically (PostgreSQL):

CREATE MATERIALIZED VIEW book_sales_summary AS
SELECT 
    b.title,
    COUNT(oi.order_item_id) AS total_orders,
    SUM(oi.total_price) AS total_revenue
FROM books b
LEFT JOIN order_items oi ON b.book_id = oi.book_id
GROUP BY b.book_id, b.title;

Query materialized view:

SELECT * FROM book_sales_summary;

Refresh materialized view:

REFRESH MATERIALIZED VIEW book_sales_summary;

Temporary Tables

Create a temporary table that exists only for the session:

CREATE TEMP TABLE temp_high_priced_books AS
SELECT * FROM books WHERE price > 30.00;

Query temporary table:

SELECT * FROM temp_high_priced_books;

Temporary table is automatically dropped when the session ends.

Advanced Features

CASE Statement

Simple CASE:

SELECT title, price,
    CASE 
        WHEN price < 20 THEN 'Cheap'
        WHEN price < 30 THEN 'Moderate'
        ELSE 'Expensive'
    END AS price_category
FROM books;

+----------------------+-------+----------------+
| title                | price | price_category |
|----------------------+-------+----------------+
| Book 1               | 24.99 | Moderate       |
| Book 2               | 19.99 | Cheap          |
| Book 3               | 29.99 | Moderate       |
| The Adventure Begins | 35.99 | Expensive      |
| Echoes of Eternity   | 18.99 | Cheap          |
| Whispers in the Wind | 27.99 | Moderate       |
| Hidden Secrets       | 22.99 | Moderate       |
| Beyond the Stars     | 31.99 | Expensive      |
| The Enchanted Garden | 26.99 | Moderate       |
| Mysteries Unveiled   | 29.99 | Moderate       |
+----------------------+-------+----------------+

Searched CASE:

SELECT title, price,
    CASE 
        WHEN price BETWEEN 0 AND 20 THEN 'Budget'
        WHEN price BETWEEN 20 AND 30 THEN 'Standard'
        WHEN price > 30 THEN 'Premium'
    END AS price_tier
FROM books;

COALESCE

Returns the first non-NULL value from a list of expressions.

SELECT title, COALESCE(publication_year, 2000) AS year FROM books;

NULLIF

Returns NULL if two expressions are equal, otherwise returns the first expression.

SELECT title, NULLIF(price, 0) AS adjusted_price FROM books;

Common Table Expressions (CTEs)

CTEs allow you to define temporary result sets that exist only for the duration of a query.

WITH expensive_books AS (
    SELECT book_id, title, price
    FROM books
    WHERE price > 30.00
)
SELECT * FROM expensive_books
ORDER BY price DESC;

+---------+----------------------+-------+
| book_id | title                | price |
|---------+----------------------+-------+
| 4       | The Adventure Begins | 35.99 |
| 8       | Beyond the Stars     | 31.99 |
+---------+----------------------+-------+

Multiple CTEs:

WITH author_stats AS (
    SELECT author_id, COUNT(*) AS book_count
    FROM books
    GROUP BY author_id
),
avg_books AS (
    SELECT AVG(book_count) AS avg_count
    FROM author_stats
)
SELECT a.author_name, ast.book_count
FROM authors a
INNER JOIN author_stats ast ON a.author_id = ast.author_id
CROSS JOIN avg_books ab
WHERE ast.book_count > ab.avg_count;

Recursive CTEs

Recursive CTEs are useful for hierarchical data:

WITH RECURSIVE category_hierarchy AS (
    -- Base case: top-level categories
    SELECT category_id, category_name, parent_category_id, 1 AS level
    FROM categories
    WHERE parent_category_id IS NULL
    
    UNION ALL
    
    -- Recursive case: child categories
    SELECT c.category_id, c.category_name, c.parent_category_id, ch.level + 1
    FROM categories c
    INNER JOIN category_hierarchy ch ON c.parent_category_id = ch.category_id
)
SELECT * FROM category_hierarchy ORDER BY level, category_name;

Window Functions

ROW_NUMBER

Assigns a unique sequential integer to each row.

SELECT 
    title,
    price,
    ROW_NUMBER() OVER (ORDER BY price DESC) AS price_rank
FROM books;

+----------------------+-------+------------+
| title                | price | price_rank |
|----------------------+-------+------------+
| The Adventure Begins | 35.99 | 1          |
| Beyond the Stars     | 31.99 | 2          |
| Book 3               | 29.99 | 3          |
| Mysteries Unveiled   | 29.99 | 4          |
| Whispers in the Wind | 27.99 | 5          |
| The Enchanted Garden | 26.99 | 6          |
| Book 1               | 24.99 | 7          |
| Hidden Secrets       | 22.99 | 8          |
| Book 2               | 19.99 | 9          |
| Echoes of Eternity   | 18.99 | 10         |
+----------------------+-------+------------+

RANK

Assigns ranks with gaps for ties.

SELECT 
    title,
    price,
    RANK() OVER (ORDER BY price DESC) AS price_rank
FROM books;

DENSE_RANK

Assigns ranks without gaps for ties.

SELECT 
    title,
    price,
    DENSE_RANK() OVER (ORDER BY price DESC) AS price_rank
FROM books;

PARTITION BY

SELECT 
    author_id,
    title,
    price,
    ROW_NUMBER() OVER (PARTITION BY author_id ORDER BY price DESC) AS rank_in_author
FROM books;

Aggregate Window Functions

SELECT 
    title,
    price,
    AVG(price) OVER () AS overall_avg,
    SUM(price) OVER (ORDER BY book_id) AS running_total
FROM books;

+----------------------+-------+--------------+---------------+
| title                | price | overall_avg   | running_total |
|----------------------+-------+--------------+---------------+
| Book 1               | 24.99 | 26.99        | 24.99         |
| Book 2               | 19.99 | 26.99        | 44.98         |
| Book 3               | 29.99 | 26.99        | 74.97         |
| The Adventure Begins | 35.99 | 26.99        | 110.96        |
| Echoes of Eternity   | 18.99 | 26.99        | 129.95        |
| Whispers in the Wind | 27.99 | 26.99        | 157.94        |
| Hidden Secrets       | 22.99 | 26.99        | 180.93        |
| Beyond the Stars     | 31.99 | 26.99        | 212.92        |
| The Enchanted Garden | 26.99 | 26.99        | 239.91        |
| Mysteries Unveiled   | 29.99 | 26.99        | 269.90        |
+----------------------+-------+--------------+---------------+

String Functions

CONCAT:

SELECT CONCAT(author_name, ' - ', 'Author') AS author_label FROM authors;

LENGTH:

SELECT title, LENGTH(title) AS title_length FROM books;

UPPER / LOWER:

SELECT UPPER(author_name) AS upper_name, LOWER(author_name) AS lower_name FROM authors;

SUBSTRING:

SELECT title, SUBSTRING(title, 1, 10) AS short_title FROM books;

TRIM:

SELECT TRIM('  Hello World  ') AS trimmed;

REPLACE:

SELECT REPLACE(title, 'Book', 'Novel') AS new_title FROM books;

Date Functions

CURRENT_DATE:

SELECT CURRENT_DATE AS today;

CURRENT_TIMESTAMP:

SELECT CURRENT_TIMESTAMP AS now;

EXTRACT:

SELECT order_date, EXTRACT(YEAR FROM order_date) AS order_year FROM orders;

DATE_PART (PostgreSQL):

SELECT order_date, DATE_PART('month', order_date) AS order_month FROM orders;

DATE_ADD / INTERVAL:

SELECT order_date, order_date + INTERVAL '30 days' AS delivery_date FROM orders;

Math Functions

ROUND:

SELECT price, ROUND(price) AS rounded_price FROM books;

CEIL / CEILING:

SELECT price, CEIL(price) AS ceiling_price FROM books;

FLOOR:

SELECT price, FLOOR(price) AS floor_price FROM books;

ABS:

SELECT ABS(-10) AS absolute_value;

POWER:

SELECT POWER(2, 3) AS result;

Type Conversion

CAST:

SELECT price, CAST(price AS INTEGER) AS price_int FROM books;

PostgreSQL shorthand (::):

SELECT price, price::INTEGER AS price_int FROM books;

TO_CHAR (PostgreSQL):

SELECT price, TO_CHAR(price, '$999.99') AS formatted_price FROM books;

TO_DATE:

SELECT TO_DATE('2024-01-15', 'YYYY-MM-DD') AS date_value;

TO_NUMBER:

SELECT TO_NUMBER('123.45', '999.99') AS number_value;

Transactions

Transactions ensure data integrity by grouping SQL statements into atomic operations.

BEGIN and COMMIT

Start a transaction and commit changes:

BEGIN;

INSERT INTO books (title, author_id, genre_id, publisher_id, language_id, format_id, publication_year, price)
VALUES ('New Book', 1, 1, 1, 1, 1, 2024, 25.99);

UPDATE books SET price = 30.00 WHERE title = 'New Book';

COMMIT;

ROLLBACK

Undo all changes in the current transaction:

BEGIN;

DELETE FROM books WHERE book_id = 1;

-- If something goes wrong, rollback
ROLLBACK;

SAVEPOINT

Create a savepoint to rollback to a specific point:

BEGIN;

INSERT INTO books (title, author_id, genre_id, publisher_id, language_id, format_id, publication_year, price)
VALUES ('Book A', 1, 1, 1, 1, 1, 2024, 20.00);

SAVEPOINT sp1;

INSERT INTO books (title, author_id, genre_id, publisher_id, language_id, format_id, publication_year, price)
VALUES ('Book B', 2, 2, 2, 2, 2, 2024, 25.00);

-- Rollback to savepoint (Book B is removed, Book A remains)
ROLLBACK TO SAVEPOINT sp1;

COMMIT;

Auto-commit

By default, each statement is automatically committed. Use BEGIN to start a transaction block.

Query Performance

EXPLAIN

View the query execution plan:

EXPLAIN SELECT * FROM books WHERE price > 25.00;

EXPLAIN ANALYZE

Execute the query and show actual execution statistics:

EXPLAIN ANALYZE SELECT * FROM books WHERE price > 25.00;

Index Usage

Indexes improve query performance. Use them on frequently queried columns:

-- Create index for faster lookups
CREATE INDEX idx_books_price ON books(price);

-- Composite index for multiple columns
CREATE INDEX idx_books_author_price ON books(author_id, price);