20 Medium-Level SQL Interview Questions with Answers and Examples

August 30, 2024

1. Write a SQL query to find the second-highest salary.

To find the second-highest salary, we can use the LIMIT clause with a subquery.

Sample Table: Employees

SQL Query:

SELECT MAX(salary) AS second_highest_salary FROM Employees WHERE salary < (SELECT MAX(salary) FROM Employees);

Explanation:

The subquery (SELECT MAX(salary) FROM Employees) finds the highest salary.
The main query finds the maximum salary that is less than the highest salary, giving us the second-highest salary.

2. How would you optimize a slow SQL query?

Optimizing a slow SQL query involves several strategies, including:

Indexing: Create indexes on columns that are frequently used in WHERE, JOIN, ORDER BY, or GROUP BY clauses.
*Avoiding SELECT : Instead of selecting all columns, select only the columns you need.
Using Joins Efficiently: Replace subqueries with joins wherever possible, as joins are generally faster.
Query Refactoring: Simplify complex queries by breaking them down into smaller subqueries or using common table expressions (CTEs).
Checking Execution Plan: Use tools like EXPLAIN or EXPLAIN ANALYZE to view the query execution plan and identify bottlenecks.

Example: If you have a query like:

SELECT * FROM Orders WHERE customer_id = 5;

You can optimize it by adding an index on the customer_id column:

CREATE INDEX idx_customer_id ON Orders(customer_id);

3. What is the difference between INNER JOIN and OUTER JOIN?

INNER JOIN returns only the rows that have matching values in both tables.
OUTER JOIN can be of three types:
- LEFT OUTER JOIN: Returns all rows from the left table and the matched rows from the right table. If no match is found, NULL is returned.
- RIGHT OUTER JOIN: Returns all rows from the right table and the matched rows from the left table. If no match is found, NULL is returned.
- FULL OUTER JOIN: Returns all rows when there is a match in either left or right table. If no match is found, NULL values are returned for the unmatched rows.

Sample Tables:

Employees
Departments

INNER JOIN Example:

SELECT Employees.name, Departments.dept_name FROM Employees INNER JOIN Departments ON Employees.dept_id = Departments.dept_id;

LEFT OUTER JOIN Example:

SELECT Employees.name, Departments.dept_name FROM Employees LEFT JOIN Departments ON Employees.dept_id = Departments.dept_id;

RIGHT OUTER JOIN Example:

SELECT Employees.name, Departments.dept_name FROM Employees RIGHT JOIN Departments ON Employees.dept_id = Departments.dept_id;

Explanation:

INNER JOIN returns rows where dept_id is 10 and 20.
LEFT JOIN returns all employees with NULL for unmatched departments (if any).
RIGHT JOIN returns all departments with NULL for unmatched employees.

4. Write a SQL query to find the top 3 departments with the highest average salary.

Sample Tables:

Employees
Departments

SQL Query:

SELECT d.dept_name, AVG(e.salary) AS avg_salary FROM Employees e JOIN Departments d ON e.dept_id = d.dept_id GROUP BY d.dept_name ORDER BY avg_salary DESC LIMIT 3;

Explanation:

We join Employees and Departments on dept_id.
Group by department and calculate the average salary.
Sort by average salary in descending order and limit the result to the top 3 departments.

5. How do you handle duplicate rows in a SQL query?

To handle duplicate rows, you can use the DISTINCT keyword, or you can remove duplicates using ROW_NUMBER() with a CTE.

Sample Table: Orders

Removing Duplicates Using DISTINCT:

SELECT DISTINCT customer_id, product FROM Orders;

Removing Duplicates Using ROW_NUMBER():

WITH CTE AS ( SELECT *, ROW_NUMBER() OVER (PARTITION BY customer_id, product ORDER BY order_id) AS rn FROM Orders ) DELETE FROM Orders WHERE order_id IN (SELECT order_id FROM CTE WHERE rn > 1);

Explanation:

DISTINCT removes duplicate rows based on all selected columns.
ROW_NUMBER() assigns a unique sequential integer to rows within a partition of a result set, allowing for the deletion of duplicates while retaining the first occurrence.

6. Write a SQL query to find the employees who have the same name and work in the same department.

Sample Table: Employees

SQL Query:

SELECT name, dept_id, COUNT(*) FROM Employees GROUP BY name, dept_id HAVING COUNT(*) > 1;

Explanation:

We group by name and dept_id.
Use HAVING COUNT(*) > 1 to filter out names that appear more than once in the same department.

7. What is the difference between UNION and UNION ALL?

UNION: Combines the results of two or more SELECT statements and removes duplicates.
UNION ALL: Combines the results of two or more SELECT statements without removing duplicates.

UNION Example:

SELECT name FROM Table1 UNION SELECT name FROM Table2;

UNION ALL Example:

SELECT name FROM Table1 UNION ALL SELECT name FROM Table2;

Explanation:

UNION will return unique names from both tables: John, Jane, Mike.
UNION ALL will return all names including duplicates: John, Jane, Jane, Mike.

8. Write a SQL query to find the departments with no employees.

Sample Tables:

Employees
Departments

SQL Query:

SELECT dept_name FROM Departments d LEFT JOIN Employees e ON d.dept_id = e.dept_id WHERE e.emp_id IS NULL;

Explanation:

We perform a LEFT JOIN between Departments and Employees.
Filter where emp_id is NULL to find departments with no employees.

9. How do you use indexing to improve SQL query performance?

Indexing improves SQL query performance by allowing the database to find rows faster instead of scanning the entire table. Indexes can be created on columns that are frequently used in WHERE, JOIN, ORDER BY, or GROUP BY clauses.

Example: If you have a table Orders and often query by customer_id, you can create an index:

CREATE INDEX idx_customer_id ON Orders(customer_id);

This index will speed up queries like:

SELECT * FROM Orders WHERE customer_id = 123;

10. Write a SQL query to find the employees who have worked for more than 5 years.

Sample Table: Employees

SQL Query:

SELECT name FROM Employees WHERE DATEDIFF(NOW(), join_date) > 5 * 365;

Explanation:

DATEDIFF(NOW(), join_date) calculates the number of days since the employee joined.
We filter for employees who have been with the company for more than 5 years.

11. What is the difference between SUBQUERY and JOIN?

Subquery: A query nested inside another query. It can return a single value or a result set that can be used in the main query.
JOIN: Combines rows from two or more tables based on a related column between them.

Example of Subquery:

SELECT name FROM Employees WHERE dept_id = (SELECT dept_id FROM Departments WHERE dept_name = ‘IT’);

Example of JOIN:

SELECT e.name, d.dept_name FROM Employees e JOIN Departments d ON e.dept_id = d.dept_id WHERE d.dept_name = ‘IT’;

Explanation:

Subquery is often used when you need to filter results based on an aggregated value or other criteria from a different table.
JOIN is used to retrieve related data from multiple tables in a single query.

12. Write a SQL query to find the top 2 products with the highest sales.

Sample Table: Sales

SQL Query:

SELECT product_id, SUM(quantity) AS total_sales FROM Sales GROUP BY product_id ORDER BY total_sales DESC LIMIT 2;

Explanation:

We sum up the quantity of each product and sort by total_sales in descending order.
The LIMIT 2 clause restricts the result to the top 2 products.

13. How do you use stored procedures to improve SQL query performance?

Stored procedures can improve SQL query performance by:

1. Precompilation: Stored procedures are compiled and stored in the database, which speeds up execution.
2. Reduced Network Traffic: A stored procedure can execute multiple queries at once, reducing the number of calls between the application and the database.
3. Reusability: Stored procedures can be reused across different applications, ensuring consistency and reducing development time.

Example of a Stored Procedure:

CREATE PROCEDURE GetEmployeeDetails(IN emp_id INT) BEGIN SELECT * FROM Employees WHERE id = emp_id; END;

Explanation:

This stored procedure GetEmployeeDetails accepts an emp_id as input and returns the corresponding employee’s details.

14. Write a SQL query to find the customers who have placed an order but have not made a payment.

Sample Tables:

Orders
Payments

SQL Query:

SELECT o.customer_id FROM Orders o LEFT JOIN Payments p ON o.order_id = p.order_id WHERE p.payment_id IS NULL;

Explanation:

We perform a LEFT JOIN between Orders and Payments.
Filter where payment_id is NULL to find orders without corresponding payments.

15. What is the difference between GROUP BY and HAVING?

GROUP BY: Groups rows that have the same values in specified columns into aggregate data.
HAVING: Filters the result set after grouping. It’s similar to WHERE but used for aggregate functions.

Example with GROUP BY and HAVING:

Sample Table: Orders

SELECT customer_id, SUM(amount) AS total_amount FROM Orders GROUP BY customer_id HAVING total_amount > 600

Explanation:

GROUP BY groups orders by customer_id.
HAVING filters out customers with a total order amount of 600 or less.

16. Write a SQL query to find the employees who work in the same department as their manager.

Sample Table: Employees

SQL Query:

SELECT e.name FROM Employees e JOIN Employees m ON e.manager_id = m.emp_id WHERE e.dept_id = m.dept_id;

Explanation:

We perform a self-join on the Employees table where the manager_id of one row matches the emp_id of another.
Filter by dept_id to ensure they work in the same department.

17. How do you use window functions to solve complex queries?

Window functions are used to perform calculations across a set of table rows related to the current row. Unlike aggregate functions, window functions do not cause rows to become grouped into a single output row.

Example using ROW_NUMBER():

Sample Table: Employees

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

Explanation:

- ROW_NUMBER() assigns a unique sequential integer to rows within a result set, ordered by salary in descending order.

18. Write a SQL query to find the top 3 products with the highest average price.

Sample Table: Products

SQL Query:

SELECT name, AVG(price) AS avg_price FROM Products GROUP BY name ORDER BY avg_price DESC LIMIT 3;

Explanation:

We group by name and calculate the average price.
Sort by avg_price in descending order and limit the result to the top 3 products.

19. What is the difference between TRUNCATE and DELETE?

DELETE: Removes rows from a table based on a WHERE condition. It can be rolled back and triggers can fire.
TRUNCATE: Removes all rows from a table without logging individual row deletions. It cannot be rolled back and is faster than DELETE.

Example using DELETE:

DELETE FROM Employees WHERE dept_id = 10;

Example using TRUNCATE:

TRUNCATE TABLE Employees;

Explanation:

Use DELETE when you want to remove specific rows.
Use TRUNCATE when you want to quickly remove all rows from a table.

20. Write a SQL query to find the employees who have not taken any leave in the last 6 months.

Sample Tables:

Employees
Leaves

SQL Query:

SELECT e.name FROM Employees e LEFT JOIN Leaves l ON e.emp_id = l.emp_id AND l.leave_date >= DATE_SUB(NOW(), INTERVAL 6 MONTH) WHERE l.leave_id IS NULL;

Explanation:

We use a LEFT JOIN to link Employees and Leaves, filtering the Leaves to those within the last 6 months.
If there are no matching leave_ids, it means the employee hasn’t taken any leave.

Conclusion: 20 Medium-Level SQL Interview Questions with Answers and Examples

These 20 SQL interview questions cover a wide range of concepts and techniques that are essential for anyone looking to strengthen their SQL skills. By understanding these questions and practicing the provided examples, you’ll be better prepared to tackle medium-level SQL queries in interviews and real-world scenarios.