SQL Interview Mastery: Days 3-10 Complete Learning Guide

After covering the fundamentals in days 1-2, it's time to dive deeper into the SQL concepts that frequently appear in technical interviews. This comprehensive guide will take you through advanced querying techniques, data manipulation, and complex problem-solving scenarios.

Day 3: Advanced Filtering and Pattern Matching

LIKE Operator and Wildcards

The LIKE operator is essential for pattern matching in SQL interviews.

-- Find employees whose names start with 'J'
SELECT * FROM employees 
WHERE first_name LIKE 'J%';

-- Find emails containing 'gmail'
SELECT * FROM users 
WHERE email LIKE '%gmail%';

-- Find phone numbers with specific pattern (XXX-XXX-XXXX)
SELECT * FROM customers 
WHERE phone LIKE '___-___-____';

IN and NOT IN Operators

-- Find employees in specific departments
SELECT * FROM employees 
WHERE department_id IN (1, 3, 5);

-- Find products not in discontinued categories
SELECT * FROM products 
WHERE category_id NOT IN (
    SELECT category_id FROM categories 
    WHERE status = 'discontinued'
);

BETWEEN Operator

-- Find orders within date range
SELECT * FROM orders 
WHERE order_date BETWEEN '2024-01-01' AND '2024-12-31';

-- Find products within price range
SELECT * FROM products 
WHERE price BETWEEN 50 AND 200;

Practice Problem

Find all customers whose names start with 'A' or 'B', have Gmail addresses, and registered between January 1, 2023, and December 31, 2023.

SELECT * FROM customers 
WHERE (first_name LIKE 'A%' OR first_name LIKE 'B%')
    AND email LIKE '%gmail%'
    AND registration_date BETWEEN '2023-01-01' AND '2023-12-31';

Day 4: Aggregate Functions and GROUP BY

Essential Aggregate Functions

-- Basic aggregations
SELECT 
    COUNT(*) as total_orders,
    SUM(amount) as total_revenue,
    AVG(amount) as average_order_value,
    MIN(amount) as smallest_order,
    MAX(amount) as largest_order
FROM orders;

GROUP BY Fundamentals

-- Sales by department
SELECT 
    department,
    COUNT(*) as employee_count,
    AVG(salary) as avg_salary
FROM employees 
GROUP BY department;

-- Monthly sales analysis
SELECT 
    EXTRACT(YEAR FROM order_date) as year,
    EXTRACT(MONTH FROM order_date) as month,
    SUM(amount) as monthly_revenue
FROM orders 
GROUP BY EXTRACT(YEAR FROM order_date), EXTRACT(MONTH FROM order_date)
ORDER BY year, month;

HAVING Clause

-- Find departments with more than 5 employees
SELECT 
    department,
    COUNT(*) as employee_count
FROM employees 
GROUP BY department 
HAVING COUNT(*) > 5;

-- Find customers with total orders > $1000
SELECT 
    customer_id,
    SUM(amount) as total_spent
FROM orders 
GROUP BY customer_id 
HAVING SUM(amount) > 1000;

Practice Problem

Find departments where the average salary is above $75,000 and there are at least 3 employees.

SELECT 
    department,
    COUNT(*) as employee_count,
    AVG(salary) as avg_salary
FROM employees 
GROUP BY department 
HAVING AVG(salary) > 75000 AND COUNT(*) >= 3;

Day 5: Joins - The Heart of SQL

INNER JOIN

-- Get employee details with department names
SELECT 
    e.first_name,
    e.last_name,
    d.department_name
FROM employees e
INNER JOIN departments d ON e.department_id = d.department_id;

LEFT JOIN

-- Get all customers and their orders (including customers without orders)
SELECT 
    c.customer_name,
    o.order_id,
    o.amount
FROM customers c
LEFT JOIN orders o ON c.customer_id = o.customer_id;

RIGHT JOIN

-- Get all departments and their employees (including empty departments)
SELECT 
    d.department_name,
    e.first_name,
    e.last_name
FROM employees e
RIGHT JOIN departments d ON e.department_id = d.department_id;

FULL OUTER JOIN

-- Complete view of customers and orders
SELECT 
    c.customer_name,
    o.order_id
FROM customers c
FULL OUTER JOIN orders o ON c.customer_id = o.customer_id;

Self JOIN

-- Find employees and their managers
SELECT 
    e1.first_name as employee_name,
    e2.first_name as manager_name
FROM employees e1
LEFT JOIN employees e2 ON e1.manager_id = e2.employee_id;

Practice Problem

Find all customers who have placed orders, along with their total order count and total amount spent.

SELECT 
    c.customer_name,
    COUNT(o.order_id) as order_count,
    SUM(o.amount) as total_spent
FROM customers c
INNER JOIN orders o ON c.customer_id = o.customer_id
GROUP BY c.customer_id, c.customer_name;

Day 6: Subqueries and Nested Queries

Scalar Subqueries

-- Find employees earning above average salary
SELECT * FROM employees 
WHERE salary > (SELECT AVG(salary) FROM employees);

Multiple Row Subqueries

-- Find employees in departments with more than 10 people
SELECT * FROM employees 
WHERE department_id IN (
    SELECT department_id 
    FROM employees 
    GROUP BY department_id 
    HAVING COUNT(*) > 10
);

Correlated Subqueries

-- Find employees earning the highest salary in their department
SELECT * FROM employees e1
WHERE salary = (
    SELECT MAX(salary) 
    FROM employees e2 
    WHERE e2.department_id = e1.department_id
);

EXISTS Operator

-- Find customers who have placed at least one order
SELECT * FROM customers c
WHERE EXISTS (
    SELECT 1 FROM orders o 
    WHERE o.customer_id = c.customer_id
);

Practice Problem

Find the second highest salary in each department.

SELECT 
    department_id,
    MAX(salary) as second_highest_salary
FROM employees e1
WHERE salary < (
    SELECT MAX(salary) 
    FROM employees e2 
    WHERE e2.department_id = e1.department_id
)
GROUP BY department_id;

Day 7: Window Functions

ROW_NUMBER()

-- Rank employees by salary within each department
SELECT 
    first_name,
    last_name,
    department_id,
    salary,
    ROW_NUMBER() OVER (PARTITION BY department_id ORDER BY salary DESC) as rank
FROM employees;

RANK() and DENSE_RANK()

-- Handle ties in ranking
SELECT 
    first_name,
    salary,
    RANK() OVER (ORDER BY salary DESC) as rank,
    DENSE_RANK() OVER (ORDER BY salary DESC) as dense_rank
FROM employees;

LAG() and LEAD()

-- Compare current month sales with previous month
SELECT 
    month,
    sales,
    LAG(sales) OVER (ORDER BY month) as previous_month_sales,
    sales - LAG(sales) OVER (ORDER BY month) as growth
FROM monthly_sales;

Running Totals

-- Calculate running total of sales
SELECT 
    order_date,
    amount,
    SUM(amount) OVER (ORDER BY order_date) as running_total
FROM orders;

Practice Problem

Find the top 3 highest-paid employees in each department.

SELECT * FROM (
    SELECT 
        first_name,
        last_name,
        department_id,
        salary,
        ROW_NUMBER() OVER (PARTITION BY department_id ORDER BY salary DESC) as rn
    FROM employees
) ranked
WHERE rn <= 3;

Day 8: Date and Time Functions

Date Extraction

-- Extract components from dates
SELECT 
    order_date,
    EXTRACT(YEAR FROM order_date) as year,
    EXTRACT(MONTH FROM order_date) as month,
    EXTRACT(DAY FROM order_date) as day,
    EXTRACT(DOW FROM order_date) as day_of_week
FROM orders;

Date Arithmetic

-- Find orders from last 30 days
SELECT * FROM orders 
WHERE order_date >= CURRENT_DATE - INTERVAL '30 days';

-- Calculate age
SELECT 
    first_name,
    birth_date,
    EXTRACT(YEAR FROM AGE(birth_date)) as age
FROM employees;

Date Formatting

-- Format dates for display
SELECT 
    order_id,
    TO_CHAR(order_date, 'DD-MM-YYYY') as formatted_date,
    TO_CHAR(order_date, 'Day, Month DD, YYYY') as full_date
FROM orders;

Practice Problem

Find total sales for each quarter of 2023.

SELECT 
    EXTRACT(QUARTER FROM order_date) as quarter,
    SUM(amount) as quarterly_sales
FROM orders 
WHERE EXTRACT(YEAR FROM order_date) = 2023
GROUP BY EXTRACT(QUARTER FROM order_date)
ORDER BY quarter;

Day 9: String Functions and Data Cleaning

Common String Functions

-- Text manipulation
SELECT 
    first_name,
    last_name,
    UPPER(first_name) as upper_name,
    LOWER(email) as lower_email,
    LENGTH(first_name) as name_length,
    CONCAT(first_name, ' ', last_name) as full_name
FROM employees;

String Cleaning

-- Clean and standardize data
SELECT 
    customer_id,
    TRIM(customer_name) as clean_name,
    REPLACE(phone, '-', '') as clean_phone,
    SUBSTRING(email FROM 1 FOR POSITION('@' IN email) - 1) as username
FROM customers;

CASE Statements

-- Categorize data
SELECT 
    employee_id,
    salary,
    CASE 
        WHEN salary >= 100000 THEN 'Senior'
        WHEN salary >= 70000 THEN 'Mid-level'
        ELSE 'Junior'
    END as level
FROM employees;

Practice Problem

Create email addresses for employees who don't have them, using format: firstname.lastname@company.com

UPDATE employees 
SET email = LOWER(first_name) || '.' || LOWER(last_name) || '@company.com'
WHERE email IS NULL;

Day 10: Advanced Topics and Interview Scenarios

Common Table Expressions (CTEs)

-- Recursive CTE for organizational hierarchy
WITH RECURSIVE employee_hierarchy AS (
    -- Base case: top-level managers
    SELECT employee_id, first_name, manager_id, 1 as level
    FROM employees 
    WHERE manager_id IS NULL

    UNION ALL

    -- Recursive case
    SELECT e.employee_id, e.first_name, e.manager_id, eh.level + 1
    FROM employees e
    INNER JOIN employee_hierarchy eh ON e.manager_id = eh.employee_id
)
SELECT * FROM employee_hierarchy ORDER BY level, employee_id;

Pivot Operations

-- Sales by month (pivot)
SELECT 
    product_id,
    SUM(CASE WHEN EXTRACT(MONTH FROM order_date) = 1 THEN amount ELSE 0 END) as jan_sales,
    SUM(CASE WHEN EXTRACT(MONTH FROM order_date) = 2 THEN amount ELSE 0 END) as feb_sales,
    SUM(CASE WHEN EXTRACT(MONTH FROM order_date) = 3 THEN amount ELSE 0 END) as mar_sales
FROM order_items oi
JOIN orders o ON oi.order_id = o.order_id
GROUP BY product_id;

Advanced Analytics

-- Customer segmentation based on purchase behavior
WITH customer_metrics AS (
    SELECT 
        customer_id,
        COUNT(*) as order_count,
        SUM(amount) as total_spent,
        AVG(amount) as avg_order_value,
        MAX(order_date) as last_order_date
    FROM orders 
    GROUP BY customer_id
)
SELECT 
    customer_id,
    CASE 
        WHEN total_spent >= 5000 AND order_count >= 10 THEN 'VIP'
        WHEN total_spent >= 2000 AND order_count >= 5 THEN 'Premium'
        WHEN total_spent >= 500 THEN 'Regular'
        ELSE 'New'
    END as customer_segment,
    total_spent,
    order_count,
    CURRENT_DATE - last_order_date as days_since_last_order
FROM customer_metrics;

Performance Optimization Tips

1. Use appropriate indexes: Create indexes on columns used in WHERE, JOIN, and ORDER BY clauses

2. Limit result sets: Use LIMIT and proper WHERE conditions

3. Avoid SELECT *: Specify only needed columns

4. Use EXISTS instead of IN for subqueries when checking existence

5. Consider query execution plans: Use EXPLAIN to understand query performance

Common Interview Questions and Solutions

Question 1: Find duplicate records

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

Question 2: Find nth highest salary

-- 3rd highest salary
SELECT salary 
FROM employees 
ORDER BY salary DESC 
LIMIT 1 OFFSET 2;

Question 3: Find customers with no orders

SELECT c.* 
FROM customers c
LEFT JOIN orders o ON c.customer_id = o.customer_id
WHERE o.customer_id IS NULL;

Key Takeaways for SQL Interviews

1. Practice complex scenarios: Focus on multi-table queries and business logic problems

2. Understand performance: Know when to use indexes and how to optimize queries

3. Master window functions: They're frequently tested in modern SQL interviews

4. Think step by step: Break complex problems into smaller, manageable parts

5. Verify your results: Always double-check your queries with sample data

6. Explain your approach: Interviewers want to understand your thought process

Remember, SQL interviews often test your ability to translate business requirements into efficient queries. Practice with real datasets and focus on understanding the logic behind each solution rather than memorizing syntax.

Next Steps

After mastering these concepts, consider practicing with:

• LeetCode SQL problems

• HackerRank SQL challenges

• Real-world datasets from Kaggle

• Company-specific SQL interview questions

The key to SQL interview success is consistent practice and understanding the business context behind each query. Good luck with your preparation!