Chapter 2 — Exercises

Now you have a real database — so these exercises are hands-on. Run everything in psql (or your GUI) against the mercado database. Don't just read the commands; type them and watch what happens.

(answer in Appendix) marks exercises with worked solutions in Answers to Selected Exercises. ⭐ marks a stretch problem.

Group A — Verify your environment

2.1 Run psql --version in your shell. What version do you have? Confirm it is 15 or newer. (answer in Appendix)

2.2 Connect to the mercado database and confirm your prompt shows mercado=# (or mercado=>). What does the difference between # and > at the end of the prompt tell you?

2.3 Use a single meta-command to list all databases on your server. Which command is it, and how many databases do you see?

2.4 Use a single meta-command to list all tables in mercado. How many are there, and does the count match the chapter's claim of 13?

Group B — psql fluency

2.5 Describe the orders table with \d orders. List its columns and identify the primary key and the foreign keys. (answer in Appendix)

2.6 Turn on \timing, run SELECT count(*) FROM order_items;, and note how long it took. Turn timing off again.

2.7 Turn on expanded display with \x, then run SELECT * FROM customers WHERE customer_id = 5;. How does the output differ from the normal table format? When is expanded display most useful?

2.8 Use \h to look up the syntax help for the INSERT command. What does \h do that \? does not? (answer in Appendix)

2.9 ⭐ Find a meta-command that lists the indexes in the database (hint: it starts with \di). How many indexes did the schema create, and on which tables? (Connect this to what you'll learn in Chapter 23.)

Group C — The create/insert/query/drop loop

2.10 Create a throwaway table called scratch with columns id (auto-generated integer primary key) and note (text, not null). Insert three rows. Select them all. Then drop the table. Paste the full sequence you ran. (answer in Appendix)

2.11 Try to insert a row into scratch (recreate it first) with a NULL note. What happens, and why? What does this demonstrate about the difference between a spreadsheet and a database (Chapter 1)?

2.12 ⭐ Create a table temps with a column celsius numeric CHECK (celsius >= -273.15). Try to insert -300. What happens? Explain how this relates to the "structure" problem from Chapter 1.

Group D — Exploring Mercado

2.13 Write a query that returns just the name and price columns of all products. (answer in Appendix)

2.14 Write a query that returns all products costing less than $50, most expensive first.

2.15 How many customers are in each loyalty tier? (Hint: GROUP BY loyalty_tier — you'll learn this properly in Chapter 7, but try it.)

2.16 Look at the employees table with SELECT employee_id, first_name, manager_id FROM employees;. Who has no manager (a NULL in manager_id), and what does that tell you about that person's role?

2.17 ⭐ Run SELECT * FROM products WHERE is_active = false;. How many discontinued products are there, and which one(s)? Why might a store keep discontinued products in the table rather than deleting them? (You'll meet "soft deletes" in Chapter 21.)

Group E — Loading and re-loading

2.18 The sample data is deterministic. Re-run psql -d mercado -f sql/seed-sample.sql. Does it error, or does it reload cleanly? Look at the top of the file to explain why re-running is safe. (answer in Appendix)

2.19 Explain the difference between seed-sample.sql and generate_data.sql. When in the book will you load the large dataset, and why not now?

2.20 ⭐ Without running it yet, open sql/generate_data.sql and find the line that controls how many orders are generated. Roughly how many rows total will the large dataset create across all tables?

Group F — Troubleshooting (diagnose the error)

For each error message, state the most likely cause and the fix.

2.21 psql: error: connection to server ... failed: Connection refused (answer in Appendix)

2.22 ERROR: relation "custmoers" does not exist

2.23 Your prompt reads mercado-# and nothing happens when you press Enter.

2.24 ERROR: database "mercado" does not exist

2.25 ⭐ You created several tables but \dt shows none of them. The prompt says postgres=#. What happened, and how do you find your tables?

Group G — Progressive project

2.26 Create the database for your project domain (createdb <yourname>). Connect to it and confirm the empty \dt (no tables yet).

2.27 Create one table for your domain's central entity (e.g., members, patients, students) with 3–4 columns and at least one NOT NULL and one CHECK constraint. Insert two rows, select them, then drop the table. Keep the CREATE TABLE statement in your project-notes.md — it's a first draft you'll refine in Part III.

2.28 ⭐ Write down three setup decisions you made (local vs. Docker, which version, which client) and one sentence on why. In Chapter 38 you'll revisit environment choices for production.

Self-check. If you can connect, navigate with \dt/\d, run the create/insert/query/drop loop, and diagnose the Group F errors, your environment is solid and you're ready for Chapter 3.