KAMAL KISHOR

Posted on Jul 23

# The Ultimate SQL Cheatsheet and Guide for 2025

#webdev #programming #sql #database

Structured Query Language (SQL) is the standard language used to manage and manipulate relational databases. Whether you're working with PostgreSQL, MySQL, SQLite, or Oracle, the syntax is largely consistent, making SQL an essential skill for developers, analysts, and DBAs.

🔹 1. Database Basics

-- Create a new database
CREATE DATABASE db_name;

-- Switch to a database
USE db_name;

-- Delete a database
DROP DATABASE db_name;

-- Show all databases
SHOW DATABASES;

🔹 2. Table Management

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

-- Show all tables
SHOW TABLES;

-- View table schema
DESCRIBE employees;

-- Delete a table
DROP TABLE employees;

-- Rename a table
RENAME TABLE employees TO staff;

-- Alter table
ALTER TABLE employees ADD salary DECIMAL(10,2);
ALTER TABLE employees DROP COLUMN salary;
ALTER TABLE employees MODIFY name VARCHAR(150);

🔹 3. Data Manipulation (CRUD)

-- Insert data
INSERT INTO employees (id, name, age, department_id)
VALUES (1, 'Alice', 30, 2);

-- Bulk insert
INSERT INTO employees (id, name, age, department_id)
VALUES
  (2, 'Bob', 25, 1),
  (3, 'Charlie', 35, 2);

-- Update data
UPDATE employees SET age = 32 WHERE name = 'Alice';

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

🔹 4. Querying Data

-- Select everything
SELECT * FROM employees;

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

-- Aliases
SELECT name AS employee_name FROM employees;

-- DISTINCT values
SELECT DISTINCT department_id FROM employees;

-- WHERE clause
SELECT * FROM employees WHERE age > 30;

-- Comparison operators
=, !=, <, >, <=, >=

-- BETWEEN, IN, LIKE
SELECT * FROM employees WHERE age BETWEEN 25 AND 35;
SELECT * FROM employees WHERE department_id IN (1, 2);
SELECT * FROM employees WHERE name LIKE 'A%';

-- NULL checks
SELECT * FROM employees WHERE department_id IS NULL;

🔹 5. Sorting and Pagination

-- ORDER BY
SELECT * FROM employees ORDER BY age DESC;

-- LIMIT and OFFSET
SELECT * FROM employees LIMIT 10;
SELECT * FROM employees LIMIT 10 OFFSET 20;

🔹 6. Aggregate Functions and Grouping

-- COUNT, SUM, AVG, MIN, MAX
SELECT COUNT(*) FROM employees;
SELECT AVG(age) FROM employees;
SELECT MAX(salary) FROM employees;

-- GROUP BY
SELECT department_id, COUNT(*) FROM employees GROUP BY department_id;

-- HAVING
SELECT department_id, COUNT(*) as total FROM employees GROUP BY department_id HAVING total > 2;

🔹 7. SQL Joins

-- INNER JOIN
SELECT e.name, d.name AS department
FROM employees e
INNER JOIN departments d ON e.department_id = d.id;

-- LEFT JOIN
SELECT e.name, d.name AS department
FROM employees e
LEFT JOIN departments d ON e.department_id = d.id;

-- RIGHT JOIN
SELECT e.name, d.name AS department
FROM employees e
RIGHT JOIN departments d ON e.department_id = d.id;

-- FULL OUTER JOIN
SELECT e.name, d.name AS department
FROM employees e
FULL OUTER JOIN departments d ON e.department_id = d.id;

-- CROSS JOIN
SELECT * FROM employees CROSS JOIN departments;

🔹 8. Subqueries

-- Subquery in SELECT
SELECT name, (SELECT COUNT(*) FROM employees) AS total_employees FROM employees;

-- Subquery in WHERE
SELECT * FROM employees WHERE department_id = (SELECT id FROM departments WHERE name = 'HR');

🔹 9. Set Operations

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

-- UNION ALL (keeps duplicates)
SELECT name FROM employees
UNION ALL
SELECT name FROM customers;

-- INTERSECT (common rows)
SELECT name FROM employees
INTERSECT
SELECT name FROM customers;

-- EXCEPT (PostgreSQL / MSSQL)
SELECT name FROM employees
EXCEPT
SELECT name FROM customers;

🔹 10. Views

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

-- Query view
SELECT * FROM senior_employees;

-- Drop view
DROP VIEW senior_employees;

🔹 11. Transactions

-- Begin a transaction
BEGIN;

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

-- Commit
COMMIT;

-- Rollback if needed
ROLLBACK;

-- Savepoint
SAVEPOINT sp1;
ROLLBACK TO sp1;
RELEASE SAVEPOINT sp1;

🔹 12. Indexes

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

-- Drop index
DROP INDEX idx_name;

🔹 13. Stored Procedures (MySQL Example)

DELIMITER //
CREATE PROCEDURE GetEmployees()
BEGIN
  SELECT * FROM employees;
END //
DELIMITER ;

-- Call procedure
CALL GetEmployees();

🔹 14. Triggers

CREATE TRIGGER before_insert_trigger
BEFORE INSERT ON employees
FOR EACH ROW
SET NEW.created_at = NOW();

🔹 15. User Management

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

-- Grant permissions
GRANT ALL PRIVILEGES ON db_name.* TO 'kamal'@'localhost';

-- Revoke
REVOKE ALL PRIVILEGES ON db_name.* FROM 'kamal'@'localhost';

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

🔹 16. Data Export & Import

MySQL:

# Export
mysqldump -u user -p dbname > backup.sql

# Import
mysql -u user -p dbname < backup.sql

🔹 17. Best Practices

Always back up before mass operations.
Use transactions to ensure data integrity.
Use proper indexing for performance.
Avoid SELECT * in production code.
Sanitize inputs to prevent SQL injection.

🌟 Want to Practice SQL?

Here are some great resources:

DEV Community