Why CRUD operations are fundamental in PostgreSQL - See It in Action

Why CRUD Operations Are Fundamental

📖 Scenario: You are helping a small bookstore manage its inventory using a database. The store needs to add new books, update book details, view the list of books, and remove books that are no longer available.

🎯 Goal: Build a simple PostgreSQL database table for books and practice the four basic CRUD operations: Create, Read, Update, and Delete.

📋 What You'll Learn

Create a table named books with columns id, title, and author

Insert three specific book records into the books table

Select all records from the books table

Update the author of a specific book

Delete a book record by its id

💡 Why This Matters

🌍 Real World

Managing inventory, customer data, or any information system requires CRUD operations to keep data accurate and up to date.

💼 Career

Database administrators, backend developers, and data analysts use CRUD operations daily to handle data efficiently.

1

Create the books table

Write a SQL statement to create a table called books with three columns: id as an integer primary key, title as text, and author as text.

PostgreSQL

-- Write the CREATE TABLE statement for books
-- Your code here

Need a hint?

Use CREATE TABLE books (id INTEGER PRIMARY KEY, title TEXT, author TEXT);

2

Insert initial book records

Insert three books into the books table with these exact values: (1, '1984', 'George Orwell'), (2, 'To Kill a Mockingbird', 'Harper Lee'), and (3, 'The Great Gatsby', 'F. Scott Fitzgerald').

PostgreSQL

CREATE TABLE books (
    id INTEGER PRIMARY KEY,
    title TEXT,
    author TEXT
);
-- Insert the three book records below
-- Your code here

Need a hint?

Use a single INSERT INTO books (id, title, author) VALUES (...), (...), (...); statement.

3

Select all books

Write a SQL query to select all columns from the books table to see all the book records.

PostgreSQL

CREATE TABLE books (
    id INTEGER PRIMARY KEY,
    title TEXT,
    author TEXT
);

INSERT INTO books (id, title, author) VALUES
(1, '1984', 'George Orwell'),
(2, 'To Kill a Mockingbird', 'Harper Lee'),
(3, 'The Great Gatsby', 'F. Scott Fitzgerald');

-- Select all books below
-- Your code here

Need a hint?

Use SELECT * FROM books; to get all records.

4

Update and delete book records

Write two SQL statements: first, update the author of the book with id = 3 to 'Francis Scott Fitzgerald'; second, delete the book with id = 2 from the books table.

PostgreSQL

CREATE TABLE books (
    id INTEGER PRIMARY KEY,
    title TEXT,
    author TEXT
);

INSERT INTO books (id, title, author) VALUES
(1, '1984', 'George Orwell'),
(2, 'To Kill a Mockingbird', 'Harper Lee'),
(3, 'The Great Gatsby', 'F. Scott Fitzgerald');

SELECT * FROM books;

-- Update author for id 3 and delete book with id 2 below
-- Your code here

Need a hint?

Use UPDATE books SET author = 'Francis Scott Fitzgerald' WHERE id = 3; and DELETE FROM books WHERE id = 2;.