SQL

SQL (Structured Query Language) is the standard language for managing and querying relational databases. Despite being decades old, it remains essential for anyone working with data.

Core Operations (CRUD)

-- Create
INSERT INTO users (name, email) VALUES ('Alice', 'alice@example.com');

-- Read
SELECT name, email FROM users WHERE active = true;

-- Update
UPDATE users SET email = 'new@example.com' WHERE id = 1;

-- Delete
DELETE FROM users WHERE id = 1;

Key Concepts

Joins

Combine data from multiple tables:

SELECT orders.id, users.name, orders.total
FROM orders
INNER JOIN users ON orders.user_id = users.id
WHERE orders.created_at > '2024-01-01';

Aggregations

Summarize data:

SELECT 
  category,
  COUNT(*) as count,
  SUM(amount) as total,
  AVG(amount) as average
FROM transactions
GROUP BY category
HAVING SUM(amount) > 1000;

Window Functions

Calculations across rows:

SELECT 
  name,
  department,
  salary,
  RANK() OVER (PARTITION BY department ORDER BY salary DESC) as dept_rank
FROM employees;

Database Dialects

Database	Notable Differences
PostgreSQL	Rich types, CTEs, JSONB
MySQL	LIMIT syntax, different string functions
SQL Server	TOP instead of LIMIT, T-SQL extensions
SQLite	Limited types, no right join
Oracle	ROWNUM, PL/SQL

What We Like

• Declarative: Describe what you want, not how to get it
• Universal: Works across most databases
• Powerful: Complex queries in few lines
• Optimised: Database engines optimise execution
• Mature: Decades of tooling and knowledge

What We Don't Like

• Verbose: Simple operations require boilerplate
• Impedance mismatch: Doesn't map cleanly to objects
• Vendor variations: Each database has quirks
• Limited abstraction: Hard to compose and reuse

Best Practices

1. Use parameterized queries: Never concatenate user input (SQL injection)
2. Index strategically: Analyse query patterns
3. Normalize thoughtfully: Balance integrity with performance
4. Use CTEs: Common Table Expressions improve readability
5. EXPLAIN your queries: Understand execution plans