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_id	customer_name	email	country
1	John Smith	[email protected]	USA
2	Maria Garcia	[email protected]	Spain
3	Yuki Tanaka	[email protected]	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_id	customer_id	order_date	total
101	1	2026-01-15	299.99
102	1	2026-01-20	149.50
103	2	2026-01-22	89.00

Here, 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! 💪