SQL high-frequency interview questions

Introduction to SQL Interview Questions

SQL interview questions often focus on core concepts, common pitfalls, and practical skills required to write efficient and correct queries. High-frequency questions typically revolve around data manipulation, joins, aggregation, and performance optimization. Understanding these topics is essential for candidates preparing for technical interviews, as they test both theoretical knowledge and real-world problem-solving abilities.

Core Topics in SQL Interview

1. Data Manipulation (SELECT)

  • SELECT Statement Basics: How to retrieve data from tables, use WHERE, ORDER BY, and GROUP BY.
  • Filtering with WHERE: Common conditions like =, >, <, LIKE, and IN for filtering rows.
  • Sorting with ORDER BY: Sorting results by columns (e.g., ORDER BY salary DESC) and handling NULL values.
  • Aggregation with GROUP BY: Use COUNT, SUM, AVG, MIN, and MAX to aggregate data.
  • Common Mistakes: Misusing GROUP BY without proper HAVING clauses, incorrect ORDER BY columns, and improper use of DISTINCT.

2. Joins

  • Types of Joins:
    • INNER JOIN: Returns rows that have matching values in both tables.
    • LEFT JOIN: Returns all rows from the left table, even if there are no matches in the right table.
    • RIGHT JOIN: Similar to LEFT JOIN but returns all rows from the right table.
    • FULL JOIN: Returns all rows from both tables, including those with no matches.
  • Self-Joins: Joining a table with itself to resolve relationships (e.g., finding employees who work in the same department).
  • Join Conditions: Writing ON clauses to specify the relationship between tables.

3. Aggregation and Subqueries

  • Aggregation: Use GROUP BY to group rows by a column and HAVING to filter groups.
  • Subqueries: Nested queries to retrieve data based on conditions from another query.
  • Common Questions:
    • How to calculate average salary per department using GROUP BY and AVG.
    • How to find the maximum salary in each department using MAX.
    • How to retrieve employees with a salary greater than the average salary of their department.

4. Transactions and ACID Properties

  • Transactions: Managing multiple database operations as a single unit to ensure data integrity.
  • ACID Properties:
    • Atomicity: All operations are completed or rolled back.
    • Consistency: Data remains in a valid state.
    • Isolation: Prevents conflicts between concurrent transactions.
    • Durability: Once a transaction is committed, the changes are permanent.
  • Real-World Example: Using transactions to update employee records and their salaries simultaneously.

5. Performance Optimization

  • Indexing: Use CREATE INDEX to speed up query execution.
  • Query Optimization: Avoid SELECT * and use specific columns instead.
  • Common Issues: Slow queries due to improper indexing, missing indexes, or inefficient joins.
  • Tools: Use EXPLAIN to analyze query execution plans and identify bottlenecks.

6. Advanced Topics

  • Window Functions: Use ROW_NUMBER(), RANK(), and LAG to rank rows and perform analytic calculations.
  • JSON Data Handling: Querying JSON fields using JSON_EXTRACT and JSON_CONTAINS.
  • Stored Procedures: Writing and calling stored procedures to modularize complex logic.
  • Common Questions:
    • How to rank employees by salary using ROW_NUMBER() and ORDER BY.
    • How to retrieve employees with a salary greater than their manager’s salary using subqueries.
    • How to handle JSON data in a table and query it efficiently.

Practical Examples

Example 1: Retrieve Employee Details

SELECT employees.name, departments.department_name 
FROM employees 
JOIN departments ON employees.department_id = departments.id 
WHERE employees.salary > 50000;

Explanation: Joins employees and departments on department_id, filters rows where salary exceeds 50,000, and returns employee names and department names.

Example 2: Calculate Average Salary per Department

SELECT department_id, AVG(salary) AS average_salary 
FROM employees 
GROUP BY department_id;

Explanation: Groups rows by department_id, calculates the average salary for each department, and returns the result.

Example 3: Find Employees with Salary Greater Than Average in Their Department

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

Explanation: Uses a subquery to calculate the average salary per department and filters employees whose salary exceeds that value.

Example 4: Use of Indexes

CREATE INDEX idx_department ON departments(id);

Explanation: Creates an index on the id column of the departments table to speed up queries involving department_id.

Example 5: Window Function Example

SELECT name, salary, ROW_NUMBER() OVER (ORDER BY salary DESC) AS rank 
FROM employees;

Explanation: Assigns a rank to each employee’s salary in descending order using the ROW_NUMBER() window function.

Conclusion

SQL interview questions are designed to assess a candidate’s ability to write efficient, correct, and scalable queries. High-frequency topics include data manipulation, joins, aggregation, and performance optimization. Mastering these concepts, along with advanced techniques like window functions and JSON handling, is critical for success in SQL interviews. Practicing real-world scenarios and using tools like EXPLAIN and CREATE INDEX can further enhance problem-solving skills.