---
title: "SQL Fundamentals – Chapter 1: Introduction to SQL (Theory)"
id: "2369"
type: "post"
slug: "sql-fundamentals-chapter-1-introduction-to-sql-theory"
published_at: "2026-02-14T16:50:46+00:00"
modified_at: "2026-02-14T16:50:46+00:00"
url: "https://labs.postgreshelp.com/sql-fundamentals-chapter-1-introduction-to-sql-theory/"
markdown_url: "https://labs.postgreshelp.com/sql-fundamentals-chapter-1-introduction-to-sql-theory.md"
excerpt: "Introduction to SQL and Relational Databases What is SQL? SQL (Structured Query Language) is the standard language for interacting with […]"
taxonomy_category:
  - "Uncategorized"
---

# Introduction to SQL and Relational Databases

## What is SQL?

**SQL (Structured Query Language)** is the standard language for interacting with relational database management systems (RDBMS). It allows you to:

- **Query** data (retrieve information)
- **Manipulate** data (insert, update, delete)
- **Define** data structures (create tables, indexes)
- **Control** access (grant permissions, manage users)

### SQL is Declarative

Unlike imperative programming languages (Python, Java), SQL is **declarative**:

```
-- You tell the database WHAT you want, not HOW to get it
SELECT customer_name, total_orders
FROM customers
WHERE country = 'USA'
ORDER BY total_orders DESC;
```

The database optimizer figures out the most efficient way to execute your query.

## Relational Database Fundamentals

### What is a Relational Database?

A relational database organizes data into **tables** (also called relations) with:

- **Rows** (tuples/records): Individual data entries
- **Columns** (attributes/fields): Properties of the data

**Example: Customers Table**

customer_idcustomer_nameemailcountry1John Smith[[email protected]](/cdn-cgi/l/email-protection)
USA2Maria Garcia[[email protected]](/cdn-cgi/l/email-protection)
Spain3Yuki Tanaka[[email protected]](/cdn-cgi/l/email-protection)
Japan### Key Concepts

#### 1. Primary Key

- Unique identifier for each row
- Cannot be NULL
- Example: `customer_id` in the table above

#### 2. Foreign Key

- References a primary key in another table
- Establishes relationships between tables

**Example: Orders Table**

order_idcustomer_idorder_datetotal10112026-01-15299.9910212026-01-20149.5010322026-01-2289.00Here, `customer_id` is a foreign key referencing the Customers table.

#### 3. Relationships

**One-to-Many:** One customer can have many orders  
 **Many-to-Many:** One order can have many products, and one product can be in many orders (requires junction table)  
 **One-to-One:** One user has one profile

## PostgreSQL: Why We Use It

PostgreSQL is an open-source, enterprise-grade RDBMS with:

✓ **ACID Compliance:** Ensures data integrity  
 ✓ **Advanced Features:** JSON support, full-text search, geospatial data  
 ✓ **Extensibility:** Custom functions, data types, operators  
 ✓ **Performance:** Handles millions of rows efficiently  
 ✓ **Standards Compliant:** Follows SQL standards closely  
 ✓ **Free & Open Source:** No licensing costs

### PostgreSQL Architecture (Simplified)

```
[Application]
  ↓
[PostgreSQL Server]
  ↓
[Database Cluster]
  ├── Database: mydb
  │   ├── Schema: public
  │   │   ├── Tables
  │   │   ├── Views
  │   │   ├── Functions
  │   │   └── Indexes
  │   └── Schema: sales
  └── Database: testdb
```

**Key Terms:**

- **Cluster:** Collection of databases managed by a single PostgreSQL server
- **Database:** Isolated collection of schemas
- **Schema:** Namespace for organizing tables, views, etc. (default: `public`)
- **Table:** Where actual data is stored

## SQL Command Categories

SQL commands are divided into 5 categories:

### 1. DDL (Data Definition Language)

Define database structure

```
CREATE TABLE employees (
    employee_id SERIAL PRIMARY KEY,
    name VARCHAR(100),
    hire_date DATE
);

ALTER TABLE employees ADD COLUMN salary NUMERIC(10,2);

DROP TABLE employees;
```

### 2. DML (Data Manipulation Language)

Modify data

```
INSERT INTO employees (name, hire_date) VALUES ('John Doe', '2026-01-15');

UPDATE employees SET salary = 75000 WHERE employee_id = 1;

DELETE FROM employees WHERE employee_id = 1;
```

### 3. DQL (Data Query Language)

Retrieve data

```
SELECT name, hire_date FROM employees WHERE salary > 50000;
```

### 4. DCL (Data Control Language)

Manage permissions

```
GRANT SELECT ON employees TO analyst_role;

REVOKE INSERT ON employees FROM intern_role;
```

### 5. TCL (Transaction Control Language)

Manage transactions

```
BEGIN;
UPDATE accounts SET balance = balance - 100 WHERE account_id = 1;
UPDATE accounts SET balance = balance + 100 WHERE account_id = 2;
COMMIT;
```

## Your First SQL Queries

Let’s start with the most fundamental command: **SELECT**

### Basic SELECT Syntax

```
SELECT column1, column2, ...
FROM table_name;
```

### Example 1: Select All Columns

```
-- The asterisk (*) means "all columns"
SELECT * FROM customers;
```

### Example 2: Select Specific Columns

```
SELECT customer_name, email FROM customers;
```

### Example 3: Select with Calculation

```
SELECT
    customer_name,
    total_orders * 100 AS total_value
FROM customers;
```

The `AS` keyword creates an **alias** (temporary name) for the calculated column.

## PostgreSQL-Specific Features

PostgreSQL extends standard SQL with powerful features:

### 1. RETURNING Clause

```
INSERT INTO customers (customer_name, email, country)
VALUES ('Alice Brown', '[email protected]', 'Canada')
RETURNING customer_id, customer_name;
```

Returns the inserted row(s) immediately—no need for a separate SELECT.

### 2. Dollar-Quoted Strings

```
-- Instead of escaping quotes
SELECT 'It''s a nice day';

-- Use dollar quoting (PostgreSQL-specific)
SELECT $$It's a nice day$$;
```

### 3. Array Types

```
SELECT ARRAY[1, 2, 3, 4, 5];
SELECT ARRAY['PostgreSQL', 'MySQL', 'Oracle'];
```

### 4. JSON/JSONB Support

```
SELECT '{"name": "John", "age": 30}'::jsonb;
```

We’ll explore these in depth in later chapters.

## SQL Syntax Rules

### 1. SQL is Case-Insensitive (Mostly)

```
SELECT * FROM customers;  -- Valid
select * from customers;  -- Also valid
SeLeCt * FrOm CuStOmErS;  -- Valid but ugly
```

**Best Practice:** Use UPPERCASE for SQL keywords, lowercase for table/column names

```
SELECT customer_name FROM customers;  -- Recommended style
```

**Exception:** Quoted identifiers ARE case-sensitive

```
CREATE TABLE "Customers" (id INT);  -- Different from customers
```

### 2. Statements End with Semicolon

```
SELECT * FROM customers;  -- Semicolon required (in scripts)
```

In `psql` interactive mode, you can skip the semicolon but the query won’t execute until you type it.

### 3. Whitespace Doesn’t Matter (Mostly)

```
-- All equivalent:
SELECT name FROM customers;

SELECT name
FROM customers;

SELECT
    name
FROM
    customers;
```

**Best Practice:** Use formatting for readability

### 4. Comments

```
-- Single-line comment

/*
Multi-line
comment
*/

SELECT name FROM customers;  -- Inline comment
```

## Setting Up Your Environment

### Connect to PostgreSQL

```
# Using psql (command-line)
psql -U postgres -d mydb

# Using connection string
psql postgresql://username:password@localhost:5432/mydb
```

### Helpful psql Commands

```
l          -- List all databases
c mydb     -- Connect to database 'mydb'
dt         -- List tables in current database
d table    -- Describe table structure
q          -- Quit psql
```

### Create a Practice Database

```
CREATE DATABASE sql_fundamentals;

c sql_fundamentals

CREATE TABLE customers (
    customer_id SERIAL PRIMARY KEY,
    customer_name VARCHAR(100) NOT NULL,
    email VARCHAR(150) UNIQUE,
    country VARCHAR(50),
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP
);
```

## Common Beginner Mistakes

### ✗ Mistake 1: Forgetting WHERE Clause

```
-- DANGER: Updates ALL rows!
UPDATE customers SET country = 'USA';

-- Correct: Update specific rows
UPDATE customers SET country = 'USA' WHERE customer_id = 1;
```

### ✗ Mistake 2: Using = for NULL

```
-- WRONG: NULL comparisons don't work with =
SELECT * FROM customers WHERE email = NULL;

-- CORRECT: Use IS NULL
SELECT * FROM customers WHERE email IS NULL;
```

### ✗ Mistake 3: SQL Injection (Security!)

```
-- VULNERABLE to SQL injection
SELECT * FROM users WHERE username = '" + userInput + "';

-- SAFE: Use parameterized queries
-- (In application code, not raw SQL)
```

We’ll cover this in detail in later chapters.

## Best Practices from Day One

✓ **Always use WHERE with UPDATE/DELETE** (unless you really mean all rows)  
 ✓ **Use meaningful column names** (`customer_email` not `ce`)  
 ✓ **Add primary keys to all tables**  
 ✓ **Use NOT NULL for required fields**  
 ✓ **Format your SQL for readability**  
 ✓ **Test on sample data before production**

## Summary

In this chapter, you learned:

✓ What SQL is and why it’s important  
 ✓ Relational database fundamentals  
 ✓ PostgreSQL architecture basics  
 ✓ 5 categories of SQL commands  
 ✓ Basic SELECT syntax  
 ✓ PostgreSQL-specific features  
 ✓ SQL syntax rules and best practices  
 ✓ Common beginner mistakes to avoid

## Next Steps

In **Chapter 02: Data Manipulation Language (DML)**, you’ll learn:

- How to insert data into tables
- How to update and delete records
- Bulk operations with COPY
- PostgreSQL’s powerful RETURNING clause
- UPSERT with ON CONFLICT

**Ready for the Chapter 01 Labs? Let’s practice!** 💪