Hey everyone! 👋
I’ve compiled a handy SQL Cheat Sheet to help you quickly reference key SQL commands and concepts. Whether you’re working with databases or just need a quick refresher, this guide has got you covered.
Let’s dive in!
🔗 Core Concepts
- SQL: Structured Query Language for managing and manipulating relational databases.
📦 SQL Commands and Concepts
-
SELECT: Retrieves data from tables.
SELECT * FROM employees;
-
INSERT: Adds new records to tables.
INSERT INTO employees (name, position) VALUES ('John Doe', 'Developer');
-
UPDATE: Modifies existing records in tables.
UPDATE employees SET position = 'Senior Developer' WHERE name = 'John Doe';
-
DELETE: Removes records from tables.
DELETE FROM employees WHERE name = 'John Doe';
-
WHERE: Filters records based on conditions.
SELECT * FROM employees WHERE position = 'Developer';
-
JOIN: Combines records from multiple tables.
SELECT employees.name, departments.department_name
FROM employees
JOIN departments ON employees.department_id = departments.id;
-
INNER JOIN: Returns matching records from tables.
SELECT * FROM orders INNER JOIN customers ON orders.customer_id = customers.id; -
LEFT JOIN: Returns all left table records.
SELECT * FROM employees LEFT JOIN departments ON employees.department_id = departments.id; -
RIGHT JOIN: Returns all right table records.
SELECT * FROM employees RIGHT JOIN departments ON employees.department_id = departments.id; -
FULL JOIN: Returns all matching/non-matching records.
SELECT * FROM employees FULL JOIN departments ON employees.department_id = departments.id;-
GROUP BY: Groups rows sharing common fields.
-
SELECT department_id, COUNT(*) as employee_count
FROM employees
GROUP BY department_id;
-
ORDER BY: Sorts records in ascending/descending order.
SELECT * FROM employees ORDER BY name ASC;
-
HAVING: Filters groups after aggregation.
SELECT department_id, COUNT(*) as employee_count
FROM employees
GROUP BY department_id
HAVING COUNT(*) > 5;
-
DISTINCT: Removes duplicate records from results.
SELECT DISTINCT department_id FROM employees;
-
LIMIT: Restricts the number of returned records.
SELECT * FROM employees LIMIT 10;
-
OFFSET: Skips a specific number of records.
SELECT * FROM employees LIMIT 10 OFFSET 20;
-
ALIAS: Renames tables or columns temporarily.
SELECT name AS employee_name FROM employees;
-
UNION: Combines results of two SELECTs.
SELECT name FROM employees
UNION
SELECT name FROM contractors;
-
INDEX: Speeds up data retrieval.
CREATE INDEX idx_employee_name ON employees (name);
-
PRIMARY KEY: Uniquely identifies each table record.
CREATE TABLE employees (
id INT PRIMARY KEY,
name VARCHAR(100)
);
-
FOREIGN KEY: Links records between tables.
CREATE TABLE orders (
id INT PRIMARY KEY,
customer_id INT,
FOREIGN KEY (customer_id) REFERENCES customers(id)
);
-
AUTO_INCREMENT: Automatically increments numeric values.
CREATE TABLE employees (
id INT AUTO_INCREMENT PRIMARY KEY,
name VARCHAR(100)
);
-
NOT NULL: Ensures column must have a value.
CREATE TABLE employees (
id INT PRIMARY KEY,
name VARCHAR(100) NOT NULL
);
-
DEFAULT: Sets default value for column.
CREATE TABLE employees (
id INT PRIMARY KEY,
status VARCHAR(20) DEFAULT 'active'
);
-
CHECK: Ensures column meets a condition.
CREATE TABLE employees (
id INT PRIMARY KEY,
age INT CHECK (age >= 18)
);
-
CONSTRAINT: Defines rules for table data integrity.
ALTER TABLE employees
ADD CONSTRAINT unique_name UNIQUE (name);
-
TRIGGER: Executes automatic actions on data changes.
CREATE TRIGGER before_employee_insert
BEFORE INSERT ON employees
FOR EACH ROW
BEGIN
SET NEW.created_at = NOW();
END;
-
VIEW: Virtual table based on a query.
CREATE VIEW employee_view AS
SELECT name, position FROM employees;
-
SUBQUERY: Nested query within another query.
SELECT name FROM employees
WHERE department_id IN (
SELECT id FROM departments WHERE name = 'Sales'
);
-
TRANSACTION: Ensures data consistency across operations.
START TRANSACTION;
UPDATE employees SET position = 'Manager' WHERE name = 'John Doe';
COMMIT;
-
COMMIT: Saves all changes in a transaction.
COMMIT;
-
ROLLBACK: Undoes changes in a transaction.
ROLLBACK;
-
ACID: Ensures reliable database transactions (Atomicity, Consistency, Isolation, Durability).
Connect with me:
- LinkedIn: https://www.linkedin.com/in/nikko-ferwelo-358b11213
- GitHub: https://github.com/NullVoidKage
Feel free to reach out or follow me for more content on database management and SQL. Happy querying! 💻
Top comments (1)
The best book I came across at the start of my career was SQL Queries for Mere Mortals by Viescas and Hernandez I still refer to it to this day. Love this cheat sheet too.