Cheatsheet: SQL

SQL

PostgreSQL

Published on Jan 9, 2024

10 MIN READ

Pre-Reqs
Database
Comments
Querying the data

I'll be using PostgreSQL through all the examples.

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

pgcli command list

Command	Description
\l	list all databases
use db-name, \c	use db-name
\d	list all tables
\d table	table info
\dt	list all tables
exit	close session
\i FILE	execute from the file
\! clear, \! cls	clear the screen

Note: In my pgcli config (~/.config/pgcli/config) I set multi_line to True.

Database with the tables (PostgreSQL)

-- 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
);

Database with the tables (SQLite version)

-- 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
);

Sample data

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);

Create database:

CREATE DATABASE authors;

Use database:

USE authors;

Drop database:

DROP DATABASE authors;

One line comment:

-- comment

Multi-line comment:

/* More
comments.
*/

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 |
+---------+----------------------+-----------+----------+--------------+-------------+-----------+------------------+-------+

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 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;

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   |
+-----------+---------------+

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.

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  |
+-----------+-------------+

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

Operator	Description
=	Equal to
<> or !=	Not equal to
>	Greater than
<	Less than
>=	Greater than or equal to
<=	Less than or equal to
BETWEEN	Between two values
LIKE	Pattern matching
IN	Matches any value in a list
IS NULL	Checks for NULL values
IS NOT NULL	Checks for non-NULL values
AND	Combines multiple conditions (all must be true)
OR	Combines multiple conditions (any must be true)
NOT	Negates a condition
EXISTS	Checks for the existence of rows in a subquery
ANY	Compares a value to any value in a subquery
ALL	Compares a value to all values in a subquery
SOME	Synonym for ANY

Table of contents

Pre-Reqs

Database

Comments

Querying the data

Retrieve the data

Distinct rows

Limit Rows

Offset

Sorting

Filtering

WHERE operators