Must-Know Top 30 SQL Query Interview Questions and Answers in 2024

Are you preparing for an upcoming SQL interview? If yes, then you’ve come to the right place! SQL (Structured Query Language) is a programming language used to manage and manipulate relational databases. It’s an essential skill for roles like Database Administrator, Data Analyst, and Business Intelligence Developer.

In this comprehensive blog post, we’ll cover the top 30 SQL query interview questions and answers that will help you ace your next interview. Let’s dive in!

1. Write a query to fetch the EmpFname from the EmployeeInfo table in uppercase and use the alias name as EmpName.

SELECT UPPER(EmpFname) AS EmpName FROM EmployeeInfo;

2. Write a query to fetch the number of employees working in the ‘HR’ department.

SELECT COUNT(*) FROM EmployeeInfo WHERE Department = 'HR';

3. Write a query to get the current date.

In SQL Server:

SELECT GETDATE();

In MySQL:

SELECT SYSTDATE();

4. Write a query to retrieve the first four characters of EmpLname from the EmployeeInfo table.

SELECT SUBSTRING(EmpLname, 1, 4) FROM EmployeeInfo;

5. Write a query to fetch only the place name (string before brackets) from the Address column of the EmployeeInfo table.

Using the MID function in MySQL:

SELECT MID(Address, 0, LOCATE('(',Address)) FROM EmployeeInfo;

Using SUBSTRING in SQL Server:

SELECT SUBSTRING(Address, 1, CHARINDEX('(',Address)) FROM EmployeeInfo;

6. Write a query to create a new table with the same data and structure as another table.

Using the SELECT INTO command in SQL Server:

SELECT * INTO NewTable FROM EmployeeInfo WHERE 1 = 0;

Using the CREATE TABLE command in MySQL:

CREATE TABLE NewTable AS SELECT * FROM EmployeeInfo;

7. Write a query to find all employees whose salary is between 50,000 and 100,000.

SELECT * FROM EmployeePosition WHERE Salary BETWEEN 50000 AND 100000;

8. Write a query to find the names of employees whose first name begins with ‘S’.

SELECT * FROM EmployeeInfo WHERE EmpFname LIKE 'S%';

9. Write a query to fetch the top N records.

Using the TOP command in SQL Server:

SELECT TOP N * FROM EmployeePosition ORDER BY Salary DESC;

Using the LIMIT command in MySQL:

SELECT * FROM EmployeePosition ORDER BY Salary DESC LIMIT N;

10. Write a query to retrieve the EmpFname and EmpLname in a single column as “FullName”. The first and last names should be separated by a space.

SELECT CONCAT(EmpFname, ' ', EmpLname) AS 'FullName' FROM EmployeeInfo;

11. Write a query to find the number of employees whose date of birth is between 05/02/1970 and 12/31/1975, grouped by gender.

SELECT COUNT(*), Gender FROM EmployeeInfo WHERE DOB BETWEEN '1970-02-05' AND '1975-12-31'GROUP BY Gender;

12. Write a query to fetch all records from the EmployeeInfo table, ordered by EmpLname in descending order and Department in ascending order.

SELECT * FROM EmployeeInfoORDER BY EmpLname DESC, Department ASC;

13. Write a query to fetch details of employees whose last name ends with ‘a’ and contains five letters.

SELECT *FROM EmployeeInfoWHERE EmpLname LIKE '____a';

14. Write a query to fetch details of all employees excluding those with the first names ‘Sanjay’ and ‘Sonia’ from the EmployeeInfo table.

SELECT *FROM EmployeeInfoWHERE EmpFname NOT IN ('Sanjay', 'Sonia');

15. Write a query to fetch details of employees with the address as ‘DELHI(DEL)’.

SELECT *FROM EmployeeInfoWHERE Address LIKE 'DELHI(DEL)%';

16. Write a query to fetch all employees who also hold a managerial position.

SELECT E.EmpFname, E.EmpLname, P.EmpPositionFROM EmployeeInfo EINNER JOIN EmployeePosition P ON E.EmpID = P.EmpIDAND P.EmpPosition = 'Manager';

17. Write a query to fetch the department-wise count of employees sorted by the department’s count in ascending order.

SELECT Department, COUNT(EmpID) AS EmpDeptCountFROM EmployeeInfoGROUP BY DepartmentORDER BY EmpDeptCount ASC;

18. Write a query to calculate the even and odd records from a table.

To retrieve even records:

SELECT EmpIDFROM (SELECT ROW_NUMBER() OVER (ORDER BY EmpID) AS RowNo, EmpID FROM EmployeeInfo)WHERE MOD(RowNo, 2) = 0;

To retrieve odd records:

SELECT EmpID FROM (SELECT ROW_NUMBER() OVER (ORDER BY EmpID) AS RowNo, EmpID FROM EmployeeInfo)WHERE MOD(RowNo, 2) = 1;

19. Write a SQL query to retrieve employee details from the EmployeeInfo table who have a date of joining in the EmployeePosition table.

SELECT *FROM EmployeeInfo EWHERE EXISTS (    SELECT *    FROM EmployeePosition P    WHERE E.EmpID = P.EmpID);

20. Write a query to retrieve the two minimum and two maximum salaries from the EmployeePosition table.

To retrieve two minimum salaries:

SELECT DISTINCT SalaryFROM EmployeePosition E1WHERE 2 >= (    SELECT COUNT(DISTINCT Salary)    FROM EmployeePosition E2    WHERE E1.Salary >= E2.Salary)ORDER BY E1.Salary ASC;

To retrieve two maximum salaries:

SELECT DISTINCT SalaryFROM EmployeePosition E1WHERE 2 >= (    SELECT COUNT(DISTINCT Salary)    FROM EmployeePosition E2    WHERE E1.Salary <= E2.Salary)ORDER BY E1.Salary DESC;

21. Write a query to find the Nth highest salary from the table without using the TOP/LIMIT keyword.

SELECT SalaryFROM EmployeePosition E1WHERE N-1 = (    SELECT COUNT(DISTINCT Salary)    FROM EmployeePosition E2    WHERE E2.Salary > E1.Salary);

22. Write a query to retrieve duplicate records from a table.

SELECT EmpID, EmpFname, Department, COUNT(*)FROM EmployeeInfoGROUP BY EmpID, EmpFname, DepartmentHAVING COUNT(*) > 1;

23. Write a query to retrieve the list of employees working in the same department.

SELECT DISTINCT E.EmpID, E.EmpFname, E.DepartmentFROM EmployeeInfo E, EmployeeInfo E1WHERE E.Department = E1.DepartmentAND E.EmpID != E1.EmpID;

24. Write a query to retrieve the last 3 records from the EmployeeInfo table.

SELECT *FROM EmployeeInfoWHERE EmpID IN (    SELECT EmpID    FROM (        SELECT EmpID, ROW_NUMBER() OVER (ORDER BY EmpID DESC) AS RowNum        FROM EmployeeInfo    ) AS subquery    WHERE RowNum <= 3);

25. Write a query to find the third-highest salary from the EmployeePosition table.

SELECT SalaryFROM (    SELECT Salary, ROW_NUMBER() OVER (ORDER BY Salary DESC) AS RowNum    FROM EmployeePosition) AS subqueryWHERE RowNum = 3;

26. Write a query to display the first and the last record from the EmployeeInfo table.

To display the first record:

SELECT *FROM EmployeeInfoWHERE EmpID = (SELECT MIN(EmpID) FROM EmployeeInfo);

To display the last record:

SELECT *FROM EmployeeInfoWHERE EmpID = (SELECT MAX(EmpID) FROM EmployeeInfo);

27. Write a query to add email validation to your database.

SELECT EmailFROM EmployeeInfoWHERE Email NOT LIKE '%@%_._%';

28. Write a query to retrieve departments that have less than 2 employees working in them.

SELECT Department, COUNT(EmpID) AS EmpNoFROM EmployeeInfoGROUP BY DepartmentHAVING COUNT(EmpID) < 2;

29. Write a query to retrieve EmpPosition along with the total salaries paid for each position.

SELECT EmpPosition, SUM(Salary) AS TotalSalaryFROM EmployeePositionGROUP BY EmpPosition;

30. Write a query to fetch 50% of the records from the EmployeeInfo table.

SELECT *FROM (    SELECT *, ROW_NUMBER() OVER (ORDER BY EmpID) AS RowNum    FROM EmployeeInfo) AS subqueryWHERE RowNum <= (SELECT COUNT(*) FROM EmployeeInfo) / 2;

That’s it! These are the top 30 SQL query interview questions and answers that you should practice to ace your next SQL interview. Remember, practice is the key to mastering SQL queries, so keep practicing and good luck!