25 SQL Queries Interview Questions and Answers

SQL (Structured Query Language) is a fundamental tool for managing and manipulating relational databases. It is essential for tasks such as querying data, updating records, and managing database structures. SQL’s versatility and efficiency make it a critical skill for roles in data analysis, software development, and database administration. Mastery of SQL can significantly enhance your ability to work with large datasets and optimize database performance.

This article offers a curated selection of SQL query questions designed to help you prepare for technical interviews. By working through these examples, you will gain a deeper understanding of SQL concepts and improve your problem-solving abilities, ensuring you are well-prepared to demonstrate your expertise in any interview setting.

SQL Queries Interview Questions and Answers

1. Write a query to select all columns from a table named ’employees’.

To select all columns from a table named ’employees’, use:

SELECT * FROM employees;

This query retrieves all data from the ’employees’ table without listing each column individually.

2. Write a query to select all employees whose salary is greater than 50000.

To select employees with a salary greater than 50000, use:

SELECT * FROM employees
WHERE salary > 50000;

3. Write a query to join two tables, ’employees’ and ‘departments’, on the department_id column.

To join ’employees’ and ‘departments’ tables on the department_id column, use:

SELECT 
    employees.employee_id, 
    employees.employee_name, 
    departments.department_name
FROM 
    employees
INNER JOIN 
    departments 
ON 
    employees.department_id = departments.department_id;

This query combines rows from both tables where department_id matches.

4. Write a query to count the number of employees in each department.

To count employees in each department, use:

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

5. Write a query to group employees by their job title and calculate the average salary for each group.

To group employees by job title and calculate the average salary, use:

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

6. Write a query to filter groups of employees having an average salary greater than 60000.

To filter groups with an average salary greater than 60000, use:

SELECT department, AVG(salary) as avg_salary
FROM employees
GROUP BY department
HAVING AVG(salary) > 60000;

7. Write a query to sort employees by their hire date in descending order.

To sort employees by hire date in descending order, use:

SELECT * 
FROM employees 
ORDER BY hire_date DESC;

8. Write a query to find employees who earn more than the average salary using a subquery.

To find employees earning more than the average salary, use a subquery:

SELECT employee_id, employee_name, salary
FROM employees
WHERE salary > (SELECT AVG(salary) FROM employees);

9. Write a query to select distinct job titles from the ’employees’ table.

To select distinct job titles from the ’employees’ table, use:

SELECT DISTINCT job_title
FROM employees;

10. Write a query to concatenate the first name and last name of employees.

To concatenate the first and last names of employees, use:

SELECT CONCAT(first_name, ' ', last_name) AS full_name
FROM employees;

Or, in PostgreSQL:

SELECT first_name || ' ' || last_name AS full_name
FROM employees;

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

To find duplicate records, use:

SELECT column1, column2, COUNT(*)
FROM table_name
GROUP BY column1, column2
HAVING COUNT(*) > 1;

Replace column1 and column2 with the columns to check for duplicates.

12. Write a query to calculate the cumulative sum of salaries within each department.

To calculate the cumulative sum of salaries within each department, use:

SELECT 
    department_id,
    employee_id,
    salary,
    SUM(salary) OVER (PARTITION BY department_id ORDER BY employee_id) AS cumulative_salary
FROM 
    employees;

13. Explain the concept of a foreign key and how it enforces referential integrity.

A foreign key is a column in one table that references a column in another table, ensuring referential integrity. For example:

CREATE TABLE Departments (
    DepartmentID INT PRIMARY KEY,
    DepartmentName VARCHAR(50)
);

CREATE TABLE Employees (
    EmployeeID INT PRIMARY KEY,
    EmployeeName VARCHAR(50),
    DepartmentID INT,
    FOREIGN KEY (DepartmentID) REFERENCES Departments(DepartmentID)
);

14. Write a query to extract the year from the hire date of employees.

To extract the year from the hire date, use:

SELECT employee_id, hire_date, YEAR(hire_date) AS hire_year
FROM employees;

15. Write a query to combine the results of two queries that select employees from different departments.

To combine results from different departments, use UNION:

SELECT employee_id, employee_name, department
FROM employees
WHERE department = 'Sales'
UNION
SELECT employee_id, employee_name, department
FROM employees
WHERE department = 'Marketing';

16. Explain what an index is and how it improves query performance.

An index improves query performance by allowing the database to find rows more quickly. Types include primary, unique, composite, clustered, and non-clustered indexes.

17. Write a query using a window function to rank employees by their salary within each department.

To rank employees by salary within each department, use:

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

18. Write a query using a Common Table Expression (CTE) to list employees and their managers.

A Common Table Expression (CTE) simplifies complex queries. For listing employees and their managers:

WITH EmployeeCTE AS (
    SELECT 
        e.EmployeeID,
        e.EmployeeName,
        e.ManagerID,
        m.EmployeeName AS ManagerName
    FROM 
        Employees e
    LEFT JOIN 
        Employees m ON e.ManagerID = m.EmployeeID
)
SELECT 
    EmployeeID,
    EmployeeName,
    ManagerID,
    ManagerName
FROM 
    EmployeeCTE;

19. Write a recursive query to generate a hierarchical list of employees and their subordinates.

To generate a hierarchical list of employees and subordinates, use a recursive CTE:

WITH RECURSIVE EmployeeHierarchy AS (
    SELECT EmployeeID, EmployeeName, ManagerID
    FROM Employees
    WHERE ManagerID IS NULL
    UNION ALL
    SELECT e.EmployeeID, e.EmployeeName, e.ManagerID
    FROM Employees e
    INNER JOIN EmployeeHierarchy eh ON e.ManagerID = eh.EmployeeID
)
SELECT * FROM EmployeeHierarchy;

20. Write a trigger to log changes to the ’employees’ table.

To log changes to the ’employees’ table, create a trigger:

CREATE TABLE employee_changes (
    change_id INT AUTO_INCREMENT PRIMARY KEY,
    employee_id INT,
    change_type VARCHAR(10),
    change_time TIMESTAMP DEFAULT CURRENT_TIMESTAMP
);

DELIMITER //

CREATE TRIGGER log_employee_changes
AFTER UPDATE ON employees
FOR EACH ROW
BEGIN
    INSERT INTO employee_changes (employee_id, change_type)
    VALUES (NEW.employee_id, 'UPDATE');
END;

//

DELIMITER ;

21. Write a query to create a view that shows employees and their department names.

To create a view showing employees and department names:

CREATE VIEW EmployeeDepartmentView AS
SELECT e.employee_id, e.employee_name, d.department_name
FROM employees e
JOIN departments d ON e.department_id = d.department_id;

22. Describe techniques for optimizing SQL query performance.

Techniques for optimizing SQL query performance include indexing, query rewriting, schema design, using EXPLAIN, partitioning, connection pooling, and caching.

23. Discuss the benefits and use cases of partitioning a large table.

Partitioning a large table divides it into smaller parts, improving query performance and manageability. Use cases include time-series data, geographical data, archival, and data warehousing.

24. Write a query to parse and extract data from a JSON column.

To extract data from a JSON column in PostgreSQL:

SELECT
    details->>'name' AS name,
    details->>'email' AS email
FROM
    users;

In MySQL:

SELECT
    JSON_UNQUOTE(JSON_EXTRACT(details, '$.name')) AS name,
    JSON_UNQUOTE(JSON_EXTRACT(details, '$.email')) AS email
FROM
    users;

25. Write a query to parse and extract data from an XML column.

To parse and extract data from an XML column in SQL Server:

DECLARE @xmlData XML
SET @xmlData = '<root><item><id>1</id><name>Item1</name></item><item><id>2</id><name>Item2</name></item></root>'

SELECT 
    x.value('(id)[1]', 'INT') AS ItemID,
    x.value('(name)[1]', 'VARCHAR(100)') AS ItemName
FROM 
    @xmlData.nodes('/root/item') AS T(x)