CREATE TABLE

Example:

-- Create a new table
CREATE TABLE users (
	id INT AUTO_INCREMENT PRIMARY KEY,
	name VARCHAR(100) NOT NULL,
	email VARCHAR(255) UNIQUE NOT NULL,
	password_hash CHAR(64) NOT NULL,
	created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
	updated_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
	INDEX idx_email (email),
	INDEX idx_created (created_at)
);

-- Create table with foreign keys
CREATE TABLE orders (
	id INT AUTO_INCREMENT PRIMARY KEY,
	user_id INT NOT NULL,
	total DECIMAL(10, 2) NOT NULL,
	status ENUM('pending', 'processing', 'completed', 'cancelled') DEFAULT 'pending',
	created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
	FOREIGN KEY (user_id) REFERENCES users(id) ON DELETE CASCADE
);

-- Add a new column
ALTER TABLE users ADD COLUMN phone VARCHAR(20);

-- Modify a column
ALTER TABLE users MODIFY COLUMN name VARCHAR(200) NOT NULL;

-- Drop a column
ALTER TABLE users DROP COLUMN temporary_field;

-- Add an index
ALTER TABLE users ADD INDEX idx_phone (phone);

-- INNER JOIN
SELECT u.name, o.total, o.created_at
FROM users u
INNER JOIN orders o ON u.id = o.user_id
WHERE o.status = 'completed';

-- LEFT JOIN
SELECT u.id, u.name, COUNT(o.id) AS order_count
FROM users u
LEFT JOIN orders o ON u.id = o.user_id
GROUP BY u.id, u.name;

-- Multiple JOINs
SELECT 
	u.name AS customer_name,
	o.id AS order_id,
	p.name AS product_name,
	oi.quantity,
	oi.price
FROM users u
INNER JOIN orders o ON u.id = o.user_id
INNER JOIN order_items oi ON o.id = oi.order_id
INNER JOIN products p ON oi.product_id = p.id
WHERE o.status = 'completed'
ORDER BY o.created_at DESC;

-- COUNT, SUM, AVG
SELECT 
	COUNT(*) AS total_orders,
	SUM(total) AS total_revenue,
	AVG(total) AS average_order_value,
	MIN(total) AS smallest_order,
	MAX(total) AS largest_order
FROM orders
WHERE status = 'completed';

-- GROUP BY with HAVING
SELECT 
	category,
	COUNT(*) AS product_count,
	AVG(price) AS avg_price
FROM products
GROUP BY category
HAVING COUNT(*) > 5
ORDER BY avg_price DESC;

-- Subquery in WHERE clause
SELECT name, email
FROM users
WHERE id IN (
	SELECT DISTINCT user_id 
	FROM orders 
	WHERE total > 1000
);

-- Subquery in FROM clause
SELECT 
	category,
	avg_price,
	product_count
FROM (
	SELECT 
		category,
		AVG(price) AS avg_price,
		COUNT(*) AS product_count
	FROM products
	GROUP BY category
) AS category_stats
WHERE product_count > 10;

-- Correlated subquery
SELECT u.name, u.email
FROM users u
WHERE EXISTS (
	SELECT 1 
	FROM orders o 
	WHERE o.user_id = u.id AND o.total > 500
);

-- ROW_NUMBER
SELECT 
	name,
	category,
	price,
	ROW_NUMBER() OVER (PARTITION BY category ORDER BY price DESC) AS price_rank
FROM products;

-- Running totals
SELECT 
	date,
	amount,
	SUM(amount) OVER (ORDER BY date) AS running_total
FROM transactions;

-- LAG and LEAD
SELECT 
	date,
	revenue,
	LAG(revenue) OVER (ORDER BY date) AS previous_day,
	LEAD(revenue) OVER (ORDER BY date) AS next_day,
	revenue - LAG(revenue) OVER (ORDER BY date) AS day_over_day_change
FROM daily_revenue;

-- Simple CTE
WITH active_users AS (
	SELECT id, name, email
	FROM users
	WHERE active = 1
)
SELECT 
	au.name,
	COUNT(o.id) AS order_count
FROM active_users au
LEFT JOIN orders o ON au.id = o.user_id
GROUP BY au.id, au.name;

-- Recursive CTE
WITH RECURSIVE category_tree AS (
	SELECT id, name, parent_id, 1 AS level
	FROM categories
	WHERE parent_id IS NULL
	
	UNION ALL
	
	SELECT c.id, c.name, c.parent_id, ct.level + 1
	FROM categories c
	INNER JOIN category_tree ct ON c.parent_id = ct.id
)
SELECT * FROM category_tree ORDER BY level, name;

-- Simple CASE
SELECT 
	name,
	price,
	CASE 
		WHEN price < 10 THEN 'Budget'
		WHEN price < 50 THEN 'Standard'
		WHEN price < 100 THEN 'Premium'
		ELSE 'Luxury'
	END AS price_category
FROM products;

-- CASE with aggregation
SELECT 
	category,
	COUNT(*) AS total_products,
	SUM(CASE WHEN price < 20 THEN 1 ELSE 0 END) AS budget_count,
	SUM(CASE WHEN price >= 20 AND price < 100 THEN 1 ELSE 0 END) AS mid_range_count,
	SUM(CASE WHEN price >= 100 THEN 1 ELSE 0 END) AS premium_count
FROM products
GROUP BY category;

-- Basic transaction
START TRANSACTION;

UPDATE accounts SET balance = balance - 100 WHERE id = 1;
UPDATE accounts SET balance = balance + 100 WHERE id = 2;

COMMIT;

-- Transaction with rollback on error
BEGIN;

INSERT INTO orders (user_id, total) VALUES (42, 99.99);
SET @order_id = LAST_INSERT_ID();

INSERT INTO order_items (order_id, product_id, quantity, price)
VALUES (@order_id, 1, 2, 19.99);

-- If error occurs, rollback
-- ROLLBACK;

COMMIT;

-- Create indexes
CREATE INDEX idx_user_email ON users(email);
CREATE INDEX idx_order_status ON orders(status, created_at);
CREATE UNIQUE INDEX idx_username ON users(username);

-- Full-text index
CREATE FULLTEXT INDEX idx_product_description ON products(name, description);

-- Full-text search
SELECT * FROM products
WHERE MATCH(name, description) AGAINST ('widget gadget' IN BOOLEAN MODE);

-- Analyze query performance
EXPLAIN SELECT * FROM orders WHERE user_id = 42 AND status = 'completed';

-- Create a view
CREATE VIEW active_customers AS
SELECT 
	u.id,
	u.name,
	u.email,
	COUNT(o.id) AS order_count,
	SUM(o.total) AS total_spent
FROM users u
LEFT JOIN orders o ON u.id = o.user_id
WHERE u.active = 1
GROUP BY u.id, u.name, u.email;

-- Use the view
SELECT * FROM active_customers WHERE total_spent > 1000;

-- Drop a view
DROP VIEW IF EXISTS active_customers;