How to Write Your First SQL Queries

-- Get all columns for all users
SELECT * FROM users;

-- Get only specific columns
SELECT name, email FROM users;

-- Filter with WHERE
SELECT name, email FROM users WHERE role = 'student';

-- Multiple conditions
SELECT name FROM users
WHERE role = 'student' AND created_at > '2024-02-01';

-- Sort results
SELECT name, created_at FROM users ORDER BY created_at DESC;

-- Limit the number of results
SELECT name FROM users ORDER BY created_at DESC LIMIT 10;

-- Search with pattern matching
SELECT name FROM users WHERE email LIKE '%@example.com';

SELECT columns
FROM table
WHERE conditions
ORDER BY column ASC|DESC
LIMIT count
OFFSET skip;

-- Insert a single row
INSERT INTO users (name, email, role)
VALUES ('Dave', 'dave@example.com', 'student');

-- Insert multiple rows
INSERT INTO users (name, email, role)
VALUES
  ('Eve', 'eve@example.com', 'student'),
  ('Frank', 'frank@example.com', 'instructor');

-- Update specific rows (always use WHERE!)
UPDATE users
SET role = 'instructor'
WHERE email = 'alice@example.com';

-- Delete specific rows (always use WHERE!)
DELETE FROM users
WHERE id = 3;

-- This deletes ALL users. Do not do this.
DELETE FROM users;

-- This is what you meant:
DELETE FROM users WHERE id = 3;

-- Check first
SELECT * FROM users WHERE role = 'student';

-- Then delete if the results look correct
DELETE FROM users WHERE role = 'student';

-- Get lessons with instructor names
SELECT lessons.title, users.name AS instructor_name
FROM lessons
JOIN users ON lessons.instructor_id = users.id;

-- INNER JOIN: only rows that match in both tables
SELECT lessons.title, users.name
FROM lessons
JOIN users ON lessons.instructor_id = users.id;

-- LEFT JOIN: all rows from the left table, even without a match
SELECT users.name, lessons.title
FROM users
LEFT JOIN lessons ON users.id = lessons.instructor_id;

-- Count lessons per instructor
SELECT users.name, COUNT(lessons.id) AS lesson_count
FROM users
LEFT JOIN lessons ON users.id = lessons.instructor_id
GROUP BY users.name;

-- COUNT: how many rows
SELECT role, COUNT(*) AS user_count
FROM users
GROUP BY role;

-- SUM: total of a numeric column
SELECT instructor_id, SUM(duration_minutes) AS total_minutes
FROM lessons
GROUP BY instructor_id;

-- AVG: average value
SELECT instructor_id, AVG(duration_minutes) AS avg_duration
FROM lessons
GROUP BY instructor_id;

-- MIN and MAX
SELECT MIN(created_at) AS first_signup, MAX(created_at) AS latest_signup
FROM users;

-- Instructors with more than 5 lessons
SELECT instructor_id, COUNT(*) AS lesson_count
FROM lessons
GROUP BY instructor_id
HAVING COUNT(*) > 5;

-- Page 1 (items 1-20)
SELECT * FROM lessons ORDER BY created_at DESC LIMIT 20 OFFSET 0;

-- Page 2 (items 21-40)
SELECT * FROM lessons ORDER BY created_at DESC LIMIT 20 OFFSET 20;

-- Page N
-- OFFSET = (page_number - 1) * page_size

SELECT EXISTS(
  SELECT 1 FROM users WHERE email = 'alice@example.com'
);

-- PostgreSQL syntax
INSERT INTO user_progress (user_id, lesson_id, percent_complete)
VALUES ('abc', 'lesson-1', 75)
ON CONFLICT (user_id, lesson_id)
DO UPDATE SET percent_complete = EXCLUDED.percent_complete;

SELECT title, ts_rank(to_tsvector(content), query) AS rank
FROM lessons, to_tsquery('javascript & async') AS query
WHERE to_tsvector(content) @@ query
ORDER BY rank DESC;

-- Find users who have completed at least one lesson
SELECT name FROM users
WHERE id IN (
  SELECT DISTINCT user_id FROM progress WHERE percent_complete = 100
);