Basic Queries

Q1: Select all employees whose salary is more than 50000

Explanation: This is a basic filtering query using the WHERE clause to filter rows based on a condition. The > operator compares numeric values.

SELECT * FROM employee WHERE salary > 50000;

Key Points:

• SELECT * returns all columns
• WHERE filters rows before return
• Comparison operators: >, <, >=, <=, =, <> or !=

Q2: Get all customers from the customers table who live in 'New York'

Explanation: String comparison in SQL requires quotes around string literals. This filters rows where the city column matches exactly 'New York'.

SELECT * FROM customers WHERE city = 'New York';

Variations:

-- Case-insensitive search (PostgreSQL)
SELECT * FROM customers WHERE city ILIKE 'new york';

-- Case-insensitive (MySQL)
SELECT * FROM customers WHERE LOWER(city) = LOWER('New York');

Q3: Find the total number of orders in the orders table

Explanation: The COUNT() aggregate function returns the number of rows. COUNT(*) counts all rows including those with NULL values.

SELECT COUNT(*) FROM orders;

Alternatives:

-- Count non-null values in specific column
SELECT COUNT(order_id) FROM orders;

-- Count distinct values
SELECT COUNT(DISTINCT customer_id) FROM orders;

Q4: List all products with a price greater than $50, sorted by price (highest first)

Explanation: Combines filtering with WHERE and sorting with ORDER BY. DESC sorts in descending order (highest first).

SELECT * FROM products 
WHERE price > 50 
ORDER BY price DESC;

Key Points:

• ORDER BY defaults to ascending (ASC) if not specified
• Can sort by multiple columns: ORDER BY price DESC, name ASC

---

Filtering and Conditions

Q5: Find duplicate email addresses in the users table

Explanation: This query uses GROUP BY to group rows by email, then HAVING to filter groups with count > 1, identifying duplicates.

SELECT email, COUNT(*) as duplicate_count
FROM users
GROUP BY email
HAVING COUNT(*) > 1;

Theoretical Explanation:

• GROUP BY creates groups of rows with same email
• COUNT(*) counts rows in each group
• HAVING filters groups (unlike WHERE which filters rows before grouping)
• Groups with count > 1 have duplicates

Q6: Fetch all employees who joined after January 1, 2023

Explanation: Date comparison in SQL. Note that date formats can vary by database system.

-- Standard SQL
SELECT * FROM employees 
WHERE joined_at > '2023-01-01';

-- MySQL
SELECT * FROM employees 
WHERE joined_at > '2023-01-01';

-- PostgreSQL
SELECT * FROM employees 
WHERE joined_at > '2023-01-01'::date;

-- SQL Server
SELECT * FROM employees 
WHERE joined_at > '2023-01-01';

Date Functions:

-- Extract year
SELECT * FROM employees WHERE YEAR(joined_at) = 2023;

-- Date arithmetic
SELECT * FROM employees WHERE joined_at > DATE_SUB(CURDATE(), INTERVAL 1 YEAR);

Q7: Find employees whose names start with 'A'

Explanation: The LIKE operator with pattern matching. % is a wildcard for any number of characters.

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

Pattern Matching:

• 'A%' - Starts with A
• '%a' - Ends with a
• '%a%' - Contains a anywhere
• 'A_' - Starts with A, exactly 2 letters (underscore is single character wildcard)

Case-Insensitive:

-- PostgreSQL
SELECT * FROM employees WHERE name ILIKE 'a%';

-- MySQL (default case-insensitive)
SELECT * FROM employees WHERE name LIKE 'a%';

-- SQL Server
SELECT * FROM employees WHERE name LIKE 'a%' COLLATE SQL_Latin1_General_CP1_CI_AS;

---

Aggregation and Grouping

Q8: Count the number of employees in each department

Explanation: Groups employees by department and counts them. Shows fundamental GROUP BY usage.

SELECT department_id, COUNT(*) as employee_count
FROM employees
GROUP BY department_id;

With Department Names:

SELECT d.department_name, COUNT(e.employee_id) as employee_count
FROM employees e
JOIN departments d ON e.department_id = d.id
GROUP BY d.department_name;

Q9: Get the average salary of employees in each department

Explanation: Using AVG() aggregate function with GROUP BY to calculate department-wise averages.

SELECT department_id, AVG(salary) as avg_salary
FROM employees
GROUP BY department_id;

With Formatting:

SELECT 
    department_id,
    ROUND(AVG(salary), 2) as avg_salary,
    MIN(salary) as min_salary,
    MAX(salary) as max_salary
FROM employees
GROUP BY department_id;

Q10: Count the number of employees in each department and sort by count

Explanation: Adds ORDER BY to the GROUP BY query, sorting by the aggregated column.

SELECT department_id, COUNT(*) as employee_count
FROM employees
GROUP BY department_id
ORDER BY employee_count DESC;

With Department Names:

SELECT 
    d.department_name,
    COUNT(e.employee_id) as employee_count
FROM employees e
JOIN departments d ON e.department_id = d.id
GROUP BY d.department_name
ORDER BY employee_count DESC;

Q11: Find departments with more than 5 employees

Explanation: Uses HAVING to filter groups after aggregation. WHERE can't be used with aggregate functions.

-- Simple version
SELECT department_id
FROM employees
GROUP BY department_id
HAVING COUNT(*) > 5;

-- With department details
SELECT d.*, COUNT(e.employee_id) as employee_count
FROM departments d
JOIN employees e ON d.id = e.department_id
GROUP BY d.id, d.department_name
HAVING COUNT(e.employee_id) > 5;

Theoretical Distinction:

• WHERE filters rows before grouping
• HAVING filters groups after grouping
• HAVING can use aggregate functions, WHERE cannot

---

Joins

Q12: Find employees who work in the 'IT' department

Explanation: Demonstrates INNER JOIN between employees and departments tables.

SELECT e.*, d.department_name
FROM employees e
JOIN departments d ON e.department_id = d.id
WHERE d.department_name = 'IT';

Different Join Types:

-- INNER JOIN (default) - only matching rows
SELECT * FROM employees e
INNER JOIN departments d ON e.department_id = d.id;

-- LEFT JOIN - all employees, even without department
SELECT * FROM employees e
LEFT JOIN departments d ON e.department_id = d.id;

-- RIGHT JOIN - all departments, even without employees
SELECT * FROM employees e
RIGHT JOIN departments d ON e.department_id = d.id;

-- FULL OUTER JOIN - all records from both tables
SELECT * FROM employees e
FULL OUTER JOIN departments d ON e.department_id = d.id;

Q13: List employees along with their department names

Explanation: Basic JOIN to combine employee and department information.

SELECT 
    e.employee_id,
    e.name as employee_name,
    d.department_name
FROM employees e
JOIN departments d ON e.department_id = d.id;

Including Employees Without Departments:

SELECT 
    e.employee_id,
    e.name as employee_name,
    COALESCE(d.department_name, 'No Department') as department_name
FROM employees e
LEFT JOIN departments d ON e.department_id = d.id;

Q14: Get all orders along with the customer's name who placed them

Explanation: JOIN between orders and customers tables using foreign key.

SELECT 
    o.order_id,
    o.order_date,
    o.total_amount,
    c.name as customer_name,
    c.email as customer_email
FROM orders o
JOIN customers c ON o.customer_id = c.id;

Q15: List employees who have not been assigned any project

Explanation: Uses LEFT JOIN and NULL check to find employees without matching projects.

-- Method 1: LEFT JOIN with IS NULL
SELECT e.*
FROM employees e
LEFT JOIN projects p ON e.id = p.employee_id
WHERE p.employee_id IS NULL;

-- Method 2: NOT EXISTS (often more efficient)
SELECT e.*
FROM employees e
WHERE NOT EXISTS (
    SELECT 1 FROM projects p WHERE p.employee_id = e.id
);

-- Method 3: NOT IN (careful with NULLs)
SELECT e.*
FROM employees e
WHERE e.id NOT IN (
    SELECT employee_id FROM projects WHERE employee_id IS NOT NULL
);

Performance Note:

• NOT EXISTS is usually fastest for this pattern
• LEFT JOIN ... IS NULL is readable
• NOT IN can be slow with large datasets

Q16: Find products that have never been ordered

Explanation: Similar pattern to above, finding products without any order items.

-- Using LEFT JOIN
SELECT p.*
FROM products p
LEFT JOIN order_items oi ON p.id = oi.product_id
WHERE oi.product_id IS NULL;

-- Using NOT EXISTS
SELECT p.*
FROM products p
WHERE NOT EXISTS (
    SELECT 1 FROM order_items oi WHERE oi.product_id = p.id
);

Q17: Count how many orders each customer has placed

Explanation: Groups by customer after joining with orders.

SELECT 
    c.id,
    c.name,
    c.email,
    COUNT(o.id) as order_count
FROM customers c
LEFT JOIN orders o ON c.id = o.customer_id
GROUP BY c.id, c.name, c.email
ORDER BY order_count DESC;

Q18: Find employees and their managers

Explanation: Self-join on employees table where manager_id references employee_id.

SELECT 
    e.name AS employee_name,
    m.name AS manager_name
FROM employees e
LEFT JOIN employees m ON e.manager_id = m.id;

-- With additional details
SELECT 
    e.name AS employee_name,
    e.position AS employee_position,
    m.name AS manager_name,
    m.position AS manager_position
FROM employees e
LEFT JOIN employees m ON e.manager_id = m.id
ORDER BY m.name, e.name;

Theoretical Concept:

• Self-join: joining a table to itself
• Requires table aliases to distinguish between the two roles
• LEFT JOIN ensures employees without managers are still shown

---

Subqueries

Q19: Second highest salary

Explanation: Multiple approaches to find the second highest salary, each with different trade-offs.

-- Method 1: Using LIMIT and OFFSET
SELECT * FROM employees 
ORDER BY salary DESC 
LIMIT 1 OFFSET 1;

-- Method 2: Using MAX with subquery (handles ties differently)
SELECT MAX(salary) as second_highest_salary
FROM employees
WHERE salary < (SELECT MAX(salary) FROM employees);

-- Method 3: Using DENSE_RANK (handles ties properly)
SELECT * FROM (
    SELECT *, DENSE_RANK() OVER (ORDER BY salary DESC) as rank
    FROM employees
) ranked
WHERE rank = 2;

-- Method 4: For nth highest (generalized)
SELECT * FROM employees e1
WHERE (n-1) = (
    SELECT COUNT(DISTINCT salary)
    FROM employees e2
    WHERE e2.salary > e1.salary
);

Theoretical Comparison:

• LIMIT/OFFSET: Simple but doesn't handle ties well
• MAX with subquery: Returns a single value, handles ties by ignoring them
• DENSE_RANK: Handles ties properly (both employees get rank 2)
• Correlated subquery: Most flexible for nth highest

Q20: Find employees who earn more than the average salary

Explanation: Subquery in WHERE clause to compare with overall average.

SELECT *
FROM employees
WHERE salary > (SELECT AVG(salary) FROM employees);

-- With department average (correlated subquery)
SELECT e1.*
FROM employees e1
WHERE salary > (
    SELECT AVG(salary)
    FROM employees e2
    WHERE e2.department_id = e1.department_id
);

Q21: Find employees who earn more than their department's average salary

Explanation: Correlated subquery that references the outer query for each row.

SELECT e1.employee_id, e1.name, e1.salary, e1.department_id
FROM employees e1
WHERE e1.salary > (
    SELECT AVG(e2.salary)
    FROM employees e2
    WHERE e2.department_id = e1.department_id
);

-- Alternative with JOIN (sometimes faster)
WITH dept_avg AS (
    SELECT department_id, AVG(salary) as avg_salary
    FROM employees
    GROUP BY department_id
)
SELECT e.*
FROM employees e
JOIN dept_avg da ON e.department_id = da.department_id
WHERE e.salary > da.avg_salary;

---

String and Date Operations

Q22: Find employees whose names start with 'A' (detailed)

Explanation: Various string pattern matching techniques across different databases.

-- Basic LIKE
SELECT * FROM employees WHERE name LIKE 'A%';

-- Case-insensitive
SELECT * FROM employees WHERE LOWER(name) LIKE 'a%';

-- Regular expressions (PostgreSQL)
SELECT * FROM employees WHERE name ~ '^A';

-- Regular expressions (MySQL)
SELECT * FROM employees WHERE name REGEXP '^A';

-- Using LEFT function
SELECT * FROM employees WHERE LEFT(name, 1) = 'A';

Q23: Fetch all employees who joined after a specific date (with date functions)

Explanation: Working with dates and using date functions for flexibility.

-- Basic comparison
SELECT * FROM employees WHERE hire_date > '2023-01-01';

-- Extract year
SELECT * FROM employees WHERE YEAR(hire_date) = 2023;

-- Extract month and year
SELECT * FROM employees 
WHERE YEAR(hire_date) = 2023 AND MONTH(hire_date) = 1;

-- Date arithmetic (employees hired in last 30 days)
SELECT * FROM employees 
WHERE hire_date > DATE_SUB(CURDATE(), INTERVAL 30 DAY);

-- Between two dates
SELECT * FROM employees 
WHERE hire_date BETWEEN '2023-01-01' AND '2023-12-31';

-- Format date in output
SELECT 
    name,
    DATE_FORMAT(hire_date, '%M %d, %Y') as formatted_hire_date
FROM employees;

---

Advanced Queries

Q24: Second highest salary (detailed with all approaches)

Explanation: Comprehensive comparison of all methods to find nth highest salary.

-- Sample data setup
CREATE TABLE employees (
    id INT PRIMARY KEY,
    name VARCHAR(50),
    salary DECIMAL(10,2)
);

INSERT INTO employees VALUES
(1, 'Alice', 100000),
(2, 'Bob', 90000),
(3, 'Charlie', 90000),
(4, 'David', 80000),
(5, 'Eve', 80000);

-- Method 1: LIMIT with OFFSET (simplest)
-- Problem: Doesn't handle ties well, returns only one row
SELECT * FROM employees 
ORDER BY salary DESC 
LIMIT 1 OFFSET 1;
-- Result: Bob (90000) - misses Charlie who also has 90000

-- Method 2: MAX with subquery
-- Problem: Returns a single value, not employee details
SELECT MAX(salary) as second_highest
FROM employees
WHERE salary < (SELECT MAX(salary) FROM employees);
-- Result: 90000

-- Method 3: Using DENSE_RANK (handles ties properly)
WITH ranked_employees AS (
    SELECT *,
        DENSE_RANK() OVER (ORDER BY salary DESC) as rank
    FROM employees
)
SELECT * FROM ranked_employees
WHERE rank = 2;
-- Result: Bob and Charlie (both with rank 2)

-- Method 4: Using ROW_NUMBER (different tie handling)
WITH ranked_employees AS (
    SELECT *,
        ROW_NUMBER() OVER (ORDER BY salary DESC) as row_num
    FROM employees
)
SELECT * FROM ranked_employees
WHERE row_num = 2;
-- Result: Bob only (arbitrary choice between ties)

-- Method 5: Correlated subquery for nth highest
-- Find 2nd highest
SELECT DISTINCT salary
FROM employees e1
WHERE 2 = (
    SELECT COUNT(DISTINCT salary)
    FROM employees e2
    WHERE e2.salary >= e1.salary
);
-- Result: 90000

-- Method 6: Get complete employee details for nth highest
SELECT *
FROM employees
WHERE salary = (
    SELECT DISTINCT salary
    FROM employees e1
    WHERE 2 = (
        SELECT COUNT(DISTINCT salary)
        FROM employees e2
        WHERE e2.salary >= e1.salary
    )
);
-- Result: Bob and Charlie

Q25: Find duplicate email addresses with details

Explanation: Advanced duplicate detection with additional information.

-- Basic duplicate count
SELECT email, COUNT(*) as duplicate_count
FROM users
GROUP BY email
HAVING COUNT(*) > 1;

-- Get all details of duplicate records
WITH duplicate_emails AS (
    SELECT email
    FROM users
    GROUP BY email
    HAVING COUNT(*) > 1
)
SELECT u.*
FROM users u
JOIN duplicate_emails de ON u.email = de.email
ORDER BY u.email, u.id;

-- Mark duplicates with row numbers
SELECT *,
    COUNT(*) OVER (PARTITION BY email) as email_count,
    ROW_NUMBER() OVER (PARTITION BY email ORDER BY id) as email_instance
FROM users;

-- Find and keep oldest record, mark others as duplicates
WITH numbered AS (
    SELECT *,
        ROW_NUMBER() OVER (PARTITION BY email ORDER BY created_at) as rn
    FROM users
)
SELECT *,
    CASE WHEN rn = 1 THEN 'Original' ELSE 'Duplicate' END as status
FROM numbered;

Q26: Pagination queries

Explanation: Different ways to implement pagination across databases.

-- MySQL, PostgreSQL, SQLite (LIMIT/OFFSET)
SELECT * FROM products
ORDER BY id
LIMIT 10 OFFSET 20;  -- Page 3 with 10 items per page

-- SQL Server (OFFSET/FETCH)
SELECT * FROM products
ORDER BY id
OFFSET 20 ROWS
FETCH NEXT 10 ROWS ONLY;

-- Oracle (ROWNUM)
SELECT * FROM (
    SELECT p.*, ROWNUM rnum
    FROM (
        SELECT * FROM products ORDER BY id
    ) p
    WHERE ROWNUM <= 30
)
WHERE rnum > 20;

-- Keyset pagination (more efficient for large datasets)
-- Get next page after last_id
SELECT * FROM products
WHERE id > last_id
ORDER BY id
LIMIT 10;

-- Get previous page before first_id
SELECT * FROM products
WHERE id < first_id
ORDER BY id DESC
LIMIT 10;

---

Common Table Expressions (CTEs)

Q27: Find employees who earn more than their department's average (using CTE)

Explanation: CTEs make complex queries more readable by breaking them into steps.

WITH dept_average AS (
    SELECT 
        department_id,
        AVG(salary) as avg_salary
    FROM employees
    GROUP BY department_id
)
SELECT 
    e.employee_id,
    e.name,
    e.salary,
    e.department_id,
    da.avg_salary,
    e.salary - da.avg_salary as salary_difference
FROM employees e
JOIN dept_average da ON e.department_id = da.department_id
WHERE e.salary > da.avg_salary
ORDER BY salary_difference DESC;

Q28: Hierarchical query for employee-manager chain

Explanation: Recursive CTE to traverse employee hierarchy.

-- PostgreSQL, SQL Server, Oracle (with recursion)
WITH RECURSIVE employee_hierarchy AS (
    -- Anchor: start with CEO (no manager)
    SELECT 
        id,
        name,
        manager_id,
        1 as level,
        name as path
    FROM employees
    WHERE manager_id IS NULL
    
    UNION ALL
    
    -- Recursive: add direct reports
    SELECT 
        e.id,
        e.name,
        e.manager_id,
        h.level + 1,
        h.path || ' -> ' || e.name
    FROM employees e
    JOIN employee_hierarchy h ON e.manager_id = h.id
)
SELECT * FROM employee_hierarchy
ORDER BY level, name;

-- Find all employees under a specific manager
WITH RECURSIVE team AS (
    SELECT id, name, manager_id
    FROM employees
    WHERE id = 5  -- Starting manager
    
    UNION ALL
    
    SELECT e.id, e.name, e.manager_id
    FROM employees e
    JOIN team t ON e.manager_id = t.id
)
SELECT * FROM team;

Q29: Running totals and cumulative sums

Explanation: Using CTEs for cumulative calculations.

-- Monthly sales with running total
WITH monthly_sales AS (
    SELECT 
        DATE_TRUNC('month', order_date) as month,
        SUM(amount) as total_sales
    FROM orders
    GROUP BY DATE_TRUNC('month', order_date)
)
SELECT 
    month,
    total_sales,
    SUM(total_sales) OVER (ORDER BY month) as running_total,
    AVG(total_sales) OVER (ORDER BY month ROWS BETWEEN 2 PRECEDING AND CURRENT ROW) as moving_avg_3months
FROM monthly_sales
ORDER BY month;

---

Window Functions

Q30: Rank employees by salary within department

Explanation: Window functions allow ranking without grouping.

SELECT 
    employee_id,
    name,
    department_id,
    salary,
    RANK() OVER (PARTITION BY department_id ORDER BY salary DESC) as rank_in_dept,
    DENSE_RANK() OVER (PARTITION BY department_id ORDER BY salary DESC) as dense_rank_in_dept,
    ROW_NUMBER() OVER (PARTITION BY department_id ORDER BY salary DESC) as row_num_in_dept,
    PERCENT_RANK() OVER (PARTITION BY department_id ORDER BY salary DESC) as percentile
FROM employees;

-- Get top 2 highest paid employees per department
WITH ranked_employees AS (
    SELECT *,
        ROW_NUMBER() OVER (PARTITION BY department_id ORDER BY salary DESC) as rn
    FROM employees
)
SELECT *
FROM ranked_employees
WHERE rn <= 2;

Q31: Calculate running totals and moving averages

Explanation: Window functions for time-series analysis.

SELECT 
    order_date,
    amount,
    SUM(amount) OVER (ORDER BY order_date) as running_total,
    SUM(amount) OVER (ORDER BY order_date ROWS UNBOUNDED PRECEDING) as total_to_date,
    AVG(amount) OVER (ORDER BY order_date ROWS BETWEEN 6 PRECEDING AND CURRENT ROW) as moving_avg_7days,
    amount - LAG(amount, 1) OVER (ORDER BY order_date) as day_over_day_change,
    amount - AVG(amount) OVER (PARTITION BY EXTRACT(MONTH FROM order_date)) as diff_from_month_avg
FROM orders;

Q32: Find employees with salary above department average (using window functions)

Explanation: Window functions can calculate aggregates without collapsing rows.

SELECT *
FROM (
    SELECT *,
        AVG(salary) OVER (PARTITION BY department_id) as dept_avg_salary,
        COUNT(*) OVER (PARTITION BY department_id) as dept_size
    FROM employees
) emp_with_dept_stats
WHERE salary > dept_avg_salary;

-- Also show how much above average
SELECT 
    name,
    department_id,
    salary,
    dept_avg_salary,
    ROUND((salary - dept_avg_salary) / dept_avg_salary * 100, 2) as pct_above_avg
FROM (
    SELECT *,
        AVG(salary) OVER (PARTITION BY department_id) as dept_avg_salary
    FROM employees
) emp_with_dept_stats
WHERE salary > dept_avg_salary
ORDER BY pct_above_avg DESC;

---

Indexing and Performance

Q33: Explain when to use different JOIN types

Explanation: Theoretical explanation of JOIN types and their performance implications.

INNER JOIN:

• Returns only matching rows from both tables
• Most efficient when you only need related data
• Use when NULL foreign keys should be excluded

LEFT JOIN:

• Returns all rows from left table, matching from right
• Right table columns will be NULL for non-matches
• Use when you need all records from main table

RIGHT JOIN:

• Opposite of LEFT JOIN, rarely used
• Can always be rewritten as LEFT JOIN

FULL OUTER JOIN:

• Returns all rows from both tables
• NULLs where no match exists
• Use for finding orphans in both tables

CROSS JOIN:

• Cartesian product (every row with every row)
• Usually accidental, rarely intentional
• Use only when explicitly needed

Performance Considerations:

• INNER JOIN is fastest
• LEFT JOIN needs careful indexing
• FULL OUTER JOIN is expensive
• Always join on indexed columns

Q34: Query optimization techniques

Explanation: How to write efficient SQL queries.

-- BAD: Using functions on indexed columns
SELECT * FROM employees 
WHERE YEAR(hire_date) = 2023;
-- Index on hire_date can't be used

-- GOOD: Sargable query (Search ARGument ABLE)
SELECT * FROM employees 
WHERE hire_date >= '2023-01-01' 
  AND hire_date < '2024-01-01';

-- BAD: SELECT * with unnecessary columns
SELECT * FROM orders WHERE customer_id = 123;

-- GOOD: Select only needed columns
SELECT order_id, order_date, total_amount 
FROM orders WHERE customer_id = 123;

-- BAD: N+1 queries (in application code)
-- for each customer, query their orders separately

-- GOOD: Use JOIN or subquery
SELECT c.name, COUNT(o.id) as order_count
FROM customers c
LEFT JOIN orders o ON c.id = o.customer_id
GROUP BY c.id, c.name;

-- BAD: DISTINCT to remove duplicates from bad JOIN
SELECT DISTINCT c.*
FROM customers c
JOIN orders o ON c.id = o.customer_id;

-- GOOD: Use EXISTS to avoid duplication
SELECT c.*
FROM customers c
WHERE EXISTS (
    SELECT 1 FROM orders o WHERE o.customer_id = c.id
);

Q35: Explain execution plan and query analysis

Explanation: Understanding how databases execute queries.

-- PostgreSQL: Get execution plan
EXPLAIN SELECT * FROM employees WHERE salary > 50000;

-- With actual execution statistics
EXPLAIN ANALYZE SELECT * FROM employees WHERE salary > 50000;

-- MySQL
EXPLAIN SELECT * FROM employees WHERE salary > 50000;

-- SQL Server
SET SHOWPLAN_XML ON;
GO
SELECT * FROM employees WHERE salary > 50000;

-- Oracle
EXPLAIN PLAN FOR
SELECT * FROM employees WHERE salary > 50000;

SELECT * FROM TABLE(DBMS_XPLAN.DISPLAY);

What to look for in execution plans:

• Seq Scan (Table Scan): Reading entire table (bad for large tables)
• Index Scan: Using index efficiently (good)
• Bitmap Heap Scan: Combining multiple indexes
• Nested Loop: Joining by looping (bad for large datasets)
• Hash Join: Building hash table for join (good for large datasets)
• Merge Join: Sorting then merging (good for sorted data)
• Sort: Expensive operation, avoid if possible

---