Databases · Week 2 practice: PostgreSQL & SQL

Answer: Lecture rule: psql commands start with a backslash; SQL commands end with a semicolon. psql is PostgreSQL's command-line client. Its backslash “meta-commands” are handled by psql itself and only work there; SQL commands are sent to the server and (mostly) work in any DBMS. Writing SQL keywords in upper case is a readability convention; most DBMSs aren't case-sensitive about keywords.

Real \d todo_item output on the practice database (shortened):

            Table "public.todo_item"
     Column      |  Type   | Collation | Nullable | Default
-----------------+---------+-----------+----------+---------
 todo_item_id    | integer |           | not null |
 description     | text    |           |          |
 owner_id        | integer |           |          |
 ...
 completion_date | date    |           |          |
Indexes:
    "todo_item_pkey" PRIMARY KEY, btree (todo_item_id)
Foreign-key constraints:
    "todo_item_owner_id_fkey" FOREIGN KEY (owner_id) REFERENCES app_user(user_id)
    ...

These all read the DBMS catalog from Week 1, the database the DBMS keeps about its own databases. The slides write \ du and \ c with a space; that's a font artefact. There's no space after the backslash.

CREATE ROLE testuser
NOINHERIT LOGIN PASSWORD 'password';

• Role: PostgreSQL's single concept for both users and groups. A role with LOGIN is effectively a user.
• LOGIN: this role may connect. Without it, it's just a group of permissions.
• PASSWORD 'password': sets its password. Single quotes, since it's a string literal.
• NOINHERIT: if testuser is made a member of another role, it does not automatically get that role's privileges; it must explicitly run SET ROLE that_role; to act with them.
• GRANT role_2 TO role_1; makes role_1 a member of role_2.

                             List of roles
 Role name |                         Attributes
-----------+------------------------------------------------------------
 postgres  | Superuser, Create role, Create DB, Replication, Bypass RLS
 testuser  | No inheritance

As the lecture notes, the new testuser shows no attributes except “No inheritance”: it isn't a superuser and can't create databases or roles. Compare postgres, the superuser.

CREATE DATABASE todo_list
    WITH
    OWNER = testuser
    ENCODING = 'UTF8'
    CONNECTION LIMIT = -1;

\c todo_list testuser

• OWNER: the owner can do almost anything with the database, which is why the lecture creates it for testuser rather than working as the superuser.
• ENCODING = 'UTF8': UTF-8 is the character encoding that turns every character into bytes. It covers all languages and symbols, so “Zoë” or “€” store correctly.
• CONNECTION LIMIT = -1: maximum simultaneous connections; −1 means no limit beyond the server's own.

I ran this on the practice server: \l todo_list then showed owner testuser and encoding UTF8. The first line is SQL (semicolon); the \c line is psql (backslash).

(a) A superuser has the highest level of access and can bypass all permission checks. Using it day to day is a security risk: one mistake, or one stolen password, can damage everything. So the lecture creates a separate user for a specific database.

REVOKE SELECT ON public.todo_item FROM testuser;
GRANT  SELECT ON public.todo_item TO   testuser;

(c) The principle of least privilege: give each user only the permissions they need (the slide's access-control picture of masked columns per user is the same idea).

(d) GRANT CREATE ON SCHEMA public TO testuser;. From PostgreSQL 15 onward, ordinary users no longer get CREATE on the public schema by default, so this grant is needed on 15+. (The slide's note says this “only works in 14 and below”. I believe it's the other way round, i.e. it's needed on 15+ and harmless on 14; worth checking on your install.)

Answer: A PostgreSQL schema groups tables within one database: e.g. a management schema holding payroll tables and an employee schema with only daily-work tables, giving finer access control. public is the default schema, created automatically with every database. public.todo_item means “the todo_item table in schema public”; plain todo_item finds the same table. Note the two meanings of “schema”: Week 1's (the description/metadata of a database) and this one (a namespace).

(a) The input's quote closes the string early, and the rest becomes SQL:

SELECT * FROM app_user WHERE username = 'x' OR '1'='1'

'1'='1' is always true, so the WHERE is true for every row and the query returns every user. This is SQL injection: user input concatenated into a statement is interpreted as code, not data. Consequences on the slide: data leakage, tampering (insert/update/delete), even administrative control.

(b) Parameterised queries/prepared statements; input validation and sanitisation (allow-lists); principle of least privilege (so a compromised account can't do much).

// The ? is a placeholder. The driver sends the value separately,
// so it is always treated as data and can never change the query's structure.
PreparedStatement ps = connection.prepareStatement(
        "SELECT * FROM app_user WHERE username = ?");
ps.setString(1, username);          // fill placeholder 1 with the input
ResultSet rs = ps.executeQuery();

CREATE TABLE public.room
(
    room_id       text NOT NULL,
    room_name     text,
    room_capacity integer,
    room_zoom     boolean,
    PRIMARY KEY (room_id)
);

INSERT INTO public.room VALUES
    ('G0210', 'Seminar Room', 50,  true),
    ('E1236', 'Lab',          40,  false),
    ('G0205', 'Auditorium',   100, true);

SELECT room_name, room_capacity
FROM public.room
WHERE room_zoom = true AND room_capacity >= 50;

INSERT 0 3
  room_name   | room_capacity 
--------------+---------------
 Seminar Room |            50
 Auditorium   |           100
(2 rows)

Column definition = name, data type, optional parameters (NOT NULL …). Column names follow Java-variable-like rules: no spaces, no special characters, can't start with a digit. room_id is text because 'G0210' contains letters; capacity is integer.

Storing Zoom as text with values 'yes'/'Yes'/'no' is a Week 1 Guideline 1 problem (unclear semantics): WHERE room_zoom = 'yes' misses 'Yes'. A boolean allows only true/false (and NULL), so the query is unambiguous.

ERROR:  duplicate key value violates unique constraint "todo_item_pkey"
DETAIL:  Key (todo_item_id)=(1) already exists.

ERROR:  null value in column "todo_item_id" of relation "todo_item" violates not-null constraint
DETAIL:  Failing row contains (null, No id at all, null, null, null, null, null, null, null).

The first breaks uniqueness (id 1 exists), the second NOT NULL (no id given, so it's NULL). Together they're what a primary key means: never missing, never repeated (Week 1: PK = NOT NULL + UNIQUE). In PostgreSQL, PRIMARY KEY on its own already implies NOT NULL, so writing both is harmless but redundant. I'd still follow the slides' style in the test.

INSERT INTO public.todo_item
VALUES (13, 'Revise ACID properties', 2, 1, 1, 1, 1, '2026-10-13'::date, NULL);

SELECT todo_item_id, description, due_date
FROM public.todo_item
WHERE todo_item_id = 13;

INSERT 0 1
 todo_item_id |      description       |  due_date  
--------------+------------------------+------------
           13 | Revise ACID properties | 2026-10-13
(1 row)

INSERT INTO public.todo_item (todo_item_id, description, owner_id, due_date)
VALUES (14, 'Find pgAdmin password', 1, '2026-10-11');

SELECT * FROM public.todo_item WHERE todo_item_id = 14;

INSERT 0 1
 todo_item_id |      description      | owner_id | priority_id | context_id | status_id | project_id |  due_date  | completion_date 
--------------+-----------------------+----------+-------------+------------+-----------+------------+------------+-----------------
           14 | Find pgAdmin password |        1 |             |            |           |            | 2026-10-11 | 
(1 row)

Positional form: values must be in the same order as the table's columns, and you must supply all of them. Explicit column list: you choose the order and can skip columns. Skipped columns get their DEFAULT, or NULL if there's none, as shown. The explicit form is safer: it still works if someone later adds or reorders columns.

::date is PostgreSQL's cast operator: convert this value to type date (standard SQL: CAST('2026-10-13' AS date)). It's optional here, since Postgres converts a well-formed string into a date column automatically, but the slides use it to make the intent explicit. INSERT 0 1 means 1 row inserted.

INSERT INTO public.status (status_id, name)
VALUES (4, 'Blocked'),
       (5, 'Cancelled');

SELECT * FROM public.status ORDER BY status_id;

INSERT 0 2
 status_id |    name     
-----------+-------------
         1 | To do
         2 | In progress
         3 | Done
         4 | Blocked
         5 | Cancelled
(5 rows)

Multi-row form from the cheat sheet: one VALUES list per row, separated by commas. It's one statement, so either both rows go in or neither does.

ERROR:  insert or update on table "todo_item" violates foreign key constraint "todo_item_owner_id_fkey"
DETAIL:  Key (owner_id)=(0) is not present in table "app_user".

In the lecture, todo_item had no foreign keys yet, so any integer was accepted. Here, owner_id REFERENCES app_user(user_id), and there's no user 0. Referential integrity stops a row pointing at something that doesn't exist. (Priority, context, status and project 0 would fail too; Postgres reports the first violation it finds.) This is the “links between tables” the FOREIGN KEY constraint protects.

BEGIN;

UPDATE public.todo_item
SET completion_date = '2026-10-11', status_id = 3
WHERE todo_item_id = 3;

SELECT todo_item_id, description, status_id, completion_date
FROM public.todo_item
WHERE todo_item_id = 3;

ROLLBACK;

UPDATE 1
 todo_item_id |     description      | status_id | completion_date 
--------------+----------------------+-----------+-----------------
            3 | Practice SQL queries |         3 | 2026-10-11
(1 row)

Structure: UPDATE table SET column = value, … WHERE condition;. The table, the columns, the new values, and which rows. The lecture writes SET completion_date = now(). now() is a built-in function returning the current date and time (stored in a date column, the time part is dropped; CURRENT_DATE gives just the date).

BEGIN starts a transaction; ROLLBACK undoes everything since; COMMIT would keep it. These are Week 1's ACID transactions, live. pgAdmin auto-commits each statement unless you use BEGIN.

BEGIN;

UPDATE public.todo_item
SET completion_date = now();

SELECT COUNT(*) AS rows_marked_complete
FROM public.todo_item
WHERE completion_date IS NOT NULL;

ROLLBACK;

UPDATE 12
 rows_marked_complete 
----------------------
                   12
(1 row)

All 12. No WHERE means every row. It's the most common destructive mistake in SQL, and the reason to try risky updates inside BEGIN … ROLLBACK first.

BEGIN;

DELETE FROM public.todo_item
WHERE completion_date < '2026-10-05';

SELECT todo_item_id, description, completion_date
FROM public.todo_item
WHERE completion_date IS NOT NULL
ORDER BY todo_item_id;

ROLLBACK;

DELETE 3
 todo_item_id |       description       | completion_date 
--------------+-------------------------+-----------------
           12 | Submit DB practice test | 2026-10-10
(1 row)

Items 1 (4 Oct), 6 (30 Sep) and 8 (2 Oct) go. DELETE removes whole rows, so you never name columns. < on dates means “earlier than”. Rows with a NULL completion date aren't deleted: NULL < '2026-10-05' is unknown, not true.

ERROR:  update or delete on table "priority" violates foreign key constraint "todo_item_priority_id_fkey" on table "todo_item"
DETAIL:  Key (priority_id)=(1) is still referenced from table "todo_item".

Items still reference priority 1 (High), so deleting it would leave them pointing at nothing. The foreign key blocks it (default behaviour, NO ACTION), and nothing is deleted.

Answer: The slide's warning: without a WHERE clause, DELETE removes all the rows. The table and its structure remain. DROP TABLE todo_item; is what removes the table itself (section E).

SELECT description AS task, due_date AS deadline
FROM public.todo_item
WHERE owner_id = 1
ORDER BY due_date;

         task         |  deadline  
----------------------+------------
 Cancel old broadband | 2026-10-01
 Book removal van     | 2026-10-20
 Renew railcard       | 
(3 rows)

AS renames a column in the result only; the table isn't changed. In ascending order PostgreSQL puts NULLs last (NULLS FIRST would override).

SELECT DISTINCT owner_id
FROM public.todo_item
ORDER BY owner_id;

 owner_id 
----------
        1
        2
        3
(3 rows)

DISTINCT removes duplicate rows from the result. Without it you'd get 12 rows, one per item. Chloe (4) doesn't appear: she has no items.

SELECT todo_item_id, description, context_id
FROM public.todo_item
WHERE context_id IN (2, 3)
ORDER BY todo_item_id;

 todo_item_id |       description       | context_id 
--------------+-------------------------+------------
            4 | Book removal van        |          3
            5 | Pack kitchen            |          2
            6 | Cancel old broadband    |          3
            9 | Email tutor about essay |          3
(4 rows)

col IN (2, 3) is shorthand for col = 2 OR col = 3. Items with a NULL context are never matched.

SELECT todo_item_id, description
FROM public.todo_item
WHERE description LIKE '%essay%';

 todo_item_id |       description       
--------------+-------------------------
            9 | Email tutor about essay
           10 | Write essay plan
(2 rows)

SELECT todo_item_id, description
FROM public.todo_item
WHERE description LIKE 'E%';

 todo_item_id |       description       
--------------+-------------------------
            7 | Erg 2k test
            9 | Email tutor about essay
(2 rows)

% matches any run of characters (including none); _ matches exactly one. '%essay%' = contains; 'E%' = starts with E. (c) No: LIKE is case-sensitive in PostgreSQL and both items have lower-case “essay”. Use ILIKE for case-insensitive matching.

SELECT description, due_date
FROM public.todo_item
WHERE due_date BETWEEN '2026-10-12' AND '2026-10-20'
ORDER BY due_date;

     description      |  due_date  
----------------------+------------
 Practice SQL queries | 2026-10-12
 Write essay plan     | 2026-10-15
 Erg 2k test          | 2026-10-18
 Book removal van     | 2026-10-20
(4 rows)

BETWEEN low AND high is inclusive at both ends (= >= low AND <= high): the 12th and 20th are both included.

SELECT todo_item_id, description
FROM public.todo_item
WHERE completion_date IS NULL
  AND due_date IS NULL;

 todo_item_id |  description   
--------------+----------------
           11 | Renew railcard
(1 row)

 todo_item_id | description 
--------------+-------------
(0 rows)

NULL means “unknown”, and any comparison with unknown is itself unknown, so due_date = NULL is never true, even for NULL dates. WHERE keeps only rows where the condition is true. Always use IS NULL / IS NOT NULL. (Week 1's Guideline 3 warned that NULLs “complicate” operations; this is what it meant.)

SELECT priority_id, due_date, description
FROM public.todo_item
WHERE status_id <> 3
ORDER BY priority_id ASC, due_date DESC;

 priority_id |  due_date  |       description       
-------------+------------+-------------------------
           1 | 2026-10-15 | Write essay plan
           1 | 2026-10-12 | Practice SQL queries
           1 | 2026-10-09 | Draw ER diagram in JDL
           2 | 2026-10-20 | Book removal van
           2 | 2026-10-18 | Erg 2k test
           2 | 2026-10-08 | Email tutor about essay
           3 |            | Renew railcard
           3 | 2026-10-24 | Pack kitchen
(8 rows)

Each column in ORDER BY gets its own direction; later columns only break ties in earlier ones. <> means “not equal”. In DESC order, NULLs come first (the reverse of ASC), which is why “Renew railcard” leads the Low group.

SELECT todo_item_id, description, due_date
FROM public.todo_item
WHERE due_date IS NOT NULL
ORDER BY due_date
LIMIT 3 OFFSET 3;

 todo_item_id |       description       |  due_date  
--------------+-------------------------+------------
            2 | Draw ER diagram in JDL  | 2026-10-09
           12 | Submit DB practice test | 2026-10-10
            3 | Practice SQL queries    | 2026-10-12
(3 rows)

OFFSET 3 skips the first 3 rows; LIMIT 3 then keeps the next 3. Order: ORDER BY first, then LIMIT/OFFSET. The cheat sheet's order is a syntax error. Without ORDER BY, “the first 3” is whatever order the DBMS happens to return: not guaranteed.

SELECT COUNT(*) AS total_items,
       COUNT(due_date) AS items_with_due_date,
       COUNT(project_id) AS items_in_a_project
FROM public.todo_item;

 total_items | items_with_due_date | items_in_a_project 
-------------+---------------------+--------------------
          12 |                  10 |                  9
(1 row)

COUNT(*) counts rows; COUNT(column) counts non-NULL values in that column. Two items have no due date; three have no project.

SELECT owner_id, COUNT(*) AS num_items
FROM public.todo_item
GROUP BY owner_id
ORDER BY owner_id;

 owner_id | num_items 
----------+-----------
        1 |         3
        2 |         6
        3 |         3
(3 rows)

SELECT owner_id, COUNT(*) AS open_items
FROM public.todo_item
WHERE completion_date IS NULL
GROUP BY owner_id
HAVING COUNT(*) >= 3;

 owner_id | open_items 
----------+------------
        2 |          4
(1 row)

GROUP BY owner_id makes one group per owner; COUNT(*) counts rows in each. Every column in the SELECT must be grouped or inside an aggregate.

WHERE vs HAVING: WHERE filters rows before grouping (only unfinished items); HAVING filters groups after (only groups with 3+). You can't write WHERE COUNT(*) >= 3, since groups don't exist yet when WHERE runs. Order of evaluation: FROM → WHERE → GROUP BY → HAVING → SELECT → ORDER BY → LIMIT.

SELECT t.description, s.name AS status, p.name AS priority
FROM public.todo_item t
INNER JOIN public.status   s ON t.status_id   = s.status_id
INNER JOIN public.priority p ON t.priority_id = p.priority_id
WHERE t.owner_id = 2
ORDER BY t.todo_item_id;

        description        |   status    | priority 
---------------------------+-------------+----------
 Read normalisation slides | Done        | High
 Draw ER diagram in JDL    | In progress | High
 Practice SQL queries      | To do       | High
 Pack kitchen              | To do       | Low
 Email tutor about essay   | To do       | Medium
 Submit DB practice test   | Done        | High
(6 rows)

INNER JOIN t2 ON condition pairs each row with every row of t2 where the condition is true. Here it's FK = PK, so each item finds its one status and one priority. t, s, p are table aliases, needed to say which table's column you mean. This is how the Week 2 normalised tables get “connected” back together.

SELECT COUNT(*) AS rows_after_inner_join
FROM public.todo_item t
INNER JOIN public.project pr ON t.project_id = pr.project_id;

 rows_after_inner_join 
-----------------------
                     9
(1 row)

(a) 9: the three items with NULL project_id match no project, and an inner join drops unmatched rows.

SELECT pr.name AS project, COUNT(t.todo_item_id) AS num_items
FROM public.project pr
LEFT JOIN public.todo_item t ON t.project_id = pr.project_id
GROUP BY pr.project_id, pr.name
ORDER BY num_items DESC, pr.name;

       project        | num_items 
----------------------+-----------
 Databases coursework |         4
 Move flat            |         3
 Rowing season        |         2
 Learn Haskell        |         0
(4 rows)

(b) LEFT JOIN keeps every row of the left table (project) even with no match, filling the right side with NULLs. Count t.todo_item_id, not *: for Learn Haskell the joined row has a NULL item id, so COUNT(t.todo_item_id) gives 0 while COUNT(*) would wrongly give 1.

SELECT t.description, pr.name AS project
FROM public.todo_item t
FULL OUTER JOIN public.project pr ON t.project_id = pr.project_id
WHERE t.todo_item_id IS NULL OR pr.project_id IS NULL
ORDER BY project, t.description;

       description       |    project    
-------------------------+---------------
                         | Learn Haskell
 Email tutor about essay | 
 Renew railcard          | 
 Write essay plan        | 
(4 rows)

A FULL OUTER JOIN keeps unmatched rows from both sides (LEFT keeps the left's, RIGHT the right's). Filtering for a NULL key on either side keeps only the orphans.

SELECT COUNT(*) AS cross_join_rows
FROM public.status
CROSS JOIN public.priority;

 cross_join_rows 
-----------------
               9
(1 row)

   status    | priority 
-------------+----------
 To do       | High
 To do       | Medium
 To do       | Low
 In progress | High
 In progress | Medium
 In progress | Low
 Done        | High
 Done        | Medium
 Done        | Low
(9 rows)

A CROSS JOIN pairs every row of one table with every row of the other: 3 × 3 = 9. It's the Cartesian product A × B from Week 1's spurious tuples guideline. Joining with no (or the wrong) condition produces combinations that were never real. Useful occasionally (e.g. generating a grid of every status/priority pair), dangerous by accident.

SELECT todo_item_id, description, name AS project
FROM public.todo_item
NATURAL JOIN public.project
ORDER BY todo_item_id;

 todo_item_id |        description        |       project        
--------------+---------------------------+----------------------
            1 | Read normalisation slides | Databases coursework
            2 | Draw ER diagram in JDL    | Databases coursework
            3 | Practice SQL queries      | Databases coursework
            4 | Book removal van          | Move flat
            6 | Cancel old broadband      | Move flat
            7 | Erg 2k test               | Rowing season
            8 | Buy heart rate strap      | Rowing season
           12 | Submit DB practice test   | Databases coursework
(8 rows)

SELECT t.todo_item_id, t.description, pr.name AS project
FROM public.todo_item t
INNER JOIN public.project pr ON t.project_id = pr.project_id
ORDER BY t.todo_item_id;

 todo_item_id |        description        |       project        
--------------+---------------------------+----------------------
            1 | Read normalisation slides | Databases coursework
            2 | Draw ER diagram in JDL    | Databases coursework
            3 | Practice SQL queries      | Databases coursework
            4 | Book removal van          | Move flat
            5 | Pack kitchen              | Move flat
            6 | Cancel old broadband      | Move flat
            7 | Erg 2k test               | Rowing season
            8 | Buy heart rate strap      | Rowing season
           12 | Submit DB practice test   | Databases coursework
(9 rows)

The NATURAL JOIN gives 8 rows, the explicit join 9. “Pack kitchen” (item 5) is missing. A NATURAL JOIN joins on every column with the same name in both tables. Both have project_id and owner_id, so it silently requires the item's owner to equal the project's owner. Item 5 is Amira's item in Test User's project, so it's dropped. Explicit JOIN … ON says exactly what you mean.

SELECT owner_id AS user_id FROM public.project
UNION
SELECT owner_id FROM public.todo_item
ORDER BY user_id;

 user_id 
---------
       1
       2
       3
(3 rows)

SELECT COUNT(*) AS union_all_rows
FROM (
    SELECT owner_id FROM public.project
    UNION ALL
    SELECT owner_id FROM public.todo_item
) AS combined;

 union_all_rows 
----------------
             16
(1 row)

Set operations combine the results of two SELECTs with the same number and types of columns. UNION removes duplicates; UNION ALL keeps them (4 projects + 12 items = 16 rows). The column name comes from the first SELECT, hence the alias there.

SELECT c.name AS context
FROM public.todo_item t JOIN public.context c ON c.context_id = t.context_id
WHERE t.owner_id = 2
INTERSECT
SELECT c.name
FROM public.todo_item t JOIN public.context c ON c.context_id = t.context_id
WHERE t.owner_id = 3;

 context 
---------
 @uni
(1 row)

SELECT c.name AS context
FROM public.todo_item t JOIN public.context c ON c.context_id = t.context_id
WHERE t.owner_id = 2
EXCEPT
SELECT c.name
FROM public.todo_item t JOIN public.context c ON c.context_id = t.context_id
WHERE t.owner_id = 3
ORDER BY context;

 context 
---------
 @home
 @phone
(2 rows)

SELECT user_id FROM public.app_user
EXCEPT
SELECT owner_id FROM public.todo_item
ORDER BY user_id;

 user_id 
---------
       4
(1 row)

INTERSECT = rows in both results; EXCEPT = rows in the first but not the second (order matters). (c) is the “find what's missing” pattern: all users EXCEPT users who own items leaves Chloe. Ben's NULL context doesn't match anything, and neither does his @gym.

ALTER TABLE public.todo_item ADD COLUMN notes text;
ALTER TABLE public.todo_item ALTER COLUMN status_id SET DEFAULT 1;

INSERT INTO public.todo_item (todo_item_id, description, owner_id)
VALUES (15, 'Try ALTER TABLE', 3);

SELECT todo_item_id, description, status_id, notes
FROM public.todo_item
WHERE todo_item_id = 15;

INSERT 0 1
 todo_item_id |   description   | status_id | notes 
--------------+-----------------+-----------+-------
           15 | Try ALTER TABLE |         1 | 
(1 row)

ADD COLUMN adds a column; existing rows get NULL. ALTER COLUMN … SET DEFAULT only affects future inserts, so item 15 gets status 1, which is the DEFAULT constraint from Week 1. Remove it with DROP DEFAULT; remove a column with DROP COLUMN.

ALTER TABLE public.context RENAME COLUMN name TO label;
ALTER TABLE public.context RENAME TO place;

SELECT * FROM public.place ORDER BY context_id;

 context_id | label  
------------+--------
          1 | @uni
          2 | @home
          3 | @phone
          4 | @gym
(4 rows)

Renaming the table doesn't break the foreign key from todo_item: PostgreSQL's FKs refer to the table itself, not its name. (The setup script drops place too, so re-running it resets everything.)

CREATE OR REPLACE VIEW overdue_items AS
SELECT t.todo_item_id, t.description, u.username, t.due_date
FROM public.todo_item t
JOIN public.app_user u ON u.user_id = t.owner_id
WHERE t.completion_date IS NULL
  AND t.due_date < '2026-10-11';

SELECT * FROM overdue_items ORDER BY due_date;

 todo_item_id |       description       | username |  due_date  
--------------+-------------------------+----------+------------
            9 | Email tutor about essay | amira    | 2026-10-08
            2 | Draw ER diagram in JDL  | amira    | 2026-10-09
(2 rows)

CREATE OR REPLACE VIEW overdue_items AS
SELECT t.todo_item_id, t.description, u.username, t.due_date
FROM public.todo_item t
JOIN public.app_user u ON u.user_id = t.owner_id
WHERE t.completion_date IS NULL
  AND t.due_date < '2026-10-11';

UPDATE public.todo_item SET completion_date = '2026-10-11' WHERE todo_item_id = 9;

SELECT * FROM overdue_items;

UPDATE 1
 todo_item_id |      description       | username |  due_date  
--------------+------------------------+----------+------------
            2 | Draw ER diagram in JDL | amira    | 2026-10-09
(1 row)

A view is Week 1's “virtual table”: a stored query, not stored data. Every SELECT from it re-runs the query against the base tables, so the view is always up to date: completing item 9 removed it. CREATE OR REPLACE VIEW creates it or updates its definition. (The cheat sheet drops the word VIEW; it's required.) CREATE MATERIALIZED VIEW instead stores the result, and needs REFRESH MATERIALIZED VIEW to update.

CREATE OR REPLACE VIEW overdue_items AS
SELECT t.todo_item_id, t.description, u.username, t.due_date
FROM public.todo_item t
JOIN public.app_user u ON u.user_id = t.owner_id
WHERE t.completion_date IS NULL
  AND t.due_date < '2026-10-11';

DROP TABLE public.app_user;

ERROR:  cannot drop table app_user because other objects depend on it
DETAIL:  constraint project_owner_id_fkey on table project depends on table app_user
constraint todo_item_owner_id_fkey on table todo_item depends on table app_user
view overdue_items depends on table app_user
HINT:  Use DROP ... CASCADE to drop the dependent objects too.

The drop is refused: two foreign keys and the view depend on app_user. CASCADE would drop the table and remove what depends on it: the view, and the FK constraints on project and todo_item (the constraints, not those tables' rows). IF EXISTS just avoids an error if the table isn't there, which is why the setup script uses both.

CREATE INDEX idx_todo_owner ON public.todo_item (owner_id);

EXPLAIN SELECT * FROM public.todo_item WHERE owner_id = 2;

                        QUERY PLAN                        
----------------------------------------------------------
 Seq Scan on todo_item  (cost=0.00..1.15 rows=1 width=64)
   Filter: (owner_id = 2)
(2 rows)

An index is a separate lookup structure (a B-tree, like a sorted map) that lets the DBMS find matching rows without reading the whole table, Week 1's CREATE INDEX constraint. EXPLAIN shows the plan without running the query; EXPLAIN ANALYZE runs it and reports real timings.

Surprise: it says Seq Scan (read every row). With 12 rows, reading the whole table is cheaper than consulting an index, so the planner ignores it. On thousands of rows it would switch to an Index Scan. Indexes are physical design (Week 1 step 5): they change speed, never results.