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

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

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

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

Hint

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

Practice

1. What does the 'C' in CRUD stand for in database operations?

easy

A. Connect

B. Copy

C. Calculate

D. Create

Solution

1. Step 1: Understand CRUD acronym

   CRUD stands for Create, Read, Update, Delete, which are basic database operations.

2. Step 2: Identify 'C' meaning

   The 'C' specifically means 'Create', which is adding new data to the database.

3. Final Answer:

   Create -> Option D

4. Quick Check:

   CRUD 'C' = Create [OK]

Hint: Remember CRUD = Create, Read, Update, Delete [OK]

Common Mistakes:

• Confusing 'C' with Copy
• Thinking 'C' means Connect
• Mixing 'C' with Calculate

2. Which SQL statement is used to update existing data in a table?

easy

A. INSERT INTO

B. DELETE

C. UPDATE

D. SELECT

Solution

1. Step 1: Recall SQL CRUD commands

   INSERT INTO adds data, SELECT reads data, DELETE removes data, UPDATE modifies data.

2. Step 2: Match operation to statement

   To change existing data, the UPDATE statement is used.

3. Final Answer:

   UPDATE -> Option C

4. Quick Check:

   Update data = UPDATE [OK]

Hint: Update data with UPDATE statement [OK]

Common Mistakes:

• Using INSERT to update
• Using DELETE instead of UPDATE
• Confusing SELECT with UPDATE

3. Given the table users(id INT, name TEXT), what will this query return?

SELECT * FROM users WHERE id = 3;

medium

A. All users with id 3

B. All users with id not equal to 3

C. Syntax error

D. Empty result always

Solution

1. Step 1: Understand SELECT with WHERE clause

   The query selects all columns from users where id equals 3.

2. Step 2: Determine result

   It returns rows where id is exactly 3, so all users with id 3.

3. Final Answer:

   All users with id 3 -> Option A

4. Quick Check:

   WHERE id = 3 returns matching rows [OK]

Hint: WHERE filters rows matching condition [OK]

Common Mistakes:

• Thinking it returns all users
• Confusing = with != operator
• Assuming syntax error

4. Identify the error in this SQL statement:

UPDTE users SET name = 'Alice' WHERE id = 1;

medium

A. Typo in UPDATE keyword

B. Missing WHERE clause

C. Incorrect table name

D. Missing SET keyword

Solution

1. Step 1: Check SQL keywords

   The statement tries to update users but spells UPDATE as UPDTE.

2. Step 2: Identify syntax error

   This typo causes a syntax error because UPDTE is not a valid SQL command.

3. Final Answer:

   Typo in UPDATE keyword -> Option A

4. Quick Check:

   Correct keyword is UPDATE [OK]

Hint: Check spelling of SQL keywords carefully [OK]

Common Mistakes:

• Ignoring typos
• Assuming missing WHERE is error
• Confusing SET keyword usage

5. You want to remove a user from the users table but keep a record of their name in a separate deleted_users table. Which sequence of CRUD operations is best?

hard

A. DELETE from users, then INSERT into deleted_users

B. INSERT into deleted_users, then DELETE from users

C. UPDATE users to mark deleted, no DELETE needed

D. SELECT user, then DELETE from users

Solution

1. Step 1: Preserve data before deletion

   First, copy the user's name into deleted_users using INSERT to keep record.

2. Step 2: Remove user from users table

   After saving, DELETE the user from users table to remove them.

3. Final Answer:

   INSERT into deleted_users, then DELETE from users -> Option B

4. Quick Check:

   Save data before delete = INSERT then DELETE [OK]

Hint: Insert backup before delete to keep data safe [OK]

Common Mistakes:

• Deleting before saving data
• Only updating without delete
• Ignoring backup step