SQL Cheat Sheet 📊

📋 SQL Server Quick Reference Guide

🔤 SQL Command Categories

🏗️ DDL (Data Definition Language)

📝 DML (Data Manipulation Language)

🔐 DCL (Data Control Language)

🔄 TCL (Transaction Control Language)

🔗 JOIN Operations

🎯 JOIN Examples with Sample Data

🗃️ Data Types

🔧 Operators

🔤 String Functions

📅 Date Functions

🔢 Mathematical Functions

📊 Aggregate Functions

🔄 Conversion Functions

❓ Conditional Functions

🏢 System Functions

🔍 Information Functions

🔐 Security Functions

📈 Window Functions

🎯 Ranking and Analytical Functions

🎯 Query Execution Sequence

Execution Order

✅ Complete Example

SELECT d.DepartmentName, AVG(e.Salary) AS AvgSalary
FROM Employees e
INNER JOIN Departments d ON e.DeptID = d.ID
WHERE e.Salary > 50000
GROUP BY d.DepartmentName
HAVING AVG(e.Salary) > 60000
ORDER BY AvgSalary DESC
LIMIT 5;

✔ Logical Execution Order for Above Query

📋 Table of Contents

🗂️ Data Types

Numeric Types

-- Integer types
INT, SMALLINT, BIGINT, TINYINT
-- Decimal types
DECIMAL(precision, scale)
NUMERIC(precision, scale)
FLOAT, REAL, DOUBLE
-- Example
CREATE TABLE Products (
 ProductID INT PRIMARY KEY,
 Price DECIMAL(10,2),
 Quantity SMALLINT
);

String Types

-- Fixed length
CHAR(n)
-- Variable length
VARCHAR(n)
TEXT
-- Example
CREATE TABLE Customers (
 CustomerID INT PRIMARY KEY,
 FirstName VARCHAR(50),
 LastName VARCHAR(50),
 Description TEXT
);

Date and Time Types

DATE -- YYYY-MM-DD
TIME -- HH:MM:SS
DATETIME -- YYYY-MM-DD HH:MM:SS
TIMESTAMP -- Unix timestamp
YEAR -- YYYY
-- Example
CREATE TABLE Orders (
 OrderID INT PRIMARY KEY,
 OrderDate DATE,
 OrderTime TIME,
 CreatedAt TIMESTAMP DEFAULT CURRENT_TIMESTAMP
);

🏢 Database Operations

Create Database

CREATE DATABASE CompanyDB;

Use Database

USE CompanyDB;

Drop Database

DROP DATABASE CompanyDB;

Show Databases

SHOW DATABASES;

📊 Table Operations

Create Table

CREATE TABLE Employees (
 EmployeeID INT PRIMARY KEY AUTO_INCREMENT,
 FirstName VARCHAR(50) NOT NULL,
 LastName VARCHAR(50) NOT NULL,
 Email VARCHAR(100) UNIQUE,
 HireDate DATE,
 Salary DECIMAL(10,2),
 DepartmentID INT,
 FOREIGN KEY (DepartmentID) REFERENCES Departments(DeptID)
);

Alter Table

-- Add column
ALTER TABLE Employees ADD COLUMN Phone VARCHAR(15);
-- Modify column
ALTER TABLE Employees MODIFY COLUMN Salary DECIMAL(12,2);
-- Drop column
ALTER TABLE Employees DROP COLUMN Phone;
-- Add constraint
ALTER TABLE Employees ADD CONSTRAINT fk_dept
FOREIGN KEY (DepartmentID) REFERENCES Departments(DeptID);

Drop Table

DROP TABLE Employees;

Show Tables

SHOW TABLES;

Describe Table Structure

DESCRIBE Employees;
-- OR
DESC Employees;

✏️ Data Manipulation

Insert Data

-- Insert single record
INSERT INTO Employees (FirstName, LastName, Email, HireDate, Salary, DepartmentID)
VALUES ('John', 'Doe', 'john.doe@email.com', '2023年01月15日', 55000.00, 1);
-- Insert multiple records
INSERT INTO Employees (FirstName, LastName, Email, HireDate, Salary, DepartmentID)
VALUES
 ('Jane', 'Smith', 'jane.smith@email.com', '2023年02月01日', 60000.00, 2),
 ('Mike', 'Johnson', 'mike.johnson@email.com', '2023年03月10日', 52000.00, 1);
-- Insert from another table
INSERT INTO EmployeeBackup
SELECT * FROM Employees WHERE DepartmentID = 1;

Update Data

-- Update single record
UPDATE Employees
SET Salary = 58000.00
WHERE EmployeeID = 1;
-- Update multiple records
UPDATE Employees
SET Salary = Salary * 1.05
WHERE DepartmentID = 1;
-- Update with JOIN
UPDATE Employees e
JOIN Departments d ON e.DepartmentID = d.DeptID
SET e.Salary = e.Salary * 1.10
WHERE d.DepartmentName = 'Engineering';

Delete Data

-- Delete specific records
DELETE FROM Employees
WHERE EmployeeID = 1;
-- Delete with condition
DELETE FROM Employees
WHERE HireDate < '2020年01月01日';
-- Delete all records (but keep table structure)
DELETE FROM Employees;

🔍 Query Operations

Basic SELECT

-- Select all columns
SELECT * FROM Employees;
-- Select specific columns
SELECT FirstName, LastName, Salary FROM Employees;
-- Select with alias
SELECT FirstName AS 'First Name', LastName AS 'Last Name' FROM Employees;

WHERE Clause

-- Comparison operators
SELECT * FROM Employees WHERE Salary > 50000;
SELECT * FROM Employees WHERE DepartmentID = 1;
SELECT * FROM Employees WHERE HireDate >= '2023年01月01日';
-- Logical operators
SELECT * FROM Employees WHERE Salary > 50000 AND DepartmentID = 1;
SELECT * FROM Employees WHERE DepartmentID = 1 OR DepartmentID = 2;
SELECT * FROM Employees WHERE NOT DepartmentID = 3;
-- LIKE operator
SELECT * FROM Employees WHERE FirstName LIKE 'J%'; -- Starts with 'J'
SELECT * FROM Employees WHERE Email LIKE '%@gmail.com'; -- Ends with '@gmail.com'
SELECT * FROM Employees WHERE FirstName LIKE 'J_hn'; -- 'J' + any char + 'hn'
-- IN operator
SELECT * FROM Employees WHERE DepartmentID IN (1, 2, 3);
-- BETWEEN operator
SELECT * FROM Employees WHERE Salary BETWEEN 50000 AND 70000;
-- IS NULL / IS NOT NULL
SELECT * FROM Employees WHERE Email IS NULL;
SELECT * FROM Employees WHERE Email IS NOT NULL;

ORDER BY

-- Ascending order (default)
SELECT * FROM Employees ORDER BY LastName;
-- Descending order
SELECT * FROM Employees ORDER BY Salary DESC;
-- Multiple columns
SELECT * FROM Employees ORDER BY DepartmentID, Salary DESC;

LIMIT and OFFSET

-- Limit results
SELECT * FROM Employees LIMIT 10;
-- Pagination
SELECT * FROM Employees LIMIT 10 OFFSET 20; -- Skip first 20, get next 10
-- TOP (SQL Server)
SELECT TOP 10 * FROM Employees;

DISTINCT

-- Remove duplicates
SELECT DISTINCT DepartmentID FROM Employees;
-- Distinct on multiple columns
SELECT DISTINCT DepartmentID, Salary FROM Employees;

🔗 Join Operations

Sample Tables for Join Examples

-- Employees table
CREATE TABLE Employees (
 EmployeeID INT PRIMARY KEY,
 FirstName VARCHAR(50),
 LastName VARCHAR(50),
 DepartmentID INT,
 Salary DECIMAL(10,2)
);
-- Departments table
CREATE TABLE Departments (
 DeptID INT PRIMARY KEY,
 DepartmentName VARCHAR(50),
 Location VARCHAR(50)
);

INNER JOIN

-- Returns only matching records from both tables
SELECT e.FirstName, e.LastName, d.DepartmentName
FROM Employees e
INNER JOIN Departments d ON e.DepartmentID = d.DeptID;

LEFT JOIN (LEFT OUTER JOIN)

-- Returns all records from left table and matching records from right table
SELECT e.FirstName, e.LastName, d.DepartmentName
FROM Employees e
LEFT JOIN Departments d ON e.DepartmentID = d.DeptID;

RIGHT JOIN (RIGHT OUTER JOIN)

-- Returns all records from right table and matching records from left table
SELECT e.FirstName, e.LastName, d.DepartmentName
FROM Employees e
RIGHT JOIN Departments d ON e.DepartmentID = d.DeptID;

FULL OUTER JOIN

-- Returns all records from both tables
SELECT e.FirstName, e.LastName, d.DepartmentName
FROM Employees e
FULL OUTER JOIN Departments d ON e.DepartmentID = d.DeptID;

CROSS JOIN

-- Cartesian product of both tables
SELECT e.FirstName, d.DepartmentName
FROM Employees e
CROSS JOIN Departments d;

SELF JOIN

-- Join table with itself
SELECT e1.FirstName AS Employee, e2.FirstName AS Manager
FROM Employees e1
LEFT JOIN Employees e2 ON e1.ManagerID = e2.EmployeeID;

📊 Aggregate Functions

Basic Aggregate Functions

-- COUNT
SELECT COUNT(*) FROM Employees; -- Count all rows
SELECT COUNT(Email) FROM Employees; -- Count non-null emails
SELECT COUNT(DISTINCT DepartmentID) FROM Employees; -- Count unique departments
-- SUM
SELECT SUM(Salary) FROM Employees; -- Total salary
SELECT SUM(Salary) FROM Employees WHERE DepartmentID = 1;
-- AVG
SELECT AVG(Salary) FROM Employees; -- Average salary
SELECT AVG(Salary) AS AvgSalary FROM Employees;
-- MIN and MAX
SELECT MIN(Salary) AS MinSalary FROM Employees;
SELECT MAX(Salary) AS MaxSalary FROM Employees;
SELECT MIN(HireDate), MAX(HireDate) FROM Employees;

GROUP BY

-- Group by single column
SELECT DepartmentID, COUNT(*) AS EmployeeCount
FROM Employees
GROUP BY DepartmentID;
-- Group by multiple columns
SELECT DepartmentID, YEAR(HireDate) AS HireYear, COUNT(*) AS Count
FROM Employees
GROUP BY DepartmentID, YEAR(HireDate);
-- With aggregate functions
SELECT DepartmentID,
 COUNT(*) AS EmployeeCount,
 AVG(Salary) AS AvgSalary,
 SUM(Salary) AS TotalSalary
FROM Employees
GROUP BY DepartmentID;

HAVING

-- Filter groups (use HAVING instead of WHERE with aggregates)
SELECT DepartmentID, COUNT(*) AS EmployeeCount
FROM Employees
GROUP BY DepartmentID
HAVING COUNT(*) > 5;
-- Multiple conditions in HAVING
SELECT DepartmentID, AVG(Salary) AS AvgSalary
FROM Employees
GROUP BY DepartmentID
HAVING AVG(Salary) > 50000 AND COUNT(*) > 3;

📝 String Functions

Common String Functions

-- LENGTH / LEN
SELECT FirstName, LENGTH(FirstName) AS NameLength FROM Employees;
-- UPPER / LOWER
SELECT UPPER(FirstName), LOWER(LastName) FROM Employees;
-- SUBSTRING / SUBSTR
SELECT SUBSTRING(FirstName, 1, 3) AS FirstThreeChars FROM Employees;
-- CONCAT
SELECT CONCAT(FirstName, ' ', LastName) AS FullName FROM Employees;
-- TRIM
SELECT TRIM(FirstName) FROM Employees; -- Remove leading/trailing spaces
SELECT LTRIM(FirstName) FROM Employees; -- Remove leading spaces
SELECT RTRIM(FirstName) FROM Employees; -- Remove trailing spaces
-- REPLACE
SELECT REPLACE(Email, '@gmail.com', '@company.com') FROM Employees;
-- LEFT / RIGHT
SELECT LEFT(FirstName, 2) AS FirstTwo FROM Employees; -- First 2 characters
SELECT RIGHT(FirstName, 2) AS LastTwo FROM Employees; -- Last 2 characters
-- CHARINDEX / INSTR (find position of substring)
SELECT CHARINDEX('@', Email) AS AtPosition FROM Employees;

String Manipulation Examples

-- Extract domain from email
SELECT Email,
 SUBSTRING(Email, CHARINDEX('@', Email) + 1, LEN(Email)) AS Domain
FROM Employees;
-- Format names properly
SELECT CONCAT(UPPER(LEFT(FirstName, 1)), LOWER(SUBSTRING(FirstName, 2, LEN(FirstName)))) AS FormattedName
FROM Employees;
-- Check if email is valid (contains @)
SELECT FirstName, Email,
 CASE
 WHEN CHARINDEX('@', Email) > 0 THEN 'Valid'
 ELSE 'Invalid'
 END AS EmailStatus
FROM Employees;

📅 Date Functions

Current Date/Time Functions

-- Current date and time
SELECT NOW(); -- MySQL
SELECT GETDATE(); -- SQL Server
SELECT CURRENT_TIMESTAMP; -- Standard SQL
-- Current date only
SELECT CURDATE(); -- MySQL
SELECT CAST(GETDATE() AS DATE); -- SQL Server
-- Current time only
SELECT CURTIME(); -- MySQL
SELECT CAST(GETDATE() AS TIME); -- SQL Server

Date Extraction Functions

-- Extract parts of date
SELECT HireDate,
 YEAR(HireDate) AS HireYear,
 MONTH(HireDate) AS HireMonth,
 DAY(HireDate) AS HireDay,
 DAYNAME(HireDate) AS HireDayName, -- MySQL
 MONTHNAME(HireDate) AS HireMonthName -- MySQL
FROM Employees;
-- SQL Server equivalents
SELECT HireDate,
 YEAR(HireDate) AS HireYear,
 MONTH(HireDate) AS HireMonth,
 DAY(HireDate) AS HireDay,
 DATENAME(WEEKDAY, HireDate) AS HireDayName,
 DATENAME(MONTH, HireDate) AS HireMonthName
FROM Employees;

Date Arithmetic

-- Add/subtract dates (MySQL)
SELECT HireDate,
 DATE_ADD(HireDate, INTERVAL 1 YEAR) AS OneYearLater,
 DATE_SUB(HireDate, INTERVAL 30 DAY) AS ThirtyDaysEarlier
FROM Employees;
-- SQL Server equivalents
SELECT HireDate,
 DATEADD(YEAR, 1, HireDate) AS OneYearLater,
 DATEADD(DAY, -30, HireDate) AS ThirtyDaysEarlier
FROM Employees;
-- Calculate age or tenure
SELECT FirstName, HireDate,
 DATEDIFF(YEAR, HireDate, CURDATE()) AS YearsOfService -- MySQL
FROM Employees;
-- SQL Server
SELECT FirstName, HireDate,
 DATEDIFF(YEAR, HireDate, GETDATE()) AS YearsOfService
FROM Employees;

Date Formatting

-- Format dates (MySQL)
SELECT HireDate,
 DATE_FORMAT(HireDate, '%Y-%m-%d') AS ISOFormat,
 DATE_FORMAT(HireDate, '%M %d, %Y') AS USFormat
FROM Employees;
-- SQL Server
SELECT HireDate,
 FORMAT(HireDate, 'yyyy-MM-dd') AS ISOFormat,
 FORMAT(HireDate, 'MMMM dd, yyyy') AS USFormat
FROM Employees;

🔢 Mathematical Functions

Basic Math Functions

-- Absolute value
SELECT ABS(-15); -- Returns 15
-- Rounding
SELECT ROUND(123.456, 2); -- Returns 123.46
SELECT CEIL(123.45); -- Returns 124 (round up)
SELECT FLOOR(123.95); -- Returns 123 (round down)
-- Power and square root
SELECT POWER(2, 3); -- Returns 8 (2^3)
SELECT SQRT(16); -- Returns 4
-- Random number
SELECT RAND(); -- Returns random number between 0 and 1
SELECT RAND() * 100; -- Random number between 0 and 100

Mathematical Operations with Data

-- Calculate percentage
SELECT FirstName, Salary,
 ROUND((Salary / (SELECT MAX(Salary) FROM Employees)) * 100, 2) AS SalaryPercentage
FROM Employees;
-- Calculate tax (assuming 25% tax rate)
SELECT FirstName, Salary,
 ROUND(Salary * 0.25, 2) AS Tax,
 ROUND(Salary * 0.75, 2) AS NetSalary
FROM Employees;
-- Salary ranges
SELECT
 CASE
 WHEN Salary < 40000 THEN 'Low'
 WHEN Salary BETWEEN 40000 AND 70000 THEN 'Medium'
 ELSE 'High'
 END AS SalaryRange,
 COUNT(*) AS EmployeeCount
FROM Employees
GROUP BY
 CASE
 WHEN Salary < 40000 THEN 'Low'
 WHEN Salary BETWEEN 40000 AND 70000 THEN 'Medium'
 ELSE 'High'
 END;

🔀 Conditional Statements

CASE Statement

-- Simple CASE
SELECT FirstName, Salary,
 CASE
 WHEN Salary > 70000 THEN 'High'
 WHEN Salary > 50000 THEN 'Medium'
 ELSE 'Low'
 END AS SalaryCategory
FROM Employees;
-- CASE with multiple conditions
SELECT FirstName, DepartmentID, Salary,
 CASE
 WHEN DepartmentID = 1 AND Salary > 60000 THEN 'Senior Developer'
 WHEN DepartmentID = 1 THEN 'Developer'
 WHEN DepartmentID = 2 AND Salary > 55000 THEN 'Senior Analyst'
 WHEN DepartmentID = 2 THEN 'Analyst'
 ELSE 'Other'
 END AS JobTitle
FROM Employees;

IF Statement (MySQL)

-- IF function
SELECT FirstName, Salary,
 IF(Salary > 50000, 'Above Average', 'Below Average') AS SalaryStatus
FROM Employees;
-- Nested IF
SELECT FirstName, Salary,
 IF(Salary > 70000, 'High',
 IF(Salary > 50000, 'Medium', 'Low')) AS SalaryCategory
FROM Employees;

ISNULL / COALESCE

-- Handle NULL values
SELECT FirstName,
 COALESCE(Email, 'No email provided') AS EmailStatus
FROM Employees;
-- SQL Server ISNULL
SELECT FirstName,
 ISNULL(Email, 'No email provided') AS EmailStatus
FROM Employees;
-- MySQL IFNULL
SELECT FirstName,
 IFNULL(Email, 'No email provided') AS EmailStatus
FROM Employees;

🔒 Constraints

Primary Key

-- Single column primary key
CREATE TABLE Departments (
 DeptID INT PRIMARY KEY,
 DepartmentName VARCHAR(50)
);
-- Composite primary key
CREATE TABLE EmployeeProjects (
 EmployeeID INT,
 ProjectID INT,
 AssignmentDate DATE,
 PRIMARY KEY (EmployeeID, ProjectID)
);
-- Add primary key to existing table
ALTER TABLE Employees ADD PRIMARY KEY (EmployeeID);

Foreign Key

-- Foreign key constraint
CREATE TABLE Employees (
 EmployeeID INT PRIMARY KEY,
 FirstName VARCHAR(50),
 DepartmentID INT,
 FOREIGN KEY (DepartmentID) REFERENCES Departments(DeptID)
);
-- Named foreign key constraint
CREATE TABLE Employees (
 EmployeeID INT PRIMARY KEY,
 FirstName VARCHAR(50),
 DepartmentID INT,
 CONSTRAINT fk_employee_department
 FOREIGN KEY (DepartmentID) REFERENCES Departments(DeptID)
);
-- Add foreign key to existing table
ALTER TABLE Employees
ADD CONSTRAINT fk_employee_department
FOREIGN KEY (DepartmentID) REFERENCES Departments(DeptID);

Unique Constraint

-- Single column unique
CREATE TABLE Employees (
 EmployeeID INT PRIMARY KEY,
 Email VARCHAR(100) UNIQUE,
 FirstName VARCHAR(50)
);
-- Multiple column unique
CREATE TABLE Employees (
 EmployeeID INT PRIMARY KEY,
 FirstName VARCHAR(50),
 LastName VARCHAR(50),
 UNIQUE (FirstName, LastName)
);
-- Add unique constraint
ALTER TABLE Employees ADD UNIQUE (Email);

Check Constraint

-- Check constraint
CREATE TABLE Employees (
 EmployeeID INT PRIMARY KEY,
 FirstName VARCHAR(50),
 Salary DECIMAL(10,2) CHECK (Salary > 0),
 Age INT CHECK (Age >= 18 AND Age <= 65)
);
-- Named check constraint
CREATE TABLE Employees (
 EmployeeID INT PRIMARY KEY,
 FirstName VARCHAR(50),
 Salary DECIMAL(10,2),
 CONSTRAINT chk_salary CHECK (Salary > 0)
);

Not Null Constraint

CREATE TABLE Employees (
 EmployeeID INT PRIMARY KEY,
 FirstName VARCHAR(50) NOT NULL,
 LastName VARCHAR(50) NOT NULL,
 Email VARCHAR(100)
);
-- Add NOT NULL to existing column
ALTER TABLE Employees MODIFY FirstName VARCHAR(50) NOT NULL;

Default Constraint

CREATE TABLE Employees (
 EmployeeID INT PRIMARY KEY,
 FirstName VARCHAR(50) NOT NULL,
 HireDate DATE DEFAULT CURRENT_DATE,
 Status VARCHAR(20) DEFAULT 'Active',
 Salary DECIMAL(10,2) DEFAULT 50000.00
);
-- Add default to existing column
ALTER TABLE Employees ALTER COLUMN Status SET DEFAULT 'Active';

📇 Indexes

Create Index

-- Single column index
CREATE INDEX idx_lastname ON Employees(LastName);
-- Multiple column index
CREATE INDEX idx_name ON Employees(FirstName, LastName);
-- Unique index
CREATE UNIQUE INDEX idx_email ON Employees(Email);
-- Partial index (with condition)
CREATE INDEX idx_active_employees ON Employees(DepartmentID)
WHERE Status = 'Active';

Drop Index

DROP INDEX idx_lastname ON Employees; -- MySQL
DROP INDEX idx_lastname; -- SQL Server, PostgreSQL

Show Indexes

-- MySQL
SHOW INDEX FROM Employees;
-- SQL Server
SELECT * FROM sys.indexes WHERE object_id = OBJECT_ID('Employees');
-- PostgreSQL
SELECT * FROM pg_indexes WHERE tablename = 'employees';

👁️ Views

Create View

-- Simple view
CREATE VIEW EmployeeView AS
SELECT EmployeeID, FirstName, LastName, DepartmentID
FROM Employees
WHERE Status = 'Active';
-- Complex view with joins
CREATE VIEW EmployeeDepartmentView AS
SELECT e.EmployeeID, e.FirstName, e.LastName,
 d.DepartmentName, d.Location
FROM Employees e
JOIN Departments d ON e.DepartmentID = d.DeptID
WHERE e.Status = 'Active';
-- View with calculations
CREATE VIEW SalarySummaryView AS
SELECT DepartmentID,
 COUNT(*) AS EmployeeCount,
 AVG(Salary) AS AvgSalary,
 MIN(Salary) AS MinSalary,
 MAX(Salary) AS MaxSalary
FROM Employees
GROUP BY DepartmentID;

Use View

-- Query view like a table
SELECT * FROM EmployeeView;
SELECT * FROM EmployeeDepartmentView
WHERE DepartmentName = 'Engineering';

Modify View

CREATE OR REPLACE VIEW EmployeeView AS
SELECT EmployeeID, FirstName, LastName, Email, DepartmentID
FROM Employees
WHERE Status = 'Active';

Drop View

DROP VIEW EmployeeView;

🔧 Stored Procedures

Create Stored Procedure

-- Simple stored procedure (SQL Server)
CREATE PROCEDURE GetEmployeesByDepartment
 @DepartmentID INT
AS
BEGIN
 SELECT * FROM Employees
 WHERE DepartmentID = @DepartmentID;
END;
-- Stored procedure with multiple parameters
CREATE PROCEDURE GetEmployeeBySalaryRange
 @MinSalary DECIMAL(10,2),
 @MaxSalary DECIMAL(10,2)
AS
BEGIN
 SELECT FirstName, LastName, Salary, DepartmentID
 FROM Employees
 WHERE Salary BETWEEN @MinSalary AND @MaxSalary
 ORDER BY Salary DESC;
END;
-- MySQL stored procedure
DELIMITER //
CREATE PROCEDURE GetEmployeesByDepartment(IN dept_id INT)
BEGIN
 SELECT * FROM Employees
 WHERE DepartmentID = dept_id;
END //
DELIMITER ;

Execute Stored Procedure

-- SQL Server
EXEC GetEmployeesByDepartment @DepartmentID = 1;
EXEC GetEmployeeBySalaryRange @MinSalary = 50000, @MaxSalary = 80000;
-- MySQL
CALL GetEmployeesByDepartment(1);

Stored Procedure with Output Parameter

-- SQL Server
CREATE PROCEDURE GetEmployeeCount
 @DepartmentID INT,
 @EmployeeCount INT OUTPUT
AS
BEGIN
 SELECT @EmployeeCount = COUNT(*)
 FROM Employees
 WHERE DepartmentID = @DepartmentID;
END;
-- Execute with output
DECLARE @Count INT;
EXEC GetEmployeeCount @DepartmentID = 1, @EmployeeCount = @Count OUTPUT;
SELECT @Count AS EmployeeCount;

⚡ Triggers

Create Trigger

-- AFTER INSERT trigger
CREATE TRIGGER tr_employee_insert
ON Employees
AFTER INSERT
AS
BEGIN
 INSERT INTO EmployeeAudit (EmployeeID, Action, ActionDate)
 SELECT EmployeeID, 'INSERT', GETDATE()
 FROM INSERTED;
END;
-- BEFORE UPDATE trigger (MySQL)
DELIMITER //
CREATE TRIGGER tr_employee_update
BEFORE UPDATE ON Employees
FOR EACH ROW
BEGIN
 INSERT INTO EmployeeAudit (EmployeeID, OldSalary, NewSalary, Action, ActionDate)
 VALUES (NEW.EmployeeID, OLD.Salary, NEW.Salary, 'UPDATE', NOW());
END //
DELIMITER ;
-- AFTER DELETE trigger
CREATE TRIGGER tr_employee_delete
ON Employees
AFTER DELETE
AS
BEGIN
 INSERT INTO EmployeeAudit (EmployeeID, Action, ActionDate)
 SELECT EmployeeID, 'DELETE', GETDATE()
 FROM DELETED;
END;

Drop Trigger

DROP TRIGGER tr_employee_insert;

💾 Transactions

Basic Transaction Control

-- Start transaction
BEGIN TRANSACTION; -- SQL Server
START TRANSACTION; -- MySQL
BEGIN; -- PostgreSQL
-- Example transaction
BEGIN TRANSACTION;
 UPDATE Employees SET Salary = Salary * 1.05 WHERE DepartmentID = 1;
 INSERT INTO SalaryAudit (EmployeeID, OldSalary, NewSalary, ChangeDate)
 SELECT EmployeeID, Salary / 1.05, Salary, GETDATE()
 FROM Employees WHERE DepartmentID = 1;
COMMIT TRANSACTION;
-- Rollback transaction
BEGIN TRANSACTION;
 UPDATE Employees SET Salary = 0; -- Dangerous operation
 -- Oops, let's undo this
ROLLBACK TRANSACTION;

Savepoints

BEGIN TRANSACTION;
 UPDATE Employees SET Salary = Salary * 1.05;
 SAVE TRANSACTION sp1; -- Create savepoint
 DELETE FROM Employees WHERE Salary < 30000;
 -- Rollback to savepoint (keeps salary updates)
 ROLLBACK TRANSACTION sp1;
COMMIT TRANSACTION;

Transaction Isolation Levels

-- Set isolation level
SET TRANSACTION ISOLATION LEVEL READ UNCOMMITTED;
SET TRANSACTION ISOLATION LEVEL READ COMMITTED;
SET TRANSACTION ISOLATION LEVEL REPEATABLE READ;
SET TRANSACTION ISOLATION LEVEL SERIALIZABLE;

🚀 Advanced Concepts

Window Functions

-- ROW_NUMBER
SELECT FirstName, LastName, Salary,
 ROW_NUMBER() OVER (ORDER BY Salary DESC) AS SalaryRank
FROM Employees;
-- RANK and DENSE_RANK
SELECT FirstName, LastName, Salary,
 RANK() OVER (ORDER BY Salary DESC) AS SalaryRank,
 DENSE_RANK() OVER (ORDER BY Salary DESC) AS SalaryDenseRank
FROM Employees;
-- PARTITION BY
SELECT FirstName, LastName, DepartmentID, Salary,
 ROW_NUMBER() OVER (PARTITION BY DepartmentID ORDER BY Salary DESC) AS DeptRank,
 AVG(Salary) OVER (PARTITION BY DepartmentID) AS DeptAvgSalary
FROM Employees;
-- LAG and LEAD
SELECT FirstName, LastName, HireDate,
 LAG(HireDate, 1) OVER (ORDER BY HireDate) AS PreviousHire,
 LEAD(HireDate, 1) OVER (ORDER BY HireDate) AS NextHire
FROM Employees;

Common Table Expressions (CTE)

-- Simple CTE
WITH DepartmentStats AS (
 SELECT DepartmentID,
 COUNT(*) AS EmployeeCount,
 AVG(Salary) AS AvgSalary
 FROM Employees
 GROUP BY DepartmentID
)
SELECT d.DepartmentName, ds.EmployeeCount, ds.AvgSalary
FROM DepartmentStats ds
JOIN Departments d ON ds.DepartmentID = d.DeptID;
-- Recursive CTE (for hierarchical data)
WITH EmployeeHierarchy AS (
 -- Anchor: Top-level managers
 SELECT EmployeeID, FirstName, LastName, ManagerID, 1 AS Level
 FROM Employees
 WHERE ManagerID IS NULL
 UNION ALL
 -- Recursive: Employees with managers
 SELECT e.EmployeeID, e.FirstName, e.LastName, e.ManagerID, eh.Level + 1
 FROM Employees e
 JOIN EmployeeHierarchy eh ON e.ManagerID = eh.EmployeeID
)
SELECT * FROM EmployeeHierarchy
ORDER BY Level, LastName;

Subqueries

-- Scalar subquery
SELECT FirstName, LastName, Salary,
 (SELECT AVG(Salary) FROM Employees) AS AvgSalary
FROM Employees;
-- Correlated subquery
SELECT FirstName, LastName, Salary, DepartmentID
FROM Employees e1
WHERE Salary > (SELECT AVG(Salary)
 FROM Employees e2
 WHERE e2.DepartmentID = e1.DepartmentID);
-- EXISTS
SELECT FirstName, LastName
FROM Employees e
WHERE EXISTS (SELECT 1 FROM Departments d
 WHERE d.DeptID = e.DepartmentID
 AND d.DepartmentName = 'Engineering');
-- IN with subquery
SELECT FirstName, LastName
FROM Employees
WHERE DepartmentID IN (SELECT DeptID FROM Departments
 WHERE Location = 'New York');

PIVOT and UNPIVOT (SQL Server)

-- PIVOT
SELECT *
FROM (
 SELECT DepartmentID, YEAR(HireDate) AS HireYear
 FROM Employees
) AS SourceTable
PIVOT (
 COUNT(DepartmentID)
 FOR HireYear IN ([2021], [2022], [2023])
) AS PivotTable;
-- UNPIVOT
SELECT DepartmentID, HireYear, EmployeeCount
FROM (
 SELECT DepartmentID, [2021], [2022], [2023]
 FROM YearlyHires
) AS SourceTable
UNPIVOT (
 EmployeeCount FOR HireYear IN ([2021], [2022], [2023])
) AS UnpivotTable;

🔍 Query Optimization Tips

Performance Best Practices

📚 Common Interview Questions

1. Find Nth Highest Salary

-- Using ROW_NUMBER()
WITH RankedSalaries AS (
 SELECT Salary, ROW_NUMBER() OVER (ORDER BY Salary DESC) AS rn
 FROM Employees
)
SELECT Salary FROM RankedSalaries WHERE rn = 2; -- 2nd highest
-- Using LIMIT/OFFSET (MySQL)
SELECT DISTINCT Salary
FROM Employees
ORDER BY Salary DESC
LIMIT 1 OFFSET 1; -- 2nd highest

2. Find Duplicate Records

-- Find employees with same name
SELECT FirstName, LastName, COUNT(*)
FROM Employees
GROUP BY FirstName, LastName
HAVING COUNT(*) > 1;
-- Find all duplicate records
SELECT *
FROM Employees e1
WHERE EXISTS (SELECT 1 FROM Employees e2
 WHERE e1.FirstName = e2.FirstName
 AND e1.LastName = e2.LastName
 AND e1.EmployeeID != e2.EmployeeID);

3. Find Employees Without Departments

SELECT e.FirstName, e.LastName
FROM Employees e
LEFT JOIN Departments d ON e.DepartmentID = d.DeptID
WHERE d.DeptID IS NULL;

4. Calculate Running Total

SELECT EmployeeID, Salary,
 SUM(Salary) OVER (ORDER BY EmployeeID) AS RunningTotal
FROM Employees
ORDER BY EmployeeID;

5. Find Department with Highest Average Salary

SELECT TOP 1 d.DepartmentName, AVG(e.Salary) AS AvgSalary
FROM Employees e
JOIN Departments d ON e.DepartmentID = d.DeptID
GROUP BY d.DepartmentName
ORDER BY AVG(e.Salary) DESC;

🎯 Conclusion

Key Takeaways

📖 Additional Resources

📞 Contributing

🤝 How to Contribute

🍴 Fork and Contribute Process

Step 1: Fork the Repository

Step 2: Clone Your Fork

# Clone your forked repository
git clone https://github.com/YOUR_USERNAME/SQL_CheatSheet.git
# Navigate to the project directory
cd SQL_CheatSheet
# Add upstream remote to sync with original repo
git remote add upstream https://github.com/Vipul1432/SQL_CheatSheet.git

Step 3: Create a Feature Branch

# Create and switch to a new branch for your feature
git checkout -b feature/your-feature-name
# Examples:
git checkout -b feature/add-nosql-examples
git checkout -b feature/fix-typos
git checkout -b feature/add-postgresql-functions

Step 4: Make Your Changes

Step 5: Commit Your Changes

# Stage your changes
git add .
# Commit with a descriptive message
git commit -m "Add PostgreSQL-specific functions to date operations

- Include PostgreSQL date/time functions
- Add examples with expected outputs
- Update table of contents
- Fix formatting issues in mathematical functions"

Step 6: Sync with Upstream (Optional but Recommended)

# Fetch latest changes from original repository
git fetch upstream
# Merge latest changes into your branch
git merge upstream/main

Step 7: Push Your Changes

# Push your feature branch to your fork
git push origin feature/your-feature-name

Step 8: Create a Pull Request

📋 Contribution Guidelines

✅ What We Accept

❌ What We Don't Accept

📝 Style Guidelines

🐛 Reporting Issues

Creating a Good Issue Report

Issue Template

**Section**: [e.g., Date Functions, JOIN Operations]
**Database**: [e.g., SQL Server, MySQL, PostgreSQL]
**Issue Type**: [Bug, Enhancement, Question]
**Description**:
Clear description of the issue or suggestion
**Current Behavior**:
What currently happens
**Expected Behavior**:
What should happen instead
**Additional Context**:
Any other relevant information

🚀 Development Setup

Prerequisites

Testing Your Changes

🏆 Recognition

📞 Contact