Data Tech Bridge

Posted on Dec 18

SQL Interview Prep: Last-Minute Brushup for Experienced Professionals

#career #sql #interview

Introduction

You've got an interview tomorrow, and you need to brush up on SQL? As you know the basics, but interviews can throw curveballs. This guide focuses on the advanced concepts and tricky questions that interviewers love to ask experienced candidates.

Let's dive into the top 20 questions that will help you ace that interview!

Top 20 SQL Interview Questions with Detailed Answers

1. Explain the difference between RANK(), DENSE_RANK(), and ROW_NUMBER()

Answer:

-- Sample data
SELECT 
    employee_name,
    salary,
    ROW_NUMBER() OVER (ORDER BY salary DESC) as row_num,
    RANK() OVER (ORDER BY salary DESC) as rank_val,
    DENSE_RANK() OVER (ORDER BY salary DESC) as dense_rank_val
FROM employees;

-- Results with salaries: 100k, 100k, 90k, 80k
-- ROW_NUMBER: 1, 2, 3, 4 (always sequential)
-- RANK: 1, 1, 3, 4 (skips numbers after ties)
-- DENSE_RANK: 1, 1, 2, 3 (no gaps after ties)

Key Point: Use ROW_NUMBER() for unique identifiers, RANK() when gaps matter, DENSE_RANK() for continuous ranking.

2. Write a query to find the second highest salary without using LIMIT or TOP

Answer:

-- Method 1: Using subquery
SELECT MAX(salary) as second_highest
FROM employees
WHERE salary < (SELECT MAX(salary) FROM employees);

-- Method 2: Using DENSE_RANK
SELECT salary
FROM (
    SELECT salary, 
           DENSE_RANK() OVER (ORDER BY salary DESC) as rnk
    FROM employees
) ranked
WHERE rnk = 2;

-- Method 3: Using NOT IN
SELECT MAX(salary)
FROM employees
WHERE salary NOT IN (SELECT MAX(salary) FROM employees);

3. Explain the difference between WHERE and HAVING clauses

Answer:

-- WHERE: Filters rows BEFORE grouping
SELECT department, AVG(salary)
FROM employees
WHERE hire_date > '2020-01-01'  -- Filters individual rows
GROUP BY department;

-- HAVING: Filters groups AFTER aggregation
SELECT department, AVG(salary) as avg_sal
FROM employees
GROUP BY department
HAVING AVG(salary) > 75000;  -- Filters aggregated results

-- Combined usage
SELECT department, AVG(salary) as avg_sal
FROM employees
WHERE hire_date > '2020-01-01'  -- First: filter rows
GROUP BY department
HAVING AVG(salary) > 75000;     -- Then: filter groups

Key Point: WHERE works on individual rows; HAVING works on grouped results.

4. Write a query to find duplicate records in a table

Answer:

-- Method 1: Using GROUP BY and HAVING
SELECT email, COUNT(*) as duplicate_count
FROM users
GROUP BY email
HAVING COUNT(*) > 1;

-- Method 2: Get all duplicate rows with details
SELECT u.*
FROM users u
INNER JOIN (
    SELECT email
    FROM users
    GROUP BY email
    HAVING COUNT(*) > 1
) duplicates ON u.email = duplicates.email;

-- Method 3: Using window functions
SELECT *
FROM (
    SELECT *,
           ROW_NUMBER() OVER (PARTITION BY email ORDER BY id) as rn
    FROM users
) ranked
WHERE rn > 1;

5. Explain different types of JOINs with examples

Answer:

-- INNER JOIN: Only matching records
SELECT e.name, d.dept_name
FROM employees e
INNER JOIN departments d ON e.dept_id = d.id;

-- LEFT JOIN: All from left + matching from right
SELECT e.name, d.dept_name
FROM employees e
LEFT JOIN departments d ON e.dept_id = d.id;

-- RIGHT JOIN: All from right + matching from left
SELECT e.name, d.dept_name
FROM employees e
RIGHT JOIN departments d ON e.dept_id = d.id;

-- FULL OUTER JOIN: All records from both tables
SELECT e.name, d.dept_name
FROM employees e
FULL OUTER JOIN departments d ON e.dept_id = d.id;

-- CROSS JOIN: Cartesian product
SELECT e.name, d.dept_name
FROM employees e
CROSS JOIN departments d;

-- SELF JOIN: Join table to itself
SELECT e1.name as employee, e2.name as manager
FROM employees e1
LEFT JOIN employees e2 ON e1.manager_id = e2.id;

6. Write a query to find employees earning more than their managers

Answer:

SELECT e.employee_id, 
       e.name as employee_name, 
       e.salary as employee_salary,
       m.name as manager_name,
       m.salary as manager_salary
FROM employees e
INNER JOIN employees m ON e.manager_id = m.employee_id
WHERE e.salary > m.salary;

7. Explain the difference between DELETE, TRUNCATE, and DROP

Answer:

Aspect	DELETE	TRUNCATE	DROP
Type	DML	DDL	DDL
Rollback	Yes (with transaction)	No (in most DBs)	No
WHERE clause	Yes	No	No
Speed	Slower	Faster	Fastest
Identity reset	No	Yes	N/A
Triggers	Fires	Doesn't fire	Doesn't fire
Structure	Keeps	Keeps	Removes

-- DELETE: Removes specific rows
DELETE FROM employees WHERE dept_id = 5;
ROLLBACK;  -- Can be rolled back

-- TRUNCATE: Removes all rows, resets identity
TRUNCATE TABLE employees;
-- Cannot rollback (in most databases)

-- DROP: Removes entire table structure
DROP TABLE employees;
-- Table no longer exists

8. Write a query to find the Nth highest salary

Answer:

-- Method 1: Using DENSE_RANK (most reliable)
WITH RankedSalaries AS (
    SELECT salary,
           DENSE_RANK() OVER (ORDER BY salary DESC) as rank_num
    FROM employees
)
SELECT DISTINCT salary
FROM RankedSalaries
WHERE rank_num = 5;  -- Replace 5 with desired N

-- Method 2: Using OFFSET (PostgreSQL, MySQL 8+)
SELECT DISTINCT salary
FROM employees
ORDER BY salary DESC
LIMIT 1 OFFSET 4;  -- For 5th highest, use N-1

-- Method 3: Using subquery (works everywhere)
SELECT MIN(salary)
FROM (
    SELECT DISTINCT TOP 5 salary
    FROM employees
    ORDER BY salary DESC
) as top_n_salaries;

9. Explain ACID properties with examples

Answer:

A - Atomicity: All or nothing

BEGIN TRANSACTION;
    UPDATE accounts SET balance = balance - 100 WHERE id = 1;
    UPDATE accounts SET balance = balance + 100 WHERE id = 2;
COMMIT;  -- Both succeed or both fail

C - Consistency: Data integrity maintained

-- Constraint ensures consistency
ALTER TABLE accounts ADD CONSTRAINT chk_balance CHECK (balance >= 0);

I - Isolation: Transactions don't interfere

-- Transaction 1
SET TRANSACTION ISOLATION LEVEL SERIALIZABLE;
BEGIN TRANSACTION;
SELECT balance FROM accounts WHERE id = 1;
-- Other transactions can't modify this row
COMMIT;

D - Durability: Committed data persists

COMMIT;  -- Data survives system crashes

10. Write a query to find cumulative sum

Answer:

-- Using window function (modern approach)
SELECT 
    order_date,
    order_amount,
    SUM(order_amount) OVER (ORDER BY order_date) as cumulative_sum
FROM orders
ORDER BY order_date;

-- With partition (cumulative sum per category)
SELECT 
    order_date,
    category,
    order_amount,
    SUM(order_amount) OVER (
        PARTITION BY category 
        ORDER BY order_date
    ) as cumulative_sum_by_category
FROM orders;

-- Using self-join (older approach)
SELECT o1.order_date, 
       o1.order_amount,
       SUM(o2.order_amount) as cumulative_sum
FROM orders o1
INNER JOIN orders o2 ON o2.order_date <= o1.order_date
GROUP BY o1.order_date, o1.order_amount
ORDER BY o1.order_date;

11. Explain different isolation levels

Answer:

-- READ UNCOMMITTED: Lowest isolation, allows dirty reads
SET TRANSACTION ISOLATION LEVEL READ UNCOMMITTED;
-- Can read uncommitted changes from other transactions

-- READ COMMITTED: Prevents dirty reads (default in many DBs)
SET TRANSACTION ISOLATION LEVEL READ COMMITTED;
-- Only reads committed data

-- REPEATABLE READ: Prevents dirty and non-repeatable reads
SET TRANSACTION ISOLATION LEVEL REPEATABLE READ;
-- Same query returns same results within transaction

-- SERIALIZABLE: Highest isolation, prevents phantom reads
SET TRANSACTION ISOLATION LEVEL SERIALIZABLE;
-- Complete isolation, as if transactions run serially

Problems they solve:

Dirty Read: Reading uncommitted data
Non-repeatable Read: Same query returns different results
Phantom Read: New rows appear in range queries

12. Write a query to pivot data

Answer:

-- Sample data: sales by quarter
-- Original: product, quarter, amount
-- Desired: product, Q1, Q2, Q3, Q4

-- Method 1: Using CASE statements
SELECT 
    product,
    SUM(CASE WHEN quarter = 'Q1' THEN amount ELSE 0 END) as Q1,
    SUM(CASE WHEN quarter = 'Q2' THEN amount ELSE 0 END) as Q2,
    SUM(CASE WHEN quarter = 'Q3' THEN amount ELSE 0 END) as Q3,
    SUM(CASE WHEN quarter = 'Q4' THEN amount ELSE 0 END) as Q4
FROM sales
GROUP BY product;

-- Method 2: Using PIVOT (SQL Server, Oracle)
SELECT *
FROM (
    SELECT product, quarter, amount
    FROM sales
) src
PIVOT (
    SUM(amount)
    FOR quarter IN ([Q1], [Q2], [Q3], [Q4])
) pvt;

-- UNPIVOT: Reverse operation
SELECT product, quarter, amount
FROM sales_pivoted
UNPIVOT (
    amount FOR quarter IN (Q1, Q2, Q3, Q4)
) unpvt;

13. Explain indexes and their types

Answer:

-- Clustered Index: Determines physical order (one per table)
CREATE CLUSTERED INDEX idx_employee_id 
ON employees(employee_id);

-- Non-Clustered Index: Separate structure with pointers
CREATE NONCLUSTERED INDEX idx_last_name 
ON employees(last_name);

-- Composite Index: Multiple columns
CREATE INDEX idx_name_dept 
ON employees(last_name, first_name, dept_id);

-- Unique Index: Ensures uniqueness
CREATE UNIQUE INDEX idx_email 
ON employees(email);

-- Filtered Index: Index subset of rows
CREATE INDEX idx_active_employees 
ON employees(last_name)
WHERE status = 'Active';

-- Covering Index: Includes additional columns
CREATE INDEX idx_dept_covering 
ON employees(dept_id)
INCLUDE (salary, hire_date);

When to use:

High selectivity columns
Frequently used in WHERE, JOIN, ORDER BY
Foreign keys
Avoid over-indexing (slows INSERT/UPDATE/DELETE)

14. Write a query to find gaps in sequential data

Answer:

-- Find missing IDs in sequence
SELECT (t1.id + 1) as missing_id_start,
       (MIN(t2.id) - 1) as missing_id_end
FROM sequence_table t1
LEFT JOIN sequence_table t2 ON t1.id < t2.id
GROUP BY t1.id
HAVING (t1.id + 1) < MIN(t2.id);

-- Alternative using LEAD
WITH gaps AS (
    SELECT id,
           LEAD(id) OVER (ORDER BY id) as next_id
    FROM sequence_table
)
SELECT id + 1 as gap_start,
       next_id - 1 as gap_end
FROM gaps
WHERE next_id - id > 1;

-- Find missing dates in date range
WITH RECURSIVE date_range AS (
    SELECT MIN(order_date) as date_val FROM orders
    UNION ALL
    SELECT DATE_ADD(date_val, INTERVAL 1 DAY)
    FROM date_range
    WHERE date_val < (SELECT MAX(order_date) FROM orders)
)
SELECT dr.date_val as missing_date
FROM date_range dr
LEFT JOIN orders o ON dr.date_val = o.order_date
WHERE o.order_date IS NULL;

15. Explain query execution order

Answer:

Logical Order:

SELECT DISTINCT TOP 10          -- 6. Select specific columns
    department,                  -- 7. Apply DISTINCT
    AVG(salary) as avg_salary   -- 5. Apply aggregate functions
FROM employees e                 -- 1. FROM: Get data source
INNER JOIN departments d         -- 2. JOIN: Combine tables
    ON e.dept_id = d.id
WHERE e.hire_date > '2020-01-01' -- 3. WHERE: Filter rows
GROUP BY department              -- 4. GROUP BY: Group rows
HAVING AVG(salary) > 50000       -- 5. HAVING: Filter groups
ORDER BY avg_salary DESC;        -- 8. ORDER BY: Sort results
                                 -- 9. TOP/LIMIT: Limit results

Execution Order:

FROM + JOINs
WHERE
GROUP BY
HAVING
SELECT
DISTINCT
ORDER BY
LIMIT/TOP

Why it matters: Understanding this helps with:

Column aliases (can't use in WHERE, can use in HAVING)
Performance optimization
Debugging complex queries

16. Write a query for running total with reset

Answer:

-- Running total that resets each month
SELECT 
    order_date,
    amount,
    DATE_TRUNC('month', order_date) as month,
    SUM(amount) OVER (
        PARTITION BY DATE_TRUNC('month', order_date)
        ORDER BY order_date
        ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW
    ) as running_total_monthly
FROM orders;

-- Running total with conditional reset
WITH flagged_orders AS (
    SELECT 
        order_date,
        amount,
        CASE 
            WHEN amount > 1000 THEN 1 
            ELSE 0 
        END as reset_flag,
        SUM(CASE WHEN amount > 1000 THEN 1 ELSE 0 END) 
            OVER (ORDER BY order_date) as group_id
    FROM orders
)
SELECT 
    order_date,
    amount,
    SUM(amount) OVER (
        PARTITION BY group_id 
        ORDER BY order_date
    ) as running_total_with_reset
FROM flagged_orders;

17. Explain normalization with examples

Answer:

1NF (First Normal Form): Atomic values, no repeating groups

-- Violation
CREATE TABLE orders_bad (
    order_id INT,
    products VARCHAR(200)  -- 'Apple,Orange,Banana'
);

-- 1NF Compliant
CREATE TABLE orders (order_id INT);
CREATE TABLE order_items (
    order_id INT,
    product VARCHAR(50)
);

2NF (Second Normal Form): 1NF + No partial dependencies

-- Violation (composite key: order_id, product_id)
CREATE TABLE order_details_bad (
    order_id INT,
    product_id INT,
    product_name VARCHAR(50),  -- Depends only on product_id
    quantity INT,
    PRIMARY KEY (order_id, product_id)
);

-- 2NF Compliant
CREATE TABLE products (
    product_id INT PRIMARY KEY,
    product_name VARCHAR(50)
);
CREATE TABLE order_details (
    order_id INT,
    product_id INT,
    quantity INT,
    PRIMARY KEY (order_id, product_id)
);

3NF (Third Normal Form): 2NF + No transitive dependencies

-- Violation
CREATE TABLE employees_bad (
    emp_id INT PRIMARY KEY,
    dept_id INT,
    dept_name VARCHAR(50),  -- Depends on dept_id, not emp_id
    salary DECIMAL
);

-- 3NF Compliant
CREATE TABLE departments (
    dept_id INT PRIMARY KEY,
    dept_name VARCHAR(50)
);
CREATE TABLE employees (
    emp_id INT PRIMARY KEY,
    dept_id INT,
    salary DECIMAL,
    FOREIGN KEY (dept_id) REFERENCES departments(dept_id)
);

18. Write a query to find consecutive dates

Answer:

-- Find users who logged in for 3+ consecutive days
WITH login_groups AS (
    SELECT 
        user_id,
        login_date,
        DATE_SUB(login_date, INTERVAL 
            ROW_NUMBER() OVER (PARTITION BY user_id ORDER BY login_date) DAY
        ) as group_date
    FROM user_logins
)
SELECT 
    user_id,
    MIN(login_date) as streak_start,
    MAX(login_date) as streak_end,
    COUNT(*) as consecutive_days
FROM login_groups
GROUP BY user_id, group_date
HAVING COUNT(*) >= 3;

-- Alternative using LEAD
WITH consecutive_check AS (
    SELECT 
        user_id,
        login_date,
        LEAD(login_date, 1) OVER (PARTITION BY user_id ORDER BY login_date) as next_date,
        LEAD(login_date, 2) OVER (PARTITION BY user_id ORDER BY login_date) as next_next_date
    FROM user_logins
)
SELECT DISTINCT user_id
FROM consecutive_check
WHERE DATE_ADD(login_date, INTERVAL 1 DAY) = next_date
  AND DATE_ADD(login_date, INTERVAL 2 DAY) = next_next_date;

19. Explain deadlock and how to prevent it

Answer:

What is a deadlock?
Two or more transactions waiting for each other to release locks, creating a circular dependency.

-- Transaction 1
BEGIN TRANSACTION;
UPDATE accounts SET balance = balance - 100 WHERE id = 1;  -- Locks row 1
-- Waiting for lock on row 2
UPDATE accounts SET balance = balance + 100 WHERE id = 2;
COMMIT;

-- Transaction 2 (running simultaneously)
BEGIN TRANSACTION;
UPDATE accounts SET balance = balance - 50 WHERE id = 2;   -- Locks row 2
-- Waiting for lock on row 1
UPDATE accounts SET balance = balance + 50 WHERE id = 1;
COMMIT;
-- DEADLOCK!

Prevention strategies:

Access resources in same order:

-- Always update accounts in order of ID
UPDATE accounts SET balance = balance - 100 
WHERE id IN (1, 2) 
ORDER BY id;

Use appropriate isolation levels:

SET TRANSACTION ISOLATION LEVEL READ COMMITTED;

Keep transactions short:

BEGIN TRANSACTION;
-- Do minimal work
UPDATE accounts SET balance = balance - 100 WHERE id = 1;
COMMIT;  -- Release locks quickly

Use timeout:

SET LOCK_TIMEOUT 5000;  -- 5 seconds

Use NOLOCK hint (with caution):

SELECT * FROM accounts WITH (NOLOCK) WHERE id = 1;

20. Write a complex query combining multiple concepts

Answer:

-- Find top 3 products per category with running sales total,
-- year-over-year growth, and ranking

WITH monthly_sales AS (
    SELECT 
        p.category,
        p.product_name,
        DATE_TRUNC('month', o.order_date) as month,
        SUM(o.amount) as monthly_revenue
    FROM orders o
    JOIN products p ON o.product_id = p.product_id
    WHERE o.order_date >= DATE_SUB(CURRENT_DATE, INTERVAL 2 YEAR)
    GROUP BY p.category, p.product_name, DATE_TRUNC('month', o.order_date)
),
sales_with_growth AS (
    SELECT 
        category,
        product_name,
        month,
        monthly_revenue,
        LAG(monthly_revenue, 12) OVER (
            PARTITION BY category, product_name 
            ORDER BY month
        ) as revenue_last_year,
        SUM(monthly_revenue) OVER (
            PARTITION BY category, product_name 
            ORDER BY month
            ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW
        ) as cumulative_revenue
    FROM monthly_sales
),
ranked_products AS (
    SELECT 
        category,
        product_name,
        month,
        monthly_revenue,
        revenue_last_year,
        CASE 
            WHEN revenue_last_year IS NOT NULL AND revenue_last_year > 0
            THEN ((monthly_revenue - revenue_last_year) / revenue_last_year * 100)
            ELSE NULL
        END as yoy_growth_pct,
        cumulative_revenue,
        DENSE_RANK() OVER (
            PARTITION BY category, month 
            ORDER BY monthly_revenue DESC
        ) as product_rank
    FROM sales_with_growth
)
SELECT 
    category,
    product_name,
    month,
    monthly_revenue,
    yoy_growth_pct,
    cumulative_revenue,
    product_rank
FROM ranked_products
WHERE product_rank <= 3
  AND month >= DATE_TRUNC('month', DATE_SUB(CURRENT_DATE, INTERVAL 6 MONTH))
ORDER BY category, month, product_rank;

This query demonstrates:

CTEs (Common Table Expressions)
Window functions (LAG, SUM, DENSE_RANK)
Partitioning
Date functions
Aggregations
JOINs
Complex calculations
Filtering at multiple levels

Quick Tips for Interview Success

1. Explain Your Thought Process

Don't just write the query—explain why you chose that approach.

Example: "I'm using a CTE here for readability, but I could also use a subquery. The CTE makes it easier to debug and maintain."

2. Discuss Trade-offs

-- Mention: "This uses a subquery, which is readable but might be slower.
-- For large datasets, I'd consider a JOIN or CTE with proper indexing."

3. Consider Performance

Always think about:

Index usage
Query optimization
Execution plans
Data volume impact

4. Handle Edge Cases

-- Always consider NULLs
SELECT COALESCE(salary, 0) as salary FROM employees;

-- Handle division by zero
SELECT 
    CASE 
        WHEN total_orders = 0 THEN 0
        ELSE revenue / total_orders 
    END as avg_order_value
FROM sales;

-- Handle empty result sets
SELECT COALESCE(MAX(salary), 0) as max_salary FROM employees;

5. Know Your Database

Be ready to discuss differences between:

MySQL vs PostgreSQL vs SQL Server vs Oracle
Syntax variations
Specific features (e.g., PostgreSQL's ARRAY types, SQL Server's MERGE)

Common Pitfalls to Avoid

1. Forgetting about NULLs

-- Wrong: NULL != NULL
WHERE column = NULL  -- Always false

-- Correct:
WHERE column IS NULL

-- Wrong: Aggregates ignore NULLs
SELECT AVG(salary) FROM employees;  -- Excludes NULL salaries

-- Correct: Handle NULLs explicitly
SELECT AVG(COALESCE(salary, 0)) FROM employees;

2. Incorrect GROUP BY

-- Wrong: Selecting non-aggregated columns not in GROUP BY
SELECT department, name, AVG(salary)
FROM employees
GROUP BY department;  -- Error: name not aggregated

-- Correct:
SELECT department, AVG(salary)
FROM employees
GROUP BY department;

3. Cartesian Products

-- Wrong: Missing JOIN condition
SELECT * FROM employees, departments;  -- Cartesian product

-- Correct:
SELECT * FROM employees e
JOIN departments d ON e.dept_id = d.id;

4. **Using SELECT * in production**

-- Bad practice
SELECT * FROM large_table;

-- Good practice
SELECT id, name, email FROM large_table;

5. Not using proper indexes

-- Slow: Function on indexed column
SELECT * FROM employees WHERE YEAR(hire_date) = 2020;

-- Fast: Preserve index usage
SELECT * FROM employees 
WHERE hire_date >= '2020-01-01' AND hire_date < '2021-01-01';

Performance Optimization Checklist

✅ Use appropriate indexes - Create indexes on frequently queried columns

✅ Avoid SELECT *** - Select only needed columns

✅ **Use EXISTS instead of IN - For large datasets with subqueries

✅ Limit result sets - Use LIMIT/TOP when possible

✅ Use JOINs wisely - Sometimes subqueries are better, sometimes JOINs

✅ Avoid functions on indexed columns - In WHERE clauses

✅ Use UNION ALL instead of UNION - When duplicates are acceptable

✅ Partition large tables - For better query performance

✅ Update statistics regularly - Keep query optimizer informed

✅ Analyze execution plans - Understand how queries execute

Advanced Concepts to Mention

1. Query Optimization Techniques

-- Use EXPLAIN to analyze queries
EXPLAIN ANALYZE 
SELECT * FROM employees WHERE dept_id = 5;

-- Check index usage
SHOW INDEX FROM employees;

-- Analyze table statistics
ANALYZE TABLE employees;

2. Partitioning

-- Range partitioning
CREATE TABLE sales (
    sale_id INT,
    sale_date DATE,
    amount DECIMAL
)
PARTITION BY RANGE (YEAR(sale_date)) (
    PARTITION p2020 VALUES LESS THAN (2021),
    PARTITION p2021 VALUES LESS THAN (2022),
    PARTITION p2022 VALUES LESS THAN (2023)
);

3. Materialized Views

-- Create materialized view for expensive queries
CREATE MATERIALIZED VIEW sales_summary AS
SELECT 
    product_id,
    DATE_TRUNC('month', sale_date) as month,
    SUM(amount) as total_sales
FROM sales
GROUP BY product_id, DATE_TRUNC('month', sale_date);

-- Refresh when needed
REFRESH MATERIALIZED VIEW sales_summary;

4. Common Table Expressions (CTEs) vs Subqueries

-- CTE: More readable, can be referenced multiple times
WITH high_earners AS (
    SELECT * FROM employees WHERE salary > 100000
)
SELECT * FROM high_earners WHERE dept_id = 5
UNION
SELECT * FROM high_earners WHERE dept_id = 10;

-- Subquery: Sometimes more efficient
SELECT * FROM employees 
WHERE salary > (SELECT AVG(salary) FROM employees);

Database-Specific Features to Know

PostgreSQL

ARRAY data types
JSON/JSONB support
Full-text search
Window functions extensions
LATERAL joins

MySQL

JSON functions
Full-text search
Spatial data types
Generated columns

SQL Server

MERGE statement
TRY_CONVERT, TRY_CAST
STRING_AGG
Temporal tables
Columnstore indexes

Oracle

ROWNUM vs ROW_NUMBER
CONNECT BY for hierarchical queries
Analytic functions
Flashback queries

Behavioral Questions to Prepare

"Tell me about a time you optimized a slow query"
- Explain the problem
- Show the before/after query
- Discuss performance metrics
- Mention tools used (EXPLAIN, profiling)
"How do you handle database design for a new project?"
- Requirements gathering
- Normalization vs denormalization
- Indexing strategy
- Scalability considerations
"Describe a complex SQL problem you solved"
- Use the STAR method (Situation, Task, Action, Result)
- Show technical depth
- Discuss alternatives considered

Quick Reference Commands

-- Check execution plan
EXPLAIN ANALYZE SELECT ...;

-- View indexes
SHOW INDEX FROM table_name;

-- Check table size (PostgreSQL)
SELECT 
    table_name,
    pg_size_pretty(pg_total_relation_size(table_name::regclass))
FROM information_schema.tables
WHERE table_schema = 'public';

-- Kill long-running query (PostgreSQL)
SELECT pg_cancel_backend(pid);

-- View active connections (PostgreSQL)
SELECT * FROM pg_stat_activity;

-- Check locks (PostgreSQL)
SELECT * FROM pg_locks;

-- View query cache (MySQL)
SHOW STATUS LIKE 'Qcache%';

-- Check table fragmentation (MySQL)
SHOW TABLE STATUS WHERE Data_free > 0;

Sample Interview Scenario

Interviewer: "We have a table with 100 million rows. Users are complaining that queries are slow. How would you approach this?"

Your Answer:

Analyze the problem:
- Check current queries using EXPLAIN
- Review execution plans
- Identify slow queries from logs
Check indexes:
- Are appropriate indexes present?
- Are they being used?
- Consider composite indexes
Query optimization:
- Rewrite queries if needed
- Avoid SELECT *
- Use appropriate JOINs
Database optimization:
- Update statistics
- Consider partitioning
- Review server configuration
Long-term solutions:
- Implement caching
- Consider read replicas
- Archive old data
- Implement materialized views

Final Preparation Checklist

Day Before Interview:

[ ] Review window functions
[ ] Practice writing complex JOINs
[ ] Understand execution order
[ ] Review normalization
[ ] Practice explaining ACID properties
[ ] Review indexing strategies
[ ] Prepare examples from your experience

During Interview:

[ ] Ask clarifying questions
[ ] Think out loud
[ ] Discuss trade-offs
[ ] Consider edge cases
[ ] Mention performance implications
[ ] Be honest about what you don't know

After Writing Query:

[ ] Test with sample data
[ ] Consider NULL values
[ ] Think about performance
[ ] Discuss alternatives
[ ] Explain your reasoning

Conclusion

As an experienced engineer, you're expected to demonstrate:

Technical depth: Advanced SQL concepts and optimization
Problem-solving: Breaking down complex problems
Communication: Explaining technical concepts clearly
Experience: Real-world examples and lessons learned
Leadership: Mentoring and best practices

Remember: Interviews aren't just about getting the right answer—they're about demonstrating your thought process, communication skills, and depth of knowledge.

Key Takeaways:

Understand the "why" behind SQL concepts
Always consider performance implications
Handle edge cases (NULLs, empty sets, duplicates)
Explain your reasoning clearly
Be prepared to discuss alternatives
Share real-world experiences

Additional Resources

Practice Platforms:

LeetCode (Database section)
HackerRank (SQL)
SQLZoo
Mode Analytics SQL Tutorial

Books to Reference:

"SQL Performance Explained" by Markus Winand
"High Performance MySQL"
"PostgreSQL: Up and Running"

Online Documentation:

PostgreSQL Official Docs
MySQL Reference Manual
SQL Server Documentation
Oracle Database Documentation

Good luck with your interview! 🚀

Remember: Confidence comes from preparation. Review these concepts, practice the queries, and you'll do great!

Top comments (1)

Satish Kandala • Dec 18

Excellent resource for interview preparation. Practicing with real queries under interview conditions is the key to success. Beyond reviewing concepts, focused practice on actual SQL scenarios makes a huge difference. If you need more hands-on interview-style questions, sql-practice.online provides structured problem sets that mirror what you'll face in real interviews. #interview #career #sql

Introduction

Top 20 SQL Interview Questions with Detailed Answers

1. Explain the difference between RANK(), DENSE_RANK(), and ROW_NUMBER()

2. Write a query to find the second highest salary without using LIMIT or TOP

3. Explain the difference between WHERE and HAVING clauses

4. Write a query to find duplicate records in a table

5. Explain different types of JOINs with examples

6. Write a query to find employees earning more than their managers

7. Explain the difference between DELETE, TRUNCATE, and DROP

8. Write a query to find the Nth highest salary

9. Explain ACID properties with examples

10. Write a query to find cumulative sum

11. Explain different isolation levels

12. Write a query to pivot data

13. Explain indexes and their types

14. Write a query to find gaps in sequential data

15. Explain query execution order

16. Write a query for running total with reset

17. Explain normalization with examples

18. Write a query to find consecutive dates

19. Explain deadlock and how to prevent it

20. Write a complex query combining multiple concepts

Quick Tips for Interview Success

1. Explain Your Thought Process

2. Discuss Trade-offs

3. Consider Performance

4. Handle Edge Cases

5. Know Your Database

Common Pitfalls to Avoid

1. Forgetting about NULLs

2. Incorrect GROUP BY

3. Cartesian Products

4. Using SELECT * in production

5. Not using proper indexes

Performance Optimization Checklist

Advanced Concepts to Mention

1. Query Optimization Techniques

2. Partitioning

3. Materialized Views

4. Common Table Expressions (CTEs) vs Subqueries

Database-Specific Features to Know

PostgreSQL

MySQL

SQL Server

Oracle

Behavioral Questions to Prepare

Quick Reference Commands

Sample Interview Scenario

Final Preparation Checklist

Conclusion

Additional Resources

4. **Using SELECT * in production**