Database
Beginner 12 min read Database

SQL Fundamentals

SQL is the declarative language of relational databases. Learn the logical order of a SELECT, how JOINs connect tables, and how to filter, aggregate, sort and paginate results with confidence.

SQLSELECTJOINWHEREGROUP BYrelational database
Introduction

What is this?

SQL (Structured Query Language) is a declarative language for querying and manipulating data in relational databases. A query describes the shape of the result you want — which tables, which rows, which columns — and the database engine chooses how to compute it. SQL is split into DML (SELECT, INSERT, UPDATE, DELETE), DDL (CREATE, ALTER, DROP) and transaction control statements.

Motivation

Why do you need it?

  • It is the industry-standard interface to almost every transactional database, so one skill transfers across PostgreSQL, MySQL, SQL Server and SQLite.
  • Relational engines are heavily optimized: you write what you want, and the cost-based planner picks the fastest way to compute it.
  • Declarative queries compose — a WHERE filter, a JOIN and a GROUP BY can be reasoned about independently.
  • Queries are deterministic and inspectable: the same statement returns the same result on the same data.
  • Analysts, engineers and BI tools all speak SQL, making it the common language of data teams.
Think about it

Mental model

Imagine

Ordering from a menu

Think of

Writing a SQL query

You describe the dish you want, not the steps the kitchen takes to cook it. SQL works the same way — you declare the result set, and the database decides how to retrieve it.

Mechanics

How it works

  • FROM identifies the tables that supply the rows and how they connect with JOINs.
  • WHERE filters rows before any aggregation, using column values and operators.
  • GROUP BY collapses rows that share a key, and HAVING filters the resulting groups.
  • SELECT projects the columns or expressions you want in the output.
  • ORDER BY and LIMIT shape the final result, which is returned as a result set.
  • The optimizer rewrites your statement into a physical execution plan and runs it.

Logical order of operations in a SELECT query.

FROM orders JOIN users
WHERE o.status = 'paid'
GROUP BY u.email
HAVING count(*) > 1
SELECT u.email, sum(o.total)
ORDER BY total DESC LIMIT 10
Reference

Key commands

psql -d appdb

Connect to the appdb database with the psql interactive client.

SELECT version();

Confirm which database engine and version you are talking to.

PostgreSQL 16.3 on x86_64-pc-linux-gnu, compiled by gcc ...
\dt

List every table in the current schema.

public | users  | table | postgres\npublic | orders | table | postgres
\d users

Describe the users table: columns, types and constraints.

SELECT count(*) FROM orders;

Count rows quickly to sanity-check a table's size.

count\n-------\n 127463
\q

Quit the psql session.

Hands-on

Examples

Top 10 paid orders so far this year

Top 10 paid orders so far this year
SELECT u.email, o.total
FROM orders o
JOIN users u ON u.id = o.user_id
WHERE o.created_at >= '2024-01-01'
  AND o.status = 'paid'
ORDER BY o.total DESC
LIMIT 10;

The query joins orders to their owner, keeps only paid orders from 2024, sorts by value descending and returns the top ten rows. Note the logical order: FROM/JOIN runs first, then WHERE, then SELECT, then ORDER BY and finally LIMIT.

Real world

In production

  • Every API endpoint that reads or writes user data is backed by parameterized SQL statements.
  • Analytics dashboards run aggregation-heavy queries (GROUP BY, window functions) against replica or warehouse databases.
  • ETL jobs move and transform data with SELECT/INSERT statements between source and target systems.
  • ORM query builders and reporting tools ultimately emit SQL to the engine, so understanding it helps you debug them.
Guidelines

Best practices

  • Always use parameterized queries or prepared statements to prevent SQL injection — never interpolate user input into SQL strings.
  • Qualify column names with their table alias (u.email) in any query that touches more than one table.
  • Select only the columns you need instead of SELECT *, especially against wide or large tables.
  • Add LIMIT while developing so an accidental full scan does not hang your session.
  • Run exploratory queries against a staging replica, never against production.
Watch out

Common mistakes

  • Building filters with string concatenation instead of parameters, opening the door to SQL injection.
  • Forgetting that NULL comparisons with = never match — you must use IS NULL and IS NOT NULL.
  • Applying a WHERE filter to an aggregated value instead of using HAVING, which causes a syntax error or wrong results.
  • Using SELECT * in application code, which fetches unneeded columns and couples code to schema changes.
Root cause analysis

Common issues

Error
syntax error at or near "GROUP BY"
Root cause

A clause is misplaced or written out of SQL's logical order.

Fix

Write clauses in order: SELECT ... FROM ... WHERE ... GROUP BY ... HAVING ... ORDER BY ... LIMIT.

Error
column "u.email" must appear in the GROUP BY clause
Root cause

You selected a non-aggregated column that is not part of the GROUP BY key.

Fix

Add the column to GROUP BY or wrap it in an aggregate such as min(), max() or count().

Error
ERROR: missing FROM-clause entry for table "users"
Root cause

A table reference is missing or an alias is wrong.

Fix

Add the missing table to FROM or the JOIN, or correct the alias reference.

When things break

Debugging tips

  • Run EXPLAIN on a slow query to see whether it scans, joins and sorts the way you expect.
  • Compare SELECT count(*) with a filtered count to validate that your WHERE logic matches reality.
  • Comment out JOINs and filters one at a time to isolate which clause changes the row count.
  • Wrap destructive statements in BEGIN; ...; ROLLBACK; so a test can never persist.
Interviews

Interview checkpoint

Practice

Hands-on lab

Querying an Online Store
25 min · beginner
In progress
Objectives
  • Connect to PostgreSQL and inspect a schema
  • Write a SELECT with JOIN, WHERE and ORDER BY
  • Apply LIMIT and OFFSET for pagination
  • Use aggregates and validate results with EXPLAIN

Querying an Online Store

25 min · beginner

0/5 steps
0%
sandbox · Linux sandbox
$
Try these commands
Test yourself

Quick quiz

0/3
1

Which clause removes rows BEFORE grouping and aggregation happen?

2

SELECT * is always the most efficient way to read a table.

3

A query returns 1,000 rows but the page only shows 25. What should you add?

Quick reference

Cheat sheet

Core SELECT shape
SELECT <cols>Project output columns
FROM <t1> JOIN <t2> ON ...Source tables and join keys
WHERE <predicate>Filter rows
GROUP BY <keys> HAVING <pred>Aggregate and filter groups
ORDER BY <col> [DESC]Sort the result
LIMIT <n> OFFSET <m>Paginate
Filtering operators
= <> != > < >=Comparison operators
IS NULL / IS NOT NULLNULL-safe checks
IN (1, 2, 3)Membership test
BETWEEN a AND bInclusive range
LIKE 'A%'Pattern match
AND / OR / NOTCombine predicates
Go deeper

Official documentation

PostgreSQL SELECT reference
docs
W3Schools SQL Tutorial
docs
psql client reference
docs
SQLite Query Language
docs