Practice & Assessment
Test your understanding of Getting Started with PostgreSQL
Multiple Choice Questions
5Which command correctly lists all databases in a psql session?
What is the recommended modern replacement for the SERIAL data type in PostgreSQL?
Which data type should you use to store exact monetary values in PostgreSQL?
You run DROP DATABASE app_db while connected to app_db. What happens?
What is the difference between TRUNCATE and DELETE FROM table?
Coding Challenges
1Design and Create a Library Database Schema
Create a new PostgreSQL database called library_db. Inside it, create three tables: authors (id, name, country, birth_year), books (id, title, author_id as FK, published_year, genre), and members (id, full_name, email UNIQUE, joined_at with default now()). Use GENERATED ALWAYS AS IDENTITY for all primary keys. Verify by running \dt and \d books in psql. Expected output: three tables visible in \dt, books table showing author_id column. Time estimate: 20 minutes.
Mini Project
Employee Directory Database
Set up a fresh PostgreSQL database called hr_db. Create four tables: departments (id, name, location), employees (id, first_name, last_name, email UNIQUE NOT NULL, department_id FK, hire_date DATE DEFAULT CURRENT_DATE, salary NUMERIC(10,2)), roles (id, title, level), and employee_roles (employee_id, role_id, assigned_at TIMESTAMPTZ DEFAULT now()). Use GENERATED ALWAYS AS IDENTITY for all surrogate keys. Include at least one composite primary key (employee_roles). Verify the full schema using psql meta-commands \dt and \d on each table. Write a short SQL comment block at the top of your script explaining the purpose of each table.