SQL

Explanation

SQL operates on tables (relations) composed of rows and columns. Every query returns a result set. The four core operations are: SELECT (retrieve data, with filtering via WHERE, sorting via ORDER BY, grouping via GROUP BY), INSERT (add new rows), UPDATE (modify existing rows), and DELETE (remove rows). These are called DML (Data Manipulation Language). DDL (Data Definition Language) defines schema: CREATE TABLE, ALTER TABLE, DROP TABLE. SQL's power comes from: JOINs (combine data from multiple tables based on related columns — INNER JOIN, LEFT JOIN, RIGHT JOIN, FULL OUTER JOIN), aggregates (COUNT, SUM, AVG, MIN, MAX with GROUP BY and HAVING), subqueries (queries nested inside queries), and window functions (aggregate over a subset of rows with OVER (PARTITION BY... ORDER BY...)). Indexes dramatically speed up queries by creating sorted auxiliary structures. A query on an unindexed column requires a full table scan (O(n)). With an index, the database uses a B-tree for O(log n) lookups. The trade-off: indexes speed up reads but slow down writes (the index must be updated on every INSERT/UPDATE/DELETE). SQL dialects: PostgreSQL (open source, most feature-rich, best for new projects), MySQL/MariaDB (widely deployed, solid for web apps), SQLite (serverless, file-based, excellent for development/testing and embedded apps), Microsoft SQL Server, and Oracle. The core SQL syntax is standard; each dialect adds its own extensions.

Code Example

-- SQL fundamentals

-- SELECT with filtering, sorting, limiting
SELECT id, name, email, created_at
FROM users
WHERE active = true AND created_at > '2024-01-01'
ORDER BY created_at DESC
LIMIT 10;

-- INNER JOIN: users with their order count
SELECT u.name, COUNT(o.id) AS order_count, SUM(o.total) AS revenue
FROM users u
INNER JOIN orders o ON u.id = o.user_id
WHERE o.status = 'completed'
GROUP BY u.id, u.name
HAVING COUNT(o.id) > 5
ORDER BY revenue DESC;

-- LEFT JOIN: users even if they have no orders
SELECT u.name, COALESCE(COUNT(o.id), 0) AS order_count
FROM users u
LEFT JOIN orders o ON u.id = o.user_id
GROUP BY u.id, u.name;

-- Window function: rank users by revenue within each region
SELECT name, region, revenue,
  RANK() OVER (PARTITION BY region ORDER BY revenue DESC) AS rank
FROM user_stats;

-- Parameterized query (prevent SQL injection)
-- In Node.js with pg:
-- await db.query('SELECT * FROM users WHERE id = $1', [userId]);
-- In MySQL: 'SELECT * FROM users WHERE id = ?', [userId]

Why It Matters for Engineers

SQL is the language of data. Every major application stores data in a relational database, and SQL is how you query it. Knowing SQL means you can understand and optimize your application's database queries, diagnose slow queries with EXPLAIN, write migrations, and answer data questions without waiting for a data analyst. SQL also demystifies what ORMs do: Sequelize, TypeORM, Prisma — they generate SQL. Knowing SQL means you can read the generated queries, add missing indexes, and optimize the queries your ORM produces.