Design, Build, and Query Relational Databases with PostgreSQL
Learn database design, SQL, and PostgreSQL administration — from your first table to a complete, normalized schema.
This course takes you from the fundamentals of the relational model through advanced SQL querying, transaction management, and database performance. By the end, you'll be able to design a normalized schema from scratch, write sophisticated queries using CTEs and window functions, and manage a PostgreSQL database with confidence.
Have a coupon code? Redeem it here
What you'll gain
You'll learn to model real-world data using entity-relationship diagrams, apply normalization through the normal forms, and translate a sound design into a working PostgreSQL schema.
Move past simple SELECT statements and into joins, subqueries, common table expressions, and window functions — the queries that power real reporting and application logic.
You'll create and evaluate indexes, analyze query execution plans with EXPLAIN ANALYZE, manage roles and permissions, and perform backups — skills that matter in production environments.
Who this course is for
You've been introduced to databases in coursework but haven't built one end to end. This course bridges theory and practice by having you design, implement, and query a complete PostgreSQL database.
You write SQL day to day but haven't studied the principles behind it. You'll gain a solid grounding in relational design and normalization, and sharpen your querying and performance skills.
You work with data but rely on others to manage the databases behind it. This course gives you the technical foundation to write your own queries, understand schema design, and work more independently with data.
What you'll learn
The course opens with the relational model and PostgreSQL fundamentals — what databases are, how PostgreSQL is structured, and how to work with it using psql and GUI tools. From there, modules on data types, keys, relationships, and normalization build the foundation for sound database design. The middle section covers SQL in depth, from basic filtering and joins through subqueries, CTEs, window functions, and transaction management. The course closes with indexing and performance analysis, database administration, and a final project in which you design and build a complete database for a domain of your choosing.
Module 1
Introduction to Databases and the Relational Model
3 lessons
▾
Module 2
Getting Started with PostgreSQL
3 lessons
▾
Module 3
Data Types and Table Basics
3 lessons
▾
Module 4
Keys and Relationships
3 lessons
▾
Module 5
Database Design Fundamentals
3 lessons
▾
Module 6
Normalization
3 lessons
▾
Module 7
Querying Data with SQL
3 lessons
▾
Module 8
Advanced Querying
3 lessons
▾
Module 9
Modifying Data and Transactions
3 lessons
▾
Module 10
Indexes and Performance
3 lessons
▾
Module 11
Backup, Security, and Administration
3 lessons
▾
Module 12
Final Project
3 lessons
▾
Learn by doing
Every module includes hands-on labs you complete in a real PostgreSQL environment using psql, GCC-compatible tooling, and standard Linux or desktop terminals. You'll work through 23 structured exercises — from creating your first table and modeling foreign key relationships to analyzing query plans with EXPLAIN ANALYZE and backing up a database with pg_dump.
- Install PostgreSQL on your operating system
- Confirm the PostgreSQL server is running and accessible
- Connect to a PostgreSQL database using the psql command-line client
- Run basic psql meta-commands to explore the server
- Create a new database and a custom schema using psql
- Verify the schema appears using psql meta-commands
- Use psql to inspect databases, roles, and configuration settings
- Connect to PostgreSQL using a GUI client such as pgAdmin
- Write CREATE TABLE statements using appropriate PostgreSQL data types
- Verify table structure using psql's \d command
- Add NOT NULL, UNIQUE, and CHECK constraints to an existing table
- Test the constraints by attempting invalid inserts
- Create related tables linked by primary and foreign keys
- Insert data that respects referential integrity
- Design and create a junction table to model a many-to-many relationship
- Query data across the junction table to verify the relationship works
- Design an ERD for a simple business scenario, identifying entities, attributes, and relationships
- Define cardinality for each relationship in the diagram
- Translate an ERD into a working PostgreSQL schema using CREATE TABLE statements
- Verify the implemented schema matches the original design
- Identify anomalies in an unnormalized table design
- Refactor the table into 1NF, 2NF, and 3NF
- Build both a normalized and a denormalized version of the same dataset
- Compare query complexity and performance between the two designs
- Write SELECT queries with filtering, sorting, and limiting
- Use aggregate functions to summarize data
- Write queries that join two or more related tables
- Compare results of INNER JOIN vs LEFT JOIN on the same tables
- Use CTEs to break a complex query into readable steps
- Produce a summary report from multiple related tables
- Use ROW_NUMBER or RANK to rank rows within groups
- Calculate a running total using a window function
- Write INSERT, UPDATE, and DELETE statements against a sample database
- Use RETURNING to confirm the result of a modification
- Wrap multiple statements in a transaction and commit or roll back based on the outcome
- Simulate a failed transaction and confirm the rollback restores the original state
- Create an index on a frequently queried column
- Compare query performance before and after adding the index
- Use EXPLAIN ANALYZE to inspect the execution plan of a query
- Identify a sequential scan and determine whether an index would help
- Create roles with specific privileges on a database and its tables
- Test that privilege restrictions behave as expected
- Back up a database using pg_dump
- Restore the database from the backup using pg_restore and verify the data matches
- Design, normalize, and implement a complete PostgreSQL database for a chosen domain
- Write and document a set of queries that demonstrate the database answers real business questions
Destin Learning
Destin Learning creates practical, project-driven courses for developers, IT professionals, and tech enthusiasts who want to build real skills — not just pass a certification exam.
Every course is built around hands-on labs, working code, and real-world tools. Our students learn by doing: writing programs, configuring systems, and solving problems in environments that mirror what they'll encounter on the job.
Our courses cover systems programming, Linux administration, cloud infrastructure, AI and machine learning, and foundational computer science — all taught with the same philosophy: understand it deeply, build it yourself, and know why it works.
Destin Learning · destinlearning.com
Start Building Real Databases Today
Contact us for pricing and gain access to self-paced video lessons, 23 hands-on labs, and a guided final project.
Have a coupon code? Redeem it here