Skip to content

exercises.md

Latest commit

 

History

History
190 lines (109 loc) · 5.77 KB

exercises.md

File metadata and controls

190 lines (109 loc) · 5.77 KB

Section 02 — Schemas

What you'll practice: creating schemas, listing them, using search_path, qualifying table names with a schema, moving tables between schemas, and dropping schemas cleanly.

A schema in PostgreSQL is a named namespace that owns tables, views, functions, and other objects. Every database has at least one schema called public, and every table lives inside a schema. Schemas are how you organise a database that has a lot going on — for example, a real app might put its sales tables under sales, its HR tables under hr, and its auth tables under auth.

Learning objectives

  • Create a schema with CREATE SCHEMA
  • List all schemas in a database with \dn
  • Reference a table using its fully qualified name (schema.table)
  • Change the search_path so you can use unqualified names
  • Move a table from one schema to another
  • Drop a schema (with and without CASCADE)

How to run these exercises

From your terminal:

psql -U postgres -d sql_exercise -f 04-advanced-databases/02-schemas/schema.sql
psql -U postgres -d sql_exercise

The first command seeds a tiny employee table inside a schema called adv_schemas. Then the second command drops you into psql.

There is no separate seed.sql for this section — the reference schema already inserts a few rows.

Exercises

Exercise 1 — List every schema in the database

Use a psql meta-command to list every schema that already exists in sql_exercise.

Hint: the meta-command starts with \d.

Question: which of the schemas you see are system schemas (shipped with PostgreSQL) and which are user schemas?

Exercise 2 — Create two new schemas

Create two new schemas called adv_schemas_sales and adv_schemas_hr. They should be empty.

Verify: \dn should now list both schemas.

Exercise 3 — Look up a table by fully qualified name

Without changing your search_path, run a SELECT against the employee table that lives inside adv_schemas.

Hint: the syntax is schema_name.table_name.

Expected: three rows — Ada, Alan, Grace.

Exercise 4 — Try the unqualified name and watch it fail

Now try:

SELECT * FROM employee;

Question: what error do you get, and why? Which schemas is PostgreSQL looking in right now? (Hint: SHOW search_path;)

Exercise 5 — Set the search path

Set your session's search_path so you can query employee without the schema prefix.

Verify: after setting it, SELECT * FROM employee; should now work.

Question: if you quit psql and reconnect, does the search_path persist?

Exercise 6 — Multiple schemas on the search path

Set search_path to adv_schemas_hr, adv_schemas (note the order). Then run SELECT * FROM employee;.

Question: which schema did the query find employee in, and why?

Exercise 7 — Create a table in a non-default schema

Create a department table inside adv_schemas_hr with columns:

Column Type
id integer
name varchar(100) not null
head_count integer not null

There are two ways to do this — using a qualified name in the CREATE TABLE, or by first switching schema. Try both in theory, do it whichever way you prefer.

Verify: \dt adv_schemas_hr.*

Exercise 8 — Insert into a specific schema

Insert two rows into adv_schemas_hr.department:

id name head_count
1 Engineering 42
2 People 8

Verify: SELECT * FROM adv_schemas_hr.department; should return both rows.

Exercise 9 — Move a table between schemas

Move the employee table from adv_schemas into adv_schemas_hr, so that both employee and department live in the HR schema.

Hint: ALTER TABLE ... SET SCHEMA ...

Verify:

  • \dt adv_schemas_hr.* should list both tables.
  • \dt adv_schemas.* should now be empty.

Exercise 10 — Query across two schemas with a JOIN

In this contrived example, assume each employee belongs to department 1. Write a query that joins adv_schemas_hr.employee and adv_schemas_hr.department so you see each employee with their department name.

You'll need to join them on a literal value since there's no department_id column yet — use ON d.id = 1 to fake it.

Expected: three rows with both an employee name and Engineering.

Exercise 11 — Drop an empty schema

Drop the adv_schemas_sales schema. It's empty, so no CASCADE is needed.

Verify: \dn

Exercise 12 — Drop a schema that has tables in it

Try to drop adv_schemas_hr without CASCADE. Watch it fail. Then drop it with CASCADE.

Question: why does PostgreSQL make you opt in to CASCADE instead of just doing it for you?

Exercise 13 — Recreate everything from the reference schema

From your terminal, re-run:

psql -U postgres -d sql_exercise -f 04-advanced-databases/02-schemas/schema.sql

Question: why can you re-run this script safely? What line at the top makes it idempotent?

Bonus challenges

Bonus 1 — Per-user default search path

Read \h ALTER ROLE in psql. Can you make it so that every time the postgres user connects to sql_exercise, their search_path already includes adv_schemas?

Bonus 2 — information_schema

Write a query against information_schema.schemata that lists every non-system schema. (Hint: system schemas start with pg_ or are called information_schema.)

Bonus 3 — Object owner

Every schema has an owner. Look up who owns adv_schemas using \dn+. What is the difference between \dn and \dn+?

Cleanup

DROP SCHEMA IF EXISTS adv_schemas       CASCADE;
DROP SCHEMA IF EXISTS adv_schemas_hr    CASCADE;
DROP SCHEMA IF EXISTS adv_schemas_sales CASCADE;