freeCodeCamp Relational Databases Syllabus

How do you add, drop, and rename a column with ALTER TABLE, and how do you delete a table or database?

`ALTER TABLE t ADD COLUMN col TYPE;`, `ALTER TABLE t DROP COLUMN col;`, `ALTER TABLE t RENAME COLUMN old TO new;`. Delete a table with `DROP TABLE t;` and a database with `DROP DATABASE db;` (cannot be connected to it).

What do the psql meta-commands `\l`, `\c`, `\d`, and `\dt` display?

`\l` lists all databases; `\c name` connects to a database; `\d table_name` describes a table's columns, types, and constraints; `\dt` lists the tables in the current database.

What does the SERIAL type do in PostgreSQL?

SERIAL is an auto-incrementing integer: each new row automatically receives the next value in a sequence ($1, 2, 3, \dots$). It is commonly used for surrogate primary key columns such as `user_id SERIAL PRIMARY KEY`.

Compare VARCHAR(n) with TEXT, and explain NUMERIC(precision, scale).

`VARCHAR(n)` stores variable-length text up to a maximum of $n$ characters; `TEXT` stores unlimited-length text. `NUMERIC(p, s)` stores exact decimals with $p$ total digits and $s$ digits after the decimal point — e.g., `NUMERIC(4,1)` can store values like $999.9$.

What does a PRIMARY KEY constraint guarantee, and how many can a table have?

A primary key uniquely identifies each row: the column(s) must be unique and NOT NULL. A table can have only one primary key, though it may be composite (span multiple columns), e.g. `PRIMARY KEY(col1, col2)`.

What is a FOREIGN KEY and what does referential integrity mean?

A foreign key is a column that references the primary key (or unique column) of another table, e.g. `ALTER TABLE orders ADD FOREIGN KEY(customer_id) REFERENCES customers(customer_id);`. Referential integrity means the database rejects values that do not exist in the referenced table.

What do the NOT NULL, UNIQUE, and CHECK constraints enforce?

NOT NULL requires the column to always have a value; UNIQUE forbids duplicate values in the column; CHECK enforces a boolean condition on inserted values, e.g. `CHECK (price > 0)`.

Give the syntax for inserting one or more rows into a table.

`INSERT INTO table_name(col1, col2) VALUES (val1, val2);` — multiple rows can be inserted at once by separating value tuples with commas: `VALUES (a, b), (c, d);`. Text values are wrapped in single quotes.

How do you change existing rows with UPDATE, and what happens if you omit the WHERE clause?

`UPDATE table_name SET column = new_value WHERE condition;` changes only matching rows. Without WHERE, every row in the table is updated — a common and dangerous mistake.

How do you delete specific rows from a table in SQL?

`DELETE FROM table_name WHERE condition;` removes only the rows matching the condition. `DELETE FROM table_name;` with no WHERE clause removes all rows while keeping the table structure.

What is the basic syntax of a SELECT query, and how do ORDER BY, LIMIT, and DISTINCT modify it?

`SELECT columns FROM table;` (`*` selects all columns). `ORDER BY col ASC|DESC` sorts the results, `LIMIT n` returns at most $n$ rows, and `SELECT DISTINCT col` removes duplicate values from the output.

List the main comparison and logical operators used in a WHERE clause.

Comparisons: `=`, `!=` (or `<>`), `<`, `>`, `<=`, `>=`; range/set tests: `BETWEEN a AND b`, `IN (list)`, `IS NULL` / `IS NOT NULL`; conditions are combined with `AND`, `OR`, and negated with `NOT`.

How do the wildcards in LIKE work, and what does ILIKE do in PostgreSQL?

In `WHERE name LIKE pattern`, `%` matches any sequence of characters (including none) and `_` matches exactly one character — e.g., `'W%'` matches names starting with W. `ILIKE` performs the same match case-insensitively.

What rows does an INNER JOIN return? Give the syntax.

`SELECT * FROM a INNER JOIN b ON a.key = b.key;` returns only rows where the join condition matches in both tables — rows without a match on either side are excluded.

What is the difference between a LEFT JOIN and a RIGHT JOIN?

LEFT JOIN returns all rows from the left (first) table plus matching rows from the right table, filling non-matches with NULL; RIGHT JOIN does the mirror image, keeping all rows of the right table.

What does a FULL (OUTER) JOIN return?

All rows from both tables: matched rows are combined, and rows with no match in the other table appear with NULL for the missing side. It is the union of the LEFT and RIGHT join results.

When can you use USING instead of ON in a join, and how does it differ?

`JOIN b USING(col)` works when the join column has the same name in both tables; it is shorthand for `ON a.col = b.col` and outputs the column once instead of twice. Multiple tables can be chained: `FULL JOIN c USING(col)`.

Name the five core SQL aggregate functions and state what AVG computes.

COUNT, SUM, AVG, MIN, MAX. AVG returns the arithmetic mean of a column: $$\bar{x} = \frac{1}{n}\sum_{i=1}^{n} x_{i}$$ NULL values are ignored by aggregates (except `COUNT(*)`, which counts rows).

What does GROUP BY do, and what rule applies to the columns in the SELECT list when it is used?

GROUP BY collapses rows sharing the same value(s) into groups so aggregates are computed per group, e.g. `SELECT major, COUNT(*) FROM students GROUP BY major;`. Every selected column must either appear in the GROUP BY clause or be inside an aggregate function.

What is the difference between WHERE and HAVING?

WHERE filters individual rows before grouping and cannot contain aggregate functions; HAVING filters the groups after GROUP BY and may use aggregates, e.g. `GROUP BY major HAVING COUNT(*) > 5;`.

How is a one-to-many relationship implemented between two tables?

The "many" side table gets a foreign key column referencing the primary key of the "one" side. Example: many `orders` rows carry a `customer_id` that references `customers(customer_id)` — one customer, many orders.

How is a many-to-many relationship implemented in a relational database?

With a third junction (join) table containing foreign keys to both tables — e.g., `students_courses(student_id, course_id)` referencing `students` and `courses` — often with a composite primary key `PRIMARY KEY(student_id, course_id)` to prevent duplicate pairs.

What does First Normal Form (1NF) require?

Every column holds atomic (indivisible) values, each row is unique (there is a primary key), and there are no repeating groups — e.g., no comma-separated lists or `phone1, phone2, phone3` columns.

State the requirements of Second Normal Form (2NF) and Third Normal Form (3NF).

2NF: the table is in 1NF and every non-key column depends on the whole primary key (no partial dependencies on part of a composite key). 3NF: the table is in 2NF and non-key columns depend only on the key, not on other non-key columns (no transitive dependencies).

In the freeCodeCamp Celestial Bodies project, what tables and key structure are required?

A `universe` database with at least the tables `galaxy`, `star`, `planet`, and `moon` (plus one more). Each table needs a `name` column and a primary key named `table_name_id` that auto-increments (SERIAL). Foreign keys model the hierarchy: each star references a galaxy, each planet a star, each moon a planet.

How do you back up and restore a PostgreSQL database as a .sql file (as done in the Celestial Bodies project)?

Dump: `pg_dump -cC --inserts -U freecodecamp universe > universe.sql` writes SQL that recreates the database. Restore: `psql -U postgres < universe.sql` runs that file to rebuild it.

Describe the schema and workflow of the freeCodeCamp World Cup project.

A `worldcup` database with a `teams` table (`team_id SERIAL PRIMARY KEY`, unique `name`) and a `games` table whose `winner_id` and `opponent_id` are foreign keys to `teams(team_id)`. A bash script reads `games.csv` line by line (with `while read` and IFS set to a comma), inserts teams if missing, then inserts each game; a second script answers queries such as win percentages, e.g. $\frac{\text{wins}}{\text{games}} \times 100$ rounded with ROUND.

In the Salon Appointment Scheduler project, how are the tables related and how does the bash script talk to the database?

Tables: `customers` (unique `phone`), `services`, and `appointments` with foreign keys `customer_id` and `service_id`. The script defines `PSQL="psql --username=freecodecamp --dbname=salon -t --no-align -c"` and runs queries via command substitution, e.g. `CUSTOMER_ID=$($PSQL "SELECT customer_id FROM customers WHERE phone='$PHONE'")`, inserting a new customer when no row is returned.

Outline the schema and script behavior of the Periodic Table project.

The `periodic_table` database is normalized into `elements` (atomic_number, symbol, name), `properties` (melting/boiling points, `atomic_mass DECIMAL`, and a `type_id` foreign key), and a `types` table (metal, nonmetal, metalloid). The bash script takes one argument — an atomic number, symbol, or element name — decides which with a numeric test, joins the tables, and prints a sentence describing the element, or "Could not find that element in the database." if absent.

In the Number Guessing Game project, how is the random number generated and what is stored in the database?

Bash's `$RANDOM` variable with the modulo operator: `SECRET=$(( RANDOM % 1000 + 1 ))` yields an integer in $[1, 1000]$. The script asks for a username, looks it up in the database, reports games played and best game (fewest guesses), loops with `read` until the guess matches (printing higher/lower hints), then saves the game's guess count for that user.