Common SQL Interview Questions: An Introduction

By /

Common SQL Interview Questions: An Introduction

SQL (Structured Query Language) is the bedrock of relational database management. Whether you’re applying for a role as a data analyst, data scientist, software engineer, or database administrator, a solid understanding of SQL is almost always a prerequisite. SQL interviews, therefore, focus on assessing your ability to retrieve, manipulate, and manage data within a database. This article provides an introduction to the common types of questions you can expect in a SQL interview, breaking down the key concepts and providing a framework for preparing.

I. Basic Data Retrieval (SELECT, FROM, WHERE, ORDER BY, LIMIT):

These form the foundation of almost every SQL query. Interviewers will assess your ability to retrieve specific data based on given criteria. Expect questions like:

  • “Write a query to retrieve all employees from the ‘Employees’ table.”
    sql
    SELECT *
    FROM Employees;
  • “Find the names and salaries of all employees who earn more than $50,000, ordered by salary in descending order.”
    sql
    SELECT employee_name, salary
    FROM Employees
    WHERE salary > 50000
    ORDER BY salary DESC;
  • “Get the top 5 highest-paid employees.”
    sql
    SELECT employee_name, salary
    FROM Employees
    ORDER BY salary DESC
    LIMIT 5;
  • “Find all employees whose names start with the letter ‘J’.”
    sql
    SELECT *
    FROM Employees
    WHERE employee_name LIKE 'J%';
    (Note: LIKE operator is used for pattern matching. % represents zero, one, or multiple characters. _ represents a single character).
  • “Find employees hired in the year 2023.”
    sql
    SELECT *
    FROM Employees
    WHERE hire_date >= '2023-01-01' AND hire_date < '2024-01-01';
    -- Or, using a date function (specific function may vary by database)
    -- WHERE YEAR(hire_date) = 2023;

Key Concepts to Master:

  • SELECT Clause: Specifies the columns to retrieve. * selects all columns.
  • FROM Clause: Indicates the table(s) from which to retrieve data.
  • WHERE Clause: Filters the data based on specified conditions.
  • ORDER BY Clause: Sorts the results in ascending (ASC, default) or descending (DESC) order.
  • LIMIT Clause: Restricts the number of rows returned.
  • Comparison Operators: =, != (or <>), >, <, >=, <=.
  • Logical Operators: AND, OR, NOT.
  • BETWEEN Operator: Selects values within a given range (inclusive).
  • IN Operator: Checks if a value matches any value in a list.
  • LIKE Operator: Used for pattern matching with wildcards (% and _).
  • IS NULL / IS NOT NULL: Used to check for missing values (NULL values).

II. Data Aggregation (GROUP BY, HAVING, Aggregate Functions):

These questions test your ability to summarize data, often calculating statistics for different groups within a table.

  • “Calculate the average salary for each department.”
    sql
    SELECT department, AVG(salary) AS average_salary
    FROM Employees
    GROUP BY department;
  • “Find the departments with more than 5 employees.”
    sql
    SELECT department
    FROM Employees
    GROUP BY department
    HAVING COUNT(*) > 5;
  • “Find the total sales for each product category, but only show categories with total sales greater than $10,000.”
    sql
    SELECT category, SUM(sales) AS total_sales
    FROM Products
    GROUP BY category
    HAVING SUM(sales) > 10000;

Key Concepts to Master:

  • GROUP BY Clause: Groups rows with the same values in specified columns.
  • HAVING Clause: Filters the grouped results, similar to how WHERE filters individual rows. HAVING is used with aggregate functions.
  • Aggregate Functions: Functions that perform calculations on a set of values. Common ones include:
    • COUNT(): Counts the number of rows (or non-NULL values in a column).
    • SUM(): Calculates the sum of values.
    • AVG(): Calculates the average of values.
    • MIN(): Finds the minimum value.
    • MAX(): Finds the maximum value.
  • Important Note: When using GROUP BY, any column in the SELECT list that is not an aggregate function must be included in the GROUP BY clause.

III. Joining Tables (JOIN, INNER JOIN, LEFT JOIN, RIGHT JOIN, FULL JOIN):

Real-world databases often involve multiple related tables. JOIN operations combine data from these tables based on related columns.

  • “Retrieve the names of employees and the names of their respective departments.” (Assume an Employees table with a department_id column and a Departments table with department_id and department_name columns).
    sql
    SELECT e.employee_name, d.department_name
    FROM Employees e
    INNER JOIN Departments d ON e.department_id = d.department_id;
  • “List all employees and their department names. Include employees even if they don’t have a department assigned (show NULL for department name).”
    sql
    SELECT e.employee_name, d.department_name
    FROM Employees e
    LEFT JOIN Departments d ON e.department_id = d.department_id;
  • “List all departments and the names of employees in each department. Include departments even if they don’t have any employees (show NULL for employee name).”
    sql
    SELECT e.employee_name, d.department_name
    FROM Employees e
    RIGHT JOIN Departments d ON e.department_id = d.department_id;
  • “Get a result set containing all rows from both Employees and Departments tables. Show NULL for missing information on either side.”
    sql
    SELECT e.employee_name, d.department_name
    FROM Employees e
    FULL OUTER JOIN Departments d ON e.department_id = d.department_id;

Key Concepts to Master:

  • JOIN (or INNER JOIN): Returns only rows where there is a match in both tables based on the join condition.
  • LEFT JOIN (or LEFT OUTER JOIN): Returns all rows from the left table (the table listed before LEFT JOIN) and the matching rows from the right table. If there’s no match in the right table, NULL values are returned for the right table’s columns.
  • RIGHT JOIN (or RIGHT OUTER JOIN): Returns all rows from the right table and the matching rows from the left table. If there’s no match in the left table, NULL values are returned for the left table’s columns.
  • FULL JOIN (or FULL OUTER JOIN): Returns all rows from both tables. If there’s no match in the other table, NULL values are returned for that table’s columns.
  • ON Clause: Specifies the join condition, typically comparing columns from the two tables.
  • Table Aliases: Using aliases (e.g., e for Employees, d for Departments) makes queries shorter and more readable, especially with joins.

IV. Subqueries:

Subqueries are queries nested within another query. They can be used in various clauses (SELECT, FROM, WHERE, HAVING).

  • “Find the employees who earn more than the average salary of all employees.”
    sql
    SELECT employee_name, salary
    FROM Employees
    WHERE salary > (SELECT AVG(salary) FROM Employees);
  • “Find the names of departments that have at least one employee earning more than $80,000.”
    sql
    SELECT department_name
    FROM Departments
    WHERE department_id IN (SELECT department_id FROM Employees WHERE salary > 80000);

Key Concepts to Master:

  • Correlated Subqueries: A subquery that references a column from the outer query. The subquery is executed for each row processed by the outer query.
  • Non-Correlated Subqueries: A subquery that is independent of the outer query. It is executed once, and its result is used by the outer query.
  • EXISTS and NOT EXISTS: Used to check for the existence (or non-existence) of rows returned by a subquery. These are often used with correlated subqueries.
  • Subquery in FROM clause: Can be used to create a temporary, derived table. This derived table is then used in the main query.

V. Data Modification (INSERT, UPDATE, DELETE):

These questions test your ability to change the data within a database.

  • “Add a new employee to the ‘Employees’ table.”
    sql
    INSERT INTO Employees (employee_name, department_id, salary)
    VALUES ('John Doe', 1, 60000);
  • “Increase the salary of all employees in department 2 by 10%.”
    sql
    UPDATE Employees
    SET salary = salary * 1.10
    WHERE department_id = 2;
  • “Delete all employees who have not been assigned a department.”
    sql
    DELETE FROM Employees
    WHERE department_id IS NULL;

Key Concepts to Master:

  • INSERT INTO: Adds new rows to a table.
  • UPDATE: Modifies existing data in a table.
  • DELETE FROM: Removes rows from a table.
  • SET Clause (in UPDATE): Specifies the columns to be updated and their new values.
  • WHERE Clause (in UPDATE and DELETE): Crucially important to specify which rows to modify or delete. Without a WHERE clause, all rows will be affected.

VI. Other Important Concepts:

  • Database Design: Understanding basic database design principles (normalization, primary keys, foreign keys) is beneficial, even if you’re not asked to design a database schema from scratch. You might be asked about relationships between tables or how to improve a query’s performance based on the schema.
  • Indexes: Understanding how indexes work and how they can improve query performance is important. You might be asked about scenarios where an index would be beneficial.
  • Transactions: Understanding the concept of transactions (ACID properties) and how to use BEGIN TRANSACTION, COMMIT, and ROLLBACK can be relevant, especially for roles involving data integrity.
  • Window Functions (Advanced): For more senior roles, knowledge of window functions (e.g., ROW_NUMBER(), RANK(), LAG(), LEAD()) is a plus. These functions perform calculations across a set of table rows that are related to the current row.
  • Common Table Expressions (CTEs) (Advanced): CTEs provide a way to define a temporary named result set that can be referenced within a single query, making complex queries more readable and manageable.
  • Date and Time Functions: Familiarity with functions to manipulate dates and times (e.g., extracting year, month, day, calculating date differences) is essential. The specific functions vary between database systems (MySQL, PostgreSQL, SQL Server, Oracle).
  • String Functions: Functions to manipulate string data such as, LENGTH, SUBSTRING, CONCAT, UPPER, LOWER.

Preparation Tips:

  • Practice, Practice, Practice: The best way to prepare is to write SQL queries. Use online resources like HackerRank, LeetCode, SQLZoo, and Stratascratch.
  • Understand the Concepts: Don’t just memorize syntax; understand why things work the way they do.
  • Review Database Schemas: Practice working with different database schemas (examples are often provided in interview questions).
  • Think Aloud: In an interview, talk through your thought process as you write the query. This shows the interviewer your problem-solving skills.
  • Ask Clarifying Questions: If a question is ambiguous, don’t hesitate to ask for clarification.
  • Know Your Database System: While the core SQL concepts are the same, there are slight syntax differences between different database systems (MySQL, PostgreSQL, SQL Server, Oracle). Be aware of the system you’ll be tested on.

By mastering these concepts and practicing extensively, you’ll be well-prepared to tackle common SQL interview questions and demonstrate your proficiency in this essential skill. Good luck!

Leave a Comment

Your email address will not be published. Required fields are marked *

Scroll to Top