SQL from Zero to Confident

SQL becomes much easier when you stop thinking of it as a language to memorize and start seeing it as a data pipeline:

FROM → WHERE → GROUP BY → HAVING → SELECT → ORDER BY → LIMIT        

This guide walks through SQL from fundamentals to advanced usage, with copy-paste CLI examples you can run locally.

Core SQL Mental Model

At its heart, SQL answers one question: “What data do I want, and how should it be shaped?”

Minimal Query

SELECT * FROM users;        

Filter Data

SELECT name, email
FROM users
WHERE active = true;        

Sort Results

SELECT name, created_at
FROM users
ORDER BY created_at DESC;        

Limit Output

SELECT *
FROM logs
LIMIT 10;        

CLI Example (SQLite)

sqlite3 app.db <<EOF
CREATE TABLE users (
  id INTEGER PRIMARY KEY,
  name TEXT,
  email TEXT,
  active BOOLEAN
);

INSERT INTO users (name, email, active)
VALUES
('Alice', 'alice@example.com', 1),
('Bob', 'bob@example.com', 0);

SELECT * FROM users;
EOF        

Filtering and Conditions

WHERE Clauses

SELECT *
FROM orders
WHERE amount > 100;        

Multiple Conditions

SELECT *
FROM orders
WHERE amount > 100 AND status = 'completed';        

Pattern Matching

SELECT *
FROM users
WHERE email LIKE '%@gmail.com';        

CLI Example (Filtering Logs)

sqlite3 app.db <<EOF
SELECT *
FROM logs
WHERE level = 'ERROR'
ORDER BY timestamp DESC
LIMIT 20;
EOF        

Aggregation and Grouping

Aggregation transforms raw data into insights.

Common Functions

• COUNT()
• SUM()
• AVG()
• MIN()
• MAX()

Example

SELECT status, COUNT(*) as total
FROM orders
GROUP BY status;        

HAVING vs WHERE

• WHERE filters rows before grouping
• HAVING filters groups after aggregation

SELECT status, COUNT(*) as total
FROM orders
GROUP BY status
HAVING total > 10;        

CLI Example (Metrics from Logs)

sqlite3 app.db <<EOF
SELECT level, COUNT(*) as count
FROM logs
GROUP BY level;
EOF        

Joins and Relationships

Joins connect data across tables.

INNER JOIN

SELECT users.name, orders.amount
FROM users
INNER JOIN orders ON users.id = orders.user_id;        

LEFT JOIN

SELECT users.name, orders.amount
FROM users
LEFT JOIN orders ON users.id = orders.user_id;        

FULL JOIN (if supported)

Returns everything from both tables.

CLI Example (Relational Data)

sqlite3 app.db <<EOF
CREATE TABLE orders (
  id INTEGER PRIMARY KEY,
  user_id INTEGER,
  amount REAL
);

SELECT u.name, o.amount
FROM users u
JOIN orders o ON u.id = o.user_id;
EOF        

Subqueries and CTEs

Subquery

SELECT *
FROM orders
WHERE amount > (
  SELECT AVG(amount) FROM orders
);        

CTE (Common Table Expression)

WITH avg_order AS (
  SELECT AVG(amount) as avg_val FROM orders
)
SELECT *
FROM orders, avg_order
WHERE orders.amount > avg_val;        

CLI Example (Readable Queries)

sqlite3 app.db <<EOF
WITH error_logs AS (
  SELECT * FROM logs WHERE level = 'ERROR'
)
SELECT COUNT(*) FROM error_logs;
EOF        

Window Functions (Advanced and Useful)

Window functions let you compute values without collapsing rows.

Example: Ranking

SELECT
  name,
  amount,
  RANK() OVER (ORDER BY amount DESC) as rank
FROM orders;        

Running Total

SELECT
  id,
  amount,
  SUM(amount) OVER (ORDER BY id) as running_total
FROM orders;        

CLI Example (Ranking Users)

sqlite3 app.db <<EOF
SELECT
  user_id,
  COUNT(*) as total_orders,
  RANK() OVER (ORDER BY COUNT(*) DESC)
FROM orders
GROUP BY user_id;
EOF        

Indexing and Performance

Indexes make queries faster by avoiding full table scans.

Create Index

CREATE INDEX idx_users_email ON users(email);        

When to Use

Frequent WHERE filters, JOIN columns, and ORDER BY columns are key elements commonly used in query optimization and database operations.

CLI Example (Performance Check)

sqlite3 app.db <<EOF
EXPLAIN QUERY PLAN
SELECT * FROM users WHERE email = 'alice@example.com';
EOF        

Constraints and Data Integrity

Constraints enforce rules on your data.

Examples

CREATE TABLE accounts (
  id INTEGER PRIMARY KEY,
  email TEXT UNIQUE NOT NULL,
  balance REAL CHECK(balance >= 0)
);        

Data Manipulation (CRUD)

Insert

INSERT INTO users (name, email)
VALUES ('Charlie', 'charlie@example.com');        

Update

UPDATE users
SET active = 1
WHERE id = 2;        

Delete

DELETE FROM users
WHERE id = 2;        

CLI Example (Batch Operations)

sqlite3 app.db <<EOF
UPDATE users SET active = 1 WHERE active = 0;
DELETE FROM logs WHERE timestamp < '2023-01-01';
EOF        

Date and Time Handling

Current Timestamp

SELECT CURRENT_TIMESTAMP;        

Date Arithmetic

SELECT DATE('now', '+7 days');        

Differences

SELECT julianday('2025-01-01') - julianday('2024-01-01');        

Data Modeling Essentials

Normalization

• Reduce duplication
• Improve consistency

Denormalization

• Improve performance
• Accept duplication

Example

-- Normalized
users(id, name)
orders(id, user_id)

-- Denormalized
orders(id, user_name, amount)        

Real-World CLI Workflows (DevOps/SRE Focus)

Log Analysis Pipeline

sqlite3 logs.db <<EOF
CREATE TABLE logs (timestamp TEXT, level TEXT, message TEXT);
.mode csv
.import logs.csv logs
EOF        

sqlite3 logs.db <<EOF
SELECT level, COUNT(*) as count
FROM logs
GROUP BY level
ORDER BY count DESC;
EOF        

Incident Investigation

sqlite3 logs.db <<EOF
SELECT *
FROM logs
WHERE level = 'ERROR'
AND timestamp > datetime('now', '-1 hour');
EOF        

Cost / Usage Analysis

sqlite3 logs.db <<EOF
SELECT service, SUM(cost) as total_cost
FROM billing
GROUP BY service
ORDER BY total_cost DESC;
EOF        

Top N Queries

sqlite3 logs.db <<EOF
SELECT query, COUNT(*) as freq
FROM query_logs
GROUP BY query
ORDER BY freq DESC
LIMIT 10;
EOF        

Putting It All Together

A realistic production-style query:

WITH recent_errors AS (
  SELECT *
  FROM logs
  WHERE level = 'ERROR'
  AND timestamp > datetime('now', '-24 hours')
)
SELECT
  service,
  COUNT(*) as error_count
FROM recent_errors
GROUP BY service
HAVING error_count > 5
ORDER BY error_count DESC;        

Final Thoughts

SQL is best understood as a data transformation pipeline rather than just syntax, and learning its execution order—FROM, WHERE, GROUP, HAVING, SELECT, then ORDER—is essential; begin by mastering SELECT with WHERE, GROUP BY with aggregates, and JOINs, then progress to more advanced concepts such as CTEs, window functions, and index optimization.