SQL  

SQL Cheatsheet: A Simple and Complete Guide

Introduction

SQL (Structured Query Language) is used to interact with relational databases. It helps you to store, retrieve, update, and delete data. SQL is used in almost every industry, from websites to banking systems. If you want to work with data, SQL is a must-have skill. This cheatsheet will give you short and clear definitions, code examples, and key points for the most important SQL topics.

1. SELECT Statement

  • Definition: Used to fetch data from a table.

  • Example

    SELECT name, age FROM Students;

  • Key Point: Use * to select all columns.

2. WHERE Clause

  • Definition: Filters records based on a condition.

  • Example

    SELECT * FROM Students WHERE age > 18;

  • Key Point: Works with operators like =, >, <, BETWEEN, IN, LIKE.

3. ORDER BY

  • Definition: Sorts the result in ascending or descending order.

  • Example

    SELECT * FROM Students ORDER BY age DESC;

  • Key Point: Default sort is ascending (ASC).

4. INSERT INTO

  • Definition: Adds new records to a table.

  • Example

    INSERT INTO Students (name, age) VALUES ('John', 20);

  • Key Point: Match the column order with the values.

5. UPDATE

  • Definition: Changes existing records.

  • Example

    UPDATE Students SET age = 21 WHERE name = 'John';

  • Key Point: Always use WHERE to avoid updating all rows.

6. DELETE

  • Definition: Removes data from a table.

  • Example

    DELETE FROM Students WHERE name = 'John';

  • Key Point: Without WHERE, all rows will be deleted.

7. CREATE TABLE

  • Definition: Creates a new table.

  • Example

    CREATE TABLE Students (
  id INT PRIMARY KEY,
  name VARCHAR(50),
  age INT
);

  • Key Point: Define data types clearly.

8. ALTER TABLE

  • Definition: Changes the structure of a table.

  • Examples

    • Add column:

      ALTER TABLE Students ADD email VARCHAR(100);

    • Drop column:

      ALTER TABLE Students DROP COLUMN email;

9. DROP TABLE

  • Definition: Deletes the table and its data.

  • Example

    DROP TABLE Students;

  • Key Point: This action cannot be undone.

10. JOINs

  • Definition: Combines rows from two or more tables.

  • Example (INNER JOIN)

    SELECT Students.name, Marks.score
FROM Students
INNER JOIN Marks ON Students.id = Marks.student_id;

  • Types

    • INNER JOIN: Only matching records.

    • LEFT JOIN: All records from left + matches.

    • RIGHT JOIN: All from right + matches.

    • FULL JOIN: All records from both.

11. GROUP BY

  • Definition: Groups rows based on a column and applies an aggregate function.

  • Example:

    SELECT age, COUNT(*) FROM Students GROUP BY age;

  • Key Point: Always use with aggregate functions.

12. HAVING

  • Definition: Filters groups created by GROUP BY.

  • Example:

    SELECT age, COUNT(*) 
FROM Students 
GROUP BY age 
HAVING COUNT(*) > 1;

  • Key Point: Use HAVING after GROUP BY.

13. Aggregate Functions

  • Definition: Perform calculations on multiple values.

  • Common ones: COUNT(), SUM(), AVG(), MIN(), MAX()

  • Example

    SELECT AVG(age) FROM Students;

14. DISTINCT

  • Definition: Removes duplicate rows.

  • Example

    SELECT DISTINCT age FROM Students;

15. BETWEEN, IN, LIKE

  • BETWEEN

    SELECT * FROM Students WHERE age BETWEEN 18 AND 25;

  • IN

    SELECT * FROM Students WHERE age IN (18, 21, 23);

  • LIKE

    SELECT * FROM Students WHERE name LIKE 'A%';

16. LIMIT / TOP

  • Definition: Restricts number of rows returned.

  • MySQL

    SELECT * FROM Students LIMIT 5;

  • SQL Server

    SELECT TOP 5 * FROM Students;

17. UNION

  • Definition: Combines results of two SELECT statements.

  • Example

    SELECT name FROM Students
UNION
SELECT name FROM Teachers;

  • Key Point: Both queries must have the same number of columns.

18. Subquery

  • Definition: A query inside another query.

  • Example

    SELECT name FROM Students 
WHERE age = (SELECT MAX(age) FROM Students);

19. Views

  • Definition: A virtual table based on the result of a query.

  • Example

    CREATE VIEW Teenagers AS
SELECT * FROM Students WHERE age BETWEEN 13 AND 19;

20. Indexes

  • Definition: Improve search performance on columns.

  • Example

    CREATE INDEX idx_name ON Students(name);

  • Key Point: Indexes speed up SELECT but may slow down INSERT/UPDATE.

21. Constraints

  • Definition: Rules applied on columns.

  • Types

    • PRIMARY KEY: Unique and not null.

    • FOREIGN KEY: Links to another table’s primary key.

    • UNIQUE: Ensures all values are different.

    • NOT NULL: Prevents null values.

    • CHECK: Validates a condition.

  • Example

    CREATE TABLE Students (
  id INT PRIMARY KEY,
  age INT CHECK (age >= 0)
);

22. DEFAULT Constraint

  • Definition: Sets a default value for a column if none is provided.

  • Example

    CREATE TABLE Students (
  id INT,
  name VARCHAR(50),
  status VARCHAR(10) DEFAULT 'active'
);

  • Key Point: Helps avoid NULL when no value is provided.

23. CASE Statement

  • Definition: Adds conditional logic inside a query.

  • Example

    SELECT name,
       CASE 
         WHEN age >= 18 THEN 'Adult'
         ELSE 'Minor'
       END AS age_group
FROM Students;

  • Key Point: Works like if-else in SQL.

24. EXISTS

  • Definition: Checks whether a subquery returns any row.

  • Example

    SELECT name FROM Students s
WHERE EXISTS (
  SELECT 1 FROM Marks m WHERE m.student_id = s.id
);

  • Key Point: Faster than IN in many cases for large datasets.

25. NOT IN vs NOT EXISTS

  • Caution: NOT IN fails if any NULL exists in subquery. Use NOT EXISTS for safer logic.

  • Example

    SELECT name FROM Students
WHERE NOT EXISTS (
  SELECT 1 FROM Graduates WHERE Students.id = Graduates.id
);

26. COALESCE and ISNULL

  • Definition: Replace NULL with a default value.

  • Example

    SELECT name, COALESCE(email, 'Not Provided') FROM Students;

  • Key Point: COALESCE returns the first non-null value from a list.

27. Window Functions

  • Definition: Performs calculation across a set of rows related to the current row.

  • Common ones: ROW_NUMBER(), RANK(), DENSE_RANK(), NTILE()

  • Example

    SELECT name, age,
       RANK() OVER (ORDER BY age DESC) AS age_rank
FROM Students;

  • Key Point: Unlike aggregate functions, window functions do not collapse rows.

28. Common Table Expressions (CTE)

  • Definition: A temporary named result set used in a query.

  • Example

    WITH Teenagers AS (
  SELECT * FROM Students WHERE age BETWEEN 13 AND 19
)
SELECT * FROM Teenagers;

  • Key Point: Makes complex queries cleaner and readable.

29. Stored Procedures

  • Definition: A saved group of SQL statements that can be executed repeatedly.

  • Example

    CREATE PROCEDURE GetAllStudents
AS
BEGIN
  SELECT * FROM Students;
END;

  • Execution

    EXEC GetAllStudents;

30. Functions (User-Defined Functions)

  • Definition: Return a value based on input parameters.

  • Example

    CREATE FUNCTION GetFullName (@first VARCHAR(50), @last VARCHAR(50))
RETURNS VARCHAR(100)
AS
BEGIN
  RETURN (@first + ' ' + @last);
END;

31. Transactions

  • Definition: A set of SQL operations that execute together or not at all.

  • Example

    BEGIN TRANSACTION;
UPDATE Accounts SET balance = balance - 100 WHERE id = 1;
UPDATE Accounts SET balance = balance + 100 WHERE id = 2;
COMMIT;

  • Key Point: Use ROLLBACK to undo changes if something fails.

32. ACID Properties

  • Definition: Ensures reliable processing of transactions.

    • Atomicity: All or nothing

    • Consistency: Valid state before/after

    • Isolation: Transactions do not affect each other

    • Durability: Changes are permanent

33. Normalization

  • Definition: Organizing data to reduce redundancy.
  • Common Forms: 1NF, 2NF, 3NF
  • Key Point: Normalize to avoid data anomalies. Denormalize only when performance requires it.

34. Foreign Key

  • Definition: A column that links to another table’s primary key.

  • Example

    CREATE TABLE Marks (
  id INT PRIMARY KEY,
  student_id INT,
  FOREIGN KEY (student_id) REFERENCES Students(id)
);

35. Triggers

  • Definition: SQL code that runs automatically on data change (INSERT, UPDATE, DELETE).

  • Example

    CREATE TRIGGER LogDelete
AFTER DELETE ON Students
FOR EACH ROW
BEGIN
  INSERT INTO DeletedRecords (name) VALUES (OLD.name);
END;

36. SET Operators (UNION ALL, INTERSECT, EXCEPT)

  • UNION ALL: Includes duplicates.

  • INTERSECT: Returns common rows between two queries.

  • EXCEPT: Returns rows in first query but not in second.

  • Example

    SELECT name FROM Students
INTERSECT
SELECT name FROM Teachers;

37. Backup and Restore

  • Backup Example (SQL Server)

    BACKUP DATABASE School TO DISK = 'D:\backup\school.bak';

  • Restore Example

    RESTORE DATABASE School FROM DISK = 'D:\backup\school.bak';

38. TEMPORARY Tables

  • Definition: Temporary tables exist only during the session or transaction.

  • Example (MySQL)

    CREATE TEMPORARY TABLE TempStudents (
  id INT,
  name VARCHAR(50)
);

  • Key Point: Good for storing intermediate results. Automatically dropped at session end.

39. TABLE Variables (SQL Server)

  • Definition: A variable to temporarily hold table data.

  • Example

    DECLARE @Temp TABLE (id INT, name VARCHAR(50));
INSERT INTO @Temp VALUES (1, 'John');

40. MERGE Statement (UPSERT)

  • Definition: Performs INSERT, UPDATE, or DELETE in one statement based on a match.

  • Example

    MERGE INTO Students AS target
USING (SELECT 1 AS id, 'John' AS name) AS source
ON target.id = source.id
WHEN MATCHED THEN
  UPDATE SET name = source.name
WHEN NOT MATCHED THEN
  INSERT (id, name) VALUES (source.id, source.name);

  • Key Point: Use cautiously. Complex logic can lead to bugs if not tested well.

41. Recursive CTE

  • Definition: A CTE that refers to itself. Useful for hierarchical data (e.g., org charts, folder trees).

  • Example

    WITH OrgChart AS (
  SELECT id, manager_id, name FROM Employees WHERE manager_id IS NULL
  UNION ALL
  SELECT e.id, e.manager_id, e.name
  FROM Employees e
  JOIN OrgChart o ON e.manager_id = o.id
)
SELECT * FROM OrgChart;

42. JSON Data Handling

  • Definition: Querying and storing JSON in SQL columns (supported in PostgreSQL, MySQL, SQL Server).

  • Example (PostgreSQL)

    SELECT data->>'name' AS name FROM users WHERE data->>'age' = '25';

  • Key Point: Useful when data structure is flexible or semi-structured.

43. PIVOT and UNPIVOT

  • Definition

    • PIVOT: Converts rows to columns.

    • UNPIVOT: Converts columns to rows.

  • Example (SQL Server):

    SELECT * FROM
(SELECT subject, score FROM Marks) AS SourceTable
PIVOT (
  MAX(score) FOR subject IN ([Math], [Science])
) AS PivotTable;

  • Key Point: Makes reporting easier. Avoid overuse in core logic.

44. ROLLUP and CUBE

  • Definition: Extensions to GROUP BY for creating subtotals and grand totals.

  • ROLLUP

    SELECT department, role, COUNT(*) 
FROM Employees
GROUP BY ROLLUP (department, role);

  • CUBE

    GROUP BY CUBE (department, role);

  • Key Point: Saves time when generating hierarchical reports.

45. WITH TIES

  • Definition: Returns additional rows that match the last value in an ORDER BY ... TOP query.

  • Example:

    SELECT TOP 3 WITH TIES name, score 
FROM Marks 
ORDER BY score DESC;

  • Key Point: Useful when ranks are tied and you want to return all top scorers.

46. SEQUENCES

  • Definition: Auto-incrementing number generator independent of tables.

  • Example:

    CREATE SEQUENCE student_seq START WITH 1 INCREMENT BY 1;
SELECT NEXT VALUE FOR student_seq;

  • Key Point: More flexible than IDENTITY in some databases.

47. IDENTITY vs SEQUENCE

  • IDENTITY: Tied to a table. Auto-increments per insert.
  • SEQUENCE: Independent object. Can be reused across tables or manually advanced.

48. LOCKING & ISOLATION LEVELS

  • Definition: Controls how transactions read/write shared data.

  • Levels

    • READ UNCOMMITTED: Dirty reads allowed

    • READ COMMITTED: Only committed data

    • REPEATABLE READ: Prevents non-repeatable reads

    • SERIALIZABLE: Strictest, full isolation

  • Key Point: Choose based on consistency vs performance trade-off.

49. DEADLOCK

  • Definition: Two or more sessions wait forever for resources locked by each other.
  • Fix: Reduce transaction size, always lock objects in the same order, use proper isolation levels.

50. EXECUTION PLAN

  • Definition: Visual or textual explanation of how SQL Server/MySQL/PostgreSQL will execute the query.
  • Key Point: Use EXPLAIN, SHOW PLAN, or right-click → “Display Estimated Execution Plan” in SQL Server Management Studio.

51. ANALYZE / UPDATE STATISTICS

  • Definition: Refreshes metadata so the query planner makes better decisions.

  • Example (PostgreSQL):

    ANALYZE Students;

  • SQL Server:

    UPDATE STATISTICS Students;

52. PARTITIONING

  • Definition: Divides large tables into smaller, more manageable parts (partitions).
  • Key Point: Boosts performance on very large tables. Query optimizer uses partition elimination.

53. Sharding

  • Definition: Horizontal partitioning across databases or servers.
  • Key Point: Needed for very high scale. Not supported by default in many RDBMS — requires custom implementation or external tools.

54. Temporal Tables (System-Versioned Tables)

  • Definition: Track historical changes automatically.
  • Example (SQL Server 2016+): 
    CREATE TABLE StudentsHistory (
  id INT,
  name VARCHAR(50),
  VALID_FROM DATETIME2 GENERATED ALWAYS AS ROW START,
  VALID_TO DATETIME2 GENERATED ALWAYS AS ROW END,
  PERIOD FOR SYSTEM_TIME (VALID_FROM, VALID_TO)
) WITH (SYSTEM_VERSIONING = ON);

Membership not found