Top 33 SQL Query Interview Questions You Must Practice In 2024

Using SUBSTRING

CREATE TABLE NewTable AS SELECT * FROM EmployeeInfo;

SELECT TOP N * FROM EmployeePosition ORDER BY Salary DESC;

By using the LIMIT command in MySQL:

SELECT * FROM EmpPosition ORDER BY Salary DESC LIMIT N;

Q10. Write a query to retrieve the EmpFname and EmpLname in a single column as “FullName”. The first name and the last name must be separated with space.

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

Q11. Write a query find number of employees whose DOB is between 02/05/1970 to 31/12/1975 and are grouped according to gender

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

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

To order the records in ascending and descnding order, you have to use the ORDER BY statement in SQL.

SELECT * FROM EmployeeInfo ORDER BY EmpFname desc, Department asc;

Q13. Write a query to fetch details of employees whose EmpLname ends with an alphabet ‘A’ and contains five alphabets.

To fetch details mathcing a certain value, you have to use the LIKE operator in SQL.

 
SELECT * FROM EmployeeInfo WHERE EmpLname LIKE '____a';

Q14. Write a query to fetch details of all employees excluding the employees with first names, “Sanjay” and “Sonia” from the EmployeeInfo table.

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

Q15. Write a query to fetch details of employees with the address as “DELHI(DEL)”.

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

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

 
SELECT E.EmpFname, E.EmpLname, P.EmpPosition 
FROM EmployeeInfo E INNER JOIN EmployeePosition P ON 
E.EmpID = P.EmpID AND P.EmpPosition IN ('Manager');

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

 
SELECT Department, count(EmpID) AS EmpDeptCount 
FROM EmployeeInfo GROUP BY Department 
ORDER BY EmpDeptCount ASC;

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

To retrieve the even records from a table, you have to use the MOD() function as follows:

 
SELECT EmpID FROM (SELECT rowno, EmpID from EmployeeInfo) WHERE MOD(rowno,2)=0;

Similarly, to retrieve the odd records from a table, you can write a query as follows:

 
SELECT EmpID FROM (SELECT rowno, EmpID from EmployeeInfo) WHERE MOD(rowno,2)=1;

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

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

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

To retrieve two minimum salaries, you can write a query as below:

 
SELECT DISTINCT Salary FROM EmployeePosition E1 
 WHERE 2 >= (SELECTCOUNT(DISTINCT Salary)FROM EmployeePosition E2 
  WHERE E1.Salary >= E2.Salary) ORDER BY E1.Salary DESC;

To retrieve two maximum salaries, you can write a query as below: 

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

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

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

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

 
SELECT EmpID, EmpFname, Department COUNT(*) 
FROM EmployeeInfo GROUP BY EmpID, EmpFname, Department 
HAVING COUNT(*) > 1;

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

 
Select DISTINCT E.EmpID, E.EmpFname, E.Department 
FROM EmployeeInfo E, Employee E1 
WHERE E.Department = E1.Department AND E.EmpID != E1.EmpID;

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

SELECT * FROM EmployeeInfo WHERE 
EmpID <=3 UNION SELECT * FROM 
(SELECT * FROM EmployeeInfo E ORDER BY E.EmpID DESC) 
AS E1 WHERE E1.EmpID <=3;

Q25. Write a query to find the third-highest salary from the EmpPosition table.

SELECT TOP 1 salary
FROM(
SELECT TOP 3 salary
FROM employee_table
ORDER BY salary DESC) AS emp
ORDER BY salary ASC;

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

To display the first record from the EmployeeInfo table, you can write a query as follows:

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

To display the last record from the EmployeeInfo table, you can write a query as follows:

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

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

SELECT Email FROM EmployeeInfo WHERE NOT REGEXP_LIKE(Email, ‘[A-Z0-9._%+-]+@[A-Z0-9.-]+.[A-Z]{2,4}’, ‘i’);

Q28. Write a query to retrieve Departments who have less than 2 employees working in it.

SELECT DEPARTMENT, COUNT(EmpID) as 'EmpNo' FROM EmployeeInfo GROUP BY DEPARTMENT HAVING COUNT(EmpD) < 2;

Q29. Write a query to retrieve EmpPostion along with total salaries paid for each of them.

SELECT EmpPosition, SUM(Salary) from EmployeePosition GROUP BY EmpPosition;

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

 SELECT * FROM EmployeeInfo WHERE EmpID <= (SELECT COUNT(EmpID)/2 from EmployeeInfo); 

 Intermediate scenario-based SQL Interview questions

Q1. How do you read the last five records from a database using a SQL query?

To retrieve the last five records from a database using a SQL query, you can use the ORDER BY clause combined with LIMIT. Here’s an example query:

SELECT * 

FROM your_table

ORDER BY id DESC

LIMIT 5;

Replace your_table_name with the name of your table and your_primary_key_column with the primary key column that defines the order of records. The ORDER BY your_primary_key_column DESC part sorts the records in descending order based on the primary key column, and LIMIT 5 limits the result to the last five records.

Q2. Write a SQL query that will provide you with the 10th-highest employee salary from an Employee table.

Here’s an example SQL query:

SELECT salary

FROM (

    SELECT salary, ROW_NUMBER() OVER (ORDER BY salary DESC) AS row_num

    FROM Employee

) AS ranked_salary

WHERE row_num = 10;

In this query:

• The inner subquery SELECT salary, ROW_NUMBER() OVER (ORDER BY salary DESC) AS row_num FROM Employee calculates the row number for each record based on the descending order of salaries.
• The outer query then selects the salary where the row number is 10, which gives you the 10th highest employee salary.

Q3. Write a query to get the last record from a table.

To get the last record from a table, you can use a query like this:

SELECT * FROM your_table_name ORDER BY your_primary_key_column DESC LIMIT 1;

Replace your_table_name with the name of your table and your_primary_key_column with the primary key column that defines the order of records. The DESC keyword sorts the records in descending order, and LIMIT 1 ensures you only get the last record.

Know How to insert date in SQL