SQL Basics: CREATE, SELECT, UPDATE & DELETE

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.

What to Remember#

• 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