Exam Prep Hub

A1.1 Databases, DBMS and our IMDB example

Explanation

Why not just keep data in CSV or JSON files read by your app? Because a DBMS gives you, for free:

SQL (Structured Query Language) is split into sub-languages:

• DDL (Data Definition Language): define the schema, e.g. CREATE TABLE, ALTER TABLE, DROP TABLE (A1.4).
• DML (Data Manipulation Language): change the instance, e.g. INSERT, UPDATE, DELETE (A1.5).
• DQL (Data Query Language): read data with SELECT (A1.8 onwards). Some books count SELECT as part of DML.
• (Also DCL for permissions, GRANT/REVOKE, and transaction control, BEGIN/COMMIT/ROLLBACK.)

The example database used in this whole module

A tiny IMDB-style database with 5 tables. Underlined = primary key; arrows = foreign keys (A1.3, A1.6).

Schema in the usual textbook notation (underline = key, written here as …):

• movie(id, title, year, length, budget) (length in minutes, budget in USD, may be NULL)
• person(id, name, birth_year, sex)
• crew(movie, person, role, character): who worked on which movie and how ('director', 'actor', 'writer'); character only for actors
• rating(movie, source, score, votes): score 0-10 from a source ('IMDb', 'Metacritic')
• genre(movie, genre): a movie can have many genres

Worked examples

The full DDL (you will learn every line in A1.3, A1.4, A1.6). It is valid PostgreSQL and also runs in SQLite:

CREATE TABLE movie (
  id      INTEGER PRIMARY KEY,
  title   VARCHAR(200) NOT NULL,
  year    INTEGER CHECK (year >= 1888),
  length  INTEGER CHECK (length > 0),
  budget  NUMERIC(12,0)
);
CREATE TABLE person (
  id         INTEGER PRIMARY KEY,
  name       VARCHAR(100) NOT NULL,
  birth_year INTEGER,
  sex        CHAR(1) CHECK (sex IN ('M','F'))
);
CREATE TABLE crew (
  movie     INTEGER REFERENCES movie(id) ON DELETE CASCADE,
  person    INTEGER REFERENCES person(id),
  role      VARCHAR(20) NOT NULL,
  character VARCHAR(100),
  PRIMARY KEY (movie, person, role)
);
CREATE TABLE rating (
  movie  INTEGER REFERENCES movie(id) ON DELETE CASCADE,
  source VARCHAR(20),
  score  NUMERIC(3,1) CHECK (score BETWEEN 0 AND 10),
  votes  INTEGER CHECK (votes >= 0),
  PRIMARY KEY (movie, source)
);
CREATE TABLE genre (
  movie INTEGER REFERENCES movie(id) ON DELETE CASCADE,
  genre VARCHAR(20),
  PRIMARY KEY (movie, genre)
);

The instance (all the data). Keep this page open while you do the exercises.

SELECT * FROM movie;

id  title            year  length  budget
--  ---------------  ----  ------  ---------
1   Inception        2010  148     160000000
2   The Matrix       1999  136     63000000
3   Interstellar     2014  169     165000000
4   Pulp Fiction     1994  154     8000000
5   The Dark Knight  2008  152     185000000
6   Memento          2000  113     9000000
7   Parasite         2019  132     11400000
8   Tenet            2020  150

SELECT * FROM person;

id  name               birth_year  sex
--  -----------------  ----------  ---
1   Christopher Nolan  1970        M
2   Leonardo DiCaprio  1974        M
3   Keanu Reeves       1964        M
4   Lana Wachowski     1965        F
5   Quentin Tarantino  1963        M
6   Uma Thurman        1970        F
7   Christian Bale     1974        M
8   Bong Joon-ho       1969        M
9   Anne Hathaway      1982        F
10  Carrie-Anne Moss   1967        F
11  Song Kang-ho       1967        M
12  Meryl Streep       1949        F

SELECT * FROM crew;

movie  person  role      character
-----  ------  --------  -----------
1      1       director
1      1       writer
1      2       actor     Cobb
2      4       director
2      3       actor     Neo
2      10      actor     Trinity
3      1       director
3      1       writer
3      9       actor     Brand
4      5       director
4      5       actor     Jimmie
4      6       actor     Mia Wallace
5      1       director
5      7       actor     Bruce Wayne
6      1       director
6      10      actor     Natalie
7      8       director
7      11      actor     Kim Ki-taek
8      1       director

SELECT * FROM rating;

movie  source      score  votes
-----  ----------  -----  -------
1      IMDb        8.8    2400000
1      Metacritic  7.4    70
2      IMDb        8.7    2000000
2      Metacritic  7.3    35
3      IMDb        8.7    2100000
4      IMDb        8.9    2200000
4      Metacritic  9.5    30
5      IMDb        9      2900000
5      Metacritic  8.4    39
6      IMDb        8.4    1300000
7      IMDb        8.5    950000
7      Metacritic  9.6    50

SELECT * FROM genre;

movie  genre
-----  --------
1      Sci-Fi
1      Action
2      Sci-Fi
2      Action
3      Sci-Fi
3      Drama
4      Crime
4      Drama
5      Action
5      Crime
6      Thriller
6      Mystery
7      Thriller
7      Drama
8      Action
8      Sci-Fi

Exercises

1. easy

   Classify each statement as DDL, DML or DQL: (a) CREATE TABLE genre (...); (b) SELECT title FROM movie; (c) UPDATE movie SET budget = 0; (d) ALTER TABLE movie ADD COLUMN plot TEXT; (e) DELETE FROM rating; (f) DROP TABLE genre.

   Show solution

   (a) DDL, (b) DQL, (c) DML, (d) DDL, (e) DML (it removes rows, the table still exists), (f) DDL (it removes the table itself, schema and data).

2. easy

   Is PostgreSQL a database or a DBMS? Is "the IMDB data on the teacher's server" a database or a DBMS?

   Show solution

   PostgreSQL is a DBMS (software). The IMDB data it manages is a database.

3. medium

   The admin adds an index on movie(year) to make queries faster. No query has to change. Which kind of data independence is this? And if the admin adds a new column plot and old applications keep working?

   Show solution

   Adding an index changes only the physical storage: physical independence. Adding a column changes the logical schema while external applications still work: logical independence.

4. exam

   Give three concrete problems you would have if 50 analysts shared the IMDB data as one CSV file on a shared drive, each editing it with a Python script, and name the DBMS feature that solves each one.

   Show solution

   • Two people save at the same time and one overwrites the other's changes (lost update) → concurrency control / transactions.
   • A script crashes halfway through and the file is half-written → atomicity and recovery.
   • Someone writes a rating of 15 or a crew row for a movie that does not exist → integrity constraints (CHECK, foreign keys).
   • (Also: everyone can read/edit everything → access control; every query scans the whole file → indexes and query optimisation.)

Self-check quiz

Common mistakes

• Calling PostgreSQL "a database". It is a DBMS; the data it manages is the database.
• Thinking DELETE is DDL. DELETE removes rows (DML); DROP removes the table (DDL).
• Mixing up physical and logical independence: physical = storage/indexes; logical = the table structure seen by applications.

A1.2 The relational model: schema vs instance

Explanation

The relational model (Codd, 1970) represents all data as tables, which in theory are called relations.

Because a relation is a set:

• there are no duplicate tuples;
• the order of tuples does not matter (row 1 is not "first");
• the order of attributes does not matter (they are identified by name);
• each value is atomic (one value per cell, not a list: that is why genres are in a separate table genre, not a list inside movie).

How do tables connect? Not with pointers (like object references in JS) but with values: the row in crew stores the number 1 in column movie, and this value matches movie.id = 1. This "value-based" model is what makes the relational model independent from physical storage.

Worked examples

Example 1: degree and cardinality. Relation rating has degree 4 (movie, source, score, votes). Its current instance has cardinality:

SELECT count(*) AS cardinality FROM rating;

cardinality
-----------
12

Example 2: same schema, different instance. After this insert the schema of movie is unchanged (same 5 attributes) but the instance changed (9 tuples now):

INSERT INTO movie (id, title, year) VALUES (9, 'Dune', 2021);
SELECT count(*) AS cardinality FROM movie;

cardinality
-----------
9

Example 3: is it a relation? Consider this table:

It breaks two rules: duplicate tuple (same row twice) and a non-atomic value (a list in genres). The relational fix is our genre(movie, genre) table: one row per (movie, genre) pair.

Exercises

1. easy

   Give the degree and the cardinality of person and of genre in our instance (see A1.1).

   Show solution

   person: degree 4 (id, name, birth_year, sex), cardinality 12. genre: degree 2 (movie, genre), cardinality 16.

2. easy

   For each change, say whether it changes the schema or the instance: (a) insert a new rating; (b) add a column plot to movie; (c) set Tenet's budget; (d) add a CHECK constraint on votes.

   Show solution

   (a) instance, (b) schema, (c) instance, (d) schema (constraints are part of the schema).

3. medium

   Two tables have the same rows but the columns are in a different order: (title, year) vs (year, title). Are they the same relation in the relational model? And are two tables with the same rows in a different row order the same?

   Show solution

   Yes in both cases. Attributes are identified by name, not position, and a relation is a set of tuples, so order of rows is irrelevant. (In SQL the column order matters only for display and for INSERT ... VALUES without a column list.)

4. medium

   A colleague wants to store the cast of a movie as a column actors = 'Keanu Reeves; Carrie-Anne Moss' in movie. Which property of the relational model does this break, and what is the relational design?

   Show solution

   It breaks atomicity of values (first normal form): the cell contains a list. Relational design: a separate table such as our crew(movie, person, role, character) with one row per (movie, person, role), linked to movie and person by values (foreign keys). Note: a document database like MongoDB does allow arrays inside a record (A1.15).

5. exam

   True or false, with a one-line reason: (a) a NULL budget means the budget is 0; (b) a relation can contain the same tuple twice; (c) the schema of a database changes every time a row is inserted; (d) a relation is a subset of the Cartesian product of its domains.

   Show solution

   (a) False: NULL = no value / unknown, not 0. (b) False: a relation is a set. (c) False: inserting changes the instance. (d) True: that is the formal definition.

Self-check quiz

Common mistakes

• Saying "schema" when you mean the data. Schema = structure, instance = data.
• Confusing degree (columns) with cardinality (rows).
• Treating NULL as 0 or ''. It is a marker for "no value".
• Thinking relations are linked by pointers. They are linked by matching values.

A1.3 Integrity constraints and keys

Explanation

• Domain constraint: a value must belong to the column type, e.g. year INTEGER.
• NOT NULL: the column must have a value. title VARCHAR(200) NOT NULL.
• CHECK (condition): a condition on the values of one row (tuple constraint). CHECK (score BETWEEN 0 AND 10). It can use several columns of the same row: CHECK (death_year IS NULL OR death_year >= birth_year).
• UNIQUE: no two rows have the same value(s) in these columns (NULLs are allowed and, in PostgreSQL, several NULLs do not clash).

Examples in our schema:

• movie: {id} is a key. {id, title} is a superkey but not a key (not minimal). {title} is not a key: two different movies can share a title (remakes).
• rating: key {movie, source}, because one movie has one score per source. {movie} alone is not a key (Inception has 2 ratings).
• crew: key {movie, person, role}: Nolan is both director and writer of Inception, so {movie, person} is not enough.

Worked examples

Each statement below breaks one constraint, so the DBMS rejects it. Output is SQLite's error message; PostgreSQL says the same thing in different words (shown in the comment).

-- duplicate primary key
-- PG: ERROR: duplicate key value violates unique constraint "movie_pkey"
INSERT INTO movie VALUES (1, 'Dune', 2021, 155, 165000000);

Error: UNIQUE constraint failed: movie.id

-- NOT NULL
-- PG: ERROR: null value in column "title" of relation "movie" violates not-null constraint
INSERT INTO movie (id, title, year) VALUES (9, NULL, 2021);

Error: NOT NULL constraint failed: movie.title

-- CHECK on one column
-- PG: ERROR: new row for relation "movie" violates check constraint "movie_length_check"
INSERT INTO movie VALUES (9, 'Dune', 2021, -5, 165000000);

Error: CHECK constraint failed: length > 0

-- CHECK with IN
INSERT INTO person VALUES (13, 'Zendaya', 1996, 'X');

Error: CHECK constraint failed: sex IN ('M','F')

-- composite primary key: Nolan is already director of movie 1
INSERT INTO crew VALUES (1, 1, 'director', NULL);

Error: UNIQUE constraint failed: crew.movie, crew.person, crew.role

Columns you do not list get NULL (or their DEFAULT). This insert is accepted, because only title is NOT NULL:

INSERT INTO movie (id, title) VALUES (9, 'Dune');
SELECT * FROM movie WHERE id = 9;

id  title  year  length  budget
--  -----  ----  ------  ------
9   Dune

Naming a constraint (useful in error messages and to drop it later):

CREATE TABLE award (
  id     INTEGER PRIMARY KEY,
  name   VARCHAR(100) NOT NULL,
  year   INTEGER NOT NULL,
  movie  INTEGER,
  CONSTRAINT award_year_ok CHECK (year >= 1929),
  CONSTRAINT award_unique UNIQUE (name, year)
);
INSERT INTO award VALUES (1, 'Best Picture', 2020, 7);
INSERT INTO award VALUES (2, 'Best Picture', 2020, 1);

Error: UNIQUE constraint failed: award.name, award.year

Exercises

1. easy

   In person(id, name, birth_year, sex), which of these are superkeys, and which are keys: {id}, {id, name}, {name}, {name, birth_year}? Use the meaning of the data, not only the current rows.

   Show solution

   {id}: superkey and key (minimal). {id, name}: superkey, not a key (remove name and it is still a superkey). {name}: not a superkey in general (two people can share a name, even if our 12 rows have distinct names). {name, birth_year}: unlikely but still possible to clash, so normally not declared as key.

2. medium

   Which of these inserts into rating are rejected, and by which constraint? Remember rating(movie, source) is the primary key, score has CHECK (score BETWEEN 0 AND 10).
   (a) (3, 'Metacritic', 7.4, 46) (b) (3, 'IMDb', 8.0, 10) (c) (3, 'Letterboxd', 11, 10) (d) INSERT INTO rating (movie, source) VALUES (3, 'RottenT')

   Show solution

   (a) accepted (Interstellar has no Metacritic row yet). (b) rejected: primary key (3,'IMDb') already exists. (c) rejected by CHECK (11 > 10). (d) accepted: score and votes become NULL, and CHECK with NULL is unknown, which passes. Verified:

   INSERT INTO rating VALUES (3, 'IMDb', 8.0, 10);

   Error: UNIQUE constraint failed: rating.movie, rating.source

   INSERT INTO rating VALUES (3, 'Letterboxd', 11, 10);

   Error: CHECK constraint failed: score BETWEEN 0 AND 10

   INSERT INTO rating (movie, source) VALUES (3, 'RottenT');
   SELECT * FROM rating WHERE source = 'RottenT';

   movie  source   score  votes
   -----  -------  -----  -----
   3      RottenT

3. medium

   Write a CHECK constraint for a table person2(id, name, birth_year, death_year) that requires death after birth when the death year is known.

   Show solution

   CREATE TABLE person2 (
     id         INTEGER PRIMARY KEY,
     name       VARCHAR(100) NOT NULL,
     birth_year INTEGER,
     death_year INTEGER,
     CHECK (death_year IS NULL OR death_year >= birth_year)
   );
   INSERT INTO person2 VALUES (1, 'Stanley Kubrick', 1928, 1999);
   INSERT INTO person2 VALUES (2, 'Wrong Person', 1950, 1940);

   Error: CHECK constraint failed: death_year IS NULL OR death_year >= birth_year

   (Strictly, death_year >= birth_year alone already accepts NULL, because a CHECK that is unknown passes; writing IS NULL OR makes the intent clear.)

4. hard

   Relation screening(cinema, room, date, time, movie): a room of a cinema shows one movie at a given date and time. Find the key(s). Is {cinema, date, time} a superkey?

   Show solution

   Key: {cinema, room, date, time}: these four together identify one screening, and removing any one of them allows clashes (two rooms at the same time, same room at two times, two cinemas with a "Room 1"...). {cinema, date, time} is not a superkey: two rooms of the same cinema can show films at the same time. {cinema, room, date, time, movie} is a superkey but not minimal.

5. exam

   Given this instance of r(A, B, C): (1, x, 10), (2, x, 20), (3, y, 10). Based only on the instance, which single attributes and pairs could be keys? Why can the instance not prove a key?

   Show solution

   Single attributes: A (all distinct) could be a key; B is not (x repeats); C is not (10 repeats). Pairs: {B, C} has values (x,10),(x,20),(y,10): all distinct, so it could be a key (and it is minimal, since neither B nor C alone works). {A, B} and {A, C} are superkeys but not minimal (they contain A). An instance only shows the current data; a future insert could create a clash, so keys must come from the meaning of the data.

Self-check quiz

Common mistakes

• Calling a non-minimal superkey a "key". Key = minimal superkey.
• Deciding a key from one instance only. Keys come from the semantics (meaning) of the data.
• Forgetting that PRIMARY KEY implies NOT NULL on every column of the key.
• Thinking CHECK rejects NULL. A CHECK that evaluates to unknown passes; add NOT NULL if needed.
• Thinking a CHECK can look at other rows or tables. A standard CHECK is about one row; other tables need a foreign key.

A1.4 SQL-DDL: creating tables, attributes and domains

Explanation

-- template
CREATE TABLE table_name (
  column_name  data_type  [DEFAULT value]  [column constraints],
  ...,
  [table constraints]          -- e.g. PRIMARY KEY (a, b), UNIQUE (c), CHECK (...), FOREIGN KEY ...
);

Common PostgreSQL data types (domains)

Column constraint vs table constraint. A constraint on a single column can be written after the column (id INTEGER PRIMARY KEY). A constraint on several columns must be written as a separate line (PRIMARY KEY (movie, source)). Both forms mean the same thing for one column.

-- PostgreSQL only
CREATE DOMAIN score_domain AS NUMERIC(3,1)
  CHECK (VALUE BETWEEN 0 AND 10);

CREATE TABLE user_review (
  id     SERIAL PRIMARY KEY,
  movie  INTEGER REFERENCES movie(id),
  score  score_domain NOT NULL,
  posted DATE DEFAULT CURRENT_DATE
);

Changing and removing tables

-- PostgreSQL only
ALTER TABLE movie ADD COLUMN plot TEXT;                   -- new column (NULL in existing rows)
ALTER TABLE movie DROP COLUMN plot;                       -- remove a column
ALTER TABLE movie ALTER COLUMN budget SET DEFAULT 0;      -- change default
ALTER TABLE movie ALTER COLUMN title TYPE VARCHAR(300);   -- change type
ALTER TABLE movie ADD CONSTRAINT budget_pos CHECK (budget > 0);
ALTER TABLE movie RENAME COLUMN length TO duration;
DROP TABLE genre;                     -- removes table: schema AND data
DROP TABLE movie CASCADE;             -- also drops objects that depend on it (e.g. foreign keys)

Worked examples

Example 1: two new tables with keys and constraints (a country table and a many-to-many "which country produced which movie"):

CREATE TABLE country (
  code CHAR(2)     PRIMARY KEY,
  name VARCHAR(60) NOT NULL UNIQUE
);
CREATE TABLE produced (
  movie   INTEGER REFERENCES movie(id) ON DELETE CASCADE ON UPDATE CASCADE,
  country CHAR(2) REFERENCES country(code),
  PRIMARY KEY (movie, country)
);
INSERT INTO country VALUES ('US','United States'), ('KR','South Korea'), ('GB','United Kingdom');
INSERT INTO produced VALUES (7,'KR'), (1,'US'), (1,'GB');
SELECT * FROM produced;
INSERT INTO produced VALUES (2,'IT');   -- 'IT' is not in country

movie  country
-----  -------
7      KR
1      US
1      GB
Error: FOREIGN KEY constraint failed

Example 2: adding a column with a default. Existing rows get the default value:

ALTER TABLE person ADD COLUMN nationality CHAR(2) DEFAULT 'US';
SELECT id, name, nationality FROM person WHERE id IN (1, 8);

id  name               nationality
--  -----------------  -----------
1   Christopher Nolan  US
8   Bong Joon-ho       US

(Of course Bong Joon-ho is Korean: a default is only a starting value, you then UPDATE the exceptions.)

Example 3: DROP vs DELETE. After DELETE FROM genre the table exists and is empty; after DROP TABLE genre the table is gone:

DELETE FROM genre;
SELECT count(*) AS n FROM genre;
DROP TABLE genre;
SELECT count(*) AS n FROM genre;

n
-
0
Error: no such table: genre

Exercises

1. easy

   Which data type would you use for: (a) a movie title; (b) an ISO country code like 'IT'; (c) an average rating with one decimal (0.0 to 10.0); (d) a release date; (e) whether a movie is for adults?

   Show solution

   (a) VARCHAR(200) or TEXT; (b) CHAR(2); (c) NUMERIC(3,1) (exact; 10.0 needs 3 digits); (d) DATE; (e) BOOLEAN.

2. medium

   Write the DDL for review(id, movie, author, score, posted): id auto-generated, movie must exist in movie, author and score required, score an integer 1-5, posted defaults to today, and one author can review a movie only once.

   Show solution

   -- PostgreSQL only
   CREATE TABLE review (
     id      SERIAL PRIMARY KEY,
     movie   INTEGER NOT NULL REFERENCES movie(id),
     author  VARCHAR(50) NOT NULL,
     score   SMALLINT NOT NULL CHECK (score BETWEEN 1 AND 5),
     posted  DATE DEFAULT CURRENT_DATE,
     UNIQUE (movie, author)
   );

   UNIQUE (movie, author) makes {movie, author} a second key (an alternative key). Using {movie, author} as primary key instead of id is also correct.

3. medium

   Read this DDL and answer: can budget be NULL? Can two movies have the same title? Can length be 0?

   -- template
   CREATE TABLE movie (id INTEGER PRIMARY KEY, title VARCHAR(200) NOT NULL,
     year INTEGER CHECK (year >= 1888), length INTEGER CHECK (length > 0), budget NUMERIC(12,0));

   Show solution

   budget: yes (no NOT NULL). Same title: yes (no UNIQUE on title). length 0: no (CHECK length > 0), but length NULL: yes.

4. hard

   You must add the column imdb_code to movie, required and unique. Why does ALTER TABLE movie ADD COLUMN imdb_code VARCHAR(10) NOT NULL UNIQUE; fail on a table that already has rows (in PostgreSQL), and how do you do it in steps?

   Show solution

   Existing rows would get NULL in the new column, which violates NOT NULL, so PostgreSQL refuses. Steps:

   -- PostgreSQL only
   ALTER TABLE movie ADD COLUMN imdb_code VARCHAR(10);            -- 1. nullable column
   UPDATE movie SET imdb_code = 'tt' || lpad(id::text, 7, '0');   -- 2. fill existing rows
   ALTER TABLE movie ALTER COLUMN imdb_code SET NOT NULL;         -- 3. now enforce
   ALTER TABLE movie ADD CONSTRAINT imdb_code_unique UNIQUE (imdb_code);

Self-check quiz

Common mistakes

• Writing a composite key as two column constraints (movie INTEGER PRIMARY KEY, source VARCHAR PRIMARY KEY): that is an error (two primary keys). Use one table constraint PRIMARY KEY (movie, source).
• Using FLOAT for money or scores that must be exact; use NUMERIC.
• Confusing DROP TABLE (schema + data gone) with DELETE FROM (rows gone, table stays).
• Forgetting the commas between column definitions or adding one after the last line (syntax error).

A1.5 SQL-DML: INSERT, UPDATE, DELETE

Explanation

-- template
INSERT INTO table (col1, col2, ...) VALUES (v1, v2, ...), (w1, w2, ...);   -- one or more rows
INSERT INTO table (col1, ...) SELECT ... ;                                   -- rows from a query
UPDATE table SET col1 = expr1, col2 = expr2 WHERE condition;                 -- change rows
DELETE FROM table WHERE condition;                                           -- remove rows

• INSERT: if you list the columns, unlisted columns get their DEFAULT or NULL. Without a column list you must give values for all columns in table order.
• UPDATE: the SET expression can use the old value (votes = votes + 1000). WHERE chooses the rows; without WHERE, every row changes.
• DELETE: removes the rows that satisfy WHERE; without WHERE the table becomes empty (but still exists).
• The WHERE of UPDATE/DELETE can contain a subquery, e.g. WHERE id IN (SELECT movie FROM crew WHERE ...) (subqueries: A1.12).
• Each statement is atomic: if one row of an UPDATE breaks a constraint, the whole statement fails and no row changes.

Worked examples

Insert two rows (only some columns; the others become NULL):

INSERT INTO movie (id, title, year, length)
VALUES (9, 'Dune', 2021, 155), (10, 'Oppenheimer', 2023, 180);
SELECT id, title, year, budget FROM movie WHERE id >= 8;

id  title        year  budget
--  -----------  ----  ------
8   Tenet        2020
9   Dune         2021
10  Oppenheimer  2023

Update one row:

UPDATE movie SET budget = 205000000 WHERE title = 'Tenet';
SELECT id, title, budget FROM movie WHERE id = 8;

id  title  budget
--  -----  ---------
8   Tenet  205000000

Update using the old value:

UPDATE rating SET votes = votes + 1000 WHERE source = 'IMDb' AND movie = 7;
SELECT * FROM rating WHERE movie = 7;

movie  source      score  votes
-----  ----------  -----  ------
7      IMDb        8.5    951000
7      Metacritic  9.6    50

Update with a subquery: add 10 minutes to every movie directed by Nolan (person 1):

UPDATE movie SET length = length + 10
WHERE id IN (SELECT movie FROM crew WHERE person = 1 AND role = 'director');
SELECT title, length FROM movie ORDER BY id;

title            length
---------------  ------
Inception        158
The Matrix       136
Interstellar     179
Pulp Fiction     154
The Dark Knight  162
Memento          123
Parasite         132
Tenet            160

Delete some rows:

DELETE FROM rating WHERE source = 'Metacritic' AND score < 8;
SELECT movie, source, score FROM rating WHERE source = 'Metacritic';

movie  source      score
-----  ----------  -----
4      Metacritic  9.5
5      Metacritic  8.4
7      Metacritic  9.6

A statement is atomic: adding 0.5 to every Metacritic score would push Parasite to 10.1, breaking the CHECK, so no row is updated:

UPDATE rating SET score = score + 0.5 WHERE source = 'Metacritic';
SELECT movie, score FROM rating WHERE source = 'Metacritic';

Error: CHECK constraint failed: score BETWEEN 0 AND 10
movie  score
-----  -----
1      7.4
2      7.3
4      9.5
5      8.4
7      9.6

Insert from a query (copy data into another table):

CREATE TABLE nolan_movie (id INTEGER PRIMARY KEY, title VARCHAR(200));
INSERT INTO nolan_movie (id, title)
  SELECT m.id, m.title FROM movie m JOIN crew c ON c.movie = m.id
  WHERE c.person = 1 AND c.role = 'director';
SELECT * FROM nolan_movie;

id  title
--  ---------------
1   Inception
3   Interstellar
5   The Dark Knight
6   Memento
8   Tenet

Exercises

1. easy

   Insert the movie Dune (id 9, 2021, 155 minutes, budget unknown) with genres Sci-Fi and Adventure and an IMDb rating of 8.0 with 900000 votes.

   Show solution

   INSERT INTO movie (id, title, year, length) VALUES (9, 'Dune', 2021, 155);
   INSERT INTO genre VALUES (9, 'Sci-Fi'), (9, 'Adventure');
   INSERT INTO rating (movie, source, score, votes) VALUES (9, 'IMDb', 8.0, 900000);
   SELECT m.title, g.genre FROM movie m JOIN genre g ON g.movie = m.id WHERE m.id = 9;

   title  genre
   -----  ---------
   Dune   Adventure
   Dune   Sci-Fi

   The movie row must be inserted first: genre and rating reference it with a foreign key.

2. easy

   Write the statement that sets the budget of every movie with unknown budget to 0. What is wrong with UPDATE movie SET budget = 0 WHERE budget = NULL?

   Show solution

   UPDATE movie SET budget = 0 WHERE budget IS NULL;
   SELECT title, budget FROM movie WHERE id = 8;

   title  budget
   -----  ------
   Tenet  0

   budget = NULL is never true (it is unknown), so that version updates 0 rows.

3. medium

   Increase all budgets by 10%. What happens to Tenet?

   Show solution

   UPDATE movie SET budget = budget * 1.1;
   SELECT title, budget FROM movie WHERE id IN (4, 8);

   title         budget
   ------------  -------
   Pulp Fiction  8800000
   Tenet

   NULL * 1.1 is NULL, so Tenet stays NULL. Arithmetic with NULL gives NULL.

4. hard

   Delete all movies that have no rating. Which rows of other tables disappear too, and why? (Look at the DDL in A1.1.)

   Show solution

   DELETE FROM movie WHERE id NOT IN (SELECT movie FROM rating);
   SELECT (SELECT count(*) FROM movie) AS movies,
          (SELECT count(*) FROM crew)  AS crew_rows,
          (SELECT count(*) FROM genre) AS genre_rows;

   movies  crew_rows  genre_rows
   ------  ---------  ----------
   7       18         14

   Only Tenet is deleted (8 → 7 movies). Its 1 crew row and 2 genre rows are deleted too (19 → 18, 16 → 14) because crew.movie and genre.movie are declared ON DELETE CASCADE (A1.6).

5. exam

   Statement: UPDATE rating SET score = score + 0.5 WHERE source = 'Metacritic';. How many rows are changed in our database and why?

   Show solution

   Zero. Parasite's Metacritic score would become 10.1, which violates CHECK (score BETWEEN 0 AND 10); a single SQL statement is atomic, so the whole UPDATE is rolled back (see the worked example above). A fix: SET score = LEAST(score + 0.5, 10) in PostgreSQL.

Self-check quiz

Common mistakes

• Forgetting WHERE in UPDATE/DELETE: all rows change.
• Writing WHERE col = NULL instead of WHERE col IS NULL.
• Writing DELETE * FROM t (wrong: no column list in DELETE) or UPDATE t SET a = 1, WHERE ... (extra comma).
• Inserting child rows (crew, rating) before the parent movie exists: foreign key violation.
• Using double quotes for strings. In SQL, 'text' is a string; "text" is an identifier (a column/table name) in PostgreSQL.

A1.6 Foreign keys and referential integrity

Explanation

In our schema: crew.movie → movie.id, crew.person → person.id, rating.movie → movie.id, genre.movie → movie.id.

-- template
-- column form
movie INTEGER REFERENCES movie(id) ON DELETE CASCADE ON UPDATE CASCADE
-- table form (needed when the foreign key has several columns)
FOREIGN KEY (movie, source) REFERENCES rating(movie, source) ON DELETE SET NULL

What can violate referential integrity?

• On the child: INSERT of a row with a non-existing parent, or UPDATE of the foreign key to a non-existing value. These are always rejected.
• On the parent: DELETE of a referenced row, or UPDATE of its key. Here you choose a policy (reaction):

The policies are separate for ON DELETE and ON UPDATE. If you write nothing, both are NO ACTION.

Worked examples

Our DDL: crew.movie, rating.movie, genre.movie are ON DELETE CASCADE; crew.person has no policy (= NO ACTION).

1. Child insert with a missing parent is rejected (there is no movie 99):

INSERT INTO crew VALUES (99, 1, 'director', NULL);

Error: FOREIGN KEY constraint failed

2. CASCADE on delete: deleting Memento (id 6) also deletes its crew, rating and genre rows:

DELETE FROM movie WHERE id = 6;
SELECT (SELECT count(*) FROM crew   WHERE movie = 6) AS crew_left,
       (SELECT count(*) FROM rating WHERE movie = 6) AS rating_left,
       (SELECT count(*) FROM genre  WHERE movie = 6) AS genre_left;

crew_left  rating_left  genre_left
---------  -----------  ----------
0          0            0

3. NO ACTION on delete: Carrie-Anne Moss (id 10) still has crew rows, so she cannot be deleted. Meryl Streep (id 12) has none, so she can:

DELETE FROM person WHERE id = 10;

Error: FOREIGN KEY constraint failed

DELETE FROM person WHERE id = 12;
SELECT count(*) AS persons FROM person;

persons
-------
11

4. NO ACTION on update: crew.movie has ON DELETE CASCADE but nothing for update, so changing a referenced movie id is rejected:

UPDATE movie SET id = 100 WHERE id = 1;

Error: FOREIGN KEY constraint failed

5. SET NULL: an award keeps existing when its winner is removed:

CREATE TABLE award (
  id     INTEGER PRIMARY KEY,
  name   VARCHAR(100),
  winner INTEGER REFERENCES person(id) ON DELETE SET NULL
);
INSERT INTO award VALUES (1, 'Best Director', 8), (2, 'Best Actor', 11);
DELETE FROM crew WHERE person = 8;      -- first remove Bong Joon-ho's credits (NO ACTION on crew.person)
DELETE FROM person WHERE id = 8;
SELECT * FROM award;

id  name           winner
--  -------------  ------
1   Best Director
2   Best Actor     11

6. CASCADE on update:

CREATE TABLE country (code CHAR(2) PRIMARY KEY, name VARCHAR(60));
CREATE TABLE produced (
  movie   INTEGER REFERENCES movie(id),
  country CHAR(2) REFERENCES country(code) ON UPDATE CASCADE,
  PRIMARY KEY (movie, country)
);
INSERT INTO country VALUES ('UK', 'United Kingdom');
INSERT INTO produced VALUES (1, 'UK'), (3, 'UK');
UPDATE country SET code = 'GB' WHERE code = 'UK';
SELECT * FROM produced;

movie  country
-----  -------
1      GB
3      GB

Exercises

1. easy

   Which of these statements violate referential integrity in our database? (a) INSERT INTO genre VALUES (9, 'Drama') (b) INSERT INTO rating VALUES (8, 'IMDb', 7.3, 600000) (c) INSERT INTO crew VALUES (2, 12, 'actor', 'Oracle') (d) UPDATE crew SET person = 50 WHERE movie = 7

   Show solution

   (a) violates: movie 9 does not exist. (b) OK: movie 8 (Tenet) exists. (c) OK: movie 2 and person 12 exist (the fact is invented but the database cannot know). (d) violates: person 50 does not exist (child updates are always checked).

2. medium

   Starting from the original data, run DELETE FROM movie WHERE title = 'Pulp Fiction';. Give the new number of rows of movie, crew, rating, genre and person. (Before: 8, 19, 12, 16, 12.)

   Show solution

   DELETE FROM movie WHERE title = 'Pulp Fiction';
   SELECT (SELECT count(*) FROM movie) AS m, (SELECT count(*) FROM crew) AS c,
          (SELECT count(*) FROM rating) AS r, (SELECT count(*) FROM genre) AS g,
          (SELECT count(*) FROM person) AS p;

   m  c   r   g   p
   -  --  --  --  --
   7  16  10  14  12

   Pulp Fiction had 3 crew rows (Tarantino as director and actor, Uma Thurman), 2 ratings and 2 genres, all removed by CASCADE. person is unchanged: cascade goes from parent to child, never from crew to person.

3. medium

   Why does DELETE FROM person WHERE name = 'Uma Thurman'; fail, and what must you do first?

   Show solution

   DELETE FROM person WHERE name = 'Uma Thurman';

   Error: FOREIGN KEY constraint failed

   crew.person is NO ACTION and she has a crew row. Delete her crew rows first, then the person:

   DELETE FROM crew WHERE person = 6;
   DELETE FROM person WHERE name = 'Uma Thurman';
   SELECT count(*) AS persons FROM person;

   persons
   -------
   11

4. hard

   Design the policies for a table watchlist(user_id, movie) (a user's saved movies) and for crew.person. Justify. Write the DDL of watchlist assuming a table app_user(id).

   Show solution

   A watchlist entry has no meaning without its user or its movie: ON DELETE CASCADE for both. For crew.person, deleting a person who has credits is probably a mistake: keep NO ACTION (block it). If ids can change, add ON UPDATE CASCADE.

   CREATE TABLE app_user (id INTEGER PRIMARY KEY, email VARCHAR(100) UNIQUE);
   CREATE TABLE watchlist (
     user_id INTEGER REFERENCES app_user(id) ON DELETE CASCADE ON UPDATE CASCADE,
     movie   INTEGER REFERENCES movie(id)    ON DELETE CASCADE ON UPDATE CASCADE,
     added   DATE,
     PRIMARY KEY (user_id, movie)
   );

5. exam

   Tables A(id), B(id, a REFERENCES A ON DELETE CASCADE), C(id, b REFERENCES B ON DELETE SET NULL), D(id, b REFERENCES B) (no policy). You delete one row of A that is referenced by 2 rows of B; one of those B rows is referenced by a row of C, and none by D. What happens? And what if one of the B rows were referenced by a row of D?

   Show solution

   Case 1: the A row is deleted; the 2 B rows are deleted (CASCADE); the C row that pointed to a deleted B row gets b = NULL (SET NULL). Everything succeeds. Case 2: deleting that B row would leave a dangling D row, and D's policy is NO ACTION, so the B delete is rejected; since everything happens inside one statement, the whole DELETE on A fails and nothing changes.

Self-check quiz

Common mistakes

• Thinking CASCADE works "upwards" (deleting a crew row deletes the movie). It only goes from the referenced (parent) row to the referencing (child) rows.
• Thinking a policy can make a child insert with a missing parent succeed. Policies only react to changes on the parent.
• Forgetting that NO ACTION is the default.
• Pointing a foreign key at a column that is not a key/UNIQUE in the parent: not allowed.
• Forgetting that a NULL foreign key value is allowed (it refers to nothing) unless the column is NOT NULL.

A1.7 Transactions, concurrency and ACID

Explanation

Concurrency anomalies (what isolation prevents)

SQL defines isolation levels: READ UNCOMMITTED, READ COMMITTED (PostgreSQL default), REPEATABLE READ, SERIALIZABLE (strongest: behaves like a serial execution). Higher level = fewer anomalies, but more waiting or aborted transactions.

Worked examples

ROLLBACK undoes everything since BEGIN. Move 100 votes from Inception to The Matrix, then change our mind:

BEGIN;
UPDATE rating SET votes = votes - 100 WHERE movie = 1 AND source = 'IMDb';
UPDATE rating SET votes = votes + 100 WHERE movie = 2 AND source = 'IMDb';
ROLLBACK;
SELECT movie, votes FROM rating WHERE source = 'IMDb' AND movie IN (1, 2);

movie  votes
-----  -------
1      2400000
2      2000000

COMMIT makes both changes permanent together (atomicity: never one without the other):

BEGIN;
UPDATE rating SET votes = votes - 100 WHERE movie = 1 AND source = 'IMDb';
UPDATE rating SET votes = votes + 100 WHERE movie = 2 AND source = 'IMDb';
COMMIT;
SELECT movie, votes FROM rating WHERE source = 'IMDb' AND movie IN (1, 2);

movie  votes
-----  -------
1      2399900
2      2000100

An error inside a transaction. Here the second insert breaks the primary key:

BEGIN;
INSERT INTO movie (id, title) VALUES (20, 'Barbie');
INSERT INTO movie (id, title) VALUES (20, 'Barbie again');
COMMIT;
SELECT id, title FROM movie WHERE id = 20;

Error: UNIQUE constraint failed: movie.id
id  title
--  ------
20  Barbie

A lost update, step by step (two sessions, PostgreSQL, default READ COMMITTED, application code that reads then writes):

-- template
-- T1                                         -- T2
BEGIN;                                        BEGIN;
SELECT votes FROM rating WHERE movie = 7 AND source = 'IMDb';   -- both read 950000
                                              SELECT votes FROM rating WHERE movie = 7 AND source = 'IMDb';
UPDATE rating SET votes = 950001 WHERE ...;   -- value computed in the app
COMMIT;
                                              UPDATE rating SET votes = 950001 WHERE ...;
                                              COMMIT;    -- final value 950001, one vote lost

Fixes: compute in SQL (SET votes = votes + 1, which re-reads the current committed value), lock the row with SELECT ... FOR UPDATE, or use SERIALIZABLE.

Exercises

1. easy

   Match each scenario to the ACID property it is about: (a) the server loses power 1 second after COMMIT; after restart the change is there. (b) A transfer of votes updates the first row, then crashes; after restart neither row is changed. (c) An update that would give a score of 11 is rejected. (d) Two analysts update the same row at the same time and the result equals some serial order.

   Show solution

   (a) Durability. (b) Atomicity. (c) Consistency. (d) Isolation.

2. easy

   What is the content of rating for movie 3 after this script (PostgreSQL)?

   BEGIN;
   UPDATE rating SET score = 9.9 WHERE movie = 3;
   DELETE FROM rating WHERE movie = 3;
   ROLLBACK;
   SELECT * FROM rating WHERE movie = 3;

   Show solution

   Unchanged: ROLLBACK undoes both statements.

   movie  source  score  votes
   -----  ------  -----  -------
   3      IMDb    8.7    2100000

3. medium

   Name the anomaly: T1 computes the average IMDb score (8.71). T2 inserts a new IMDb rating and commits. T1 computes the average again in the same transaction and gets a different value.

   Show solution

   A phantom (a new row appeared in the set read by T1). If T2 had instead modified an existing row that T1 had read, it would be a non-repeatable read. Both are prevented by SERIALIZABLE (in PostgreSQL also REPEATABLE READ prevents them).

4. hard

   Two sessions each run UPDATE rating SET votes = votes + 1 WHERE movie = 7 AND source = 'IMDb'; at the same time. Is an update lost? Explain.

   Show solution

   No. An UPDATE locks the row it changes. The second session waits until the first commits, then re-reads the current value and adds 1, so the final value is +2. The lost update happens when the application reads the value and writes back a value computed outside SQL.

5. exam

   Explain the difference between consistency (the C in ACID) and integrity constraints. Can a transaction be temporarily inconsistent?

   Show solution

   Integrity constraints define which states are legal. Consistency means a committed transaction moves the database from a legal state to a legal state. Inside a transaction, intermediate states can break some rules (for example with DEFERRABLE foreign keys, checked only at COMMIT); what matters is the state at commit. Non-deferred constraints, as in our tables, are checked at the end of every statement.

Self-check quiz

Common mistakes

• Mixing atomicity (all or nothing) and durability (committed changes survive crashes).
• Thinking isolation means "transactions run one at a time". They run concurrently; the result must look serial.
• Thinking ROLLBACK can undo a transaction that is already committed. It cannot.
• Assuming PostgreSQL commits the good statements after an error inside a transaction block. It aborts the whole transaction.

A1.8 SELECT basics: WHERE, BETWEEN, LIKE, IS NULL, IN, ORDER BY, AS, DISTINCT

Explanation

-- template
SELECT [DISTINCT] expr1 [AS name1], expr2 ...   -- 5. which columns (projection)
FROM table                                       -- 1. where the rows come from
WHERE condition                                  -- 2. keep only some rows (selection)
ORDER BY expr [ASC|DESC], ...                    -- 6. sort the result
LIMIT n;                                         -- 7. keep the first n rows (PostgreSQL, SQLite)

The numbers show the logical order of evaluation (GROUP BY and HAVING, steps 3-4, come in A1.11). This explains a classic rule: an alias defined in SELECT can be used in ORDER BY but not in WHERE (WHERE runs before SELECT).

Worked examples

AND:

SELECT title, year FROM movie WHERE year >= 2010 AND length > 140;

title         year
------------  ----
Inception     2010
Interstellar  2014
Tenet         2020

BETWEEN includes both ends (1999 and 2008 are in):

SELECT title, year FROM movie WHERE year BETWEEN 1999 AND 2008 ORDER BY year;

title            year
---------------  ----
The Matrix       1999
Memento          2000
The Dark Knight  2008

LIKE with % and _:

SELECT title FROM movie WHERE title LIKE 'The %';

title
---------------
The Matrix
The Dark Knight

SELECT name FROM person WHERE name LIKE '_e%';   -- second letter is 'e'

name
-----------------
Leonardo DiCaprio
Keanu Reeves
Meryl Streep

SELECT name FROM person WHERE name LIKE '%an%' ORDER BY name;

name
-----------------
Christian Bale
Christopher Nolan
Keanu Reeves
Lana Wachowski
Quentin Tarantino
Song Kang-ho
Uma Thurman

(Anne Hathaway and Carrie-Anne Moss are not there: LIKE is case-sensitive in PostgreSQL, and they only have An. Use ILIKE in PostgreSQL for case-insensitive matching.)

IS NULL vs = NULL:

SELECT title FROM movie WHERE budget IS NULL;

title
-----
Tenet

SELECT title FROM movie WHERE budget = NULL;   -- always UNKNOWN: returns nothing

(no rows)

NOT and NULL. Tenet is in neither result: budget > 100000000 is unknown for it, and NOT unknown is still unknown:

SELECT title FROM movie WHERE NOT (budget > 100000000);

title
------------
The Matrix
Pulp Fiction
Memento
Parasite

Precedence: AND before OR. The first query means year < 2000 OR (year > 2015 AND length > 140):

SELECT title, year FROM movie WHERE year < 2000 OR year > 2015 AND length > 140;

title         year
------------  ----
The Matrix    1999
Pulp Fiction  1994
Tenet         2020

SELECT title, year FROM movie WHERE (year < 2000 OR year > 2015) AND length > 140;

title         year
------------  ----
Pulp Fiction  1994
Tenet         2020

IN, and ORDER BY on two columns (second column breaks ties):

SELECT name, birth_year FROM person
WHERE birth_year IN (1970, 1974)
ORDER BY birth_year DESC, name ASC;

name               birth_year
-----------------  ----------
Christian Bale     1974
Leonardo DiCaprio  1974
Christopher Nolan  1970
Uma Thurman        1970

DISTINCT:

SELECT DISTINCT role FROM crew;

role
--------
director
writer
actor

Expressions and aliases (alias used in ORDER BY):

SELECT title AS movie_title, length / 60.0 AS hours
FROM movie WHERE length > 150 ORDER BY hours DESC;

movie_title      hours
---------------  ----------------
Interstellar     2.81666666666667
Pulp Fiction     2.56666666666667
The Dark Knight  2.53333333333333

Sorting with NULLs. PostgreSQL puts NULLs last in ascending order (first in DESC); SQLite does the opposite. Write NULLS LAST/NULLS FIRST to be explicit (works in both):

SELECT title, budget FROM movie ORDER BY budget NULLS LAST LIMIT 3;

title         budget
------------  --------
Pulp Fiction  8000000
Memento       9000000
Parasite      11400000

Exercises

1. easy

   Titles and years of the movies released between 2000 and 2015 (included) that last less than 150 minutes, newest first.

   Show solution

   SELECT title, year FROM movie
   WHERE year BETWEEN 2000 AND 2015 AND length < 150
   ORDER BY year DESC;

   title      year
   ---------  ----
   Inception  2010
   Memento    2000

2. easy

   Names and birth years of the women born before 1966 or after 1980, oldest first.

   Show solution

   SELECT name, birth_year FROM person
   WHERE sex = 'F' AND (birth_year < 1966 OR birth_year > 1980)
   ORDER BY birth_year;

   name            birth_year
   --------------  ----------
   Meryl Streep    1949
   Lana Wachowski  1965
   Anne Hathaway   1982

   Without the parentheses, the men born after 1980 would also qualify (AND binds tighter than OR).

3. medium

   The distinct character names that contain a lowercase a, in alphabetical order (ignore crew rows with no character).

   Show solution

   SELECT DISTINCT character FROM crew
   WHERE character IS NOT NULL AND character LIKE '%a%'
   ORDER BY character;

   character
   -----------
   Brand
   Bruce Wayne
   Kim Ki-taek
   Mia Wallace
   Natalie

   IS NOT NULL is not strictly needed (NULL LIKE ... is unknown, so the row is dropped anyway) but it shows intent.

4. medium

   For movies whose title does not start with "The " and whose budget is known: title and budget in millions (one decimal) as budget_musd, most expensive first.

   Show solution

   SELECT title, round(budget / 1000000.0, 1) AS budget_musd
   FROM movie
   WHERE budget IS NOT NULL AND title NOT LIKE 'The %'
   ORDER BY budget_musd DESC;

   title         budget_musd
   ------------  -----------
   Interstellar  165.0
   Inception     160.0
   Parasite      11.4
   Memento       9.0
   Pulp Fiction  8.0

   (PostgreSQL prints 165.0, 11.4 etc. the same way here because the type is numeric.)

5. exam

   How many rows does each query return on our data? (a) SELECT * FROM movie WHERE budget < 50000000 OR budget >= 50000000; (b) SELECT DISTINCT movie FROM rating; (c) SELECT title FROM movie WHERE year NOT IN (1994, 1999) AND budget IS NOT NULL;

   Show solution

   (a) 7, not 8: for Tenet both comparisons are unknown, and unknown OR unknown is unknown. (b) 7: every movie except Tenet has at least one rating. (c) 5: remove Pulp Fiction (1994), The Matrix (1999) and Tenet (NULL budget) from 8.

   SELECT (SELECT count(*) FROM movie WHERE budget < 50000000 OR budget >= 50000000) AS a,
          (SELECT count(DISTINCT movie) FROM rating) AS b,
          (SELECT count(*) FROM movie WHERE year NOT IN (1994, 1999) AND budget IS NOT NULL) AS c;

   a  b  c
   -  -  -
   7  7  5

Self-check quiz

Common mistakes

• WHERE x = NULL: always empty. Use IS NULL.
• Forgetting parentheses when mixing AND and OR.
• Using a SELECT alias in WHERE (WHERE hours > 2): error, because WHERE is evaluated before SELECT. Repeat the expression instead.
• Thinking BETWEEN excludes the ends. It includes them.
• Using * instead of % in LIKE, or double quotes for strings.
• Expecting rows in a given order without ORDER BY. Without ORDER BY the order is not guaranteed.

A1.9 Cartesian product, inner joins and table aliases

Explanation

-- template
-- 1) implicit join (condition in WHERE)
SELECT ... FROM movie m, rating r WHERE m.id = r.movie AND ...;
-- 2) explicit join (preferred)
SELECT ... FROM movie m JOIN rating r ON m.id = r.movie WHERE ...;

• JOIN means INNER JOIN: rows with no partner are dropped (Tenet has no rating, so it disappears from movie JOIN rating).
• Table aliases: movie m or movie AS m. Then write m.title. Aliases are required when the same table appears twice (self-join) and when a column name is ambiguous (both crew and rating have movie).
• Chain joins for 3+ tables: movie m JOIN crew c ON c.movie = m.id JOIN person p ON p.id = c.person.
• Other forms: JOIN ... USING (col) when both tables have a column with the same name; NATURAL JOIN joins on all columns with equal names (dangerous: rarely what you want).

Worked examples

Cartesian product size: 8 movies × 12 ratings:

SELECT count(*) AS n FROM movie, rating;

n
--
96

Implicit vs explicit join, same result: ratings of at least 9 with the movie title.

SELECT m.title, r.source, r.score
FROM movie m, rating r
WHERE m.id = r.movie AND r.score >= 9;

title            source      score
---------------  ----------  -----
Pulp Fiction     Metacritic  9.5
The Dark Knight  IMDb        9
Parasite         Metacritic  9.6

SELECT m.title, r.source, r.score
FROM movie AS m JOIN rating AS r ON m.id = r.movie
WHERE r.score >= 9;

title            source      score
---------------  ----------  -----
Pulp Fiction     Metacritic  9.5
The Dark Knight  IMDb        9
Parasite         Metacritic  9.6

Three tables: every movie with its director.

SELECT m.title, p.name AS director
FROM movie m
JOIN crew c   ON c.movie = m.id
JOIN person p ON p.id = c.person
WHERE c.role = 'director'
ORDER BY m.year;

title            director
---------------  -----------------
Pulp Fiction     Quentin Tarantino
The Matrix       Lana Wachowski
Memento          Christopher Nolan
The Dark Knight  Christopher Nolan
Inception        Christopher Nolan
Interstellar     Christopher Nolan
Parasite         Bong Joon-ho
Tenet            Christopher Nolan

Self-join on crew: actors who acted in a movie directed by Christopher Nolan. We need crew twice (once for the director row, once for the actor row) and person twice (director name, actor name):

SELECT DISTINCT a.name
FROM crew c1
JOIN crew c2   ON c1.movie = c2.movie
JOIN person d  ON d.id = c1.person
JOIN person a  ON a.id = c2.person
WHERE c1.role = 'director' AND d.name = 'Christopher Nolan'
  AND c2.role = 'actor'
ORDER BY a.name;

name
-----------------
Anne Hathaway
Carrie-Anne Moss
Christian Bale
Leonardo DiCaprio

Self-join on person: pairs of people born in the same year. p1.id < p2.id avoids pairing a person with themselves and listing each pair twice:

SELECT p1.name AS person1, p2.name AS person2, p1.birth_year
FROM person p1 JOIN person p2
  ON p1.birth_year = p2.birth_year AND p1.id < p2.id;

person1            person2         birth_year
-----------------  --------------  ----------
Christopher Nolan  Uma Thurman     1970
Leonardo DiCaprio  Christian Bale  1974
Carrie-Anne Moss   Song Kang-ho    1967

Condition in ON + LIMIT: top 3 movies by IMDb score.

SELECT m.title, r.score
FROM movie m JOIN rating r ON m.id = r.movie AND r.source = 'IMDb'
ORDER BY r.score DESC
LIMIT 3;

title            score
---------------  -----
The Dark Knight  9
Pulp Fiction     8.9
Inception        8.8

Exercises

1. easy

   Title and genre of every movie released before 2000, sorted by title and genre.

   Show solution

   SELECT m.title, g.genre
   FROM movie m JOIN genre g ON g.movie = m.id
   WHERE m.year < 2000
   ORDER BY m.title, g.genre;

   title         genre
   ------------  ------
   Pulp Fiction  Crime
   Pulp Fiction  Drama
   The Matrix    Action
   The Matrix    Sci-Fi

2. medium

   For every acting role played by a woman: actress name, movie title and character.

   Show solution

   SELECT p.name, m.title, c.character
   FROM person p
   JOIN crew c  ON c.person = p.id
   JOIN movie m ON m.id = c.movie
   WHERE c.role = 'actor' AND p.sex = 'F'
   ORDER BY p.name;

   name              title         character
   ----------------  ------------  -----------
   Anne Hathaway     Interstellar  Brand
   Carrie-Anne Moss  The Matrix    Trinity
   Carrie-Anne Moss  Memento       Natalie
   Uma Thurman       Pulp Fiction  Mia Wallace

3. medium

   Titles of the movies whose director was born before 1968 and whose IMDb score is at least 8.7 (four tables).

   Show solution

   SELECT DISTINCT m.title
   FROM movie m
   JOIN crew c   ON c.movie = m.id
   JOIN person p ON p.id = c.person
   JOIN rating r ON r.movie = m.id
   WHERE c.role = 'director' AND p.birth_year < 1968
     AND r.source = 'IMDb' AND r.score >= 8.7;

   title
   ------------
   The Matrix
   Pulp Fiction

   Lana Wachowski (1965) and Quentin Tarantino (1963) qualify; Nolan (1970) and Bong (1969) do not.

4. hard

   Movies where the director also acts: title, director name and actor name.

   Show solution

   SELECT m.title, d.name AS director, a.name AS actor
   FROM movie m
   JOIN crew cd  ON cd.movie = m.id AND cd.role = 'director'
   JOIN person d ON d.id = cd.person
   JOIN crew ca  ON ca.movie = m.id AND ca.role = 'actor'
   JOIN person a ON a.id = ca.person
   WHERE d.id = a.id;

   title         director           actor
   ------------  -----------------  -----------------
   Pulp Fiction  Quentin Tarantino  Quentin Tarantino

   Shorter: FROM crew cd JOIN crew ca ON cd.movie = ca.movie AND cd.person = ca.person with the two role conditions.

5. exam

   Pairs of movies directed by Christopher Nolan where the second came out at most 2 years after the first (older title, newer title).

   Show solution

   SELECT a.title AS older, b.title AS newer
   FROM movie a
   JOIN crew ca  ON ca.movie = a.id AND ca.role = 'director'
   JOIN movie b  ON b.year > a.year AND b.year - a.year <= 2
   JOIN crew cb  ON cb.movie = b.id AND cb.role = 'director' AND cb.person = ca.person
   JOIN person p ON p.id = ca.person
   WHERE p.name = 'Christopher Nolan'
   ORDER BY a.year;

   older            newer
   ---------------  ---------
   The Dark Knight  Inception

   The Dark Knight (2008) and Inception (2010). Note the join condition does not have to be an equality.

Self-check quiz

Common mistakes

• Listing two tables in FROM and forgetting the join condition: you get the full Cartesian product.
• Ambiguous column: SELECT movie FROM crew JOIN rating ... fails, both tables have movie. Prefix with the alias.
• Using one alias for two roles: to talk about "the director" and "the actor" of a movie you need two copies of crew (and of person).
• Forgetting DISTINCT when a join multiplies rows (a movie with 2 ratings appears twice).
• After FROM movie AS m, writing movie.title: in PostgreSQL the alias replaces the name, so use m.title.

A1.10 Outer joins: LEFT, RIGHT, FULL

Explanation

Anti-join pattern ("A rows with no B"): A LEFT JOIN B ON ... WHERE B.key IS NULL. (Alternatives with NOT EXISTS / NOT IN / EXCEPT: A1.12, A1.13.)

Worked examples

All movies with their Metacritic score, if any (condition in ON):

SELECT m.title, r.score
FROM movie m LEFT JOIN rating r
  ON m.id = r.movie AND r.source = 'Metacritic'
ORDER BY m.id;

title            score
---------------  -----
Inception        7.4
The Matrix       7.3
Interstellar
Pulp Fiction     9.5
The Dark Knight  8.4
Memento
Parasite         9.6
Tenet

Same condition in WHERE: the NULL rows are removed, we lost Interstellar, Memento, Tenet:

SELECT m.title, r.score
FROM movie m LEFT JOIN rating r ON m.id = r.movie
WHERE r.source = 'Metacritic'
ORDER BY m.id;

title            score
---------------  -----
Inception        7.4
The Matrix       7.3
Pulp Fiction     9.5
The Dark Knight  8.4
Parasite         9.6

Anti-join: movies without any rating.

SELECT m.title
FROM movie m LEFT JOIN rating r ON m.id = r.movie
WHERE r.movie IS NULL;

title
-----
Tenet

RIGHT JOIN: people who are in no crew (person is on the right, so all persons are kept):

SELECT p.name, c.movie
FROM crew c RIGHT JOIN person p ON p.id = c.person
WHERE c.movie IS NULL;

name          movie
------------  -----
Meryl Streep

FULL JOIN: compare our movies with a small list of Oscar winners. Matches in the middle, unmatched rows on both sides padded with NULL:

CREATE TABLE oscar (movie_title VARCHAR(200), year INTEGER);
INSERT INTO oscar VALUES ('Parasite', 2020), ('Oppenheimer', 2024);
SELECT m.title, o.movie_title, o.year AS oscar_year
FROM movie m FULL OUTER JOIN oscar o ON m.title = o.movie_title
WHERE m.year > 2015 OR m.year IS NULL;

title     movie_title  oscar_year
--------  -----------  ----------
Parasite  Parasite     2020
Tenet
          Oppenheimer  2024

(The WHERE just keeps the output short: recent movies plus the Oscar rows with no movie.)

LEFT JOIN + count: count(column) skips NULLs, so people with no credits get 0 (with count(*) they would get 1):

SELECT p.name, count(c.movie) AS n_credits
FROM person p LEFT JOIN crew c ON c.person = p.id
GROUP BY p.id, p.name
ORDER BY n_credits, p.name
LIMIT 3;

name           n_credits
-------------  ---------
Meryl Streep   0
Anne Hathaway  1
Bong Joon-ho   1

Exercises

1. easy

   Every movie with its number of ratings (0 if none), fewest first.

   Show solution

   SELECT m.title, count(r.movie) AS n_ratings
   FROM movie m LEFT JOIN rating r ON r.movie = m.id
   GROUP BY m.id, m.title
   ORDER BY n_ratings, m.title;

   title            n_ratings
   ---------------  ---------
   Tenet            0
   Interstellar     1
   Memento          1
   Inception        2
   Parasite         2
   Pulp Fiction     2
   The Dark Knight  2
   The Matrix       2

2. medium

   Names of the people who never directed a movie (use a LEFT JOIN).

   Show solution

   SELECT p.name
   FROM person p LEFT JOIN crew c ON c.person = p.id AND c.role = 'director'
   WHERE c.person IS NULL
   ORDER BY p.name;

   name
   -----------------
   Anne Hathaway
   Carrie-Anne Moss
   Christian Bale
   Keanu Reeves
   Leonardo DiCaprio
   Meryl Streep
   Song Kang-ho
   Uma Thurman

   The role condition must be in ON. In WHERE (WHERE c.role = 'director' AND c.person IS NULL) the result would be empty.

3. medium

   One row per movie with two columns imdb and metacritic (NULL when missing).

   Show solution

   SELECT m.title, i.score AS imdb, mc.score AS metacritic
   FROM movie m
   LEFT JOIN rating i  ON i.movie = m.id  AND i.source = 'IMDb'
   LEFT JOIN rating mc ON mc.movie = m.id AND mc.source = 'Metacritic'
   ORDER BY m.id;

   title            imdb  metacritic
   ---------------  ----  ----------
   Inception        8.8   7.4
   The Matrix       8.7   7.3
   Interstellar     8.7
   Pulp Fiction     8.9   9.5
   The Dark Knight  9     8.4
   Memento          8.4
   Parasite         8.5   9.6
   Tenet

   Two LEFT JOINs to the same table with two aliases. Inner joins would keep only the 5 movies that have both scores.

4. hard

   Movies from 2010 onwards with their Metacritic score, showing 0 when it is missing (use COALESCE(x, default), which returns the first non-NULL argument).

   Show solution

   SELECT m.title, COALESCE(r.score, 0) AS metacritic
   FROM movie m
   LEFT JOIN rating r ON r.movie = m.id AND r.source = 'Metacritic'
   WHERE m.year >= 2010
   ORDER BY m.title;

   title         metacritic
   ------------  ----------
   Inception     7.4
   Interstellar  0
   Parasite      9.6
   Tenet         0

   Here the condition on m.year is on the left table, so WHERE is correct.

5. exam

   Without running: how many rows do these return? (a) movie m JOIN rating r ON r.movie = m.id (b) movie m LEFT JOIN rating r ON r.movie = m.id (c) person p LEFT JOIN crew c ON c.person = p.id (d) movie m FULL JOIN rating r ON r.movie = m.id

   Show solution

   (a) 12: one per rating row. (b) 13: the 12 matches + Tenet padded with NULL. (c) 20: 19 crew rows (each matches one person) + Meryl Streep. (d) 13: same as (b), because every rating references an existing movie (foreign key), so there are no unmatched ratings.

   SELECT (SELECT count(*) FROM movie m JOIN rating r ON r.movie = m.id) AS a,
          (SELECT count(*) FROM movie m LEFT JOIN rating r ON r.movie = m.id) AS b,
          (SELECT count(*) FROM person p LEFT JOIN crew c ON c.person = p.id) AS c,
          (SELECT count(*) FROM movie m FULL JOIN rating r ON r.movie = m.id) AS d;

   a   b   c   d
   --  --  --  --
   12  13  20  13

Self-check quiz

Common mistakes

• Putting a condition on the right table in WHERE: the LEFT JOIN becomes an inner join.
• Using count(*) after a LEFT JOIN to count matches: unmatched rows count as 1. Use count(b.column).
• In the anti-join, testing a column that can be NULL anyway (like character). Test the join column or the key of the right table.
• Thinking FULL JOIN = UNION. FULL JOIN combines columns side by side; UNION stacks rows (A1.13).

A1.11 Aggregate functions, GROUP BY and HAVING

Explanation

Without GROUP BY, an aggregate collapses the whole table into one row. With GROUP BY cols, rows with equal values on cols form a group and you get one row per group.

-- template
SELECT group_cols, aggregates        -- 5
FROM ... JOIN ...                    -- 1
WHERE row_condition                  -- 2  filters ROWS, before grouping (no aggregates here)
GROUP BY group_cols                  -- 3
HAVING group_condition               -- 4  filters GROUPS, after grouping (aggregates allowed)
ORDER BY ...;                        -- 6

Worked examples

Whole-table aggregates (note count(*) = 8 but count(budget) = 7):

SELECT count(*) AS n_movies, count(budget) AS n_with_budget,
       min(year) AS first, max(year) AS last,
       avg(length) AS avg_len, sum(budget) AS total_budget
FROM movie;

n_movies  n_with_budget  first  last  avg_len  total_budget
--------  -------------  -----  ----  -------  ------------
8         7              1994   2020  144.25   601400000

avg ignores NULL: it divides by 7, not 8.

SELECT avg(budget) AS avg_ignoring_null, sum(budget) / count(*) AS sum_over_all_rows
FROM movie;

avg_ignoring_null  sum_over_all_rows
-----------------  -----------------
85914285.7142857   75175000

GROUP BY one column:

SELECT source, count(*) AS n, avg(score) AS avg_score, max(score) AS best
FROM rating
GROUP BY source;

source      n  avg_score         best
----------  -  ----------------  ----
IMDb        7  8.71428571428571  9
Metacritic  5  8.44              9.6

HAVING: genres with at least 3 movies.

SELECT genre, count(*) AS n_movies
FROM genre
GROUP BY genre
HAVING count(*) >= 3
ORDER BY n_movies DESC, genre;

genre   n_movies
------  --------
Action  4
Sci-Fi  4
Drama   3

Join + WHERE + GROUP BY + HAVING: people who directed more than one movie.

SELECT p.name, count(*) AS n_directed
FROM person p JOIN crew c ON c.person = p.id
WHERE c.role = 'director'
GROUP BY p.id, p.name
HAVING count(*) > 1;

name               n_directed
-----------------  ----------
Christopher Nolan  5

We group by p.id, p.name (not only name) because two different people could share a name.

WHERE and HAVING together: among movies from 2000 onwards, genres whose average length is above 140:

SELECT g.genre, avg(m.length) AS avg_len
FROM genre g JOIN movie m ON m.id = g.movie
WHERE m.year >= 2000
GROUP BY g.genre
HAVING avg(m.length) > 140
ORDER BY g.genre;

genre   avg_len
------  ----------------
Action  150.0
Crime   152.0
Drama   150.5
Sci-Fi  155.666666666667

count(DISTINCT ...):

SELECT count(DISTINCT person) AS n_people_in_crew, count(person) AS n_rows FROM crew;

n_people_in_crew  n_rows
----------------  ------
11                19

The GROUP BY rule broken. SQLite accepts this and returns an arbitrary-looking row; PostgreSQL rejects it. The correct way to get "the most recent movie" is a subquery (A1.12) or ORDER BY year DESC LIMIT 1.

SELECT title, max(year) FROM movie;   -- WRONG in PostgreSQL

title  max(year)
-----  ---------
Tenet  2020

Exercises

1. easy

   For each role in crew: number of rows and number of different people.

   Show solution

   SELECT role, count(*) AS n, count(DISTINCT person) AS n_people
   FROM crew GROUP BY role ORDER BY role;

   role      n  n_people
   --------  -  --------
   actor     9  8
   director  8  4
   writer    2  1

2. medium

   Number of movies and average length (1 decimal) per decade (1990, 2000, ...).

   Show solution

   SELECT year / 10 * 10 AS decade, count(*) AS n, round(avg(length), 1) AS avg_len
   FROM movie
   GROUP BY year / 10 * 10
   ORDER BY decade;

   decade  n  avg_len
   ------  -  -------
   1990    2  145.0
   2000    2  132.5
   2010    3  149.7
   2020    1  150.0

   Integer division: 2014 / 10 * 10 = 2010. (PostgreSQL also allows GROUP BY decade, using the alias.)

3. medium

   People who worked on at least 2 different movies (in any role), with the number of movies.

   Show solution

   SELECT p.name, count(DISTINCT c.movie) AS n_movies
   FROM person p JOIN crew c ON c.person = p.id
   GROUP BY p.id, p.name
   HAVING count(DISTINCT c.movie) >= 2
   ORDER BY n_movies DESC, p.name;

   name               n_movies
   -----------------  --------
   Christopher Nolan  5
   Carrie-Anne Moss   2

   With count(*) Tarantino would count 2 (director + actor in the same movie) and appear by mistake.

4. hard

   Average IMDb score per genre (2 decimals), only for genres with at least 2 IMDb-rated movies, best first.

   Show solution

   SELECT g.genre, round(avg(r.score), 2) AS avg_imdb
   FROM genre g JOIN rating r ON r.movie = g.movie
   WHERE r.source = 'IMDb'
   GROUP BY g.genre
   HAVING count(*) >= 2
   ORDER BY avg_imdb DESC;

   genre     avg_imdb
   --------  --------
   Crime     8.95
   Action    8.83
   Sci-Fi    8.73
   Drama     8.7
   Thriller  8.45

   Mystery has only Memento, so HAVING removes it.

5. exam

   For each director: total budget of the movies they directed and year of their latest movie, by total budget descending. What does Nolan's total ignore?

   Show solution

   SELECT p.name, sum(m.budget) AS total_budget, max(m.year) AS last_year
   FROM person p
   JOIN crew c  ON c.person = p.id AND c.role = 'director'
   JOIN movie m ON m.id = c.movie
   GROUP BY p.id, p.name
   ORDER BY total_budget DESC;

   name               total_budget  last_year
   -----------------  ------------  ---------
   Christopher Nolan  519000000     2020
   Lana Wachowski     63000000      1999
   Bong Joon-ho       11400000      2019
   Quentin Tarantino  8000000       1994

   sum ignores Tenet's NULL budget: 160 + 165 + 185 + 9 = 519 million. But max(year) still sees Tenet (2020).

Self-check quiz

Common mistakes

• Aggregates in WHERE (WHERE count(*) > 1): error. Use HAVING.
• Selecting a column that is neither grouped nor aggregated.
• Using count(*) when you need count(DISTINCT ...) after a join that duplicates rows.
• Forgetting that aggregates ignore NULL (avg is over non-NULL values; count(col) skips NULLs).
• Putting a row filter in HAVING (works, but is slower and confusing). Row conditions → WHERE; group conditions → HAVING.

A1.12 Nested queries: IN, EXISTS, ANY/ALL, scalar and correlated subqueries

Explanation

A subquery is a SELECT in parentheses used inside another statement. Kinds:

Worked examples

Scalar subquery: movies longer than the average length (144.25).

SELECT title, length FROM movie
WHERE length > (SELECT avg(length) FROM movie);

title            length
---------------  ------
Inception        148
Interstellar     169
Pulp Fiction     154
The Dark Knight  152
Tenet            150

IN: crime movies.

SELECT title FROM movie
WHERE id IN (SELECT movie FROM genre WHERE genre = 'Crime');

title
---------------
Pulp Fiction
The Dark Knight

NOT IN: people who never acted.

SELECT name FROM person
WHERE id NOT IN (SELECT person FROM crew WHERE role = 'actor');

name
-----------------
Christopher Nolan
Lana Wachowski
Bong Joon-ho
Meryl Streep

Correlated EXISTS: movies with a Metacritic score above 9.

SELECT m.title FROM movie m
WHERE EXISTS (SELECT * FROM rating r
              WHERE r.movie = m.id AND r.source = 'Metacritic' AND r.score > 9);

title
------------
Pulp Fiction
Parasite

NOT EXISTS: movies without any rating.

SELECT m.title FROM movie m
WHERE NOT EXISTS (SELECT * FROM rating r WHERE r.movie = m.id);

title
-----
Tenet

Correlated scalar subquery (compare each Sci-Fi movie with the average of its own genre):

SELECT m.title, m.length
FROM movie m JOIN genre g ON g.movie = m.id
WHERE g.genre = 'Sci-Fi'
  AND m.length > (SELECT avg(m2.length)
                  FROM movie m2 JOIN genre g2 ON g2.movie = m2.id
                  WHERE g2.genre = g.genre);

title         length
------------  ------
Interstellar  169

Subquery in SELECT (one value per output row):

SELECT m.title,
       (SELECT count(*) FROM crew c WHERE c.movie = m.id) AS crew_size
FROM movie m
ORDER BY crew_size DESC, m.title;

title            crew_size
---------------  ---------
Inception        3
Interstellar     3
Pulp Fiction     3
The Matrix       3
Memento          2
Parasite         2
The Dark Knight  2
Tenet            1

ANY / ALL (PostgreSQL)

SQLite does not support ANY/ALL, so these were run in DuckDB (which uses PostgreSQL syntax) on the same data. The outputs are shown as plain tables.

-- PostgreSQL only
-- the longest movie(s): length >= every length
SELECT m.title, m.length FROM movie m
WHERE m.length >= ALL (SELECT length FROM movie);

Error: near "ALL": syntax error
   m.title, m.length FROM movie m WHERE m.length >= ALL (SELECT length FROM movi
                                      error here ---^

-- PostgreSQL only
-- movies more recent than at least one Crime movie (Crime years: 1994, 2008)
SELECT title, year FROM movie
WHERE year > ANY (SELECT m.year FROM movie m JOIN genre g ON g.movie = m.id
                  WHERE g.genre = 'Crime')
ORDER BY year;

Error: near "SELECT": syntax error
  SELECT title, year FROM movie WHERE year > ANY (SELECT m.year FROM movie m JOI
                                    error here ---^

ALL and NULL trap: Tenet's budget is NULL, so "budget >= ALL budgets" is never true (comparison with the NULL is unknown):

-- PostgreSQL only
SELECT title, budget FROM movie WHERE budget >= ALL (SELECT budget FROM movie);
-- 0 rows
SELECT title, budget FROM movie
WHERE budget >= ALL (SELECT budget FROM movie WHERE budget IS NOT NULL);

Error: near "ALL": syntax error
  SELECT title, budget FROM movie WHERE budget >= ALL (SELECT budget FROM movie)
                                    error here ---^
Error: near "ALL": syntax error
  SELECT title, budget FROM movie WHERE budget >= ALL (SELECT budget FROM movie
                                    error here ---^

The same trap with NOT IN: if the subquery contains a NULL, the result is empty. Here the subquery returns 8 and NULL:

SELECT name FROM person
WHERE id NOT IN (SELECT 8 UNION ALL SELECT NULL);   -- 0 rows!

(no rows)

Why: for Nolan, 1 NOT IN (8, NULL) = 1 <> 8 AND 1 <> NULL = true AND unknown = unknown. Prefer NOT EXISTS when the subquery column may be NULL.

Exercises

1. easy

   The oldest movie(s): title and year (use a scalar subquery).

   Show solution

   SELECT title, year FROM movie
   WHERE year = (SELECT min(year) FROM movie);

   title         year
   ------------  ----
   Pulp Fiction  1994

2. medium

   Names of actors who acted in a Sci-Fi movie, using only nested IN (no JOIN).

   Show solution

   SELECT name FROM person
   WHERE id IN (SELECT person FROM crew
                WHERE role = 'actor'
                  AND movie IN (SELECT movie FROM genre WHERE genre = 'Sci-Fi'))
   ORDER BY name;

   name
   -----------------
   Anne Hathaway
   Carrie-Anne Moss
   Keanu Reeves
   Leonardo DiCaprio

3. medium

   Directors who never directed a Sci-Fi movie (use EXISTS and NOT EXISTS).

   Show solution

   SELECT p.name FROM person p
   WHERE EXISTS (SELECT * FROM crew c WHERE c.person = p.id AND c.role = 'director')
     AND NOT EXISTS (SELECT * FROM crew c JOIN genre g ON g.movie = c.movie
                     WHERE c.person = p.id AND c.role = 'director' AND g.genre = 'Sci-Fi')
   ORDER BY p.name;

   name
   -----------------
   Bong Joon-ho
   Quentin Tarantino

4. hard

   For each genre, the longest movie(s) of that genre: genre, title, length (correlated subquery).

   Show solution

   SELECT g.genre, m.title, m.length
   FROM genre g JOIN movie m ON m.id = g.movie
   WHERE m.length = (SELECT max(m2.length)
                     FROM movie m2 JOIN genre g2 ON g2.movie = m2.id
                     WHERE g2.genre = g.genre)
   ORDER BY g.genre;

   genre     title            length
   --------  ---------------  ------
   Action    The Dark Knight  152
   Crime     Pulp Fiction     154
   Drama     Interstellar     169
   Mystery   Memento          113
   Sci-Fi    Interstellar     169
   Thriller  Parasite         132

5. exam

   People who worked (in any role) on every movie directed by Christopher Nolan. (Hint: "there is no Nolan movie on which this person did not work": double NOT EXISTS, the SQL way to express relational division.)

   Show solution

   SELECT p.name FROM person p
   WHERE NOT EXISTS (
     SELECT * FROM movie m
     JOIN crew c0  ON c0.movie = m.id AND c0.role = 'director'
     JOIN person n ON n.id = c0.person
     WHERE n.name = 'Christopher Nolan'
       AND NOT EXISTS (SELECT * FROM crew c WHERE c.movie = m.id AND c.person = p.id)
   );

   name
   -----------------
   Christopher Nolan

   Only Nolan himself (trivially). Carrie-Anne Moss worked on Memento but not on the others. An alternative with grouping: count the distinct Nolan movies of each person and compare with the total number of Nolan movies (HAVING count(DISTINCT c.movie) = (SELECT count(*) ...)).

Self-check quiz

Common mistakes

• NOT IN with a subquery that can return NULL: empty result. Use NOT EXISTS or filter IS NOT NULL inside.
• Using = with a subquery that returns several rows: runtime error ("more than one row returned by a subquery used as an expression"). Use IN.
• Forgetting the correlation condition in EXISTS (WHERE r.movie = m.id): EXISTS becomes true for every row (if any rating exists at all).
• Forgetting the alias on a subquery in FROM: PostgreSQL requires (SELECT ...) AS t (since version 16 it is optional, but write it).
• Thinking > ALL of an empty set is false. It is true.

A1.13 Set operators: UNION, INTERSECT, EXCEPT

Explanation

• The two queries must be union-compatible: same number of columns, compatible types, matched by position (not by name).
• The result's column names come from the first query.
• ORDER BY can appear only once, at the end, and sorts the whole result.
• Without ALL, the result is a set (no duplicates). PostgreSQL also has INTERSECT ALL and EXCEPT ALL.
• For set operators, two NULLs are treated as equal (unlike =).

Worked examples

UNION vs UNION ALL: ids of movies that are Sci-Fi or Drama. Interstellar (3) is both:

SELECT movie FROM genre WHERE genre = 'Sci-Fi'
UNION
SELECT movie FROM genre WHERE genre = 'Drama'
ORDER BY movie;

movie
-----
1
2
3
4
7
8

SELECT movie FROM genre WHERE genre = 'Sci-Fi'
UNION ALL
SELECT movie FROM genre WHERE genre = 'Drama'
ORDER BY movie;

movie
-----
1
2
3
3
4
7
8

INTERSECT: Sci-Fi and Action. EXCEPT: Sci-Fi but not Action.

SELECT movie FROM genre WHERE genre = 'Sci-Fi'
INTERSECT
SELECT movie FROM genre WHERE genre = 'Action'
ORDER BY movie;

movie
-----
1
2
8

SELECT movie FROM genre WHERE genre = 'Sci-Fi'
EXCEPT
SELECT movie FROM genre WHERE genre = 'Action';

movie
-----
3

People who are both director and actor (somewhere):

SELECT p.name FROM person p JOIN crew c ON c.person = p.id WHERE c.role = 'director'
INTERSECT
SELECT p.name FROM person p JOIN crew c ON c.person = p.id WHERE c.role = 'actor';

name
-----------------
Quentin Tarantino

EXCEPT as anti-join: movie ids with no rating.

SELECT id FROM movie
EXCEPT
SELECT movie FROM rating;

id
--
8

Mixing two tables: column name from the first query, constant column to tag the origin, one ORDER BY at the end:

SELECT title AS name, 'movie' AS kind FROM movie WHERE year > 2015
UNION
SELECT name, 'person' FROM person WHERE birth_year > 1980
ORDER BY name;

name           kind
-------------  ------
Anne Hathaway  person
Parasite       movie
Tenet          movie

Not union-compatible (2 columns vs 1):

SELECT title, year FROM movie
UNION
SELECT name FROM person;

Error: SELECTs to the left and right of UNION do not have the same number of result columns

(PostgreSQL: ERROR: each UNION query must have the same number of columns.)

Exercises

1. easy

   What does SELECT genre FROM genre WHERE movie = 1 UNION SELECT genre FROM genre WHERE movie = 2 return? And with UNION ALL?

   Show solution

   Inception and The Matrix are both Sci-Fi + Action. UNION: 2 rows (Action, Sci-Fi). UNION ALL: 4 rows (each twice).

   SELECT genre FROM genre WHERE movie = 1
   UNION
   SELECT genre FROM genre WHERE movie = 2
   ORDER BY genre;

   genre
   ------
   Action
   Sci-Fi

2. medium

   Titles of Drama movies that have a Metacritic rating (use INTERSECT).

   Show solution

   SELECT m.title FROM movie m JOIN genre g ON g.movie = m.id WHERE g.genre = 'Drama'
   INTERSECT
   SELECT m.title FROM movie m JOIN rating r ON r.movie = m.id WHERE r.source = 'Metacritic'
   ORDER BY 1;

   title
   ------------
   Parasite
   Pulp Fiction

   ORDER BY 1 = sort by the first column. Careful: intersecting on titles would wrongly merge two different movies with the same title; intersecting on ids and then joining is more robust.

3. medium

   Actors who acted only in movies released before 2000 (use EXCEPT).

   Show solution

   SELECT p.name FROM person p JOIN crew c ON c.person = p.id WHERE c.role = 'actor'
   EXCEPT
   SELECT p.name FROM person p JOIN crew c ON c.person = p.id
   JOIN movie m ON m.id = c.movie WHERE m.year >= 2000
   ORDER BY 1;

   name
   -----------------
   Keanu Reeves
   Quentin Tarantino
   Uma Thurman

   Carrie-Anne Moss is removed because of Memento (2000).

4. hard

   One list called label with the titles of movies released before 1996 and the names of people born before 1964, alphabetically.

   Show solution

   SELECT title AS label FROM movie WHERE year < 1996
   UNION
   SELECT name FROM person WHERE birth_year < 1964
   ORDER BY label;

   label
   -----------------
   Meryl Streep
   Pulp Fiction
   Quentin Tarantino

5. exam

   Rewrite the EXCEPT query "movie ids with no rating" in two other ways, and explain one situation where EXCEPT and NOT IN give different results.

   Show solution

   SELECT m.id FROM movie m WHERE NOT EXISTS (SELECT * FROM rating r WHERE r.movie = m.id);
   SELECT m.id FROM movie m LEFT JOIN rating r ON r.movie = m.id WHERE r.movie IS NULL;

   id
   --
   8
   id
   --
   8

   If the subquery column contains a NULL, NOT IN returns nothing, while EXCEPT still works (it treats NULLs as equal and just removes matching rows). Also, EXCEPT removes duplicates from the result.

Self-check quiz

Common mistakes

• Different number of columns or incompatible types in the two queries.
• Putting ORDER BY in the first query (only one ORDER BY, at the very end).
• Using UNION when you need to keep duplicates (e.g. before counting): use UNION ALL.
• Expecting columns to match by name: they match by position.

A1.14 Using the database from Python: drivers, pandas, Streamlit

Explanation

• A driver (connector) is a library that speaks the DBMS network protocol. For PostgreSQL in Python: psycopg2 (classic) or psycopg (version 3). Python's standard DB-API defines the same pattern for all drivers.
• Pattern: connect → get a cursor → execute(sql, params) → fetchone()/fetchall() → commit() (for changes) → close().
• DB-API drivers open a transaction automatically: changes are lost unless you call conn.commit() (or use with conn:, which commits on success and rolls back on error).
• Never build SQL with f-strings from user input (SQL injection). Pass parameters separately: placeholders are %s in psycopg2, ? in sqlite3, :name in SQLAlchemy text().
• SQLAlchemy gives an engine from a URL (postgresql+psycopg2://user:password@host:5432/dbname); pandas uses it in pd.read_sql(query, engine) (result → DataFrame) and df.to_sql(name, engine) (DataFrame → table).
• Streamlit turns a Python script into a web app (a simple frontend for the database): widgets like st.slider give values, you query with them, and st.dataframe shows the result. Run with streamlit run app.py.

Worked examples

psycopg2 with PostgreSQL (not run here: no PostgreSQL server in the verification environment; the API is the standard one):

import psycopg2

conn = psycopg2.connect(host="localhost", port=5432, dbname="imdb",
                        user="postgres", password="secret")
cur = conn.cursor()
cur.execute("SELECT title, year FROM movie WHERE year >= %s ORDER BY year", (2010,))
for title, year in cur.fetchall():      # list of tuples
    print(title, year)

cur.execute("UPDATE movie SET budget = %s WHERE title = %s", (205000000, "Tenet"))
conn.commit()                           # make the change permanent
cur.close()
conn.close()

The same pattern with sqlite3 (standard library, same DB-API), plus pandas. This one was run on our IMDB sample:

import sqlite3
import pandas as pd

conn = sqlite3.connect("imdb.db")
cur = conn.cursor()

# 1) query with a parameter (never build SQL with f-strings!)
cur.execute("SELECT title, year FROM movie WHERE year >= ? ORDER BY year", (2010,))
rows = cur.fetchall()          # list of tuples
print(rows)

# 2) write inside a transaction
cur.execute("UPDATE movie SET budget = ? WHERE title = ?", (205000000, "Tenet"))
conn.commit()                  # without commit the change is lost

# 3) pandas: query result -> DataFrame
df = pd.read_sql("""
    SELECT m.title, r.source, r.score
    FROM movie m JOIN rating r ON r.movie = m.id
""", conn)
print(df.head(4))
print(df.pivot(index="title", columns="source", values="score").round(1))
conn.close()

[('Inception', 2010), ('Interstellar', 2014), ('Parasite', 2019), ('Tenet', 2020)]
        title      source  score
0   Inception        IMDb    8.8
1   Inception  Metacritic    7.4
2  The Matrix        IMDb    8.7
3  The Matrix  Metacritic    7.3
source           IMDb  Metacritic
title
Inception         8.8         7.4
Interstellar      8.7         NaN
Memento           8.4         NaN
Parasite          8.5         9.6
Pulp Fiction      8.9         9.5
The Dark Knight   9.0         8.4
The Matrix        8.7         7.3

Note how SQL NULL / missing becomes NaN in pandas.

SQLAlchemy engine + named parameters + to_sql (run with a SQLite URL; for PostgreSQL only the URL changes):

import pandas as pd
from sqlalchemy import create_engine, text

engine = create_engine("sqlite:///imdb.db")
# PostgreSQL: create_engine("postgresql+psycopg2://user:pwd@localhost:5432/imdb")
with engine.connect() as conn:
    df = pd.read_sql(text("SELECT title, length FROM movie WHERE length > :min_len"),
                     conn, params={"min_len": 150})
print(df)
summary = df.assign(hours=df.length / 60)
summary.to_sql("long_movies", engine, if_exists="replace", index=False)
print(pd.read_sql("SELECT * FROM long_movies", engine))

             title  length
0     Interstellar     169
1     Pulp Fiction     154
2  The Dark Knight     152
             title  length     hours
0     Interstellar     169  2.816667
1     Pulp Fiction     154  2.566667
2  The Dark Knight     152  2.533333

A minimal Streamlit frontend (not run here; shown for the idea):

# app.py  ->  run with:  streamlit run app.py
import pandas as pd
import streamlit as st
from sqlalchemy import create_engine, text

engine = create_engine("postgresql+psycopg2://user:pwd@localhost:5432/imdb")

st.title("IMDB explorer")
min_year = st.slider("Released from", 1990, 2025, 2000)
df = pd.read_sql(text("SELECT title, year FROM movie WHERE year >= :y ORDER BY year"),
                 engine, params={"y": min_year})
st.dataframe(df)
st.bar_chart(df.set_index("title")["year"])

Exercises

1. easy

   Put these in the right order: fetchall(), connect(), close(), cursor(), execute().

   Show solution

   connect() → cursor() → execute() → fetchall() → close() (and commit() before closing if you changed data).

2. medium

   A script runs cur.execute("DELETE FROM rating WHERE source = 'Metacritic'") with psycopg2 and then ends without anything else. Are the rows deleted in the database?

   Show solution

   No. psycopg2 opened a transaction implicitly and it was never committed; when the connection closes, the transaction is rolled back. Call conn.commit() (or use with conn:).

3. medium

   What is wrong with this code, and how do you fix it? cur.execute(f"SELECT * FROM movie WHERE title = '{user_input}'")

   Show solution

   SQL injection: with user_input = "x' OR '1'='1" the query returns every row (or worse, with a ; DROP TABLE ...). It also breaks on titles containing a quote. Fix: cur.execute("SELECT * FROM movie WHERE title = %s", (user_input,)). Note the comma: the parameters must be a tuple.

4. hard

   Write Python code that loads, for each genre, the number of movies and the average IMDb score into a DataFrame (one SQL query, with SQLAlchemy and pandas), then prints the genre with the highest average.

   Show solution

   import pandas as pd
   from sqlalchemy import create_engine
   
   engine = create_engine("sqlite:///imdb.db")   # or the PostgreSQL URL
   df = pd.read_sql("""
       SELECT g.genre, count(*) AS n, avg(r.score) AS avg_imdb
       FROM genre g JOIN rating r ON r.movie = g.movie
       WHERE r.source = 'IMDb'
       GROUP BY g.genre
   """, engine)
   best = df.sort_values("avg_imdb", ascending=False).iloc[0]
   print(best["genre"], round(best["avg_imdb"], 2))

   Crime 8.95

   Prefer doing the aggregation in SQL (the database is optimised for it, and less data travels to Python).

Self-check quiz

Common mistakes

• Forgetting conn.commit() after INSERT/UPDATE/DELETE.
• Building queries with f-strings or + (SQL injection, quoting bugs).
• Passing a single parameter as (2010) instead of the tuple (2010,).
• Mixing placeholder styles: %s (psycopg2), ? (sqlite3), :name (SQLAlchemy text).
• Loading a whole table into pandas and filtering in Python when a WHERE/GROUP BY in SQL would do it.

A1.15 NoSQL: data models, comparison with relational, the document model

Explanation

Why NoSQL appeared ("Not only SQL", around 2009): web-scale applications needed (1) horizontal scaling (many cheap servers instead of one big one), (2) a flexible schema for irregular, fast-changing data (JSON from APIs, logs, user profiles), (3) high availability across data centres. The relational model with strict schemas, joins and full ACID across servers is hard to scale this way.

The four families

Key-value, document and column-family stores are called aggregate-oriented: they store a whole "aggregate" (e.g. a movie with its cast and ratings) as one unit, which is easy to distribute across servers. Graph databases are not aggregate-oriented.

Relational vs NoSQL (document)

The document model (MongoDB)

• MongoDB server → databases → collections (≈ tables) → documents (≈ rows) → fields (≈ columns).
• Documents are JSON-like, stored as BSON (binary JSON, with extra types such as int, double, date, ObjectId). Max document size 16 MB.
• Every document has a unique _id (the primary key); if you do not give one, MongoDB creates an ObjectId.
• Embedding: put related data inside the document (cast and ratings inside the movie). One read gets everything; good for "contains" / one-to-few relationships read together. Referencing: store the other document's _id (like a foreign key). Better for many-to-many, very large or independently updated data. Design rule: model for the queries you will run.

Worked examples

One movie in the relational model needs rows in 5 tables (movie, crew, person, rating, genre). In the document model it is one document. This is the collection used in A1.16 and A1.17 (first document shown):

db.movies.findOne({ _id: 1 })

{
  _id: 1,
  title: 'Inception',
  year: 2010,
  length: 148,
  budget: 160000000,
  genres: [ 'Sci-Fi', 'Action' ],
  director: 'Christopher Nolan',
  cast: [ { name: 'Leonardo DiCaprio', character: 'Cobb' } ],
  ratings: [
    { source: 'IMDb', score: 8.8, votes: 2400000 },
    { source: 'Metacritic', score: 7.4, votes: 70 }
  ]
}

Flexible schema: in the same collection, Tenet has no ratings field at all, budget: null, an empty cast, and (on purpose, as "dirty data") its year stored as the string "2020". MongoDB accepts all of it; a relational table would not.

db.movies.findOne({ title: "Tenet" })

{
  _id: 8,
  title: 'Tenet',
  year: '2020',
  length: 150,
  budget: null,
  genres: [ 'Action', 'Sci-Fi' ],
  director: 'Christopher Nolan',
  cast: []
}

Embedding vs referencing for the director: we embedded just the director's name (director: "Christopher Nolan"). If we needed the director's birth year and bio in many queries, we could store people in a separate people collection and reference them: director_id: 1. Trade-off: embedding duplicates data (if Nolan's name changes we must update 5 documents), referencing needs a second query or $lookup.

Exercises

1. easy

   Which NoSQL family fits best? (a) storing user sessions by session id; (b) a "friends of friends who liked this movie" recommendation; (c) a product catalogue where each product type has different attributes; (d) billions of sensor readings per day, written constantly.

   Show solution

   (a) key-value; (b) graph; (c) document; (d) column-family (wide-column, e.g. Cassandra).

2. easy

   Map the terms: table, row, column, primary key, join → MongoDB.

   Show solution

   table → collection; row → document; column → field; primary key → _id; join → embedding (or references + $lookup).

3. medium

   Design a MongoDB document for a person that also stores the movies they worked on. What would you embed and what would you reference? Write one example document for Carrie-Anne Moss.

   Show solution

   Embed small, stable info read together (name, birth year, a short list of credits with movie id, title and role); reference the full movie by its _id.

   // template
   {
     _id: 10,
     name: "Carrie-Anne Moss",
     birth_year: 1967,
     sex: "F",
     credits: [
       { movie_id: 2, title: "The Matrix", role: "actor", character: "Trinity" },
       { movie_id: 6, title: "Memento",    role: "actor", character: "Natalie" }
     ]
   }

   The title is duplicated from the movie document (denormalisation) to avoid a second query when listing credits.

4. medium

   During a network partition, an AP system and a CP system receive a read for data that was just updated on the other side of the partition. What does each do?

   Show solution

   AP: answers, possibly with the old (stale) value; replicas reconcile later (eventual consistency). CP: refuses the request or waits (error/timeout) rather than return a possibly stale value.

5. exam

   True or false: (a) NoSQL means SQL can never be used. (b) In MongoDB all documents of a collection must have the same fields. (c) The CAP theorem says a distributed system can have at most two of C, A, P during a partition. (d) BASE systems guarantee that every read returns the latest write. (e) In a relational DB, many-to-many relationships need an extra table.

   Show solution

   (a) False: "Not only SQL"; some NoSQL systems even have SQL-like languages. (b) False: flexible schema. (c) True (in practice: with P, choose C or A). (d) False: eventual consistency. (e) True (like crew between movie and person, or genre).

Self-check quiz

Common mistakes

• Saying "schemaless" means "no structure". The structure exists, it is just not enforced by the DBMS up front.
• Mixing key-value and document stores: in key-value the value is opaque; in document stores you can query fields inside the value.
• Reading CAP's "C" as ACID's "C". CAP consistency = all replicas show the latest write; ACID consistency = integrity constraints hold.
• Thinking MongoDB has no transactions at all: single-document operations are atomic, and multi-document transactions exist since 4.0.

A1.16 MongoDB: find queries and operators

Explanation

// template
db.collection.find(filter, projection).sort({ field: 1 | -1 }).limit(n)
db.collection.findOne(filter, projection)       // first matching document (or null)
db.collection.countDocuments(filter)            // number of matching documents

• filter: a document of conditions; {} matches everything. Several fields in one filter are combined with AND (implicit AND): { director: "Christopher Nolan", length: { $gt: 150 } }.
• projection: which fields to return. { title: 1, year: 1 } = only these (+ _id, which is included unless you write _id: 0). { cast: 0 } = everything except cast. Do not mix 1 and 0 (except for _id: 0).
• sort: 1 ascending, -1 descending; limit: keep the first n.

Worked examples

Collection movies (8 documents, see A1.15). Outputs are real mongosh outputs.

Comparison + projection without _id:

db.movies.find({ year: { $gte: 2010 } }, { title: 1, year: 1, _id: 0 })

[
  { title: 'Inception', year: 2010 },
  { title: 'Interstellar', year: 2014 },
  { title: 'Parasite', year: 2019 }
]

Tenet (2020) is missing: its year is the string "2020", and comparison operators only match values of the same type class (numbers with numbers).

Implicit AND (and _id is returned by default):

db.movies.find({ director: "Christopher Nolan", length: { $gt: 150 } }, { title: 1, length: 1 })

[
  { _id: 3, title: 'Interstellar', length: 169 },
  { _id: 5, title: 'The Dark Knight', length: 152 }
]

Value in an array, $in, $nin:

db.movies.find({ genres: "Crime" }, { title: 1, _id: 0 })

[ { title: 'Pulp Fiction' }, { title: 'The Dark Knight' } ]

db.movies.find({ genres: { $in: ["Mystery", "Crime"] } }, { title: 1, genres: 1, _id: 0 })

[
  { title: 'Pulp Fiction', genres: [ 'Crime', 'Drama' ] },
  { title: 'The Dark Knight', genres: [ 'Action', 'Crime' ] },
  { title: 'Memento', genres: [ 'Thriller', 'Mystery' ] }
]

db.movies.find({ genres: { $nin: ["Action", "Sci-Fi"] } }, { title: 1, _id: 0 })

[
  { title: 'Pulp Fiction' },
  { title: 'Memento' },
  { title: 'Parasite' }
]

$or, $and, $ne, $eq:

db.movies.find(
  { $or: [ { year: { $lt: 1995 } }, { length: { $gte: 160 } } ] },
  { title: 1, year: 1, length: 1, _id: 0 }
)

[
  { title: 'Interstellar', year: 2014, length: 169 },
  { title: 'Pulp Fiction', year: 1994, length: 154 }
]

db.movies.find({ $and: [ { genres: "Sci-Fi" }, { genres: "Drama" } ] }, { title: 1, _id: 0 })

[ { title: 'Interstellar' } ]

(Here $and is needed: { genres: "Sci-Fi", genres: "Drama" } is a JS object with a duplicated key, and the second one silently wins.)

db.movies.find({ director: { $ne: "Christopher Nolan" } }, { title: 1, director: 1, _id: 0 })

[
  { title: 'The Matrix', director: 'Lana Wachowski' },
  { title: 'Pulp Fiction', director: 'Quentin Tarantino' },
  { title: 'Parasite', director: 'Bong Joon-ho' }
]

db.movies.find({ director: { $eq: "Bong Joon-ho" } }, { title: 1, _id: 0 })

[ { title: 'Parasite' } ]

$not vs the opposite operator: $not also returns documents where the condition cannot be true (here Tenet, whose year is a string):

db.movies.find({ year: { $not: { $gte: 2000 } } }, { title: 1, year: 1, _id: 0 })

[
  { title: 'The Matrix', year: 1999 },
  { title: 'Pulp Fiction', year: 1994 },
  { title: 'Tenet', year: '2020' }
]

db.movies.find({ year: { $lt: 2000 } }, { title: 1, year: 1, _id: 0 })

[
  { title: 'The Matrix', year: 1999 },
  { title: 'Pulp Fiction', year: 1994 }
]

$exists, null, $type:

db.movies.find({ ratings: { $exists: false } }, { title: 1, _id: 0 })

[ { title: 'Tenet' } ]

db.movies.find({ budget: null }, { title: 1, budget: 1, _id: 0 })

[ { title: 'Tenet', budget: null } ]

db.movies.find({ year: { $type: "string" } }, { title: 1, year: 1, _id: 0 })

[ { title: 'Tenet', year: '2020' } ]

Dot notation vs $elemMatch (the key difference). Request: movies with a Metacritic score of at least 9.

db.movies.find({ "ratings.source": "Metacritic", "ratings.score": { $gte: 9 } }, { title: 1, _id: 0 })

[
  { title: 'Pulp Fiction' },
  { title: 'The Dark Knight' },
  { title: 'Parasite' }
]

Wrong: The Dark Knight matches because it has a Metacritic rating (8.4) and a score ≥ 9 (its IMDb 9.0): two different elements.

db.movies.find({ ratings: { $elemMatch: { source: "Metacritic", score: { $gte: 9 } } } }, { title: 1, _id: 0 })

[ { title: 'Pulp Fiction' }, { title: 'Parasite' } ]

The same issue with a range on one array field:

db.movies.find({ "ratings.score": { $gt: 8.5, $lt: 8.8 } }, { title: 1, _id: 0 })

[
  { title: 'Inception' },
  { title: 'The Matrix' },
  { title: 'Interstellar' },
  { title: 'The Dark Knight' },
  { title: 'Parasite' }
]

db.movies.find({ ratings: { $elemMatch: { score: { $gt: 8.5, $lt: 8.8 } } } }, { title: 1, _id: 0 })

[ { title: 'The Matrix' }, { title: 'Interstellar' } ]

Dot notation in the projection, $size, exact array:

db.movies.find({ "cast.name": "Carrie-Anne Moss" }, { title: 1, "cast.character": 1, _id: 0 })

[
  {
    title: 'The Matrix',
    cast: [ { character: 'Neo' }, { character: 'Trinity' } ]
  },
  { title: 'Memento', cast: [ { character: 'Natalie' } ] }
]

db.movies.find({ cast: { $size: 2 } }, { title: 1, _id: 0 })

[ { title: 'The Matrix' }, { title: 'Pulp Fiction' } ]

db.movies.find({ genres: ["Sci-Fi", "Action"] }, { title: 1, _id: 0 })

[ { title: 'Inception' }, { title: 'The Matrix' } ]

(Tenet has ["Action", "Sci-Fi"]: different order, so no match.)

sort, limit, count:

db.movies.find({}, { title: 1, year: 1, _id: 0 }).sort({ year: -1 }).limit(3)

[
  { title: 'Tenet', year: '2020' },
  { title: 'Parasite', year: 2019 },
  { title: 'Interstellar', year: 2014 }
]

Tenet comes first: when types are mixed, MongoDB sorts by type first (numbers before strings), so the string "2020" is "greater" than every number.

db.movies.countDocuments({ director: "Christopher Nolan" })

5

Projection error (mixing inclusion and exclusion):

db.movies.find({ title: "Memento" }, { title: 1, ratings: 0 })

MongoServerError: Cannot do exclusion on field ratings in inclusion projection

Exercises

1. easy

   Titles and years of movies released from 1995 to 2005 (included), oldest first, without _id.

   Show solution

   db.movies.find({ year: { $gte: 1995, $lte: 2005 } }, { _id: 0, title: 1, year: 1 }).sort({ year: 1 })

   [
     { title: 'The Matrix', year: 1999 },
     { title: 'Memento', year: 2000 }
   ]

2. easy

   Thriller movies not directed by Christopher Nolan (titles only).

   Show solution

   db.movies.find({ genres: "Thriller", director: { $ne: "Christopher Nolan" } }, { _id: 0, title: 1 })

   [ { title: 'Parasite' } ]

3. medium

   Movies with an IMDb rating that has more than 2 000 000 votes, sorted by title.

   Show solution

   db.movies.find(
     { ratings: { $elemMatch: { source: "IMDb", votes: { $gt: 2000000 } } } },
     { _id: 0, title: 1 }
   ).sort({ title: 1 })

   [
     { title: 'Inception' },
     { title: 'Interstellar' },
     { title: 'Pulp Fiction' },
     { title: 'The Dark Knight' }
   ]

   The Matrix has exactly 2 000 000, so it is excluded ($gt, not $gte).

4. medium

   Movies whose budget is unknown or below 10 million: title and budget.

   Show solution

   db.movies.find(
     { $or: [ { budget: null }, { budget: { $lt: 10000000 } } ] },
     { _id: 0, title: 1, budget: 1 }
   )

   [
     { title: 'Pulp Fiction', budget: 8000000 },
     { title: 'Memento', budget: 9000000 },
     { title: 'Tenet', budget: null }
   ]

5. hard

   Movies that have no Metacritic rating. Does { "ratings.source": { $ne: "Metacritic" } } work? Does it include Tenet?

   Show solution

   db.movies.find({ "ratings.source": { $ne: "Metacritic" } }, { _id: 0, title: 1 })

   [ { title: 'Interstellar' }, { title: 'Memento' }, { title: 'Tenet' } ]

   Yes: on an array, $ne means "no element equals", and it also matches documents without the field, so Tenet (no ratings) is included. (Equivalent: { "ratings.source": { $nin: ["Metacritic"] } }.)

6. exam

   The two longest Nolan movies whose year is stored as a number: title and year.

   Show solution

   db.movies.find(
     { director: "Christopher Nolan", year: { $type: "number" } },
     { _id: 0, title: 1, year: 1 }
   ).sort({ length: -1 }).limit(2)

   [
     { title: 'Interstellar', year: 2014 },
     { title: 'The Dark Knight', year: 2008 }
   ]

   "number" is an alias that matches int, long, double and decimal. You can sort on a field (length) that is not projected.

Self-check quiz

Common mistakes

• Using dot notation with two conditions on the same array when the request is about one element: use $elemMatch.
• Repeating the same key in one filter object ({ genres: "A", genres: "B" }): only the last one counts. Use $and (or $all).
• Mixing inclusion and exclusion in the projection (only _id: 0 may be mixed).
• Forgetting quotes around dotted field names: { ratings.score: 9 } is a JS syntax error.
• Writing $not: { ... } at top level like $and. $not goes inside a field: { year: { $not: { $gte: 2000 } } }.
• Forgetting that $ne, $nin, $not and { f: null } also match documents where the field is missing.

A1.17 MongoDB: the aggregation pipeline

Explanation

A pipeline is an array of stages. Documents flow through the stages in order; each stage transforms the stream and passes it on (like chained .filter().map()... in JS, or a Unix pipe).

// template
db.movies.aggregate([
  { $match:   { ...filter like find... } },
  { $unwind:  "$arrayField" },
  { $group:   { _id: "$groupField", total: { $sum: 1 }, avg: { $avg: "$x" }, list: { $push: "$y" } } },
  { $match:   { total: { $gte: 2 } } },          // filter groups = HAVING
  { $project: { _id: 0, group: "$_id", total: 1 } },
  { $sort:    { total: -1 } }
])

Worked examples

$match + $project + $sort (a find could do this too):

db.movies.aggregate([
  { $match: { director: "Christopher Nolan" } },
  { $project: { _id: 0, title: 1, length: 1 } },
  { $sort: { length: -1 } }
])

[
  { title: 'Interstellar', length: 169 },
  { title: 'The Dark Knight', length: 152 },
  { title: 'Tenet', length: 150 },
  { title: 'Inception', length: 148 },
  { title: 'Memento', length: 113 }
]

$group: movies and average length per director (SQL: GROUP BY director).

db.movies.aggregate([
  { $group: { _id: "$director", n_movies: { $sum: 1 }, avg_length: { $avg: "$length" } } },
  { $sort: { n_movies: -1, _id: 1 } }
])

[
  { _id: 'Christopher Nolan', n_movies: 5, avg_length: 146.4 },
  { _id: 'Bong Joon-ho', n_movies: 1, avg_length: 132 },
  { _id: 'Lana Wachowski', n_movies: 1, avg_length: 136 },
  { _id: 'Quentin Tarantino', n_movies: 1, avg_length: 154 }
]

$unwind + $group + $push + $match after group (HAVING): genres with at least 3 movies and their titles.

db.movies.aggregate([
  { $unwind: "$genres" },
  { $group: { _id: "$genres", n: { $sum: 1 }, titles: { $push: "$title" } } },
  { $match: { n: { $gte: 3 } } },
  { $sort: { n: -1, _id: 1 } }
])

[
  {
    _id: 'Action',
    n: 4,
    titles: [ 'Inception', 'The Matrix', 'The Dark Knight', 'Tenet' ]
  },
  {
    _id: 'Sci-Fi',
    n: 4,
    titles: [ 'Inception', 'The Matrix', 'Interstellar', 'Tenet' ]
  },
  {
    _id: 'Drama',
    n: 3,
    titles: [ 'Interstellar', 'Pulp Fiction', 'Parasite' ]
  }
]

To see what $unwind produces: Inception becomes two documents, one per genre.

db.movies.aggregate([
  { $match: { _id: 1 } },
  { $unwind: "$genres" },
  { $project: { title: 1, genres: 1 } }
])

[
  { _id: 1, title: 'Inception', genres: 'Sci-Fi' },
  { _id: 1, title: 'Inception', genres: 'Action' }
]

$unwind on an array of sub-documents: average score and total votes per rating source.

db.movies.aggregate([
  { $unwind: "$ratings" },
  { $group: { _id: "$ratings.source", avg_score: { $avg: "$ratings.score" }, total_votes: { $sum: "$ratings.votes" } } },
  { $sort: { _id: 1 } }
])

[
  { _id: 'IMDb', avg_score: 8.714285714285714, total_votes: 13850000 },
  { _id: 'Metacritic', avg_score: 8.440000000000001, total_votes: 224 }
]

$size and $ifNull: number of cast members and of ratings. $size fails on Tenet's missing ratings, so replace missing with an empty array first:

db.movies.aggregate([
  { $project: { _id: 0, title: 1, n_cast: { $size: "$cast" },
                n_ratings: { $size: { $ifNull: ["$ratings", []] } } } }
])

[
  { title: 'Inception', n_cast: 1, n_ratings: 2 },
  { title: 'The Matrix', n_cast: 2, n_ratings: 2 },
  { title: 'Interstellar', n_cast: 1, n_ratings: 1 },
  { title: 'Pulp Fiction', n_cast: 2, n_ratings: 2 },
  { title: 'The Dark Knight', n_cast: 1, n_ratings: 2 },
  { title: 'Memento', n_cast: 1, n_ratings: 1 },
  { title: 'Parasite', n_cast: 1, n_ratings: 2 },
  { title: 'Tenet', n_cast: 0, n_ratings: 0 }
]

db.movies.aggregate([ { $project: { title: 1, n: { $size: "$ratings" } } } ])   // without $ifNull

MongoServerError: Executor error during aggregate command on namespace: study_verify_a1.movies :: caused by :: The argument to $size must be an array, but was of type: missing

$type: inspect the types (find the dirty data):

db.movies.aggregate([
  { $match: { title: { $in: ["Tenet", "Memento"] } } },
  { $project: { _id: 0, title: 1, year_type: { $type: "$year" },
                budget_type: { $type: "$budget" }, ratings_type: { $type: "$ratings" } } }
])

[
  {
    title: 'Memento',
    year_type: 'int',
    budget_type: 'int',
    ratings_type: 'array'
  },
  {
    title: 'Tenet',
    year_type: 'string',
    budget_type: 'null',
    ratings_type: 'missing'
  }
]

$set + $cond: label each movie (array form of $cond):

db.movies.aggregate([
  { $set: { kind: { $cond: [ { $gte: ["$length", 150] }, "long", "normal" ] } } },
  { $project: { _id: 0, title: 1, length: 1, kind: 1 } }
])

[
  { title: 'Inception', length: 148, kind: 'normal' },
  { title: 'The Matrix', length: 136, kind: 'normal' },
  { title: 'Interstellar', length: 169, kind: 'long' },
  { title: 'Pulp Fiction', length: 154, kind: 'long' },
  { title: 'The Dark Knight', length: 152, kind: 'long' },
  { title: 'Memento', length: 113, kind: 'normal' },
  { title: 'Parasite', length: 132, kind: 'normal' },
  { title: 'Tenet', length: 150, kind: 'long' }
]

$ifNull for display:

db.movies.aggregate([
  { $match: { title: { $in: ["Tenet", "Parasite"] } } },
  { $project: { _id: 0, title: 1, budget: { $ifNull: ["$budget", "unknown"] } } }
])

[
  { title: 'Parasite', budget: 11400000 },
  { title: 'Tenet', budget: 'unknown' }
]

$avg on an array inside $set (no $unwind needed): average score of each movie.

db.movies.aggregate([
  { $match: { ratings: { $exists: true } } },
  { $set: { avg_score: { $avg: "$ratings.score" } } },
  { $project: { _id: 0, title: 1, avg_score: 1 } },
  { $sort: { avg_score: -1, title: 1 } }
])

[
  { title: 'Pulp Fiction', avg_score: 9.2 },
  { title: 'Parasite', avg_score: 9.05 },
  { title: 'Interstellar', avg_score: 8.7 },
  { title: 'The Dark Knight', avg_score: 8.7 },
  { title: 'Memento', avg_score: 8.4 },
  { title: 'Inception', avg_score: 8.100000000000001 },
  { title: 'The Matrix', avg_score: 8 }
]

(8.100000000000001 is ordinary floating-point rounding, as in JS (8.8 + 7.4) / 2.)

Conditional counting with _id: null (one group for the whole collection): total movies, how many are long, average length.

db.movies.aggregate([
  { $group: { _id: null,
              n: { $sum: 1 },
              n_long: { $sum: { $cond: { if: { $gte: ["$length", 150] }, then: 1, else: 0 } } },
              avg_length: { $avg: "$length" } } }
])

[ { _id: null, n: 8, n_long: 4, avg_length: 144.25 } ]

$unwind drops documents with an empty array: Tenet has cast: [], so after unwinding the cast it disappears:

db.movies.aggregate([
  { $unwind: "$cast" },
  { $match: { title: "Tenet" } }
])

(no rows)

(Use { $unwind: { path: "$cast", preserveNullAndEmptyArrays: true } } to keep them, like a LEFT JOIN.)

Exercises

1. easy

   Number of movies per director, most first (ties by name).

   Show solution

   db.movies.aggregate([
     { $group: { _id: "$director", n: { $sum: 1 } } },
     { $sort: { n: -1, _id: 1 } }
   ])

   [
     { _id: 'Christopher Nolan', n: 5 },
     { _id: 'Bong Joon-ho', n: 1 },
     { _id: 'Lana Wachowski', n: 1 },
     { _id: 'Quentin Tarantino', n: 1 }
   ]

2. medium

   Average IMDb score per director with the list of their IMDb-rated titles, best average first.

   Show solution

   db.movies.aggregate([
     { $unwind: "$ratings" },
     { $match: { "ratings.source": "IMDb" } },
     { $group: { _id: "$director", avg_imdb: { $avg: "$ratings.score" }, titles: { $push: "$title" } } },
     { $sort: { avg_imdb: -1 } }
   ])

   [
     {
       _id: 'Quentin Tarantino',
       avg_imdb: 8.9,
       titles: [ 'Pulp Fiction' ]
     },
     {
       _id: 'Christopher Nolan',
       avg_imdb: 8.725,
       titles: [ 'Inception', 'Interstellar', 'The Dark Knight', 'Memento' ]
     },
     { _id: 'Lana Wachowski', avg_imdb: 8.7, titles: [ 'The Matrix' ] },
     { _id: 'Bong Joon-ho', avg_imdb: 8.5, titles: [ 'Parasite' ] }
   ]

   The second $match works on single rating sub-documents because of $unwind (no $elemMatch needed).

3. medium

   Genres whose movies have an average length above 145: output fields genre, avg_len, n (no _id), longest first.

   Show solution

   db.movies.aggregate([
     { $unwind: "$genres" },
     { $group: { _id: "$genres", avg_len: { $avg: "$length" }, n: { $sum: 1 } } },
     { $match: { avg_len: { $gt: 145 } } },
     { $sort: { avg_len: -1 } },
     { $project: { _id: 0, genre: "$_id", avg_len: 1, n: 1 } }
   ])

   [
     { avg_len: 153, n: 2, genre: 'Crime' },
     { avg_len: 151.66666666666666, n: 3, genre: 'Drama' },
     { avg_len: 150.75, n: 4, genre: 'Sci-Fi' },
     { avg_len: 146.5, n: 4, genre: 'Action' }
   ]

   genre: "$_id" renames _id. Field order in the output follows the original document, not the projection.

4. hard

   Actors (cast names) who appear in more than one movie, with the movie titles.

   Show solution

   db.movies.aggregate([
     { $unwind: "$cast" },
     { $group: { _id: "$cast.name", n_movies: { $sum: 1 }, movies: { $push: "$title" } } },
     { $match: { n_movies: { $gt: 1 } } }
   ])

   [
     {
       _id: 'Carrie-Anne Moss',
       n_movies: 2,
       movies: [ 'The Matrix', 'Memento' ]
     }
   ]

5. exam

   For each movie: title, avg_score (average over its ratings) and label: "unrated" if it has no ratings, "top" if the average is at least 8.7, otherwise "good". Sort by title. Use $set, $cond and $type (or $ifNull).

   Show solution

   db.movies.aggregate([
     { $set: { avg_score: { $avg: "$ratings.score" } } },
     { $set: { label: { $cond: [ { $eq: [ { $type: "$avg_score" }, "null" ] },
                                 "unrated",
                                 { $cond: [ { $gte: ["$avg_score", 8.7] }, "top", "good" ] } ] } } },
     { $project: { _id: 0, title: 1, avg_score: 1, label: 1 } },
     { $sort: { title: 1 } }
   ])

   [
     { title: 'Inception', avg_score: 8.100000000000001, label: 'good' },
     { title: 'Interstellar', avg_score: 8.7, label: 'top' },
     { title: 'Memento', avg_score: 8.4, label: 'good' },
     { title: 'Parasite', avg_score: 9.05, label: 'top' },
     { title: 'Pulp Fiction', avg_score: 9.2, label: 'top' },
     { title: 'Tenet', avg_score: null, label: 'unrated' },
     { title: 'The Dark Knight', avg_score: 8.7, label: 'top' },
     { title: 'The Matrix', avg_score: 8, label: 'good' }
   ]

   For Tenet, $avg over a missing array gives null, whose $type is "null". Careful: without the first check, { $gte: [null, 8.7] } is simply false, so Tenet would be labelled "good". An alternative test: { $eq: [ { $ifNull: ["$avg_score", "none"] }, "none" ] }.

6. exam

   For each genre: number of IMDb-rated movies, average IMDb score and titles; keep only genres with average ≥ 8.7; best first.

   Show solution

   db.movies.aggregate([
     { $unwind: "$ratings" },
     { $match: { "ratings.source": "IMDb" } },
     { $unwind: "$genres" },
     { $group: { _id: "$genres", n: { $sum: 1 }, avg_imdb: { $avg: "$ratings.score" }, titles: { $push: "$title" } } },
     { $match: { avg_imdb: { $gte: 8.7 } } },
     { $sort: { avg_imdb: -1 } }
   ])

   [
     {
       _id: 'Crime',
       n: 2,
       avg_imdb: 8.95,
       titles: [ 'Pulp Fiction', 'The Dark Knight' ]
     },
     {
       _id: 'Action',
       n: 3,
       avg_imdb: 8.833333333333334,
       titles: [ 'Inception', 'The Matrix', 'The Dark Knight' ]
     },
     {
       _id: 'Sci-Fi',
       n: 3,
       avg_imdb: 8.733333333333333,
       titles: [ 'Inception', 'The Matrix', 'Interstellar' ]
     },
     {
       _id: 'Drama',
       n: 3,
       avg_imdb: 8.700000000000001,
       titles: [ 'Interstellar', 'Pulp Fiction', 'Parasite' ]
     }
   ]

   Same groups and numbers as the SQL version in A1.11 (Crime 8.95, Action 8.83, Sci-Fi 8.73, Drama 8.7), but with floating-point digits: Drama's exact average 8.7 comes out as 8.700000000000001. Here that is just above 8.7, so Drama is kept, but with other data a value like 8.699999999999999 would be dropped. Exact-threshold comparisons on float averages are fragile: round first ($round) if the boundary matters.

Self-check quiz

Common mistakes

• Forgetting the $ in field paths: { $avg: "length" } averages the string "length" (result null). Write "$length".
• Putting $ on field names in $match ({ "$year": 2010 }). In filters, the field name has no $.
• Forgetting that $group outputs only _id and the accumulators: other fields (like title) are gone unless you $push them.
• Using $project when you want to keep all other fields: use $set.
• Grouping by array elements without $unwind: _id: "$genres" groups by the whole array.
• $size on a field that may be missing: wrap it with $ifNull: ["$f", []].
• Writing a comparison expression in query form inside $cond: use { $gte: ["$length", 150] }, not { length: { $gte: 150 } }.

A2.1 R and RStudio: help, workspace, naming, packages

Explanation

R is a free language for statistics and data analysis (created by Ross Ihaka and Robert Gentleman in the 1990s as an open-source version of the S language). RStudio is an IDE (integrated development environment) for R. It has four panes: the script editor (your .R file), the console (where code actually runs), the environment/history pane (objects in memory) and the files/plots/packages/help pane. In the editor, Ctrl/Cmd+Enter sends the current line or selection to the console.

Assignment and printing

The assignment operator is <- (read "gets"). = also works at top level, but the course style (and most R code) uses <-; inside a function call, = names an argument (mean(x = v)). At the console, the value of an expression is auto-printed; an assignment is not printed, unless you wrap it in parentheses.

x <- 5        # nothing printed
x             # auto-print
(y <- x * 2)  # assignment + print
10 -> z       # right assignment also exists (rare)

[1] 5
[1] 10

The [1] is not part of the value: it is the index of the first element shown on that line. In R even a single number is a vector of length 1.

Help

Workspace and working directory

The workspace (global environment) is the set of objects you created in the session. The working directory is the folder where R reads and writes files with relative paths.

Naming rules

Valid: my_var, my.var, x2, .hidden. Invalid: 2x, _x, my-var (parsed as my - var), .2x, if. Names starting with a dot are hidden from ls() (use ls(all.names = TRUE)). A non-syntactic name can still be used with backticks: `my var` <- 1. Unlike JS, the dot has no special meaning in a name: my.var is just one name, not a property access. Avoid overwriting built-ins such as c, T, mean, data.

Packages

A package is a bundle of functions, data and documentation (like an npm package). The official repository is CRAN. Base R already loads a few packages (base, stats, graphics, utils, datasets, ...).

• library(pkg) gives an error if the package is not installed (there is no package called ‘pkg’); require(pkg) gives a warning and returns FALSE. So if (!require(pkg)) install.packages("pkg") is a common idiom.
• Masking: when two attached packages export the same name, the one attached last wins. Loading dplyr prints that filter and lag from package stats are masked. Use stats::filter to reach the masked one.
• installed.packages(), update.packages(), remove.packages("pkg"), search() (attached packages), .libPaths() (where packages are installed), help(package = "dplyr").

Worked examples

rm(list = ls())        # start clean
a <- 1; b <- "hi"; .secret <- 42
ls()
exists("secret")
exists(".secret")
rm(a)
ls()

[1] "a" "b"
[1] FALSE
[1] TRUE
[1] "b"

if (!require(ggplot2)) {      # TRUE (silently) if already installed
  install.packages("ggplot2")
  library(ggplot2)
}
stats::sd(c(2, 4, 4, 4, 5, 5, 7, 9))   # call with namespace prefix

[1] 2.13809

Exercises

1. easy

   Which of these names are valid: total_2024, 2nd_place, .temp, my-data, .5a, Mean.Value, else?

   Show solution

   Valid: total_2024, .temp, Mean.Value. Invalid: 2nd_place (starts with a digit), my-data (minus sign: R reads my - data), .5a (dot followed by a digit), else (reserved word).

2. easy

   What is printed by this script (run in a fresh session)?

   x <- 3
   x * 2
   y <- x + 1
   (z <- y^2)

   Show solution

   [1] 6
   [1] 16

   Line 1 and 3 are assignments (not printed). Line 2 is auto-printed. Line 4 is an assignment in parentheses, so its value \(4^2 = 16\) is printed.

3. medium

   A script starts with library(tidyverse) and fails with Error in library(tidyverse) : there is no package called ‘tidyverse’. Explain the cause and the fix. Then explain why install.packages(tidyverse) (without quotes) also fails.

   Show solution

   The package is not installed on this computer. Run once install.packages("tidyverse"), then library(tidyverse). Without quotes, R evaluates tidyverse as an object name; no such object exists, so you get object 'tidyverse' not found. library() accepts an unquoted name because it uses non-standard evaluation, install.packages() does not.

4. exam

   What does the last line print?

   rm(list = ls())
   Data <- 1:3
   data_2 <- "a"
   .cfg <- TRUE
   `my var` <- 10
   ls()

   Show solution

   [1] "Data"   "data_2" "my var"

   .cfg is hidden (starts with a dot). The non-syntactic name created with backticks is a normal object. ls() returns the names sorted; "Data" comes before "data_2" because it is a prefix of it when case is ignored (and in the C locale upper case sorts first anyway). The key points are: 3 names, .cfg missing.

Self-check quiz

Common mistakes

• Calling install.packages() inside every script run (slow, and not needed): install once, library() every session.
• Forgetting quotes in install.packages("pkg").
• Thinking [1] is part of the output value. It is only an index label.
• Using backslashes in Windows paths: "C:\data" is an error. Use /.
• Naming objects c, T, df or data: it works but shadows built-ins and confuses readers (and T <- 0 breaks code that uses T for TRUE).

A2.2 Basic data types and coercion

Explanation

R has six atomic types. In practice you meet four.

Special values

• NA: missing value ("not available"). It has a type: NA is logical; NA_integer_, NA_real_, NA_character_ exist. Almost any operation with NA gives NA. Test with is.na(x), never x == NA.
• NULL: the empty object (length 0, "nothing"). Like JS null/undefined but it disappears inside c(): c(1, NULL, 2) is c(1, 2). Test with is.null(x).
• NaN ("not a number", e.g. 0/0), Inf and -Inf (e.g. 1/0). is.na(NaN) is TRUE, but is.nan(NA) is FALSE.

Coercion

Explicit coercion uses as.logical(), as.integer(), as.numeric() (= as.double()), as.character(). If the conversion is impossible you get NA with a warning NAs introduced by coercion (not an error). as.integer() truncates toward zero (as.integer(3.9) is 3, as.integer(-3.9) is -3). as.logical() accepts "TRUE", "true", "T", "FALSE", "false", "F"; any other string gives NA; numbers: 0 is FALSE, anything else TRUE.

Comparisons also coerce: 1 == "1" is TRUE (the number becomes "1"), and "10" < "9" is TRUE because strings compare character by character. Arithmetic with a string is an error: "1" + 1 gives non-numeric argument to binary operator (unlike JS, where "1" + 1 is "11").

Arithmetic facts

• / always returns a double: 5L / 2L is 2.5. Integer division %/%, remainder %% (both floor-based: -5 %/% 2 is -3, -5 %% 2 is 1; JS -5 % 2 is -1).
• ^ (or **) is power. integer + integer stays integer, integer + double becomes double.
• Floating point: 0.1 + 0.2 == 0.3 is FALSE; use isTRUE(all.equal(0.1 + 0.2, 0.3)).
• round(2.5) is 2 and round(0.5) is 0 (rounding to even, IEC 60559 standard); round(3.14159, 2) is 3.14. Also floor, ceiling, trunc, signif.

Strings

paste("a", "b") gives "a b" (default sep = " "); paste0("a", "b") gives "ab"; paste(c("x", "y"), collapse = "+") gives "x+y". Also nchar(), toupper(), substr(s, start, stop), sprintf("%.2f", pi), grepl(), sub()/gsub(), strsplit() (returns a list). length("hello") is 1 (one element), nchar("hello") is 5.

Worked examples

typeof(5); class(5); typeof(5L); typeof(1:3)
typeof(TRUE + 1L)
typeof(5L / 1L)
c(1, "a", TRUE)
c(1, TRUE, FALSE)
c(1.5, 2L)
sum(c(TRUE, FALSE, TRUE, TRUE))
as.numeric("3.5"); as.integer("3.5")
as.numeric("abc")

[1] "double"
[1] "numeric"
[1] "integer"
[1] "integer"
[1] "integer"
[1] "double"
[1] "1"    "a"    "TRUE"
[1] 1 1 0
[1] 1.5 2.0
[1] 3
[1] 3.5
[1] 3
[1] NA
Warning message:
NAs introduced by coercion 

Note how R prints c(1.5, 2.0) with the same number of decimals for every element, and character vectors with quotes and padded to the same width.

NA + 1; NA > 3; NA == NA
NA & FALSE        # FALSE whatever NA is
NA | TRUE         # TRUE  whatever NA is
c(1, NULL, 3); length(NULL); length(NA)
1/0; -1/0; 0/0
is.na(c(1, NA, NaN))

[1] NA
[1] NA
[1] NA
[1] FALSE
[1] TRUE
[1] 1 3
[1] 0
[1] 1
[1] Inf
[1] -Inf
[1] NaN
[1] FALSE  TRUE  TRUE

Exercises

1. easy

   Give typeof() of: 3, 3L, "3", 3 > 2, c(1L, 2.5), c(TRUE, 2L).

   Show solution

   "double", "integer", "character", "logical", "double" (integer coerced up to double), "integer" (logical coerced up to integer).

2. medium

   What does each line print?

   c(FALSE, 2, "b")
   as.logical(c("yes", "T", "false", "0"))
   as.logical(c(0, 2, -1))
   TRUE + TRUE * 3

   Show solution

   [1] "FALSE" "2"     "b"
   [1]    NA  TRUE FALSE    NA
   [1] FALSE  TRUE  TRUE
   [1] 4

   Line 1: the character type wins, the logical becomes "FALSE" (not "0"!) because each element is converted directly to character. Line 2: only the recognised strings convert; "yes" and "0" are NA (a string "0" is not a number for as.logical). Line 4: \(1 + 1 \cdot 3 = 4\).

3. medium

   Predict: 5 %/% 2, 5 %% 2, -7 %% 3, 7.5 %/% 2, typeof(4L %/% 2L), typeof(4L / 2L).

   Show solution

   2, 1, 2 (because \(-7 = 3 \cdot (-3) + 2\)), 3, "integer", "double".

4. exam

   What does this print? Explain each step.

   x <- c("10", "9", "100")
   max(x)
   max(as.numeric(x))
   sort(x)
   x == 10

   Show solution

   [1] "9"
   [1] 100
   [1] "10"  "100" "9"
   [1]  TRUE FALSE FALSE

   Strings are compared character by character: "9" is greater than "1...", so max(x) is "9" and the sorted order is "10" "100" "9". After conversion to numbers the maximum is 100. In x == 10 the number 10 is coerced to "10", so only the first element matches.

Self-check quiz

Common mistakes

• Believing 5 is an integer. It is a double; write 5L.
• Testing missing values with x == NA (always NA). Use is.na(x).
• Expecting "1" + 1 to work as in JS. It is an error in R.
• Converting a factor or a character with numbers using the wrong function (see A2.6 for factors).
• Confusing NULL (nothing, length 0) with NA (a missing value, length 1).

A2.3 Vectors: creation, indexing, recycling, NA

Explanation

Creating vectors

Indexing with [ ] (1-based!)

You cannot mix positive and negative indices: x[c(-1, 2)] is an error. Assigning beyond the end extends the vector and fills the gap with NA: x <- 1:3; x[5] <- 9L gives 1 2 3 NA 9. which(cond) returns the positions where a logical vector is TRUE; which.max()/which.min() return the position of the (first) max/min.

Vectorised operations and recycling

Arithmetic and comparison are element-wise: c(1, 2, 3) * 2 is 2 4 6; c(1, 2, 3) + c(10, 20, 30) is 11 22 33. If lengths differ, the shorter vector is recycled (repeated) to the length of the longer one. If the longer length is not a multiple of the shorter, R still computes but gives a warning: longer object length is not a multiple of shorter object length.

Useful functions

length, sum, mean, median, var, sd, min, max, range, prod, cumsum, cumprod, diff, rev, sort (values), order (positions that would sort), rank, unique, duplicated, table, %in%, head(x, n), tail(x, n), round, abs, exp, log (natural), log10, log(x, base = 2). Built-in vectors: letters, LETTERS, month.name, pi.

Missing values

Summary functions return NA if any element is NA, unless you set na.rm = TRUE. sum(is.na(x)) counts the missing values; x[!is.na(x)] removes them; mean(is.na(x)) is the proportion missing.

Worked examples

x <- c(3, 8, NA, 5, 1)
length(x)
mean(x)
mean(x, na.rm = TRUE)
x[!is.na(x) & x > 2]
which(x > 2)
sort(x)                      # NA removed by default
order(c(30, 10, 20))
cumsum(1:4)
1:6 + c(10, 100)
1:5 + 1:2

[1] 5
[1] NA
[1] 4.25
[1] 3 8 5
[1] 1 2 4
[1] 1 3 5 8
[1] 2 3 1
[1]  1  3  6 10
[1]  11 102  13 104  15 106
[1] 2 4 4 6 6
Warning message:
In 1:5 + 1:2 :
  longer object length is not a multiple of shorter object length

Note: x[x > 2] on a vector with NA would keep an NA in the result (NA > 2 is NA, and indexing with NA gives NA). which() ignores NAs, so x[which(x > 2)] is a safe alternative.

v <- c(a = 1, b = 2, c = 3)
v * 10
names(v)
v[c("c", "a")]
3 %in% v; c(1, 9) %in% v

 a  b  c
10 20 30
[1] "a" "b" "c"
c a
3 1
[1] TRUE
[1]  TRUE FALSE

Exercises

1. easy

   x <- c(5, 2, 9, 4). Give the output of x[2], x[-2], x[x %% 2 == 0], x[5], rev(x).

   Show solution

   [1] 2
   [1] 5 9 4
   [1] 2 4
   [1] NA
   [1] 4 9 2 5

2. medium

   Let n <- 4. What is 1:n-1? 1:(n-1)? -1:2? 2^(0:3)? seq(10, 1, by = -4)?

   Show solution

   [1] 0 1 2 3
   [1] 1 2 3
   [1] -1  0  1  2
   [1] 1 2 4 8
   [1] 10  6  2

3. medium

   What does this print?

   y <- c(2, NA, 7, 1)
   sum(y); sum(y, na.rm = TRUE); sum(is.na(y))
   y[y > 1]
   y[which(y > 1)]

   Show solution

   [1] NA
   [1] 10
   [1] 1
   [1]  2 NA  7
   [1] 2 7

   The logical index y > 1 is TRUE NA TRUE FALSE; an NA index returns NA. which() keeps only the TRUE positions (1 and 3).

4. hard

   Without running: x <- 1:10. What is x[c(TRUE, FALSE, FALSE)]? What is x[x > 3 & x %% 2 == 1]? What is sum(x[-(1:8)])?

   Show solution

   [1]  1  4  7 10
   [1] 5 7 9
   [1] 19

   The logical index is recycled: TRUE at positions 1, 4, 7, 10. Last: drops elements 1-8, so 9 + 10 = 19.

5. exam

   Write one line (no loop) that, for temps <- c(12.5, NA, 18.0, 25.3, 9.8, NA, 30.1), returns (a) the number of non-missing values above 15, (b) the positions of the missing values, (c) the mean of the non-missing values rounded to 1 decimal.

   Show solution

   sum(temps > 15, na.rm = TRUE)            # (a) 3
   which(is.na(temps))                       # (b) 2 6
   round(mean(temps, na.rm = TRUE), 1)       # (c) 19.1

   (c): \((12.5 + 18.0 + 25.3 + 9.8 + 30.1)/5 = 95.7/5 = 19.14\), rounded 19.1. Summing a logical vector counts the TRUEs.

Self-check quiz

Common mistakes

• Using 0-based thinking: x[0] is an empty vector, not the first element.
• Thinking x[-1] is the last element (as in Python). In R it removes the first element. Last element: x[length(x)] or tail(x, 1).
• Forgetting brackets around arithmetic in ranges: 1:n-1.
• Ignoring the recycling warning: it usually means a bug.
• Confusing sort(x) (sorted values) and order(x) (indices).
• Using 1:length(x) when x may be empty: it gives 1 0. Use seq_along(x).

A2.4 Matrices and arrays

Explanation

matrix(data, nrow, ncol, byrow = FALSE) builds a matrix. Like any atomic vector it has one type only. By default R fills column by column (column-major order); use byrow = TRUE to fill row by row.

m <- matrix(1:6, nrow = 2)
m
matrix(1:6, nrow = 2, byrow = TRUE)
dim(m); nrow(m); ncol(m); length(m)

     [,1] [,2] [,3]
[1,]    1    3    5
[2,]    2    4    6
     [,1] [,2] [,3]
[1,]    1    2    3
[2,]    4    5    6
[1] 2 3
[1] 2
[1] 3
[1] 6

Indexing: m[row, col]

• m[2, 3] is one element (6). m[2, ] is the whole 2nd row, m[, 2] the whole 2nd column. Negative and logical indices work as with vectors.
• drop = TRUE (the default): if the result has only one row or one column, R drops the dimension and returns a plain vector. Write m[, 2, drop = FALSE] to keep a 2×1 matrix.
• A single index, m[5], treats the matrix as the underlying vector in column-major order: m[5] is 5. m[m > 3] returns a vector of the elements greater than 3.
• Rows and columns can have names: dimnames(m), rownames(m), colnames(m); then m["r1", "B"] works.

Building and combining

cbind() binds vectors/matrices as columns, rbind() as rows. dim(x) <- c(2, 3) turns a vector into a matrix. t(m) transposes.

Math

Arrays

array(1:24, dim = c(2, 3, 4)) is a 2×3×4 array (2 rows, 3 columns, 4 "layers"). Index with three positions: a[i, j, k]. Filling is still "first index fastest": element a[i, j, k] is at vector position \(i + (j-1)\cdot 2 + (k-1)\cdot 6\). a[, , 1] is the first layer (a 2×3 matrix). In R ≥ 4.0, class(matrix(1:4, 2)) is "matrix" "array": a matrix is a 2-D array.

Worked examples

m <- matrix(1:6, nrow = 2)
m[2, ]
m[, 2]
m[, 2, drop = FALSE]
m[m > 3]
m %*% t(m)
m * c(10, 100)            # recycled down the columns

[1] 2 4 6
[1] 3 4
     [,1]
[1,]    3
[2,]    4
[1] 4 5 6
     [,1] [,2]
[1,]   35   44
[2,]   44   56
     [,1] [,2] [,3]
[1,]   10   30   50
[2,]  200  400  600

Check the product: row 1 of m is (1, 3, 5), so \(1\cdot1+3\cdot3+5\cdot5 = 35\); row 1 times row 2: \(1\cdot2 + 3\cdot4 + 5\cdot6 = 44\); row 2 with itself: \(4+16+36 = 56\).

m2 <- matrix(1:6, nrow = 2,
             dimnames = list(c("r1", "r2"), c("A", "B", "C")))
m2
m2["r2", "B"]
m2[, "C"]
rbind(a = 1:2, b = 3:4)
a <- array(1:24, dim = c(2, 3, 4))
a[2, 3, 4]; a[1, 2, 3]; dim(a[, , 1])

   A B C
r1 1 3 5
r2 2 4 6
[1] 4
r1 r2
 5  6
  [,1] [,2]
a    1    2
b    3    4
[1] 24
[1] 15
[1] 2 3

Exercises

1. easy

   Print matrix(1:6, ncol = 2). What are dim() and the element in row 3, column 1?

   Show solution

        [,1] [,2]
   [1,]    1    4
   [2,]    2    5
   [3,]    3    6

   dim is 3 2; element [3,1] is 3. With 2 columns and 6 values, R computes 3 rows and fills column by column.

2. medium

   m <- matrix(1:6, nrow = 2). What are class(m[1, ]), dim(m[1, , drop = FALSE]), m[-1, ], m[4], sum(m[, c(1, 3)])?

   Show solution

   "integer" (a plain vector because of drop = TRUE); 1 3; [1] 2 4 6 (the 2nd row, dropped to a vector); 4; \(1+2+5+6 = 14\).

3. medium

   What does this print?

   A <- matrix(c(2, 0, 1, 3), nrow = 2)
   A
   A * A
   A %*% A
   A %*% c(1, 1)

   Show solution

        [,1] [,2]
   [1,]    2    1
   [2,]    0    3
        [,1] [,2]
   [1,]    4    1
   [2,]    0    9
        [,1] [,2]
   [1,]    4    5
   [2,]    0    9
        [,1]
   [1,]    3
   [2,]    3

   \(A^2\): row 1 = (2·2 + 1·0, 2·1 + 1·3) = (4, 5); row 2 = (0·2 + 3·0, 0·1 + 3·3) = (0, 9). The vector c(1, 1) is treated as a column; the result is a 2×1 matrix.

4. exam

   Write code that builds a 3×3 matrix with the numbers 1 to 9 filled by row, sets every element greater than 6 to 0, and returns the column sums. Give the result.

   Show solution

   m <- matrix(1:9, nrow = 3, byrow = TRUE)
   m[m > 6] <- 0
   colSums(m)

   [1] 5 7 9

   Rows are (1,2,3), (4,5,6), (0,0,0) after replacement, so column sums are 5, 7, 9. (colSums returns a double vector; values print as whole numbers.)

Self-check quiz

Common mistakes

• Assuming row-wise filling (as you would write a nested JS array). R fills by column unless byrow = TRUE.
• Using * for matrix multiplication. * is element-wise; use %*%.
• Forgetting that m[, 1] is a vector, not a 1-column matrix (drop = TRUE).
• Mixing numbers and strings in cbind()/matrix(): everything becomes character. Use a data frame for mixed types.

A2.5 Lists

Explanation

l <- list(a = 1, b = "x", c = 1:3)
str(l)

List of 3
 $ a: num 1
 $ b: chr "x"
 $ c: int [1:3] 1 2 3

Modifying

• Add or replace: l$d <- TRUE, l[["e"]] <- c("p", "q").
• Remove an element: l$a <- NULL (the list gets shorter). To store a NULL as a value, use l["a"] <- list(NULL).
• Combine: c(l1, l2), append(l, list(z = 0)). Nested lists: list(p = list(q = 1)), accessed as x$p$q.
• unlist(l) flattens to an atomic vector (with coercion and generated names like c1 c2 c3).
• length(l) is the number of top-level elements, names(l) their names.

Worked examples

l <- list(a = 1, b = "x", c = 1:3)
l["b"]
l[["b"]]
length(l$c)
l$a <- NULL
names(l)
unlist(list(a = 1, b = 2:3))
unlist(list(1, "a", TRUE))

$b
[1] "x"

[1] "x"
[1] 3
[1] "b" "c"
 a b1 b2
 1  2  3
[1] "1"    "a"    "TRUE"

student <- list(name = "Ann", grades = c(28, 30, 27),
                address = list(city = "Milano", cap = "20133"))
mean(student$grades)
student$address$city
student[["grades"]][student[["grades"]] > 27]
p <- student
p$name <- "Bob"
student$name           # unchanged (copy-on-modify)

[1] 28.33333
[1] "Milano"
[1] 28 30
[1] "Ann"

Exercises

1. easy

   x <- list(1:4, "hi", c(TRUE, FALSE)). Give length(x), length(x[[1]]), class(x[2]), class(x[[2]]), x[[3]][2].

   Show solution

   3, 4, "list", "character", FALSE.

2. medium

   What is printed?

   l <- list(n = 10, v = c(2, 4))
   l$v[2] <- 40
   l$w <- NULL
   l[["n"]] <- NULL
   length(l)
   l

   Show solution

   [1] 1
   $v
   [1]  2 40
   

   Setting a non-existing element w to NULL does nothing; setting n to NULL deletes it. Only v is left, modified in place.

3. medium

   Why does sum(l[2]) fail while sum(l[[2]]) works, with l <- list("a", 1:3)?

   Show solution

   l[2] is a list containing the vector, and sum() does not accept a list: error invalid 'type' (list) of argument. l[[2]] extracts the integer vector, so the sum is 6.

4. exam

   Given exams <- list(r = c(24, 27), py = c(30), dm = c(18, 22, 26)), write one line that returns the number of grades per subject and one line that returns the overall mean of all grades. Give the results.

   Show solution

   sapply(exams, length)
   mean(unlist(exams))

    r py dm
    2  1  3
   [1] 24.5

   All grades: 24, 27, 30, 18, 22, 26, sum 147, mean \(147/6 = 24.5\). (lengths(exams) also gives the counts.)

Self-check quiz

Common mistakes

• Using [ ] when you want the element: l[1] is still a list.
• Expecting an error from l$missing: it silently returns NULL (typos go unnoticed).
• Expecting l$x <- NULL to store NULL: it removes x.
• Expecting reference semantics as in JS objects.

A2.6 Data frames and factors

Explanation

df <- data.frame(name = c("Ann", "Bob", "Cy", "Dan"),
                 age  = c(28, 35, 42, 19),
                 city = c("MI", "RM", "MI", "TO"))
df
str(df)
dim(df); nrow(df); ncol(df); length(df); names(df)

  name age city
1  Ann  28   MI
2  Bob  35   RM
3   Cy  42   MI
4  Dan  19   TO
'data.frame':	4 obs. of  3 variables:
 $ name: chr  "Ann" "Bob" "Cy" "Dan"
 $ age : num  28 35 42 19
 $ city: chr  "MI" "RM" "MI" "TO"
[1] 4 3
[1] 4
[1] 3
[1] 3
[1] "name" "age"  "city"

Since R 4.0.0 stringsAsFactors = FALSE is the default, so text columns stay character. (Older code and slides may show factors here.)

Indexing

Add a column: df$senior <- df$age >= 35. Remove: df$senior <- NULL. Add rows: rbind(df, data.frame(name = "Eve", age = 50, city = "RM")). Join two data frames on a key: merge(df1, df2, by = "id") (inner join; all.x = TRUE for a left join). Explore: head(df), tail(df), summary(df), View(df), colMeans(df[, num_cols]), table(df$city).

Group summaries in base R: aggregate(age ~ city, data = df, FUN = mean), or tapply(df$age, df$city, mean) (A2.9).

Factors

f <- factor(c("low", "high", "med", "low"))
f
levels(f); as.integer(f); nlevels(f)
f2 <- factor(c("low", "high", "med", "low"), levels = c("low", "med", "high"))
as.integer(f2)
table(f2)

[1] low  high med  low
Levels: high low med
[1] "high" "low"  "med"
[1] 2 1 3 2
[1] 3
[1] 1 3 2 1
f2
 low  med high
   2    1    1 

• Assigning a value that is not a level gives NA with a warning (invalid factor level, NA generated).
• factor(x, levels = ..., labels = ...) renames levels; ordered = TRUE (or ordered()) makes an ordinal factor where < works.
• droplevels(f) removes unused levels (e.g. after filtering). Unused levels still appear in table() with count 0.
• Trap: as.numeric(factor(c("10", "20", "10"))) returns the codes 1 2 1. To get the numbers back: as.numeric(as.character(f)) or as.numeric(levels(f))[f].
• cut(x, breaks) turns numbers into a factor of intervals; relevel(f, ref = "med") changes the first (reference) level.

Worked examples

df[df$age > 30, ]
df[df$age > 30, "name"]
df[2, ]
mean(df$age[df$city == "MI"])
aggregate(age ~ city, data = df, FUN = mean)
table(df$city)

  name age city
2  Bob  35   RM
3   Cy  42   MI
[1] "Bob" "Cy"
  name age city
2  Bob  35   RM
[1] 35
  city age
1   MI  35
2   RM  35
3   TO  19

MI RM TO
 2  1  1 

Notice that filtered rows keep their original row names (2, 3). table() of an unnamed vector prints an empty first line (where a variable name would be).

Exercises

1. easy

   With df above, give the class of: df$city, df["city"], df[, 3], df[3, ]. What is length(df)?

   Show solution

   "character", "data.frame", "character", "data.frame". length(df) is 3 (number of columns, because a data frame is a list of columns).

2. medium

   Write base-R code that returns the names of people from MI sorted by age descending, and code that adds a column decade equal to the age rounded down to a multiple of 10.

   Show solution

   mi <- df[df$city == "MI", ]
   mi[order(-mi$age), "name"]          # "Cy"  "Ann"
   df$decade <- (df$age %/% 10) * 10   # 20 30 40 10

3. medium

   What is printed?

   sizes <- factor(c("M", "S", "L", "M"), levels = c("S", "M", "L"))
   as.integer(sizes)
   levels(sizes)[2]
   sizes[2] <- "XL"
   sizes

   Show solution

   [1] 2 1 3 2
   [1] "M"
   Warning message:
   In `[<-.factor`(`*tmp*`, 2, value = "XL") :
     invalid factor level, NA generated
   [1] M    <NA> L    M
   Levels: S M L

   The codes follow the given level order S=1, M=2, L=3. "XL" is not a level, so element 2 becomes NA (printed <NA> inside factors). The warning text is shown for completeness; on the exam "NA with a warning" is enough.

4. hard

   v <- factor(c("5", "10", "5", "20")). Give levels(v), as.numeric(v), as.numeric(as.character(v)), and sum(as.numeric(v)).

   Show solution

   Levels are sorted as strings: "10" "20" "5". Codes: 3 1 3 2. Real numbers: 5 10 5 20. sum(as.numeric(v)) is \(3+1+3+2 = 9\), a silent bug (the right sum is 40).

5. exam

   A data frame diab has columns age (numeric), glucose (numeric, with some NA) and outcome (0/1). Write base-R code to: (a) count missing glucose values; (b) compute the mean glucose of patients over 50, ignoring NA; (c) compute mean glucose by outcome; (d) convert outcome to a factor with labels "neg" and "pos".

   Show solution

   sum(is.na(diab$glucose))                                         # (a)
   mean(diab$glucose[diab$age > 50], na.rm = TRUE)                   # (b)
   tapply(diab$glucose, diab$outcome, mean, na.rm = TRUE)            # (c)
   # or: aggregate(glucose ~ outcome, data = diab, FUN = mean)  (formula drops NA rows)
   diab$outcome <- factor(diab$outcome, levels = c(0, 1),
                          labels = c("neg", "pos"))                 # (d)

   Marking idea: correct logical indexing with $, na.rm = TRUE in the right place (extra arguments of tapply are passed to mean), and levels/labels matched in order.

Self-check quiz

Common mistakes

• Forgetting the comma: df[df$age > 30] selects columns with a logical vector, which is wrong (and errors if lengths differ). Write df[df$age > 30, ].
• Writing df[age > 30, ] without df$: age is not a variable in the workspace (only subset() and dplyr let you omit it).
• Expecting df[, "x"] to be a data frame (it is a vector, drop = TRUE).
• Converting a numeric factor with as.numeric() directly.
• Assuming factor levels follow the order of appearance: they are sorted alphabetically unless you give levels.

A2.7 Environments and scoping

Explanation

• globalenv() (printed <environment: R_GlobalEnv>) is the workspace. Its parents are the attached packages (see search()), then base, then the empty environment.
• Every function call creates a new, temporary environment for its arguments and local variables. Its parent is the environment where the function was defined (lexical scoping), not where it was called. This is the same rule as JS closures.
• Create and use: e <- new.env(), assign("x", 1, envir = e), e$x <- 1, get("x", envir = e), exists("x", envir = e), ls(e), rm("x", envir = e), environment(f), environment() (current), parent.frame() (the caller).
• Reference semantics: unlike lists, environments are not copied. If two names point to the same environment, or a function modifies an environment passed as argument, everyone sees the change (like JS objects).

Worked examples

x <- 1
f <- function() { x <- 2; g() }
g <- function() x      # g was defined globally, so it sees the global x
f()
h <- function() { x <- 2; k <- function() x; k() }
h()                    # k was defined inside h, so it sees h's x

[1] 1
[1] 2

count <- 0
inc_local  <- function() count <- count + 1    # creates a local 'count'
inc_global <- function() count <<- count + 1
inc_local(); count
inc_global(); inc_global(); count

[1] 0
[1] 2

(The calls themselves print nothing: an assignment returns its value invisibly.)

e <- new.env()
e$v <- 1
set_v <- function(env) env$v <- 99
set_v(e)
e$v                     # 99: environments are modified in place
l <- list(v = 1)
set_l <- function(lst) lst$v <- 99
set_l(l)
l$v                     # 1: the list was copied inside the function
assign("w", 5, envir = e)
sort(ls(e)); get("w", envir = e)
exists("w"); exists("w", envir = e)

[1] 99
[1] 1
[1] "v" "w"
[1] 5
[1] FALSE
[1] TRUE

(exists("w") is FALSE assuming there is no global w.)

Exercises

1. easy

   What is printed?

   a <- 10
   f <- function(a) { a <- a * 2; a }
   f(3)
   a

   Show solution

   [1] 6
   [1] 10

   The argument a is local to the call; the global a is untouched.

2. medium

   What is printed?

   for (i in 1:3) { tmp <- i * 10 }
   i
   tmp
   if (TRUE) { inside <- "yes" }
   inside

   Show solution

   [1] 3
   [1] 30
   [1] "yes"

   No block scope: loop variable and variables created in braces at top level are global.

3. hard

   What is printed?

   y <- 1
   outer_f <- function() {
     y <- 5
     inner_f <- function() y <<- y + 1
     inner_f()
     y
   }
   outer_f()
   y

   Show solution

   [1] 6
   [1] 1

   <<- inside inner_f finds y in the enclosing environment (the call of outer_f, where y = 5) and sets it to 6. The global y is not reached.

4. exam

   Write a function make_account(balance) that returns a list of two functions, deposit(x) and get(), sharing the same balance. Show that two accounts are independent.

   Show solution

   make_account <- function(balance = 0) {
     list(deposit = function(x) { balance <<- balance + x; invisible(balance) },
          get     = function() balance)
   }
   a1 <- make_account(100); a2 <- make_account()
   a1$deposit(50)
   a1$get(); a2$get()

   [1] 150
   [1] 0

   Each call of make_account creates its own environment holding balance; both inner functions are defined there, so they share it (a closure).

Self-check quiz

Common mistakes

• Thinking a function sees the variables of its caller (dynamic scoping). R uses the environment where the function was defined.
• Using <- inside a function and expecting the global variable to change.
• Overusing <<-: functions that change globals are hard to debug. Prefer returning values.
• Expecting block scope as in JS let.

A2.8 Control structures, logical operators and split()

Explanation

if / else

if (x > 0) {
  "positive"
} else if (x == 0) {
  "zero"
} else {
  "negative"
}

• The condition must be one TRUE/FALSE. A condition of length > 1 is an error (since R 4.2: the condition has length > 1); an NA condition is an error (missing value where TRUE/FALSE needed).
• if is an expression and returns a value: y <- if (x > 0) "pos" else "neg" (like the JS ternary). If the condition is FALSE and there is no else, the value is NULL (invisible).
• At top level, else must be on the same line as the closing }; otherwise R finishes the if at the end of the line and else alone is a syntax error.

Logical operators

Since R 4.3, &&/|| with a vector of length > 1 is an error. Edge cases: all(logical(0)) is TRUE, any(logical(0)) is FALSE; any(c(NA, TRUE)) is TRUE but any(c(NA, FALSE)) is NA.

Vectorised conditional: ifelse()

ifelse(test, yes, no) works element-wise on a whole vector (the "special conditional operator" of lesson 3). Where test is NA the result is NA. Nest it for 3 classes, or use cut() or dplyr::case_when().

switch()

switch(type,
       mean   = mean(x),
       median = median(x),
       stop("unknown type"))    # unnamed last argument = default

With a character first argument, R returns the value of the matching name. An empty argument falls through to the next one: switch("a", a = , b = "A or B") gives "A or B". Without a match and without a default, switch returns NULL invisibly. With a number n, it returns the n-th argument: switch(2, "x", "y", "z") is "y".

Stopping with errors

stop("message") raises an error, warning("message") a warning, stopifnot(is.numeric(x), length(x) > 0) checks conditions (A2.16).

split()

split(x, f) divides a vector or data frame x into a named list of groups defined by f (converted to a factor, so groups come out in level order). unsplit() reverses it. Typical pattern (split-apply-combine): sapply(split(df$value, df$group), mean).

Worked examples

x <- c(-2, 0, 3, NA, 7)
ifelse(x > 0, "pos", "non-pos")
ifelse(is.na(x), 0, x)
x > 0 & x < 5
any(x > 5); all(x > -5); all(x > -5, na.rm = TRUE)
which(x > 0)
switch("b", a = "one", b = "two", "other")
switch("z", a = "one", b = "two", "other")

[1] "non-pos" "non-pos" "pos"     NA        "pos"
[1] -2  0  3  0  7
[1] FALSE FALSE  TRUE    NA FALSE
[1] TRUE
[1] NA
[1] TRUE
[1] 3 5
[1] "two"
[1] "other"

all(x > -5) is NA because the only non-TRUE element is NA: R cannot know if it would be TRUE. any(x > 5) is TRUE because 7 > 5 already decides it.

split(1:6, c("a", "b"))                          # grouping vector recycled
g <- c("x", "y", "x", "y", "x")
v <- c(10, 20, 30, 40, 50)
s <- split(v, g)
s
sapply(s, sum)

$a
[1] 1 3 5

$b
[1] 2 4 6

$x
[1] 10 30 50

$y
[1] 20 40

 x  y
90 60 

Exercises

1. easy

   What does ifelse(c(4, 9, 1, 6) %% 2 == 0, "even", "odd") return? And ifelse(c(1, NA, 3) > 2, 1, 0)?

   Show solution

   [1] "even" "odd"  "odd"  "even"
   [1]  0 NA  1

2. medium

   For v <- c(2, 5), say what happens: (a) if (v > 3) "big"; (b) v > 3 & v < 10; (c) v > 3 && v < 10; (d) if (any(v > 3)) "some big".

   Show solution

   (a) error: the condition has length > 1 (R ≥ 4.2; older R used only the first element with a warning). (b) [1] FALSE TRUE. (c) error in R ≥ 4.3 ('length = 2' in coercion to 'logical(1)'). (d) [1] "some big".

3. medium

   What is printed?

   f <- function(d) switch(d, sat = , sun = "weekend", "weekday")
   f("sat"); f("mon"); f("sun")
   r <- if (3 > 5) "yes"
   is.null(r)

   Show solution

   [1] "weekend"
   [1] "weekday"
   [1] "weekend"
   [1] TRUE

   sat has an empty value, so it falls through to sun. An if without else whose condition is FALSE returns NULL.

4. hard

   df <- data.frame(team = c("A", "B", "A", "C", "B"), pts = c(3, 1, 0, 3, 3)). What do names(split(df$pts, df$team)) and sapply(split(df$pts, df$team), mean) return? What is class(split(df, df$team)$A)?

   Show solution

   [1] "A" "B" "C"
     A   B   C
   1.5 2.0 3.0 

   A: (3+0)/2 = 1.5; B: (1+3)/2 = 2; C: 3. Splitting a data frame gives a list of data frames, so the class is "data.frame".

5. exam

   Write a function bmi_class(w, h) that is vectorised (works on vectors of weights in kg and heights in m) and returns "under" if BMI < 18.5, "normal" if 18.5 ≤ BMI < 25, "over" otherwise, with BMI = w / h². What does bmi_class(c(50, 70, 90), c(1.80, 1.75, 1.70)) return?

   Show solution

   bmi_class <- function(w, h) {
     bmi <- w / h^2
     ifelse(bmi < 18.5, "under", ifelse(bmi < 25, "normal", "over"))
   }
   bmi_class(c(50, 70, 90), c(1.80, 1.75, 1.70))

   [1] "under"  "normal" "over"  

   BMIs: 50/3.24 = 15.43, 70/3.0625 = 22.86, 90/2.89 = 31.14. A version with if would fail on vectors; ifelse (or cut(bmi, c(-Inf, 18.5, 25, Inf), right = FALSE, labels = ...)) is required.

Self-check quiz

Common mistakes

• Using if on a vector instead of ifelse().
• Using && for element-wise filtering, or & inside if with vectors.
• Putting else on a new line at top level.
• Forgetting that == with NA gives NA, which breaks if.
• Expecting split() groups in order of first appearance: they come in sorted (factor level) order.

A2.9 Loops and loop functionals (apply, lapply, sapply, tapply, mapply)

Explanation

Loops

for (i in 1:3) { ... }          # i takes each value of the vector (like JS for...of)
for (name in c("a", "b")) { ... }
while (cond) { ... }             # repeat while cond is TRUE
repeat { ...; if (done) break }  # infinite loop, exit with break
next                             # skip to next iteration (JS continue)

• Inside a loop (or a function), values are not auto-printed: for (i in 1:3) i prints nothing. Use print(i) or cat(i, "\n").
• Loop over positions with seq_along(x) (safe for empty x; 1:length(x) gives 1 0 when x is empty).
• Pre-allocate the result (res <- numeric(n), vector("list", n)) and fill res[i], instead of growing with res <- c(res, val) (slow: copies every time).
• A for loop changes the loop variable for good: after for (i in 1:3), i is 3. Modifying the sequence inside the loop does not change the iterations.

The apply family

Extra arguments after FUN are passed to it: sapply(lst, mean, na.rm = TRUE). Applied to a data frame, lapply/sapply iterate over the columns (a data frame is a list of columns). Anonymous functions: function(x) x^2, or the short form \(x) x^2 (R ≥ 4.1), like a JS arrow function.

Worked examples

total <- 0
for (i in 1:5) {
  if (i %% 2 == 0) next
  total <- total + i
}
total
n <- 1
while (n < 100) n <- n * 3
n
i <- 0
repeat { i <- i + 1; if (i^2 > 50) break }
i

[1] 9
[1] 243
[1] 8

m <- matrix(1:6, nrow = 2)
apply(m, 1, sum)
apply(m, 2, max)
apply(m, 1, function(r) r * 2)    # note: result is transposed (3 x 2)

[1]  9 12
[1] 2 4 6
     [,1] [,2]
[1,]    2    4
[2,]    6    8
[3,]   10   12

lapply(1:2, function(x) x * 10)
sapply(1:3, function(x) x^2)
sapply(1:3, function(x) c(x, x^2))
sapply(c(a = 1, b = 2), function(x) x * 2)
sapply(c("x", "yy", "zzz"), nchar)
sapply(1:3, seq_len)

[[1]]
[1] 10

[[2]]
[1] 20

[1] 1 4 9
     [,1] [,2] [,3]
[1,]    1    2    3
[2,]    1    4    9
a b
2 4
  x  yy zzz
  1   2   3
[[1]]
[1] 1

[[2]]
[1] 1 2

[[3]]
[1] 1 2 3

The last call cannot be simplified (results of lengths 1, 2, 3), so sapply returns a list. With a character input, sapply uses the strings as names (USE.NAMES = TRUE).

sales  <- c(10, 20, 30, 40, 50)
region <- c("N", "S", "N", "S", "N")
tapply(sales, region, sum)
tapply(sales, region, length)
mapply(function(a, b) a * b, 1:3, 4:6)
mapply(rep, 1:3, 3:1)
Reduce(`+`, 1:4, accumulate = TRUE)
Filter(function(v) v > 2, c(5, 1, 3))

 N  S
90 60
N S
3 2
[1]  4 10 18
[[1]]
[1] 1 1 1

[[2]]
[1] 2 2

[[3]]
[1] 3

[1]  1  3  6 10
[1] 5 3

Exercises

1. easy

   What is x after this code? x <- 0; for (k in c(2, 5, 1)) x <- x * 10 + k

   Show solution

   0 → 2 → 25 → 251. x is 251.

2. medium

   Write a loop with pre-allocation that stores the first 10 Fibonacci numbers (1, 1, 2, ...) in fib. Give the printed result.

   Show solution

   fib <- numeric(10)
   fib[1:2] <- 1
   for (i in 3:10) fib[i] <- fib[i - 1] + fib[i - 2]
   fib

    [1]  1  1  2  3  5  8 13 21 34 55

   With 10 or more elements R reserves room for the label [10], so the first label is printed as [1] with a leading space (try 1:10).

3. medium

   For each call say whether the result is a vector, a matrix or a list, and its dimensions/length: (a) sapply(1:4, function(i) i %% 2 == 0); (b) sapply(1:4, function(i) c(min = i, max = i * 2)); (c) lapply(1:4, sqrt); (d) sapply(1:4, function(i) rep(i, i)); (e) apply(matrix(1:12, 3), 2, range).

   Show solution

   (a) logical vector of length 4: FALSE TRUE FALSE TRUE. (b) a 2×4 matrix with row names min, max. (c) a list of length 4. (d) a list of length 4 (different lengths). (e) a 2×4 matrix (each column's min and max become a column).

4. hard

   What is printed?

   df <- data.frame(a = c(1, 2, 3), b = c(10, 20, 30))
   sapply(df, mean)
   apply(df, 1, sum)
   for (i in 1:3) i * 2
   res <- for (i in 1:3) i * 2
   is.null(res)

   Show solution

    a  b
    2 20
   [1] 11 22 33
   [1] TRUE

   sapply runs on the columns; apply(df, 1, sum) sums each row. The loop prints nothing, and a for loop returns NULL (invisibly).

5. exam

   A data frame birds has columns species (character), height (numeric, may contain NA) and damage (0/1). (a) With tapply, compute the mean height per species ignoring missing values. (b) With sapply, count the missing values in every column. (c) Rewrite (a) as a for loop over the species.

   Show solution

   # (a)
   tapply(birds$height, birds$species, mean, na.rm = TRUE)
   # (b)
   sapply(birds, function(col) sum(is.na(col)))
   # (c)
   sp  <- sort(unique(birds$species))
   res <- numeric(length(sp)); names(res) <- sp
   for (s in sp) {
     res[s] <- mean(birds$height[birds$species == s], na.rm = TRUE)
   }
   res

   Marking idea: correct grouping vector in tapply; na.rm = TRUE passed through; anonymous function in sapply; pre-allocated, named result in the loop.

Self-check quiz

Common mistakes

• Expecting a loop body to print values automatically.
• Forgetting that apply(m, 1, f) with a vector-valued f returns the transpose (results as columns).
• Assuming sapply always returns a vector: it can return a matrix or a list (and list() for an empty input). Use vapply to be sure.
• Using apply on a data frame with mixed types: it is converted to a character matrix first.
• Swapping arguments in tapply(X, INDEX, FUN): values first, groups second.

A2.10 User-defined functions

Explanation

name <- function(arg1, arg2 = default) {
  # body
  result            # the value of the LAST expression is returned
}

• A function is an object like any other (first-class, as in JS): you can assign it, pass it to sapply, return it from another function.
• Return value: the last evaluated expression. return(x) exits early. To return several things, return a list (or a named vector). If the last expression is an assignment, the value is returned invisibly (not printed at the console). invisible(x) does the same on purpose.
• Argument matching, in this order: exact name, then partial name (prefix), then position. power(p = 3, x = 2) works in any order. Arguments after ... can only be matched by full name.
• Defaults can refer to other arguments (function(x, n = length(x))). They are evaluated inside the function, at the moment they are first used.
• missing(arg) tells you if the caller supplied an argument.
• ... (dots) collects any number of extra arguments; forward them (plot(x, ...)) or capture them (list(...), ...length()). Like JS rest/spread (...args).

Worked examples

power <- function(x, p = 2) x^p
power(3); power(3, 3); power(p = 3, x = 2); power(1:4)
f <- function(x, y) if (missing(y)) "no y" else x + y
f(1); f(1, 2)
g <- function(a, b) a * 2
g(5, stop("never evaluated"))
h <- function(x, y = x * 2) { x <- 10; y }
h(1)

[1] 9
[1] 27
[1] 8
[1]  1  4  9 16
[1] "no y"
[1] 3
[1] 10
[1] 20

h(1) is 20, not 2: the default y = x * 2 is evaluated only when y is used, and by then the local x is 10.

my_sum <- function(...) sum(...)
count_args <- function(...) length(list(...))
my_sum(1, 2, 3); count_args("a", 1:10, NULL)
describe <- function(x) list(mean = mean(x), sd = sd(x))
r <- describe(c(2, 4, 6)); r$mean; r$sd
set_y <- function(x) { y <- x + 1 }
set_y(1)          # prints nothing (invisible)
(set_y(1))
make_power <- function(p) function(x) x^p
square <- make_power(2); cube <- make_power(3)
square(4); cube(2)
fact <- function(n) if (n <= 1) 1 else n * fact(n - 1)
fact(5)

[1] 6
[1] 3
[1] 4
[1] 2
[1] 2
[1] 16
[1] 8
[1] 120

Exercises

1. easy

   Write celsius_to_f(c) (F = C·9/5 + 32). What does celsius_to_f(c(0, 100, -40)) return?

   Show solution

   celsius_to_f <- function(c) c * 9 / 5 + 32
   celsius_to_f(c(0, 100, -40))

   [1]  32 212 -40

   The body is vectorised, so no loop is needed. (Naming the argument c works but shadows the function c() inside; a better name is temp.)

2. medium

   What is printed?

   f <- function(x) { x[1] <- 100; x }
   v <- 1:3
   f(v)
   v
   k <- function(n = 5, step) n * 2
   k()
   k(step = stop("boom"))

   Show solution

   [1] 100   2   3
   [1] 1 2 3
   [1] 10
   [1] 10

   The function modifies a local copy. step is never used, so its absence (or the stop) is never evaluated.

3. medium

   What does s(4) return, and what does s(-1) return?

   s <- function(x) {
     if (x < 0) return("negative")
     out <- sqrt(x)
     paste("root:", out)
   }

   Show solution

   [1] "root: 2"
   [1] "negative"

4. hard

   What is printed?

   adders <- lapply(1:3, function(i) function(x) x + i)
   adders[[2]](10)
   compose <- function(f, g) function(x) g(f(x))
   h <- compose(sqrt, function(z) z * 10)
   h(16)

   Show solution

   [1] 12
   [1] 40

   Each inner function remembers its own i (since R 3.2.0 lapply forces the argument, so adders[[2]] has i = 2). h(16) = 10 * sqrt(16) = 40.

5. exam

   Write summary_stats(x, na.rm = TRUE) that stops with an error if x is not numeric and otherwise returns a named numeric vector with n (number of non-missing values), mean and sd. Show the call on c(2, 4, 4, 4, 5, 5, 7, 9, NA).

   Show solution

   summary_stats <- function(x, na.rm = TRUE) {
     if (!is.numeric(x)) stop("x must be numeric")
     if (na.rm) x <- x[!is.na(x)]
     c(n = length(x), mean = mean(x), sd = sd(x))
   }
   summary_stats(c(2, 4, 4, 4, 5, 5, 7, 9, NA))

         n    mean      sd
   8.00000 5.00000 2.13809 

   All elements of a numeric vector share one format, so 8 and 5 are printed with 5 decimals like the sd \( \sqrt{32/7} \approx 2.13809\). Marking idea: input check, NA handling, named return value, no printing inside the function.

Self-check quiz

Common mistakes

• Printing the result inside the function (print(res)) instead of returning it.
• Ending the body with an assignment and wondering why nothing is shown.
• Writing return x (JS style). In R, return is a function: return(x).
• Expecting a function to change its argument in the caller (copy-on-modify).
• Forgetting that defaults are evaluated lazily, inside the function.

A2.11 Importing and exporting data, data from the web, quantmod

Explanation

Text files

Important arguments: na.strings = c("", "NA", "?") (which strings mean missing), stringsAsFactors (default FALSE since R 4.0), skip (lines to skip), nrows, colClasses, encoding/fileEncoding. The file can be a path relative to the working directory, an absolute path, or a URL: read.csv("https://.../data.csv") reads directly from the web. download.file(url, destfile) saves a copy first. file.choose() opens a dialog.

After reading, always inspect: str(df), head(df), summary(df), dim(df), colSums(is.na(df)).

Writing

• write.csv(df, "out.csv", row.names = FALSE). By default row.names = TRUE: the row names (1, 2, 3...) are written as an extra first column with an empty header, which comes back as a column named X when you re-read with read.csv.
• write.table(df, "out.txt", sep = "\t", quote = FALSE); readr::write_csv(df, "out.csv") (never writes row names).

R binary formats

Other formats via packages: haven (SPSS, Stata, SAS), jsonlite::fromJSON(), DBI for databases.

Financial data from the web: quantmod

library(quantmod)
getSymbols("AAPL", src = "yahoo", from = "2024-01-01", to = "2024-12-31")
# creates an object called AAPL in the workspace (auto.assign = TRUE)
class(AAPL)          # "xts" "zoo": a time series indexed by date
head(AAPL)           # columns AAPL.Open AAPL.High AAPL.Low AAPL.Close AAPL.Volume AAPL.Adjusted
close <- Cl(AAPL)    # also Op(), Hi(), Lo(), Vo(), Ad()
ret <- dailyReturn(AAPL)
chartSeries(AAPL)    # financial chart
x <- getSymbols("MSFT", auto.assign = FALSE)   # return the data instead

getSymbols() itself returns the name of the symbol ("AAPL") and, as a side effect, creates the object. The data requires an internet connection; sources (Yahoo, FRED: src = "FRED") may change, so the exact columns can differ check against the lesson 4 code.

Worked examples

df <- data.frame(id = 1:2, score = c(7.5, 8))
write.csv(df, "scores.csv")                 # row names written too
back <- read.csv("scores.csv")
names(back)
write.csv(df, "scores.csv", row.names = FALSE)
names(read.csv("scores.csv"))
saveRDS(df, "scores.rds")
same <- readRDS("scores.rds")
identical(df, same)

[1] "X"     "id"    "score"
[1] "id"    "score"
[1] TRUE

The file written by the first write.csv looks like this:

"","id","score"
"1",1,7.5
"2",2,8

# Lab-style import (file in the working directory, "?" used for missing values)
diab <- read.csv("diabetes_dataset.csv", na.strings = c("", "NA", "?"))
str(diab)
colSums(is.na(diab))

Exercises

1. easy

   Write the line to read sales.csv, where values are separated by semicolons and decimals use a comma (12,50).

   Show solution

   sales <- read.csv2("sales.csv")
   # equivalent: read.csv("sales.csv", sep = ";", dec = ",")

2. medium

   A colleague runs write.csv(df, "a.csv") then df2 <- read.csv("a.csv"), with df having 3 columns. What is ncol(df2)? How do you fix it on writing and on reading?

   Show solution

   ncol(df2) is 4 (the extra column X with the old row names). Fix when writing: row.names = FALSE. Fix when reading: read.csv("a.csv", row.names = 1) (use the first column as row names) or drop it: df2$X <- NULL.

3. medium

   What is the difference between these two scripts, in terms of which objects exist afterwards?

   # A
   save(df, file = "d.RData"); rm(df); load("d.RData")
   # B
   saveRDS(df, "d.rds"); rm(df); data2 <- readRDS("d.rds")

   Show solution

   After A, an object named df exists again (load restores original names). After B, the data is in data2 and there is no df.

4. exam

   Write code that: downloads https://example.org/birds.csv directly into a data frame (empty strings are missing), keeps only rows where SPECIES is not missing, and saves the result as birds_clean.csv without row names and as birds_clean.rds.

   Show solution

   birds <- read.csv("https://example.org/birds.csv", na.strings = c("", "NA"))
   birds <- birds[!is.na(birds$SPECIES), ]
   write.csv(birds, "birds_clean.csv", row.names = FALSE)
   saveRDS(birds, "birds_clean.rds")

Self-check quiz

Common mistakes

• File not found: the file is not in the working directory. Check getwd() and list.files().
• Using read.csv on a semicolon file: you get one single column. Use read.csv2.
• Missing values coded as "?" or "" make a numeric column character. Use na.strings.
• Forgetting row.names = FALSE in write.csv.
• Writing x <- load("f.RData"): load returns only the names of the loaded objects.

A2.12 Base graphics: plot(), layers, layouts, histograms, statistical plots

Explanation

plot() and its main arguments

plot() is generic: plot(x, y) scatterplot; plot(y) values against index 1..n; plot(y ~ x, data = df) formula interface; plot(df) for a numeric data frame gives a scatterplot matrix; plot(factor) gives a bar chart; plot(ts_object) a time-series line.

Adding layers

• points(x, y, col = "red"), lines(x, y, lty = 2)
• abline(h = 0) horizontal, abline(v = 5) vertical, abline(a = 1, b = 2) line \(y = a + bx\), abline(lm(y ~ x)) regression line
• text(x, y, labels), legend("topright", legend = c("A", "B"), col = c("black", "red"), lty = 1)

Multi-plot layouts

par() sets graphical parameters. par(mfrow = c(2, 3)) splits the device into 2 rows × 3 columns, filled row by row (mfcol fills column by column). Settings persist until changed, so save and restore: old <- par(mfrow = c(1, 2)); ...; par(old). layout(matrix(...)) allows unequal panels. Other par options: mar (margins), bg, las (axis label orientation).

Distributions and classical statistical plots

Saving to a file: open a device, plot, close it: png("plot.png", width = 800, height = 600); plot(x); dev.off() (also pdf(), jpeg()).

Worked examples

x <- 1:10
y <- x^2
plot(x, y, type = "b", pch = 19, col = "blue",
     main = "Square vs cube", xlab = "x", ylab = "value")
lines(x, x^1.5, lty = 2, col = "red")
abline(h = 50, col = "grey")
legend("topleft", legend = c("x^2", "x^1.5"),
       col = c("blue", "red"), lty = c(1, 2), pch = c(19, NA))

set.seed(1)
v <- rnorm(200, mean = 10, sd = 2)
old <- par(mfrow = c(1, 3))          # 1 row, 3 panels
hist(v, freq = FALSE, breaks = 15, main = "Histogram + density")
lines(density(v), lwd = 2)
curve(dnorm(x, 10, 2), add = TRUE, lty = 2)
boxplot(len ~ supp, data = ToothGrowth, main = "Boxplot by group")
qqnorm(v); qqline(v)
par(old)                              # restore the layout

ToothGrowth is a built-in dataset (columns len, supp, dose), like mtcars, iris, airquality.

Exercises

1. easy

   Match: (1) type = "l", (2) pch, (3) lty = 2, (4) abline(v = 3), (5) par(mfrow = c(2, 2)) with: (a) dashed line, (b) 2×2 grid of plots, (c) draw lines instead of points, (d) vertical line at x = 3, (e) point symbol.

   Show solution

   1-c, 2-e, 3-a, 4-d, 5-b.

2. medium

   This code fails or does not show the density curve properly. Find the two problems.

   lines(density(mtcars$mpg))
   hist(mtcars$mpg)

   Show solution

   (1) lines() is low-level: it needs an existing plot, so call hist() first (else: plot.new has not been called yet). (2) The histogram must be on the density scale (freq = FALSE), otherwise the density curve (values around 0.05) is flattened at the bottom of a count axis.

   hist(mtcars$mpg, freq = FALSE)
   lines(density(mtcars$mpg))

3. medium

   After par(mfrow = c(2, 2)) you call hist(a); hist(b); plot(a, b). Where is each plot, and where would a fifth plot go?

   Show solution

   hist(a) top-left, hist(b) top-right, plot(a, b) bottom-left (filled by rows). The bottom-right stays empty until the 4th plot; a 5th high-level plot starts a new page at top-left.

4. exam

   Using the built-in airquality data frame (columns Ozone, Temp, Month), write code that draws side by side: (a) a scatterplot of Ozone against Temp with filled red points, title and axis labels, and the least-squares line; (b) boxplots of Temp by Month.

   Show solution

   old <- par(mfrow = c(1, 2))
   plot(Ozone ~ Temp, data = airquality, pch = 16, col = "red",
        main = "Ozone vs temperature", xlab = "Temp (F)", ylab = "Ozone (ppb)")
   abline(lm(Ozone ~ Temp, data = airquality), lwd = 2)
   boxplot(Temp ~ Month, data = airquality, main = "Temperature by month")
   par(old)

   Rows with NA Ozone are dropped automatically by the formula interface and by lm().

Self-check quiz

Common mistakes

• Calling points()/lines() before plot().
• Adding a density curve on a count-scale histogram.
• Calling barplot(df$city) on raw strings instead of barplot(table(df$city)).
• Forgetting to reset par(mfrow): later plots stay in the grid.
• Forgetting dev.off() after png()/pdf(): the file stays empty.

A2.13 Tidyverse: tibbles, dplyr verbs and pipes (data manipulation)

Explanation

library(tidyverse) attaches the core packages: ggplot2 (plots), dplyr (manipulation), tidyr (reshaping), readr (import), tibble, purrr (functional programming, map()), stringr (strings), forcats (factors) and, since tidyverse 2.0, lubridate (dates). "Tidy data": each variable a column, each observation a row, each value a cell.

Tibbles versus data frames

Convert with as_tibble(df) and as.data.frame(tb). readr::read_csv() returns a tibble. Extracting one column as a vector from a tibble: tb$col, tb[["col"]] or pull(tb, col).

Pipes

x |> f(y) means f(x, y): the left side becomes the first argument. |> is the native pipe (R ≥ 4.1); %>% is the magrittr pipe loaded with the tidyverse (it also accepts . as a placeholder: x %>% lm(y ~ z, data = .); the native pipe's placeholder is _, must be used with a named argument, R ≥ 4.2). Read a pipe as "then". JS analogy: arr.filter(...).map(...).

The main dplyr verbs

Inside the verbs you write column names directly, without df$ and without quotes ("data masking"). dplyr keeps the input class: a data.frame in gives a data.frame out (but group_by creates a grouped tibble). tidyr reshapes: pivot_longer(df, cols = c(a, b), names_to = "var", values_to = "val") (wide → long) and pivot_wider() (long → wide); drop_na() removes rows with NA.

Worked examples

library(dplyr)
emp <- tibble(name   = c("Ann", "Bob", "Cy", "Dan", "Eve"),
              dept   = c("IT", "HR", "IT", "HR", "IT"),
              salary = c(3000, 2500, 3500, 2800, 4000))
emp |> filter(dept == "IT", salary > 3200) |> select(name, salary)

# A tibble: 2 × 2
  name  salary
  <chr>  <dbl>
1 Cy      3500
2 Eve     4000

emp |>
  group_by(dept) |>
  summarise(n = n(), avg = mean(salary))

# A tibble: 2 × 3
  dept      n   avg
  <chr> <int> <dbl>
1 HR        2  2650
2 IT        3  3500

emp |>
  mutate(bonus = salary * 0.1) |>
  arrange(desc(salary)) |>
  slice(1:2)

# A tibble: 2 × 4
  name  dept  salary bonus
  <chr> <chr>  <dbl> <dbl>
1 Eve   IT      4000   400
2 Cy    IT      3500   350

emp[, "salary"]                 # still a tibble (5 x 1)
as.data.frame(emp)[, "salary"]  # a plain vector
emp |> mutate(level = case_when(salary >= 3500 ~ "high",
                                 salary >= 2800 ~ "mid",
                                 .default = "low")) |> pull(level)

# A tibble: 5 × 1
  salary
   <dbl>
1   3000
2   2500
3   3500
4   2800
5   4000
[1] 3000 2500 3500 2800 4000
[1] "mid"  "low"  "high" "mid"  "high"

The same group summary in base R: aggregate(salary ~ dept, data = emp, FUN = mean) or tapply(emp$salary, emp$dept, mean). (.default in case_when needs dplyr ≥ 1.1; older code writes TRUE ~ "low".)

Exercises

1. easy

   Rewrite without pipes: emp |> filter(salary > 2600) |> arrange(salary). How many rows does it return and who is first?

   Show solution

   arrange(filter(emp, salary > 2600), salary). Bob (2500) is removed, so 4 rows; the first is Dan (2800).

2. medium

   With emp as a tibble and d <- as.data.frame(emp), what is the class of: emp[, 2], d[, 2], emp$dept, d["dept"]? What does d$sal return, and emp$sal?

   Show solution

   emp[, 2]: tibble ("tbl_df" "tbl" "data.frame"); d[, 2]: character vector; emp$dept: character vector; d["dept"]: data.frame. d$sal partially matches salary and returns the 5 salaries; emp$sal returns NULL with a warning.

3. medium

   What does this return (number of rows and the values)?

   t1 <- tibble(x = c(1, NA, 3, 4), g = c("a", "a", "b", "b"))
   t1 |> filter(x > 1) |> nrow()
   t1 |> group_by(g) |> summarise(s = sum(x)) |> pull(s)
   t1 |> group_by(g) |> summarise(s = sum(x, na.rm = TRUE)) |> pull(s)

   Show solution

   [1] 2
   [1] NA  7
   [1] 1 7

   filter drops the NA row and the 1, leaving 3 and 4. Group a contains an NA, so its sum is NA unless na.rm = TRUE.

4. hard

   Explain why tibble(x = 1:4, y = 1:2) fails while data.frame(x = 1:4, y = 1:2) works, and what the second gives for y.

   Show solution

   Tibbles only recycle vectors of length 1 (to avoid hidden bugs): error Tibble columns must have compatible sizes. data.frame recycles 1:2 because 4 is a multiple of 2, so y is 1 2 1 2.

5. exam

   Using the diabetes data (tibble diab with age, bmi, glucose, outcome 0/1), write a dplyr pipeline that removes rows with missing glucose, adds age_group ("<40", "40-60", ">60"), and returns per age group and outcome the number of patients and mean BMI, sorted by mean BMI descending.

   Show solution

   diab |>
     filter(!is.na(glucose)) |>
     mutate(age_group = case_when(age < 40  ~ "<40",
                                  age <= 60 ~ "40-60",
                                  TRUE      ~ ">60")) |>
     group_by(age_group, outcome) |>
     summarise(n = n(), mean_bmi = mean(bmi, na.rm = TRUE), .groups = "drop") |>
     arrange(desc(mean_bmi))

   Marking idea: correct verb order (filter → mutate → group_by → summarise → arrange), case_when conditions checked top to bottom, n(), desc().

Self-check quiz

Common mistakes

• Writing df$col inside dplyr verbs (works but defeats the purpose and breaks with groups).
• Using = instead of == in filter(): error We detected a named input.
• Forgetting to assign the result: pipelines do not modify df in place (df <- df |> ...).
• Using + instead of a pipe between dplyr steps (the + is for ggplot2 layers).
• Expecting tb[, 1] to be a vector.

A2.14 ggplot2: aesthetic mappings, geoms, facets, combining plots with patchwork

Explanation

ggplot(data = df, mapping = aes(x = var1, y = var2, colour = group)) +
  geom_point() +
  labs(title = "...", x = "...", y = "...") +
  theme_minimal()

• ggplot() creates the plot object with the data and (optionally) the global mapping. Layers are added with + (not with a pipe). The + must be at the end of a line, not at the start of the next one.
• A ggplot is an object: p <- ggplot(...) + ... draws nothing until printed (p or print(p)). You can add more later: p + geom_smooth().

Common geoms

Aesthetics given in ggplot(aes(...)) apply to all layers; aesthetics inside one geom apply only to that layer; a geom can also use different data.

Facets, scales, themes, saving

• facet_wrap(~ var): one panel per value of var; facet_grid(rows ~ cols): a grid by two variables.
• labs(title, subtitle, x, y, colour, caption); xlim(), scale_x_log10(), scale_colour_manual(values = c(...)), scale_fill_brewer(); coord_flip().
• Themes: theme_minimal(), theme_bw(), theme_classic(); theme(legend.position = "bottom").
• ggsave("plot.png", p, width = 7, height = 5) (default: the last plot).

patchwork

The patchwork package combines ggplot objects with operators: p1 + p2 (side by side), p1 | p2 (side by side), p1 / p2 (stacked), (p1 | p2) / p3 (two on top, one below), p1 + p2 + plot_layout(ncol = 1), + plot_annotation(title = "...", tag_levels = "A"). It works only with ggplot objects (not base plots; for those use par(mfrow)).

Worked examples

library(ggplot2)
library(patchwork)
p1 <- ggplot(mtcars, aes(x = wt, y = mpg, colour = factor(cyl))) +
  geom_point(size = 2) +
  geom_smooth(method = "lm", se = FALSE) +
  labs(title = "Fuel use vs weight", x = "Weight (1000 lb)", y = "Miles per gallon",
       colour = "Cylinders")
p2 <- ggplot(mtcars, aes(x = factor(cyl), y = mpg)) +
  geom_boxplot(fill = "lightblue") +
  labs(x = "Cylinders", y = "mpg")
p3 <- ggplot(mtcars, aes(x = mpg)) +
  geom_histogram(binwidth = 2, colour = "white") +
  facet_wrap(~ am)
(p1 | p2) / p3 + plot_annotation(title = "mtcars overview", tag_levels = "A")

factor(cyl) is used because cyl is numeric (4, 6, 8): mapped as a number it would give a continuous colour gradient instead of 3 distinct colours, and a boxplot needs a discrete x. With colour = factor(cyl) in the global aes, geom_smooth fits one line per group.

counts <- data.frame(city = c("MI", "RM", "TO"), n = c(12, 7, 4))
ggplot(counts, aes(x = city, y = n)) + geom_col()       # heights given
ggplot(df, aes(x = city)) + geom_bar()                   # counts the rows of df

Exercises

1. easy

   Which geom for: (a) the distribution of one numeric variable; (b) counts of a categorical variable from raw rows; (c) bar heights already computed in a column; (d) a numeric variable by group; (e) a time series?

   Show solution

   (a) geom_histogram or geom_density; (b) geom_bar; (c) geom_col; (d) geom_boxplot (or geom_violin); (e) geom_line.

2. medium

   Find the errors.

   ggplot(mtcars, aes(x = hp, y = mpg))
     + geom_point(aes(colour = "red"))
     |> labs(title = "HP vs MPG")

   Show solution

   (1) + at the start of a line: the first line is a complete statement, so the plot is printed empty and + geom_point(...) fails on its own. Put + at the end of each line. (2) colour = "red" inside aes() maps a constant: points are not red (default palette colour) and a useless legend appears; write geom_point(colour = "red"). (3) Layers are added with +, not |>.

   ggplot(mtcars, aes(x = hp, y = mpg)) +
     geom_point(colour = "red") +
     labs(title = "HP vs MPG")

3. medium

   What is the difference between ggplot(df, aes(x, y, colour = g)) + geom_point() + geom_smooth(method = "lm") and ggplot(df, aes(x, y)) + geom_point(aes(colour = g)) + geom_smooth(method = "lm")?

   Show solution

   In the first, colour = g is global: points are coloured and geom_smooth draws one regression line per group. In the second, colour only applies to the points; geom_smooth fits a single line to all the data.

4. exam

   Using the iris dataset, write code for: p1 = scatterplot of Petal.Length vs Sepal.Length coloured by Species; p2 = histograms of Sepal.Width with one panel per species; then combine them with p1 on top and p2 below using patchwork, and save as iris.png.

   Show solution

   library(ggplot2); library(patchwork)
   p1 <- ggplot(iris, aes(x = Sepal.Length, y = Petal.Length, colour = Species)) +
     geom_point()
   p2 <- ggplot(iris, aes(x = Sepal.Width)) +
     geom_histogram(bins = 15) +
     facet_wrap(~ Species)
   p <- p1 / p2
   ggsave("iris.png", p, width = 8, height = 8)