CREATE TABLE with PostgreSQL types - Mini Project: Build & Apply

Create a PostgreSQL Table with Various Data Types

📖 Scenario: You are setting up a database for a small bookstore. You need to create a table to store information about books, including their title, author, price, publication date, and whether they are currently in stock.

🎯 Goal: Create a PostgreSQL table named books with appropriate columns and data types to store the bookstore's book information.

📋 What You'll Learn

Create a table named books

Include a column id as a primary key with type SERIAL

Include a column title with type VARCHAR(100)

Include a column author with type VARCHAR(50)

Include a column price with type NUMERIC(6,2)

Include a column published_date with type DATE

Include a column in_stock with type BOOLEAN

💡 Why This Matters

🌍 Real World

Creating tables with correct data types is essential for storing and organizing data efficiently in real-world applications like bookstores, libraries, or inventory systems.

💼 Career

Database developers and administrators frequently create tables with appropriate data types to ensure data integrity and optimize storage.

1

Create the basic table structure

Write a CREATE TABLE statement to create a table named books with a column id of type SERIAL as the primary key.

PostgreSQL

-- Your code here to create table books with id column

Need a hint?

Use CREATE TABLE books (id SERIAL PRIMARY KEY); to start.

2

Add title and author columns

Add two columns to the books table: title with type VARCHAR(100) and author with type VARCHAR(50).

PostgreSQL

CREATE TABLE books (
    id SERIAL PRIMARY KEY,
    -- Add title and author columns here
)

Need a hint?

Separate columns with commas and specify the length in VARCHAR.

3

Add price and published_date columns

Add two more columns to the books table: price with type NUMERIC(6,2) and published_date with type DATE.

PostgreSQL

CREATE TABLE books (
    id SERIAL PRIMARY KEY,
    title VARCHAR(100),
    author VARCHAR(50),
    -- Add price and published_date columns here
);

Need a hint?

Use NUMERIC(6,2) for prices with two decimal places.

4

Add in_stock column

Add a final column in_stock with type BOOLEAN to the books table to indicate if the book is currently available.

PostgreSQL

CREATE TABLE books (
    id SERIAL PRIMARY KEY,
    title VARCHAR(100),
    author VARCHAR(50),
    price NUMERIC(6,2),
    published_date DATE,
    -- Add in_stock column here
);

Need a hint?

Use BOOLEAN type for true/false values.