Databases Lesson 2: SQL Basics

SQL (Structured Query Language) is the language of relational databases. Master these 4 operations and you can work with any SQL database.

CREATE & INSERT

-- Create a table
CREATE TABLE users (
  id SERIAL PRIMARY KEY,  -- auto-increment in PostgreSQL
  name VARCHAR(100) NOT NULL,
  email VARCHAR(200) UNIQUE NOT NULL,
  age INTEGER CHECK (age >= 0),
  role VARCHAR(20) DEFAULT 'user',
  created_at TIMESTAMP DEFAULT NOW()
);

-- Insert rows
INSERT INTO users (name, email, age) VALUES
  ('Alice', 'alice@example.com', 30),
  ('Bob', 'bob@example.com', 25);

SELECT, WHERE, ORDER BY

-- Select all
SELECT * FROM users;

-- Select specific columns
SELECT name, email, age FROM users;

-- Filter with WHERE
SELECT * FROM users WHERE age > 25;
SELECT * FROM users WHERE role = 'admin' AND age < 40;
SELECT * FROM users WHERE name LIKE 'A%';  -- starts with A
SELECT * FROM users WHERE age IN (25, 30, 35);

-- Sort and limit
SELECT * FROM users ORDER BY age DESC LIMIT 10 OFFSET 20;

-- Aggregations
SELECT COUNT(*) FROM users;
SELECT AVG(age), MAX(age), MIN(age) FROM users;
SELECT role, COUNT(*) as count FROM users GROUP BY role;

UPDATE & DELETE

-- ALWAYS use WHERE with UPDATE and DELETE!
UPDATE users SET role = 'admin' WHERE email = 'alice@example.com';
UPDATE users SET age = age + 1 WHERE id = 5;

-- Delete specific rows
DELETE FROM users WHERE role = 'inactive';

-- CAREFUL: this deletes EVERYTHING
-- DELETE FROM users;  -- NO WHERE clause