freeCodeCamp Relational Databases Syllabus

Relational Databases syllabus — full chapter and topic list

Expand any chapter to see its topics and sub-topics. This is the whole examinable outline for Relational Databases in freeCodeCamp, not a summary of it.

1. 1

   Bash and the Command Line

   2 topics

   • Navigating the Filesystem
   • Bash Scripting
2. 2

   Git and Version Control

   2 topics

   • Repositories and Commits
   • Branching and Merging
3. 3

   PostgreSQL Fundamentals

   3 topics

   • Creating Tables and Databases
   • Data Types and Constraints
   • Inserting and Updating Data
4. 4

   Querying Data with SQL

   4 topics

   • SELECT and WHERE
   • Joins
   • Aggregations and Grouping
   • Relationships and Normalization
5. 5

   Certification Projects

   5 topics

   • Celestial Bodies Database
   • World Cup Database
   • Salon Appointment Scheduler
   • Periodic Table Database
   • Number Guessing Game

Relational Databases flashcards for freeCodeCamp

25 of 50 cards from the Relational Databases deck — real questions with worked answers.

1. In the bash terminal, what does the `pwd` command do?

   `pwd` stands for "print working directory" — it outputs the absolute path of the directory you are currently in.

2. What is the difference between an absolute path and a relative path, and what do `.` and `..` mean?

   An absolute path starts from the root directory `/` (e.g., `/home/user/project`); a relative path starts from the current working directory. `.` refers to the current directory and `..` refers to the parent directory (e.g., `cd ..` moves up one level).

3. What do the `ls` flags `-l` and `-a` show?

   `ls -l` shows the long listing format (permissions, owner, size, modification date); `ls -a` shows all files including hidden ones whose names begin with a dot. Combined as `ls -la`.

4. Which commands create, copy, move/rename, and delete files or directories in bash?

   `touch file` creates an empty file; `mkdir dir` creates a directory (`-p` for nested paths); `cp src dest` copies (`-r` for directories); `mv src dest` moves or renames; `rm file` deletes (`rm -r dir` for directories).

5. In `chmod 755 script.sh`, what permissions does each octal digit grant?

   Each digit is a sum of read $= 4$, write $= 2$, execute $= 1$. Owner gets $7 = 4 + 2 + 1$ (rwx); group and others each get $5 = 4 + 1$ (r-x). This makes a script executable by everyone but writable only by the owner.

6. What is the shebang line in a bash script and what does it do?

   It is the first line `#!/bin/bash`. It tells the operating system which interpreter to use to execute the script, so the file can be run directly (e.g., `./script.sh`) once it is executable.

7. How do you assign and use a variable in bash, and how do you read user input into one?

   Assign with no spaces around `=`: `NAME=value`. Access it with a dollar sign: `$NAME` (or `${NAME}`). Read keyboard input with `read NAME`, which stores the user's input in the variable.

8. Which operators does bash use inside `[[ ... ]]` to compare integers in an if statement?

   `-eq` (equal), `-ne` (not equal), `-lt` (less than), `-le` (less or equal), `-gt` (greater than), `-ge` (greater or equal). Example: `if [[ $NUM -gt 10 ]]`. Strings use `=` and `!=` instead.

9. Give the basic syntax of `while` and `until` loops in bash.

   `while [[ CONDITION ]]; do COMMANDS; done` repeats while the condition is true; `until [[ CONDITION ]]; do COMMANDS; done` repeats until the condition becomes true (i.e., while it is false). `for X in LIST; do ...; done` iterates over a list.

10. In a bash script, what do `$1`, `$#`, `$@`, and `$?` mean?

    `$1` is the first positional (command-line) argument, `$#` is the number of arguments, `$@` expands to all arguments, and `$?` is the exit status of the last command ($0$ means success, non-zero means failure).

11. What is command substitution in bash and why is it useful in database scripts?

    `$(command)` runs the command and substitutes its output in place. Example: `RESULT=$($PSQL "SELECT count(*) FROM users")` captures a psql query result into a variable for use in the script.

12. What does `git init` do, and what is a git repository?

    `git init` creates a new repository by adding a hidden `.git` directory to the project folder. A repository is the full project history: all tracked files, commits, and branches stored by git.

13. Describe the roles of the working directory, the staging area, and `git add` / `git commit`.

    Changes are made in the working directory; `git add file` moves them to the staging area (index); `git commit -m "message"` permanently records the staged snapshot in the repository history.

14. What is the difference between `git status` and `git log`?

    `git status` shows the current state of the working directory and staging area (modified, staged, untracked files); `git log` shows the commit history (hashes, authors, dates, messages), with `--oneline` for a condensed view.

15. What do `git clone`, `git push`, and `git pull` do?

    `git clone URL` copies a remote repository to your machine; `git push` uploads your local commits to the remote; `git pull` fetches remote commits and merges them into your current branch.

16. How do you create a new branch and switch to it in one command, and why use branches?

    `git checkout -b branch_name` (or `git switch -c branch_name`) creates and switches to a new branch. Branches let you develop features or fixes in isolation without changing the main branch until the work is merged.

17. What does `git merge` do, and what is a fast-forward merge?

    `git merge branch` combines the named branch's commits into the current branch. If the current branch has no new commits since the branch diverged, git simply moves the pointer forward (fast-forward); otherwise it creates a merge commit combining both histories.

18. What is a merge conflict and how do you resolve it?

    A conflict occurs when the same lines were changed differently on both branches. Git marks the file with `<<<<<<<`, `=======`, `>>>>>>>` sections; you manually edit the file to the desired content, remove the markers, then `git add` the file and complete the merge with a commit.

19. In PostgreSQL, how do you create a new database and connect to it in psql?

    `CREATE DATABASE database_name;` creates it; the psql meta-command `\c database_name` connects to it. Note that SQL statements end with a semicolon, while `\` commands do not.

20. Give the general syntax for creating a table with columns in PostgreSQL.

    `CREATE TABLE table_name(column_name DATATYPE CONSTRAINTS, ...);` — for example: `CREATE TABLE students(student_id SERIAL PRIMARY KEY, name VARCHAR(30) NOT NULL);`. A table can also be created empty with `CREATE TABLE name();` and columns added later.

21. 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).

22. 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.

23. 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`.

24. 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$.

25. 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)`.

See more Relational Databases flashcards →

Planning Relational Databases for freeCodeCamp

Relational Databases (freeCodeCamp) FAQ