Database Engineering with PostgreSQL

Learn relational databases and SQL with PostgreSQL: installation, roles, querying, filtering, ER diagrams and joins, with hands-on tasks on the DVD Rental sample database.

How each session works

1. The big idea: the topic in plain words, with an everyday example.

2. A picture: a diagram of the idea.

3. Real queries: every SQL statement was run on PostgreSQL 17 and the output you see is what it printed.

4. Common mistakes: what trips beginners up.

5. Hands-on exercises: try first, then open the solution (each was run too).

6. Practice: a quiz for the session.

Tip

All sessions use one small movie database. Create it once with code/00-setup.sql (psql -d course -f 00-setup.sql), then follow along in psql. Install PostgreSQL first from the Installing PostgreSQL page below if you have not yet.

Sessions

• Session 1 · Why we need a database
• Session 2 · Database and DBMS
• Session 3 · The relational model
• Session 4 · Querying data with SELECT
• Session 5 · Filtering rows with WHERE
• Session 6 · Entity relationship diagrams
• Session 7 · Joins
• Session 8 · Joins on an e-commerce database

Reference: install and sample data

• Installing Postgresql
  • Installing PostgreSQL on Linux
  • Installing PostgreSQL on Windows
  • Installing PostgreSQL on Mac
  • Postgres in Docker
• What happens during the initial installation ?
  • Roles, Users & Groups
  • More examples on Roles
• Sample Databases
  • DVD Rental Database

Querying Data

• SELECT Query
• Column Aliases
• Order By
• SELECT DISTINCT
• DB-TASK-001

Filtering Data

• WHERE ?
• AND Operator
• OR Operator
• LIMIT
• FETCH
• IN
• BETWEEN
• LIKE
• SIMILAR TO
• IS NULL
• ESCAPE
• DB-TASK-002

Entity Relationship Diagram

• What is an ER Diagram ?
• Entity
• Identifiers
• Relationship
• Attributes
• Cardinality
• Entity Relationship Diagram Tutorial
• DB-TASK-003

Joins

• Joins
• Joins With Employee and Departments