SQL Query Cheat Sheet

-- Select all columns
SELECT * FROM employees;

-- Select specific columns
SELECT first_name, last_name, email FROM employees;

-- Select with alias
SELECT first_name AS "First Name", 
       last_name AS "Last Name"
FROM employees;

-- Basic filtering
SELECT * FROM employees 
WHERE department = 'Engineering';

-- Multiple conditions
SELECT * FROM employees 
WHERE department = 'Engineering' 
  AND salary > 80000;

-- Pattern matching with LIKE
SELECT * FROM employees 
WHERE email LIKE '%@company.com';

-- IN operator
SELECT * FROM employees 
WHERE department IN ('Engineering', 'Marketing');

-- BETWEEN operator
SELECT * FROM employees 
WHERE salary BETWEEN 50000 AND 100000;

SELECT e.first_name, e.last_name, d.department_name
FROM employees e
INNER JOIN departments d ON e.department_id = d.id;

SELECT e.first_name, e.last_name, d.department_name
FROM employees e
LEFT JOIN departments d ON e.department_id = d.id;

SELECT e.first_name, e.last_name, d.department_name
FROM employees e
FULL OUTER JOIN departments d ON e.department_id = d.id;

SELECT 
    COUNT(*) AS total_employees,
    AVG(salary) AS average_salary,
    MIN(salary) AS min_salary,
    MAX(salary) AS max_salary,
    SUM(salary) AS total_salary
FROM employees;

SELECT department, 
       COUNT(*) AS employee_count,
       AVG(salary) AS avg_salary
FROM employees
GROUP BY department;

-- Employees with above-average salary
SELECT first_name, last_name, salary
FROM employees
WHERE salary > (SELECT AVG(salary) FROM employees);

-- Employees who have orders
SELECT first_name, last_name
FROM employees e
WHERE EXISTS (
    SELECT 1 FROM orders o 
    WHERE o.employee_id = e.id
);

-- Insert single row
INSERT INTO employees (first_name, last_name, email, department)
VALUES ('John', 'Doe', 'john@company.com', 'Engineering');

-- Insert multiple rows
INSERT INTO employees (first_name, last_name, email, department)
VALUES 
    ('Jane', 'Smith', 'jane@company.com', 'Marketing'),
    ('Bob', 'Johnson', 'bob@company.com', 'Sales');

-- Update specific records
UPDATE employees 
SET salary = salary * 1.1 
WHERE department = 'Engineering';

CREATE TABLE employees (
    id INT PRIMARY KEY AUTO_INCREMENT,
    first_name VARCHAR(50) NOT NULL,
    last_name VARCHAR(50) NOT NULL,
    email VARCHAR(100) UNIQUE,
    department VARCHAR(50) DEFAULT 'General',
    salary DECIMAL(10,2) CHECK (salary >= 0),
    hire_date DATE NOT NULL,
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP
);

-- Single column index
CREATE INDEX idx_email ON employees(email);

-- Composite index
CREATE INDEX idx_dept_salary ON employees(department, salary);

SELECT 
    first_name,
    UPPER(first_name) AS upper_name,
    LOWER(first_name) AS lower_name,
    LENGTH(first_name) AS name_length,
    CONCAT(first_name, ' ', last_name) AS full_name,
    SUBSTRING(email, 1, 5) AS email_prefix
FROM employees;

SELECT 
    hire_date,
    CURDATE() AS today,
    DATEDIFF(CURDATE(), hire_date) AS days_employed,
    DATE_ADD(hire_date, INTERVAL 1 YEAR) AS anniversary
FROM employees;

SELECT 
    first_name,
    last_name,
    salary,
    ROW_NUMBER() OVER (ORDER BY salary DESC) AS salary_rank
FROM employees;

SELECT 
    first_name,
    last_name,
    salary,
    SUM(salary) OVER (ORDER BY hire_date) AS running_total,
    AVG(salary) OVER (PARTITION BY department) AS dept_avg
FROM employees;

WITH department_stats AS (
    SELECT 
        department,
        COUNT(*) AS emp_count,
        AVG(salary) AS avg_salary
    FROM employees
    GROUP BY department
)
SELECT * FROM department_stats 
WHERE emp_count > 5
ORDER BY avg_salary DESC;

-- 1. Use EXISTS instead of IN for subqueries
SELECT * FROM employees e
WHERE EXISTS (SELECT 1 FROM orders o WHERE o.employee_id = e.id);

-- 2. Avoid SELECT * - specify only needed columns
SELECT id, first_name, last_name FROM employees;

-- 3. Use LIMIT to test queries
SELECT * FROM employees LIMIT 10;

-- 4. Use JOINs instead of subqueries when possible
SELECT e.first_name, d.department_name
FROM employees e
JOIN departments d ON e.department_id = d.id;

-- 5. Analyze query execution plan
EXPLAIN SELECT * FROM employees WHERE department = 'Engineering';