SQL Quick Reference Cheat Sheet

-- Basic Query Structure
SELECT DISTINCT column1, column2, COUNT(*)
FROM table_name
WHERE condition
GROUP BY column1, column2
HAVING aggregate_condition
ORDER BY column1 ASC, column2 DESC
LIMIT 10 OFFSET 0;

-- Pattern Matching & Ranges
SELECT * FROM Employees WHERE LastName LIKE 'Sm%';         -- Starts with "Sm"
SELECT * FROM Employees WHERE Age BETWEEN 25 AND 40;       -- Inclusive range
SELECT * FROM Employees WHERE DepartmentID IN (1, 3, 5);   -- List matching
SELECT * FROM Employees WHERE ManagerID IS NULL;           -- NULL check

-- Inner Join (Only matching records in both tables)
SELECT A.col1, B.col2
FROM TableA A
INNER JOIN TableB B ON A.id = B.a_id;

-- Left Join (All records from Left table + matching from Right)
SELECT A.col1, B.col2
FROM TableA A
LEFT JOIN TableB B ON A.id = B.a_id;

-- Right Join (All records from Right table + matching from Left)
SELECT A.col1, B.col2
FROM TableA A
RIGHT JOIN TableB B ON A.id = B.a_id;

-- Full Outer Join (All records from both tables)
SELECT A.col1, B.col2
FROM TableA A
FULL JOIN TableB B ON A.id = B.a_id;

-- Insert Single Row
INSERT INTO Customers (CustomerName, Email, Country)
VALUES ('Jane Doe', 'jane@example.com', 'USA');

-- Insert Multiple Rows
INSERT INTO Customers (CustomerName, Email, Country)
VALUES 
    ('John Smith', 'john@example.com', 'Canada'),
    ('Alex Wong', 'alex@example.com', 'UK');

-- Update Records (Always verify WHERE clause!)
UPDATE Customers
SET Email = 'j.doe@example.com', Country = 'USA'
WHERE CustomerID = 101;

-- Delete Records
DELETE FROM Customers
WHERE CustomerID = 101;

-- Create Table with Constraints
CREATE TABLE Orders (
    OrderID      INT PRIMARY KEY,
    CustomerID   INT NOT NULL,
    OrderDate    DATE DEFAULT CURRENT_DATE,
    TotalAmount  DECIMAL(10, 2) CHECK (TotalAmount >= 0),
    Status       VARCHAR(20) DEFAULT 'Pending',
    FOREIGN KEY (CustomerID) REFERENCES Customers(CustomerID)
);

-- Modify Table Structure
ALTER TABLE Customers ADD Phone VARCHAR(20);             -- Add Column
ALTER TABLE Customers DROP COLUMN Phone;                 -- Drop Column
ALTER TABLE Customers RENAME COLUMN Email TO WorkEmail;  -- Rename Column (Postgres/MySQL 8+)

-- Drop or Truncate Table
TRUNCATE TABLE TempOrders;                               -- Fast row removal (retains structure)
DROP TABLE IF EXISTS TempOrders;                         -- Complete table deletion

-- Conditional Branching (CASE)
SELECT 
    ProductName,
    Price,
    CASE 
        WHEN Price >= 100 THEN 'Premium'
        WHEN Price >= 50  THEN 'Mid-Range'
        ELSE 'Budget'
    END AS PriceCategory
FROM Products;

-- Handling NULLs
SELECT COALESCE(Phone, Mobile, 'No Phone Provided') AS ContactNumber FROM Users;

-- Common String & Aggregate Functions
SELECT LOWER(FirstName), UPPER(LastName), LENGTH(Email) FROM Users;
SELECT COUNT(*), SUM(TotalAmount), AVG(TotalAmount), MIN(TotalAmount), MAX(TotalAmount) FROM Orders;

-- Set Operations
SELECT Email FROM Customers
UNION                                                   -- UNION ALL includes duplicates
SELECT Email FROM Employees;

-- Common Table Expression (CTE)
WITH RegionalSales AS (
    SELECT Region, SUM(Amount) AS TotalSales
    FROM Sales
    GROUP BY Region
)
SELECT Region, TotalSales
FROM RegionalSales
WHERE TotalSales > 100000;