Databases•25 min read•Beginner
SQL Basics
SELECT, INSERT, UPDATE, DELETE — the four CRUD operations and the grammar around them.
Setting up some data
CREATE TABLE users (
id INTEGER PRIMARY KEY,
name TEXT NOT NULL,
email TEXT UNIQUE,
active BOOLEAN DEFAULT TRUE
);
INSERT INTO users (id, name, email) VALUES
(1, 'Ada', 'ada@example.com'),
(2, 'Bob', 'bob@example.com'),
(3, 'Cylene', 'cy@example.com');SELECT
-- All columns, all rows
SELECT * FROM users;
-- Specific columns, with a filter
SELECT name, email FROM users WHERE active = TRUE;
-- Sort and limit
SELECT * FROM users ORDER BY name DESC LIMIT 5;
-- Aggregation
SELECT COUNT(*) AS total, COUNT(DISTINCT email) AS unique_emails FROM users;INSERT, UPDATE, DELETE
-- Add a row
INSERT INTO users (id, name, email) VALUES (4, 'Dee', 'dee@example.com');
-- Modify rows
UPDATE users SET active = FALSE WHERE id = 2;
-- Remove rows
DELETE FROM users WHERE active = FALSE;⚠ Watch out
ALWAYS write a WHERE clause on UPDATE and DELETE. `DELETE FROM users` (no WHERE) deletes every row. Run inside a transaction (BEGIN / ROLLBACK) when uncertain.
GROUP BY
-- Count posts per user
SELECT user_id, COUNT(*) AS post_count
FROM posts
GROUP BY user_id
ORDER BY post_count DESC;