🗃️

CREATE TABLE users (
  id INTEGER PRIMARY KEY AUTOINCREMENT,
  name TEXT NOT NULL,
  email TEXT UNIQUE,
  age INTEGER DEFAULT 0,
  created_at DATETIME DEFAULT CURRENT_TIMESTAMP
);

CREATE TABLE IF NOT EXISTS users (
  id INTEGER PRIMARY KEY,
  name TEXT
);

CREATE TABLE posts (
  id INTEGER PRIMARY KEY,
  user_id INTEGER NOT NULL,
  title TEXT,
  FOREIGN KEY (user_id) REFERENCES users(id)
    ON DELETE CASCADE
);

CREATE TABLE user_roles (
  user_id INTEGER,
  role_id INTEGER,
  PRIMARY KEY (user_id, role_id)
);

ALTER TABLE users ADD COLUMN phone TEXT;

ALTER TABLE users RENAME TO customers;

ALTER TABLE users RENAME COLUMN name TO full_name;

DROP TABLE IF EXISTS users;

CREATE INDEX idx_users_email ON users(email);
CREATE UNIQUE INDEX idx_users_name ON users(name);

DROP INDEX idx_users_email;

INSERT INTO users (name, email) VALUES ('John', 'john@example.com');

INSERT INTO users (name, email) VALUES
  ('Alice', 'alice@example.com'),
  ('Bob', 'bob@example.com');

INSERT OR REPLACE INTO users (id, name, email)
VALUES (1, 'John', 'john@new.com');

INSERT OR IGNORE INTO users (name, email)
VALUES ('John', 'john@example.com');

INSERT INTO users (name, email) VALUES ('John', 'john@example.com')
ON CONFLICT(email) DO UPDATE SET name = excluded.name;

UPDATE users SET name = 'Jane' WHERE id = 1;

UPDATE users SET age = age + 1 WHERE age < 30;

DELETE FROM users WHERE id = 1;

DELETE FROM users;
-- or faster:
DELETE FROM users WHERE 1=1;

SELECT * FROM users;

SELECT name, email FROM users;

SELECT name AS full_name, email AS contact FROM users;

SELECT DISTINCT city FROM users;

SELECT * FROM users WHERE age > 18;
SELECT * FROM users WHERE name LIKE 'J%';
SELECT * FROM users WHERE email IS NOT NULL;

SELECT * FROM users ORDER BY name ASC;
SELECT * FROM users ORDER BY age DESC, name ASC;

SELECT * FROM users LIMIT 10;
SELECT * FROM users LIMIT 10 OFFSET 20;

SELECT COUNT(*) FROM users;
SELECT COUNT(DISTINCT city) FROM users;

SELECT SUM(amount) FROM orders;
SELECT AVG(age) FROM users;

SELECT MIN(age), MAX(age) FROM users;

SELECT city, COUNT(*) as count
FROM users
GROUP BY city;

SELECT city, COUNT(*) as count
FROM users
GROUP BY city
HAVING count > 5;

SELECT users.name, posts.title
FROM users
INNER JOIN posts ON users.id = posts.user_id;

SELECT users.name, posts.title
FROM users
LEFT JOIN posts ON users.id = posts.user_id;

SELECT * FROM colors CROSS JOIN sizes;

SELECT e.name, m.name as manager
FROM employees e
LEFT JOIN employees m ON e.manager_id = m.id;

SELECT LENGTH(name) FROM users;
SELECT UPPER(name), LOWER(name) FROM users;
SELECT SUBSTR(name, 1, 3) FROM users;
SELECT REPLACE(name, 'a', 'b') FROM users;
SELECT TRIM(name) FROM users;
SELECT name || ' ' || email FROM users;  -- Concat

SELECT DATE('now');
SELECT TIME('now');
SELECT DATETIME('now');
SELECT DATE('now', '+1 day');
SELECT DATE('now', '-1 month');
SELECT STRFTIME('%Y-%m-%d', created_at) FROM users;

SELECT ABS(-5);
SELECT ROUND(3.14159, 2);
SELECT RANDOM();
SELECT MAX(10, 20);
SELECT MIN(10, 20);

SELECT COALESCE(email, 'no email') FROM users;
SELECT IFNULL(email, 'no email') FROM users;
SELECT NULLIF(a, b);  -- Returns NULL if a = b

SELECT name,
  CASE
    WHEN age < 18 THEN 'minor'
    WHEN age < 65 THEN 'adult'
    ELSE 'senior'
  END as age_group
FROM users;

CREATE VIEW active_users AS
SELECT * FROM users WHERE active = 1;

DROP VIEW active_users;

CREATE TRIGGER update_timestamp
AFTER UPDATE ON users
BEGIN
  UPDATE users SET updated_at = DATETIME('now')
  WHERE id = NEW.id;
END;

BEGIN TRANSACTION;
INSERT INTO users (name) VALUES ('John');
INSERT INTO users (name) VALUES ('Jane');
COMMIT;
-- or ROLLBACK;

SELECT * FROM users
WHERE id IN (
  SELECT user_id FROM posts
  WHERE created_at > DATE('now', '-7 days')
);

WITH recent_users AS (
  SELECT * FROM users
  WHERE created_at > DATE('now', '-30 days')
)
SELECT * FROM recent_users
WHERE age > 18;

SELECT json_extract(data, '$.name') FROM users;
SELECT * FROM users WHERE json_extract(data, '$.active') = 1;

EXPLAIN QUERY PLAN SELECT * FROM users WHERE email = 'a@b.com';

ANALYZE;

VACUUM;

PRAGMA table_info(users);
PRAGMA foreign_keys = ON;
PRAGMA journal_mode = WAL;
PRAGMA cache_size = 10000;