SELECT 1 AS some_number,
	true AS is_true,
	'hello' AS greeting,
	3.1415 AS pi;

-- PRIMARY KEY is an alias for UNIQUE NOT NULL
CREATE TABLE IF NOT EXISTS users (
	id UINT PRIMARY KEY,
	name TEXT,
	email TEXT,
	password TEXT
);

INSERT INTO users VALUES
(1, 'John Doe', 'example@email.com', 'password'),
(2, 'Jane Doe', 'example2@email.com', 'password');

SELECT name, id FROM users;

SELECT * FROM users
WHERE id < 2;

SELECT * FROM users
WHERE id = 1
	OR name = 'Jane Doe';

-- PREWHERE forces an index search
-- Can also do ranges
-- Index is automatically created on UNIQUE columns
SELECT * FROM users PREWHERE (id BETWEEN 1 AND 2);

CREATE TABLE IF NOT EXISTS addresses (
	user_id UINT,
	address TEXT,
	active BOOL
);

INSERT INTO addresses(active, user_id, address) VALUES
(true, 1, '123 Main St'),
(false, 1, '456 Elm St'),
(true, 2, '789 Oak St');

-- Begin a transaction, insert data, and commit the changes
BEGIN;
INSERT INTO users VALUES (3, 'Alice', 'alice@email.com', 'password');
INSERT INTO addresses(active, user_id, address) VALUES (true, 3, '321 Maple St');
COMMIT;

-- Begin a transaction, insert data, then rollback the changes
BEGIN;
INSERT INTO users VALUES (4, 'Bob', 'bob@email.com', 'password');
INSERT INTO addresses(active, user_id, address) VALUES (true, 4, '654 Pine St');
ROLLBACK;

-- Union
SELECT id FROM users
UNION
SELECT user_id FROM addresses;

-- EXPLAIN shows the query plan without executing it
-- ANALYZE executes the plan and times it
EXPLAIN ANALYZE SELECT address
FROM users
INNER JOIN addresses
	ON id = user_id
WHERE active
LIMIT 1 OFFSET 1;

UPDATE addresses
SET address = '123 Main St'
WHERE user_id = 2;

DELETE FROM addresses
WHERE user_id <= 1;

DELETE FROM users
WHERE id = 1;

TRUNCATE TABLE users, addresses;

-- This is a read-only table
SELECT * from __CATALOG__;

DROP TABLE IF EXISTS users, addresses;