📝 SQL Cheat Sheet for Developers

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! 💻

---

Comments

[Martin Baun]

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.