SQL Fundamentals
Master the core SQL syntax including SELECT, INSERT, UPDATE, DELETE, JOINs, subqueries, and aggregations
SQL (Structured Query Language) is the standard language for managing and manipulating relational databases. This section covers the foundational concepts that apply across all major database platforms.
Cross-Platform
The SQL fundamentals covered here work across PostgreSQL, MySQL, and SQL Server with minimal syntax differences.
How SQL Queries Work
When you execute a SQL query, the database follows a specific order of operations—different from how you write the query:
Understanding this execution order helps you write better queries and debug issues:
| Order | Clause | Purpose |
|---|---|---|
| 1 | FROM / JOIN | Identify tables and combine rows |
| 2 | WHERE | Filter rows before grouping |
| 3 | GROUP BY | Group rows for aggregation |
| 4 | HAVING | Filter groups after aggregation |
| 5 | SELECT | Choose columns and compute expressions |
| 6 | DISTINCT | Remove duplicate rows |
| 7 | ORDER BY | Sort the result set |
| 8 | LIMIT / OFFSET | Restrict returned rows |
Common Mistake
You cannot use column aliases from SELECT in WHERE because WHERE is evaluated before SELECT.
Quick Navigation
SELECT Statements
Data retrieval, filtering, and sorting
JOINs
Combining data from multiple tables
Aggregations
GROUP BY, HAVING, and aggregate functions
Subqueries
Nested queries and CTEs
CTEs
Common Table Expressions and recursive queries
Window Functions
Analytics and ranking functions
Data Modification
INSERT, UPDATE, DELETE, UPSERT operations
SQL Statement Categories
Data Query Language - Retrieve data from the database
SELECT column1, column2
FROM table_name
WHERE condition
ORDER BY column1;DQL is the most commonly used category. The SELECT statement and all its clauses fall under DQL.
Data Manipulation Language - Modify data in tables
-- Insert new data
INSERT INTO users (name, email) VALUES ('John', 'john@example.com');
-- Update existing data
UPDATE users SET status = 'active' WHERE id = 1;
-- Delete data
DELETE FROM users WHERE status = 'inactive';Data Definition Language - Define database structure
-- Create table
CREATE TABLE users (
id SERIAL PRIMARY KEY,
name VARCHAR(100)
);
-- Modify table
ALTER TABLE users ADD COLUMN email VARCHAR(255);
-- Remove table
DROP TABLE users;Data Control Language - Control access permissions
-- Grant permissions
GRANT SELECT, INSERT ON users TO app_user;
-- Revoke permissions
REVOKE DELETE ON users FROM app_user;Sample Database Schema
Throughout this documentation, we'll use this sample e-commerce schema:
Learning Path
Start with SELECT
Learn how to retrieve data from single tables with filtering, sorting, and limiting results.
Use Window Functions
Perform calculations across related rows without grouping.
Quick Reference
Basic SELECT Template
SELECT
column1,
column2,
aggregate_function(column3) AS alias
FROM table1
JOIN table2 ON table1.id = table2.foreign_id
WHERE condition
GROUP BY column1, column2
HAVING aggregate_condition
ORDER BY column1 DESC
LIMIT 10 OFFSET 20;Common Operators
| Operator | Description | Example |
|---|---|---|
= | Equal to | WHERE status = 'active' |
<> or != | Not equal to | WHERE status <> 'deleted' |
>, <, >=, <= | Comparison | WHERE price > 100 |
BETWEEN | Range (inclusive) | WHERE price BETWEEN 10 AND 50 |
IN | Match any in list | WHERE status IN ('a', 'b') |
LIKE | Pattern matching | WHERE name LIKE 'John%' |
IS NULL | Check for NULL | WHERE phone IS NULL |
AND, OR | Logical operators | WHERE a = 1 AND b = 2 |
NOT | Negate condition | WHERE NOT status = 'deleted' |
Pattern Matching
| Pattern | Matches |
|---|---|
% | Any sequence of characters |
_ | Any single character |
LIKE 'a%' | Starts with 'a' |
LIKE '%a' | Ends with 'a' |
LIKE '%a%' | Contains 'a' |
LIKE 'a_b' | 'a' + any char + 'b' |