CREATE DATABASE mydb;
DROP DATABASE mydb;
USE mydb;
SHOW DATABASES;

CREATE TABLE users (
  id INT PRIMARY KEY AUTO_INCREMENT,
  name VARCHAR(100),
  age INT
);

DROP TABLE users;
ALTER TABLE users ADD COLUMN email VARCHAR(255);
ALTER TABLE users DROP COLUMN age;
RENAME TABLE users TO people;
SHOW TABLES;
DESCRIBE users;

INSERT INTO users (name, age) VALUES ('Alice', 30);
INSERT INTO users (name, age) VALUES ('Bob', 25), ('Carol', 22);

SELECT * FROM users;
SELECT name, age FROM users WHERE age > 25;
SELECT * FROM users ORDER BY age DESC LIMIT 5;
SELECT COUNT(*) FROM users;

UPDATE users SET age = 31 WHERE name = 'Alice';
DELETE FROM users WHERE name = 'Carol';

SELECT * FROM users WHERE age BETWEEN 20 AND 30;
SELECT * FROM users WHERE name LIKE 'A%';
SELECT * FROM users WHERE age IN (22, 25, 30);

-- INNER JOIN
SELECT users.name, orders.amount
FROM users
INNER JOIN orders ON users.id = orders.user_id;

-- LEFT JOIN
SELECT users.name, orders.amount
FROM users
LEFT JOIN orders ON users.id = orders.user_id;

CREATE INDEX idx_name ON users(name);
DROP INDEX idx_name ON users;
SHOW INDEX FROM users;

SELECT AVG(age) FROM users;
SELECT MIN(age), MAX(age) FROM users;
SELECT SUM(age) FROM users;
SELECT age, COUNT(*) FROM users GROUP BY age;
HAVING COUNT(*) > 1;

ALTER TABLE users ADD CONSTRAINT unique_email UNIQUE (email);
ALTER TABLE users ADD CONSTRAINT fk_order FOREIGN KEY (id) REFERENCES orders(user_id);

-- MySQL
SOURCE backup.sql;
-- PostgreSQL
\i backup.sql

SHOW PROCESSLIST;
SHOW STATUS;
SHOW VARIABLES;
SELECT @@version;