Complete List of All Essential SQL Queries for Beginners and Experts

SQL queries vary by use case, database system, and complexity, so a truly "complete" list of essential queries is tough to pin down—different roles (analyst, developer, DBA) prioritize different queries. Instead, I’ll provide a concise, comprehensive set of fundamental SQL queries covering common operations: data retrieval, manipulation, aggregation, joins, subqueries, and schema management. These are "essential" for most relational database tasks, based on standard SQL (compatible with systems like MySQL, PostgreSQL, SQL Server, etc.). I’ll group them by category and keep examples clear and practical.

1. Database Operations

-- Create a new database
CREATE DATABASE database_name;

-- Show all databases
SHOW DATABASES;

-- Use a database
USE database_name;

-- Delete a database
DROP DATABASE database_name;

-- Rename a database (Note: MySQL doesn't support directly)
ALTER DATABASE old_name MODIFY NAME = new_name;

2. Table Operations

-- Create a table
CREATE TABLE table_name (
  id INT PRIMARY KEY,
  name VARCHAR(100),
  age INT
);

-- Show all tables
SHOW TABLES;

-- Show table structure
DESCRIBE table_name;

-- Rename table
RENAME TABLE old_table TO new_table;

-- Drop a table
DROP TABLE table_name;

-- Truncate table (delete all rows)
TRUNCATE TABLE table_name;

-- Alter table: add column
ALTER TABLE table_name ADD email VARCHAR(100);

-- Alter table: modify column
ALTER TABLE table_name MODIFY age SMALLINT;

-- Alter table: drop column
ALTER TABLE table_name DROP COLUMN email;

3. Insert, Select, Update, Delete (CRUD)

-- Insert one row
INSERT INTO table_name (id, name, age) VALUES (1, 'Alice', 25);

-- Insert multiple rows
INSERT INTO table_name (id, name, age) VALUES 
(2, 'Bob', 30),
(3, 'Charlie', 28);

-- Select all rows
SELECT * FROM table_name;

-- Select specific columns
SELECT name, age FROM table_name;

-- Update data
UPDATE table_name SET age = 27 WHERE name = 'Alice';

-- Delete data
DELETE FROM table_name WHERE id = 2;

4. Filtering Data (WHERE Clause)

SELECT * FROM table_name WHERE age > 25;

-- Operators: =, !=, <, >, <=, >=, BETWEEN, IN, LIKE, IS NULL
SELECT * FROM table_name WHERE name LIKE 'A%';
SELECT * FROM table_name WHERE age BETWEEN 20 AND 30;
SELECT * FROM table_name WHERE id IN (1, 3, 5);
SELECT * FROM table_name WHERE email IS NULL;

5. Sorting Results (ORDER BY)

SELECT * FROM table_name ORDER BY age ASC;
SELECT * FROM table_name ORDER BY name DESC;

6. Limiting Results (LIMIT/OFFSET)

SELECT * FROM table_name LIMIT 5;
SELECT * FROM table_name LIMIT 5 OFFSET 10;

7. Joins (Combine Multiple Tables)

-- INNER JOIN
SELECT a.name, b.salary
FROM employees a
JOIN salaries b ON a.id = b.emp_id;

-- LEFT JOIN
SELECT a.name, b.salary
FROM employees a
LEFT JOIN salaries b ON a.id = b.emp_id;

-- RIGHT JOIN
SELECT a.name, b.salary
FROM employees a
RIGHT JOIN salaries b ON a.id = b.emp_id;

-- FULL JOIN (via UNION in MySQL)
SELECT * FROM a LEFT JOIN b ON a.id = b.id
UNION
SELECT * FROM a RIGHT JOIN b ON a.id = b.id;

8. Group By and Aggregation

-- Aggregate functions: COUNT, SUM, AVG, MAX, MIN
SELECT COUNT(*) FROM table_name;
SELECT AVG(age) FROM table_name;
SELECT SUM(age) FROM table_name;

-- Group By
SELECT age, COUNT(*) FROM table_name GROUP BY age;

-- Group By with HAVING
SELECT age, COUNT(*) FROM table_name
GROUP BY age
HAVING COUNT(*) > 1;

9. Subqueries

-- In SELECT
SELECT name, (SELECT COUNT(*) FROM orders WHERE user_id = users.id) AS order_count
FROM users;

-- In WHERE
SELECT * FROM employees
WHERE department_id = (SELECT id FROM departments WHERE name = 'IT');

-- In FROM (derived table)
SELECT t.age_avg FROM (
  SELECT AVG(age) AS age_avg FROM table_name
) t;

10. Set Operations

-- UNION (removes duplicates)
SELECT name FROM employees
UNION
SELECT name FROM managers;

-- UNION ALL (includes duplicates)
SELECT name FROM employees
UNION ALL
SELECT name FROM managers;

-- INTERSECT (common in both - Not supported in MySQL directly)
-- MINUS or EXCEPT (in PostgreSQL)

11. Views

-- Create a view
CREATE VIEW senior_employees AS
SELECT * FROM employees WHERE age > 40;

-- Use the view
SELECT * FROM senior_employees;

-- Drop the view
DROP VIEW senior_employees;

12. Indexing

-- Create index
CREATE INDEX idx_name ON employees(name);

-- Drop index (MySQL syntax)
DROP INDEX idx_name ON employees;

13. Constraints

-- NOT NULL
ALTER TABLE employees MODIFY name VARCHAR(100) NOT NULL;

-- UNIQUE
ALTER TABLE employees ADD CONSTRAINT unique_email UNIQUE (email);

-- PRIMARY KEY
ALTER TABLE employees ADD PRIMARY KEY (id);

-- FOREIGN KEY
ALTER TABLE orders ADD CONSTRAINT fk_user_id
FOREIGN KEY (user_id) REFERENCES users(id);

-- CHECK
ALTER TABLE employees ADD CONSTRAINT check_age CHECK (age >= 18);

14. Transactions

-- Start transaction
START TRANSACTION;

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

-- Commit or rollback
COMMIT;
-- or
ROLLBACK;

15. User Management (MySQL)

-- Create a user
CREATE USER 'user1'@'localhost' IDENTIFIED BY 'password';

-- Grant privileges
GRANT ALL PRIVILEGES ON database_name.* TO 'user1'@'localhost';

-- Revoke privileges
REVOKE ALL PRIVILEGES ON database_name.* FROM 'user1'@'localhost';

-- Delete user
DROP USER 'user1'@'localhost';

16. Export/Import (MySQL CLI)

-- Export database to file
mysqldump -u root -p database_name > backup.sql

-- Import from file
mysql -u root -p database_name < backup.sql