15 SQL Query Based Interview Questions and Answers

SQL remains a cornerstone of database management and manipulation, essential for handling structured data in various applications. Its ability to efficiently query, update, and manage data makes it indispensable for roles in data analysis, software development, and database administration. Mastery of SQL is crucial for anyone looking to work with relational databases, as it forms the backbone of data-driven decision-making processes.

This article offers a curated selection of SQL query-based questions designed to test and enhance your understanding of database concepts and query optimization. By working through these examples, you’ll be better prepared to demonstrate your SQL proficiency and problem-solving skills in technical interviews.

SQL Query Based Interview Questions and Answers

1. Write a query to join two tables, ‘orders’ and ‘customers’, on the ‘customer_id’ column.

To join two tables, ‘orders’ and ‘customers’, on the ‘customer_id’ column, use the SQL JOIN clause to combine rows based on a related column.

Example:

SELECT orders.order_id, customers.customer_name, orders.order_date
FROM orders
JOIN customers ON orders.customer_id = customers.customer_id;

2. Write a query to group data by the ‘department’ column in a table named ’employees’ and count the number of employees in each department.

To group data by the ‘department’ column in a table named ’employees’ and count the number of employees in each department, use the GROUP BY clause with the COUNT function.

Example:

SELECT department, COUNT(*) AS employee_count
FROM employees
GROUP BY department;

3. Write a query to filter groups having more than 10 employees in the previous question.

To filter groups having more than 10 employees, use the HAVING clause to filter records that work on aggregated data.

Example:

SELECT group_id, COUNT(employee_id) as employee_count
FROM employees
GROUP BY group_id
HAVING COUNT(employee_id) > 10;

4. Write a query to find employees who earn more than the average salary in their department using a subquery.

To find employees who earn more than the average salary in their department, use a subquery to calculate the average salary for each department and compare each employee’s salary to this average.

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

5. Write a query to rank employees based on their salaries within each department using a window function.

Window functions perform calculations across a set of table rows related to the current row. Use the RANK() function to rank employees based on their salaries within each department.

Example:

SELECT 
    employee_id,
    department_id,
    salary,
    RANK() OVER (PARTITION BY department_id ORDER BY salary DESC) AS salary_rank
FROM 
    employees;

6. Write a query using a Common Table Expression (CTE) to find the top 3 highest-paid employees in each department.

To find the top 3 highest-paid employees in each department using a Common Table Expression (CTE), use the following query:

WITH RankedSalaries AS (
    SELECT 
        EmployeeID,
        DepartmentID,
        Salary,
        ROW_NUMBER() OVER (PARTITION BY DepartmentID ORDER BY Salary DESC) AS Rank
    FROM 
        Employees
)
SELECT 
    EmployeeID,
    DepartmentID,
    Salary
FROM 
    RankedSalaries
WHERE 
    Rank <= 3;

7. Write a stored procedure to insert a new record into a table named ‘products’.

A stored procedure is a set of SQL statements that can be stored and executed on the database server. To insert a new record into a table named ‘products’, create a stored procedure that accepts parameters for the product details and performs the insertion.

Example:

CREATE PROCEDURE InsertProduct
    @ProductName NVARCHAR(100),
    @Price DECIMAL(10, 2),
    @Quantity INT
AS
BEGIN
    INSERT INTO products (ProductName, Price, Quantity)
    VALUES (@ProductName, @Price, @Quantity);
END;

To call this stored procedure and insert a new record:

EXEC InsertProduct 'New Product', 19.99, 100;

8. Write a trigger that automatically updates the ‘last_updated’ column in a table named ‘inventory’ whenever a record is updated.

To create a trigger that automatically updates the ‘last_updated’ column in a table named ‘inventory’ whenever a record is updated, use the following code:

CREATE TRIGGER update_last_updated
BEFORE UPDATE ON inventory
FOR EACH ROW
BEGIN
    SET NEW.last_updated = NOW();
END;

9. Write a query to create a view that shows the total sales per customer from the ‘sales’ table.

A view is a virtual table that simplifies complex queries and enhances security by restricting access to specific data. Create a view to show the total sales per customer.

Example:

CREATE VIEW CustomerSales AS
SELECT customer_id, SUM(sales_amount) AS total_sales
FROM sales
GROUP BY customer_id;

10. Write a query to handle division by zero errors when calculating the average order value in a table named ‘orders’.

To handle division by zero errors when calculating the average order value, use conditional logic to check for zero values before performing the division.

Example:

SELECT 
    CASE 
        WHEN COUNT(*) = 0 THEN 0 
        ELSE SUM(order_value) / COUNT(*) 
    END AS average_order_value
FROM orders;

11. Write a recursive query to display a hierarchy of employees and their managers from a table named ’employee_hierarchy’.

Recursive queries are used to query hierarchical data. Use a recursive Common Table Expression (CTE) to display a hierarchy of employees and their managers.

Example:

WITH RECURSIVE EmployeeCTE AS (
    SELECT employee_id, employee_name, manager_id
    FROM employee_hierarchy
    WHERE manager_id IS NULL

    UNION ALL

    SELECT e.employee_id, e.employee_name, e.manager_id
    FROM employee_hierarchy e
    INNER JOIN EmployeeCTE cte
    ON e.manager_id = cte.employee_id
)
SELECT employee_id, employee_name, manager_id
FROM EmployeeCTE;

12. Write a query to extract a value from a JSON column named ‘data’ in a table named ‘json_table’.

To extract a value from a JSON column named ‘data’ in a table named ‘json_table’, use the JSON functions provided by your SQL database.

Example in PostgreSQL:

SELECT data->>'name' AS name_value
FROM json_table;

In MySQL:

SELECT JSON_EXTRACT(data, '$.name') AS name_value
FROM json_table;

In SQL Server:

SELECT JSON_VALUE(data, '$.name') AS name_value
FROM json_table;

13. Write a query to calculate the total sales amount from a table named ‘sales’.

To calculate the total sales amount from a table named ‘sales’, use the SUM function.

Example:

SELECT SUM(sales_amount) AS total_sales
FROM sales;

14. Write a query to start a transaction, update a record in a table named ‘accounts’, and then commit the transaction.

A transaction is a sequence of operations performed as a single logical unit of work. Use BEGIN TRANSACTION, UPDATE, and COMMIT to manage transactions.

Example:

BEGIN TRANSACTION;

UPDATE accounts
SET balance = balance + 100
WHERE account_id = 1;

COMMIT;

15. Write a query to perform a self join on a table named ’employees’ to find pairs of employees who work in the same department.

To perform a self join on a table named ’employees’ to find pairs of employees who work in the same department, use the following query:

SELECT e1.employee_id AS Employee1, e2.employee_id AS Employee2, e1.department_id
FROM employees e1
JOIN employees e2 ON e1.department_id = e2.department_id
WHERE e1.employee_id < e2.employee_id;