Introduction to SQL and PostgreSQL
Welcome to the SQL & PostgreSQL guide. This book aims to give you a foundational to advanced understanding of SQL (Structured Query Language), tightly coupled with relational database theory. We use PostgreSQL (often simply called Postgres) as our database of choice because it is robust, open-source, and highly standards-compliant.
What is SQL?
SQL is the standard language for communicating with Relational Database Management Systems (RDBMS). If you need to store, retrieve, update, or delete data stored in tables, you will use SQL.
SQL statements fall into several categories:
- DDL (Data Definition Language): Creating and modifying tables (
CREATE,ALTER,DROP). - DML (Data Manipulation Language): Modifying data (
INSERT,UPDATE,DELETE). - DQL (Data Query Language): Querying data (
SELECT). - DCL (Data Control Language): Managing permissions (
GRANT,REVOKE).
Why PostgreSQL?
PostgreSQL is a powerful, open-source object-relational database system with over 35 years of active development. It has earned a strong reputation for reliability, feature robustness, and performance.
NOTE
While SQL is a standard, every database system (PostgreSQL, MySQL, SQLite, Oracle) implements a slightly different "dialect". We will focus on standard SQL with specific PostgreSQL extensions where applicable.
Learning Path
This book emphasizes understanding the why alongside the how.
- We will start by understanding the Relational Model, keys, and normalization.
- We will translate that theory into Data Definition (DDL) to create our schemas.
- We will populate and interact with our data using Data Manipulation (DML).
- Finally, we will leverage Advanced Querying to extract deep insights from our data.
Let's start by understanding how data is fundamentally structured in a relational database.