SQL Basics — Your First Queries

SQL (Structured Query Language) is the language of relational databases. You use it to create tables, insert data, and retrieve exactly what you need. It's declarative — you describe what you want, not how to get it.

Data Types

Every column has a type. Common ones:

Type	Use for	Example
INTEGER / INT	Whole numbers	42, -7
DECIMAL(p,s)	Exact decimals	99.99 (price)
VARCHAR(n)	Variable-length strings	'Alice'
TEXT	Unlimited strings	Long descriptions
DATE	Calendar date	'2026-05-29'
TIMESTAMP	Date + time	'2026-05-29 14:30:00'
BOOLEAN	True/False	TRUE, FALSE

Creating Tables (DDL)

DDL (Data Definition Language) defines the structure of your data.

Constraints:

• PRIMARY KEY — unique, not null; used to identify rows
• NOT NULL — column must have a value
• UNIQUE — no duplicate values in this column
• DEFAULT — value used if not specified on insert
• REFERENCES — foreign key; must match a value in the referenced table
• ON DELETE CASCADE — deleting the parent automatically deletes child rows

Inserting Data (DML)

DML (Data Manipulation Language) adds, changes, and removes data.

Querying Data — SELECT

Comparison Operators

Operator	Meaning
=	Equal
<> or !=	Not equal
<, >, <=, >=	Comparison
BETWEEN a AND b	Inclusive range
IN (v1, v2, ...)	Matches any value in list
IS NULL / IS NOT NULL	Null check
LIKE 'pattern%'	String pattern (% = any chars, _ = one char)

Updating and Deleting

Always use WHERE with UPDATE and DELETE — forgetting it modifies or removes every row.

Aggregate Functions

GROUP BY

Altering Tables

Python: sqlite3

Always use parameterised queries (? placeholders) — never format values directly into SQL strings. Direct interpolation is the cause of SQL injection attacks.

Key Takeaways

• DDL (CREATE, ALTER, DROP) defines structure; DML (INSERT, UPDATE, DELETE, SELECT) works with data
• WHERE filters rows; ORDER BY sorts; LIMIT/OFFSET paginates
• GROUP BY aggregates groups of rows; HAVING filters those groups
• Always use parameterised queries — never concatenate user input into SQL
• Constraints (NOT NULL, UNIQUE, FOREIGN KEY) enforce data integrity at the database level