Bookstore Example

Intro

Throughout the Database tutorial series we built a simple database called bookstore, focusing on one topic for each tutorial including SQL syntax, data types, tables, modify data queries, read data queries, and joins.

We are using a Postgres database server with the PG Admin graphical interface.

Below are the steps taken to build the database without the detailed explanations around each step.

It is recommended that you follow the steps below to rebuild the database. Use the Database CheatSheet as a reference.

Repeat the process until you are comfortable with the steps and familiar with the CheatSheet.

Build the bookstore database

Plan your database

Name: bookstore
Schema (blueprint of tables, columns, associations):

authors: name, bio, date of birth
books: International Standard Book Number (isbn), title, author, description, quantity in stock, price, is it on sale?, published date.
Associations: one-to-many relationship. Authors is the parent table and books is the Child table in the association. Each book has exactly one author. Each author can have zero, one, or many books.

Create a database named bookstore

Open PG Admin > In the left panel drill into databases > Rt-click: Create database > Enter: bookstore

Tables

Review:

Syntax: semicolons, quotes, naming conventions, capitalization.
Data types.

Open the Query Tool: bookstore > Rt-click > Query Tool

-- Create an "authors" table:

CREATE TABLE authors (
  id INT GENERATED ALWAYS AS IDENTITY PRIMARY KEY,
    name VARCHAR(255),
    bio TEXT,
  date_of_birth DATE
);

-- Insert some rows:

INSERT INTO authors(name, bio, date_of_birth) 
VALUES('Joey R.', 'Lorem ipsum.', '1990-05-19'),
        ('Sheena R.', 'Lorem ipsum.', '1993-06-28');

-- Create a "books" table:

CREATE TABLE books (
  id INT GENERATED ALWAYS AS IDENTITY PRIMARY KEY,
    isbn CHAR(13),
    title VARCHAR(255),
    author VARCHAR(100),
    description TEXT,
    quantity INTEGER,
    price NUMERIC(5,2),
  on_sale BOOLEAN,
  published_date DATE,
    created_at TIMESTAMP
);

-- Insert a row:

INSERT INTO books(
    isbn, title, author, description, 
    quantity, price, on_sale, published_date
  ) 
VALUES(
    '1234567891011', 'Learn Databases', 'Joey R.', 'Lorem ipsum.', 
    47, 19.99, false, '2022-07-01'
  );

View the data: click on table name > click View Data icon

or Rt-click on table name > click View/Edit Data: All Rows

View table column properties: Rt-click on table name > properties > columns tab

DROP TABLE authors;
  DROP TABLE books;

Table Defaults and Constraints

Review:

Defaults and constraints (NOT NULL, UNIQUE, CHECK)
Primary key.

-- Recreate the "books" table with defaults and constraints:

CREATE TABLE books (
  id INT GENERATED ALWAYS AS IDENTITY PRIMARY KEY,
    isbn CHAR(13) NOT NULL UNIQUE,
    title VARCHAR(255) NOT NULL,
    author VARCHAR(100) NOT NULL,
    description TEXT,
    quantity INTEGER NOT NULL CHECK(quantity >= 0),
    price NUMERIC(5,2) NOT NULL CHECK(price >= 0),
  on_sale BOOLEAN DEFAULT false,
  published_date DATE,
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP
);

-- Fail:

INSERT INTO books(isbn, author, description, quantity, price, on_sale) 
VALUES('1234567891011', 'Joey R.', 'Lorem ipsum.', -3, 0, true);

-- Ok:

INSERT INTO books(isbn, title, author, description, quantity, price, published_date) 
VALUES('1234567891011', 'Learn Databases', 'Joey R.', 'Lorem ipsum.', 43, 19.99, '2022-07-01');

Index

CREATE UNIQUE INDEX books_index_isbn 
  ON books(isbn);

-- Fail (isbn not unique):

INSERT INTO books(isbn, title, author, description, quantity, price, on_sale, published_date)
VALUES('1234567891011', 'Learn Node.js', 'Sheena R.', 'Lorem ipsum.', 26, 24.99, false, '2022-05-01');

-- Ok:

INSERT INTO books(isbn, title, author, description, quantity, price, on_sale, published_date)
VALUES('1234567891012', 'Learn Node.js', 'Sheena R.', 'Lorem ipsum.', 26, 24.99, false, '2022-05-01');

-- Query on indexed field:

SELECT * FROM books WHERE isbn = '1234567891012';

Foreign Key

Associate the authors and book tables by setting a foreign key constraint on author_id
Parent > Child tables | Authors > Books

Create an "authors" table:

CREATE TABLE authors (
  id INT GENERATED ALWAYS AS IDENTITY PRIMARY KEY,
    name VARCHAR(255),
    bio TEXT,
  date_of_birth DATE
);

-- Insert some author rows:

INSERT INTO authors(name, bio, date_of_birth) 
VALUES('Joey R.', 'Lorem ipsum.', '1990-05-19'),
        ('Sheena R.', 'Lorem ipsum.', '1993-06-28');

Drop the books table:

DROP TABLE books;

Recreate the "books" table with foreign key constraint on author_id:

CREATE TABLE books (
  id INT GENERATED ALWAYS AS IDENTITY PRIMARY KEY,
    isbn CHAR(13) NOT NULL UNIQUE,
    title VARCHAR(255) NOT NULL,
    author_id INTEGER NOT NULL,
    description TEXT,
    quantity INTEGER NOT NULL CHECK(quantity >= 0),
    price NUMERIC(5,2) NOT NULL CHECK(price >= 0),
  on_sale BOOLEAN DEFAULT false,
  published_date DATE DEFAULT CURRENT_DATE,
    FOREIGN KEY (author_id) REFERENCES authors (id)
);

-- Insert some book rows:

INSERT INTO books(isbn, title, author_id, description, quantity, price, published_date) 
VALUES('1234567891011', 'Learn Databases', 1, 'Lorem ipsum.', 43, 19.99, '2022-07-01'),
  ('1234567891012', 'Learn Node.js', 2, 'Lorem ipsum.', 26, 24.99, '2022-05-01');

Fail (author id doesn't exist)

INSERT INTO books(isbn, title, author_id, description, quantity, price, published_date) 
VALUES('1234567891013', 'Learn JavaScript', 3, 'Lorem ipsum.', 18, 23.99, '2022-08-01');

Delete author fail due to foreign key constraint (author has a book):

DELETE FROM authors WHERE id = 1;

Foreign Key Constraints: On Delete actions

Default constraint is to prevent author from being deleted if there are one or more books associated with that author. You can change this behavior by appending:

To delete associated books: ON DELETE CASCADE
To set associated book author_id values to null: ON DELETE SET NULL

In the CREATE TABLE statement, the foreign key line would be:

FOREIGN KEY (author_id) REFERENCES authors (id) ON DELETE CASCADE

CRUD Operations

Modify Data

Insert Rows

-- Insert single author row:

INSERT INTO authors(name, bio, date_of_birth)
VALUES ('Johnny R.', 'Lorem ipsum', '1992-07-14');

-- Multiple rows:

INSERT INTO authors(name, bio, date_of_birth)
VALUES ('DeeDee R.', 'Lorem ipsum', '1982-02-12'),
   ('Judy R.', 'Blah blah', '1996-09-27');

-- Insert multiple rows in the books table.

INSERT INTO books(isbn, title, author_id, description, quantity, price) 
VALUES ('1234567891013', 'Learn SQL', 1, 'Blah blah.', 162, 34.98),
  ('1234567891014', 'Learn Postgres', 1, 'Blah blah.', 32, 39.98),
  ('1234567891015', 'Learn MySQL', 2, 'Blah blah.', 28, 34.98),
  ('1234567891016', 'Learn JavaScript', 2, 'Lorem ipsum.', 0, 24.98),
  ('1234567891017', 'Learn HTML', 2, 'Blah blah.', 17, 44.98),
  ('1234567891018', 'Learn CSS', 1, 'Blah blah.', 75, 24.98),
  ('1234567891019', 'Learn Bootstrap', 3, 'Lorem ipsum.', 159, 34.98),
  ('1234567891020', 'Learn Unix/Linux', 3, 'Blah blah.', 0, 37.98),
  ('1234567891021', 'Learn Git', 1, 'Lorem ipsum.', 84, 28.98),
    ('1234567891022', 'Learn Pascal', 4, 'Lorem ipsum.', 84, 5.98),
    ('1234567891023', 'Learn Cobol', 4, 'Lorem ipsum.', 84, 7.98),
    ('1234567891024', 'Learn Objective C', 4, 'Lorem ipsum.', 84, 12.98)
    ('1234567891025', 'Sqlite Database Fundamentals', 3, 'Blah blah.', 57, 49.98);

Update Rows

-- Update row. Find by id.

UPDATE books SET title = 'Learn Relational Databases'
WHERE id = 1;

-- Update row. Find by isbn.

UPDATE books SET title = 'Learn Node'
WHERE isbn = '1234567891012';

-- Update multiple columns

UPDATE books SET on_sale = true, price = price * 0.8
WHERE author_id = 4;

-- Multiple conditions (can use AND, OR, NOT)

UPDATE books SET on_sale = true, price = price * 0.8
WHERE quantity >= 50 AND price >= 25.00;

Delete Rows

-- Delete row. Find by id.

DELETE FROM books
WHERE id = 9;

-- Delete multiple rows that meet specific WHERE clause condition

DELETE FROM books
where author_id = 4;

Read Data

Query all or specific columns

-- Select all columns by using wildcard. Select all rows by not having a where clause.

SELECT * FROM books;

-- Specify columns. Select all rows by not having a where clause.

SELECT id, title, quantity, price FROM books;

-- Eliminate duplicates with DISTINCT.

 SELECT DISTINCT author_id FROM books;

Add Conditions with the WHERE clause

-- Select specific row id:

SELECT id, title, quantity, price FROM books
  WHERE id = 3;

-- Select rows that meet a WHERE clause condition:

SELECT id, title, quantity, price FROM books
WHERE on_sale = true;

-- Select rows that meet multiple WHERE clause conditions:

SELECT id, title, quantity, price FROM books
WHERE quantity >= 27 AND price >= 30;

SELECT id, title, quantity, price FROM books
WHERE quantity >= 27 OR price >= 30;

ORDER BY

SELECT id, title, quantity, price FROM books
ORDER BY price ASC;

-- Order by multiple columns: order by first column, then second.

SELECT id, title, quantity, price FROM books
ORDER BY author_id DESC, title ASC;

LIMIT

-- Use LIMIT with ORDER BY otherwise the returned rows will be random.

SELECT id, title, quantity, price FROM books
ORDER BY title
LIMIT 5;

-- With Offset (to get rows 6-10, offset 5):

SELECT id, title, quantity, price FROM books
ORDER BY title
LIMIT 5 OFFSET 5;

Aggregation (Count, Avg, Min/Max, Sum)

-- COUNT:

SELECT COUNT(*) FROM books;

-- With condition:

SELECT COUNT(*) FROM books
WHERE quantity = 0;

-- Get average price

SELECT AVG(price) FROM books;

-- Get lowest price

SELECT MIN(price) FROM books;

-- Get highest price

SELECT MAX(price) FROM books;

-- Get total quantity of books

SELECT SUM(quantity) FROM books;

LIKE

-- The LIKE operator in the WHERE clause allows you to match a pattern.
-- % before a pattern: any characters can come before it.
-- % after a pattern: any characters can come after it.

SELECT id, title, quantity, price FROM books
WHERE title LIKE '%SQL%';

-- Case insensitive: use ILIKE

SELECT id, title, quantity, price FROM books
WHERE title ILIKE '%SQL%';

-- NOT

SELECT id, title, quantity, price FROM books
WHERE NOT title ILIKE '%SQL%';

-- Multiple

SELECT id, title, quantity, price FROM books
WHERE title ILIKE '%SQL%' 
  OR title ILIKE '%database%'
  OR title ILIKE '%postgres%';

Joins

Joins are data queries on two (or more) tables. The tables are associated with each other though matches on a joining column.

Temporarily drop the foreign key and not null constraints on author_id, then set the first book's author_id to null.

ALTER TABLE books DROP CONSTRAINT books_author_id_fkey;
ALTER TABLE books ALTER COLUMN author_id DROP NOT NULL; 
UPDATE books SET author_id = null WHERE id = 1;

Inner Join using a WHERE clause:

Get author name from associated author table. The tables are joined through author ID.
Only pulls rows with a valid author_id. Rows with null or invalid author_id values are ignored.

SELECT books.id AS book_id, books.title, authors.name AS author, books.quantity, books.price
  FROM books, authors
  WHERE books.author_id = authors.id
  ORDER BY book_id;

Inner Join using a JOIN clause:

Get author name from associated author table. Tables joined through author ID.
Only pulls rows with a valid author_id. Rows with null or invalid author_id values are ignored.

SELECT books.id AS book_id, books.title, authors.name AS author, books.quantity, books.price
  FROM books
  INNER JOIN authors 
    ON books.author_id = authors.id
  ORDER BY book_id;

Note: "INNER" is the default join type so it can be left out.

Left Outer Join:

Gets all rows from left table (authors). Authors that have no books will have null values for book title, quantity, and price.
Rows from right table (books) with no associated author (i.e., author_id is null or invalid) are ignored.

SELECT authors.name AS author, books.id AS book_id, books.title, books.quantity, books.price
FROM authors
LEFT OUTER JOIN books ON authors.id = books.author_id
ORDER BY author;

Right Outer Join:

Gets all rows from right table (books). Books that have no author_id value will have null values for author_id and name.
Rows from left table (authors) with no associated books are ignored.

SELECT authors.name AS author, books.id AS book_id, books.title, books.quantity, books.price
FROM authors
RIGHT OUTER JOIN books ON authors.id = books.author_id
ORDER BY author;

Full Outer Join:

Gets all rows from left table (authors). Authors that have no books will have null values for book title, quantity, and price.
Gets all rows from right table (books). Books that have no author_id value will have null values for author_id and name.

SELECT authors.name as author, books.id AS book_id, books.title, books.quantity, books.price
FROM books
FULL OUTER JOIN authors ON books.author_id = authors.id
ORDER BY author;

Restore the foreign key and not null constraints on author_id.

UPDATE books SET author_id = 1 WHERE id = 1;
ALTER TABLE books ALTER COLUMN author_id SET NOT NULL; 
ALTER TABLE books
    ADD CONSTRAINT books_author_id_fkey 
      FOREIGN KEY (author_id) 
      REFERENCES authors (id);