• This is the first in a series of tutorials on how to use a relational database.
• We are using Postgres, and it is assumed you have a Postgres database server installed on your computer, with the PG Admin graphical interface.
• In this tutorial we will: 
• Plan the database structure and create the schema.
• Create the database.
• Review SQL Syntax.
• Use the PG Admin tool to add tables and rows to the database.

• There is both a written and accompanying video version of this tutorial. There are timestamps under the headings that align with the video version of the topic.
• Read it: For each topic heading, First read the topic. 
• Watch it: Then watch the associated section of the video.
• Do it: Then follow the instructions to replicate the steps in your own version of the bookstore database.
• The Database CheatSheet has a major category that aligns with this tutorial.
• Review it: When you are done with the whole tutorial, open the CheatSheet and review the SQL category from this tutorial. Make sure you understand everything, and can refer back to the CheatSheet when you are working on your own projects in the future. 

Plan your database

• 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 the bookstore database

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

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

/* 
  This is a 
  multi-line comment. 
*/ 

Add tables to the database

1. Use PG Admin's Query Tool to execute the SQL statements.
2. Use PG Admin's GUI directly.
3. Use the PSQL Shell to enter the SQL statements.

1. Create table using the PG Admin Query Tool

• With PG Admin open, in the Browser side panel drill down to the bookstore database: Servers > PostgreSQL > Databases > bookstore. Then either:
• Right click on bookstore > Query Tool.
• Or click the Tools menu > Query Tool
• Or click the Query Tool toolbar: 

• Copy and Paste the SQL statements from the SQL Syntax section above, into the Query Tool's editor window.
• Then click the ▶ Execute toolbar.
• You can then view the new tables: bookstore > Schemas > public > Right click Tables > Refresh: You should then see the authors and books tables.
• To see the columns either: 
• Click the table name > Columns
• Or right click the table name > Properties > Columns tab.
• To see the rows we added either: 
• Right click the table name > View/Edit Data > All Rows. This will query the database and return all rows for that table. 
• Or click the table name > then click the View Data toolbar: 

2. Create table using the PG Admin GUI

• With PG Admin open, in the Browser column click: Servers > PostgreSQL > Databases > bookstore > Schemas > public > Right click on Tables > Create Table > Enter the table name and click the save button.
• Then add the first column: Click the table name > Right click Columns > Create Column > Enter column name > Definitions tab: Select data type > Constraints tab: optionally add a default value or Not NULL constraint > SQL tab: view the generated SQL statement. If satisfied click the Save button.
• Repeat above for each column.
  
  

3. Create table using the PSQL Tool

• With PG Admin open, in the Browser side panel drill down to the bookstore database: Servers > PostgreSQL > Databases > bookstore. Then either:
• Right click on bookstore > PSQL Tool.
• Or click the Tools menu > PSQL Tool
• Or click the PSQL Tool toolbar: