Exercises

Objectives

This lesson introduces no new concepts. It’s a chance to practice everything from this module: CREATE TABLE, data types, NULL/DEFAULT, PRIMARY KEY, NOT NULL, UNIQUE, SERIAL/IDENTITY, and DROP/TRUNCATE TABLE.

All exercises are runnable against a real PostgreSQL database.

Exercises

1. Write a CREATE TABLE statement for a departments table: an auto-incrementing id (SERIAL PRIMARY KEY), a required name, and a budget of type NUMERIC(12,2).

2. Write a CREATE TABLE statement for a products table: an auto-incrementing id, a required name, a description that can hold any amount of text, a required price, and an in_stock boolean that defaults to true.

3. Insert a product without mentioning in_stock at all, then confirm it defaulted to true.

4. Add a UNIQUE constraint to the products table’s name column, then try inserting two products with the same name, and confirm the second one fails.

5. Add a NOT NULL constraint to departments.name, then try inserting a department with name left out entirely, and confirm it fails.

6. Create a table using GENERATED ALWAYS AS IDENTITY for its primary key instead of SERIAL, insert two rows, and confirm the ids were assigned automatically.

7. TRUNCATE the products table from exercise 2, confirm it’s empty, then insert a new product and check whether its id continues from before or restarts.

8. Write a DROP TABLE IF EXISTS statement for a table name that doesn’t exist, and confirm it doesn’t raise an error.

Recap

You can now create a real table with the right data types, control what’s required and what’s optional with NULL and DEFAULT, enforce uniqueness with PRIMARY KEY and UNIQUE, generate ids automatically with SERIAL or IDENTITY, and remove or empty a table safely. That’s everything needed to design and build a table from scratch.

Next module: CRUD Operations, where you’ll finally put real data into the tables you now know how to build, with INSERT, UPDATE, and DELETE.