Query real data with confidence — from SELECT basics to window functions, performance, and transactions.

SQL (Structured Query Language, usually pronounced “sequel” or spelled out letter by letter) is how you ask a relational database for data — filtering, combining, and summarizing rows stored in tables. It was first developed at IBM in the 1970s, and despite being one of the oldest languages still in everyday use, it remains the […]

SELECT * FROM students; — every column, every row * means “all columns.” It’s convenient while exploring a table you don’t know yet, but in real application code it’s considered bad practice — naming the exact columns you need makes a query faster, and protects your code from breaking silently if someone adds a new […]

ORDER BY SELECT name, score FROM students ORDER BY score DESC; — highest first SELECT name, score FROM students ORDER BY course ASC, score DESC; — sort by course, then score within each course ASC (ascending, low to high) is the default if you don’t specify a direction. You can sort by multiple columns at […]

Aggregate functions SELECT COUNT(*) FROM students; — total rows SELECT AVG(score) FROM students; — average score SELECT MAX(score), MIN(score) FROM students; SELECT SUM(score) FROM students; An aggregate function collapses many rows down into a single summary value. Used alone like this, they summarize the entire table at once — GROUP BY is what lets you […]

Real data almost never lives in a single table. Splitting related data across multiple tables — a practice called normalization — avoids repeating the same information over and over, and JOINs are how you bring that split data back together for a query. Say an enrollments table links student_id to course_id, a separate students table […]

A subquery inside WHERE SELECT name FROM students WHERE score > (SELECT AVG(score) FROM students); The inner query runs first, producing a single value (the class average), which the outer query then compares each row against. A subquery like this can return a single value, a single column of many values (for use with IN), […]

Primary and foreign keys A primary key uniquely identifies a row within its table (students.id) — no two rows can share the same primary key value, and most databases auto-generate it for you. A foreign key in one table points to a primary key in another (enrollments.student_id → students.id) — this is exactly what makes […]

A window function calculates across a set of rows related to the current row — without collapsing them into one summary row the way GROUP BY does. This is one of the most powerful, and most underused, features in SQL for anyone coming from a beginner-level background. Ranking rows SELECT name, course, score, RANK() OVER […]

Why queries get slow Without an index, the database has to scan every single row to find matches for your WHERE clause — this is called a full table scan, and it’s fine for a few hundred rows but becomes painfully slow once a table reaches millions of rows. Creating an index CREATE INDEX idx_students_course […]