INSERT, UPDATE, and DELETE Queries

# CHAPTER 6

INSERT, UPDATE, and DELETE Queries #

1. Introduction #

2. Learning Objectives #

• Use INSERT INTO to add new single or multiple rows.

• Use UPDATE to modify existing data.

• Understand the critical importance of the WHERE clause.

• Use DELETE to safely erase rows.

• Utilize the PostgreSQL-exclusive RETURNING clause.

3. Inserting Data (INSERT INTO) #

Syntax:

INSERT INTO table_name (column1, column2, column3) 
VALUES (value1, value2, value3);

Let's insert our first employee into the table we built in Chapter 5. *(Note: Strings and Dates must be wrapped in single quotes ''. Numbers and Booleans do not).*

INSERT INTO employees (first_name, email, salary, hire_date, is_manager) 
VALUES ('John', 'john@company.com', 55000.00, '2024-01-15', TRUE);

*(Notice we completely ignored the emp_id column! Because we defined it as SERIAL, PostgreSQL will automatically generate ID #1 for John).*

Inserting Multiple Rows at Once: You don't need to write the INSERT INTO command 50 times. You can chain values!

INSERT INTO employees (first_name, email, salary) 
VALUES 
('Sarah', 'sarah@company.com', 85000.00),
('Mike', 'mike@company.com', 45000.00),
('Anna', 'anna@company.com', 120000.00);

4. Updating Data (UPDATE) #

The Golden Rule: You almost *always* must use a WHERE clause to target a specific row. If you forget the WHERE clause, PostgreSQL will update EVERY SINGLE ROW in the entire table!

-- Let's give Sarah (ID 2) a massive raise!
UPDATE employees 
SET salary = 95000.00 
WHERE emp_id = 2;

-- We can update multiple columns at once
UPDATE employees 
SET salary = 50000.00, is_manager = FALSE 
WHERE emp_id = 3;

5. Deleting Data (DELETE) #

The Golden Rule applies here too: If you forget the WHERE clause, you will wipe out the entire table in a millisecond!

-- Safely delete Mike from the company
DELETE FROM employees 
WHERE emp_id = 3;

6. The PostgreSQL Superpower: RETURNING #

-- Insert a user, and instantly return the ID that was generated!
INSERT INTO employees (first_name, email) 
VALUES ('David', 'david@company.com')
RETURNING emp_id;

-- Output: 
-- emp_id
-- 5

7. Mini Project: Manage Employee Records #

-- 1. Hire a new employee and get their ID
INSERT INTO employees (first_name, salary) 
VALUES ('Eve', 70000.00)
RETURNING emp_id, first_name;

-- 2. Oops, we forgot her email. Update it and confirm!
UPDATE employees 
SET email = 'eve@company.com' 
WHERE emp_id = 6
RETURNING email;

-- 3. Eve was fired. Remove her and return the deleted record for the log!
DELETE FROM employees 
WHERE emp_id = 6
RETURNING *;

8. Common Mistakes #

• String Quote Errors: Typing VALUES (John, 55000) will fail. PostgreSQL will think John is the name of a column! Text must be wrapped in single quotes: VALUES ('John', 55000).

• Double Quotes: In PostgreSQL, single quotes ' ' are for Strings/Text. Double quotes " " are strictly for forcing case-sensitivity on Table/Column names. Never use double quotes for string values!

9. Best Practices #

• Never rely on Table Order: When writing INSERT INTO employees VALUES ('John', 55000); (without specifying the columns in parentheses), PostgreSQL assumes you are inserting data in the exact order the table was created. If someone adds a column to the table later, your query will crash. Always explicitly declare the columns: INSERT INTO employees (name, salary) VALUES...

10. Exercises #

1. 1. Write the SQL command to update the ismanager status of all employees who make exactly $120,000 to TRUE.

DELETE FROM employees 
WHERE email IS NULL 
RETURNING first_name;

12. MCQ Quiz with Answers #

13. Interview Questions #

• Q: Explain the difference between single quotes (') and double quotes (") in PostgreSQL syntax.

• Q: Describe a backend web development scenario where the PostgreSQL RETURNING clause drastically improves application efficiency.

14. FAQs #

15. Summary #

16. Next Chapter Recommendation #