Exercises

Objectives

This lesson introduces no new concepts. It’s a chance to practice everything from this module: INSERT, multi-row INSERT, INSERT ... SELECT, UPDATE, DELETE, and RETURNING, by building a small employee database from scratch.

All exercises are runnable against a real PostgreSQL database, using the employees table from Module 3 (recreate it if needed).

Exercises

1. INSERT

a. Insert these three employees, one INSERT statement each: Erin Castillo (erin.castillo@example.com, Engineering, 74000), Jordan Blake (jordan.blake@example.com, Sales, 61000), and Maya Fischer (maya.fischer@example.com, Marketing, 67000).

b. Insert these three more employees in a single multi-row INSERT statement: Priya Desai (priya.desai@example.com, Engineering, 79000), Sam Whitfield (sam.whitfield@example.com, Sales, 63000), and Devon Ellery (devon.ellery@example.com, Marketing, 58000).

c. Try inserting Taylor Nakamura into Engineering at a salary of 70000, without an email (a NOT NULL column, from Module 3), and confirm you get an error.

d. Insert Taylor Nakamura for real this time, with email set to taylor.nakamura@example.com, but without mentioning hire_date or is_active at all, then confirm both filled in from their DEFAULT.

2. INSERT … SELECT

a. Create a sales_employees table with id, first_name, last_name, and salary columns.

b. Use INSERT ... SELECT to populate it with every employee in the Sales department, Jordan Blake and Sam Whitfield should be the two rows that land in it.

c. Create a high_earners table with the same shape, and populate it with every employee earning more than 70000, Erin Castillo and Priya Desai should be the two rows that land in it.

d. Explain, in your own words, when you’d reach for INSERT ... SELECT instead of typing VALUES out by hand.

3. UPDATE

a. Give every employee in the Engineering department a 10% raise with a single UPDATE statement, using RETURNING to show each employee’s new salary, Erin Castillo should end up at 81400, Priya Desai at 86900, and Taylor Nakamura at 77000.

b. Move Devon Ellery to the Product department and set is_active to false, in a single UPDATE statement, using RETURNING to confirm both new values.

c. Give every employee in the Sales department a flat 500 bonus (salary = salary + 500), using RETURNING to confirm Jordan Blake lands at 61500 and Sam Whitfield at 63500.

d. Write, but don’t run, an UPDATE statement with no WHERE clause that would set every employee’s is_active to true, and add a comment above it explaining exactly what it would do if it were run for real, and why that’s risky.

4. DELETE

a. Delete Taylor Nakamura by id, using RETURNING * to confirm exactly which row was removed.

b. Delete every employee in the Marketing department in a single statement, using RETURNING first_name, last_name, Maya Fischer should be the one row returned (Devon Ellery has already moved to Product by this point).

c. Explain, in your own words, the difference between DELETE FROM employees; (no WHERE) and TRUNCATE TABLE employees; (from Module 3).

d. Explain why TRUNCATE TABLE can’t be used to remove just the Sales department’s employees, while DELETE can.

Recap

You can now insert one row or many, copy data from a query straight into a table, update and delete existing rows precisely, and get back exactly what changed without a follow-up query. That’s full CRUD, the core lifecycle every row in a real database goes through.

Next module: Altering Tables, changing a table’s own structure after it already exists and already has data in it.