SQL Queries for Interview

Posted on: January 18, 2025 Posted by: rahulgite Comments: 0

Q1. Write a query to fetch the EmpFname from the EmployeeInfo table in the upper case and use the alias name as EmpName.

SELECT UPPER(EmpFname) AS EmpName FROM EmployeeInfo;

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

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

Q3. Write a query to get the current date.

  • SQL Server:
SELECT GETDATE();
  • MySQL:
SELECT SYSDATE();

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

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

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

  • Using MID (MySQL):
SELECT MID(Address, 0, LOCATE('(', Address)) FROM EmployeeInfo;
  • Using SUBSTRING (SQL Server):
SELECT SUBSTRING(Address, 1, CHARINDEX('(', Address)) FROM EmployeeInfo;

Q6. Write a query to create a new table that consists of data and structure copied from the other table.

  • Using SELECT INTO (SQL Server):
SELECT * INTO NewTable FROM EmployeeInfo WHERE 1 = 0;
  • Using CREATE TABLE (MySQL):
CREATE TABLE NewTable AS SELECT * FROM EmployeeInfo;

Q7. Write a query to find all the employees whose salary is between 50000 to 100000.

SELECT * FROM EmployeePosition WHERE Salary BETWEEN '50000' AND '100000';

Q8. Write a query to find the names of employees that begin with ‘S’.

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

Q9. Write a query to fetch top N records.

  • SQL Server:
SELECT TOP N * FROM EmployeePosition ORDER BY Salary DESC;
  • MySQL:
SELECT * FROM EmployeePosition ORDER BY Salary DESC LIMIT N;

Q10. Write a query to retrieve the EmpFname and EmpLname in a single column as “FullName”.

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

Q11. Write a query to find the number of employees whose DOB is between 02/05/1970 to 31/12/1975 and group them by gender.

SELECT COUNT(*), Gender FROM EmployeeInfo WHERE DOB BETWEEN '1970-05-02' AND '1975-12-31' 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 ascending order.

SELECT * FROM EmployeeInfo ORDER BY EmpLname DESC, Department ASC;

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

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”.

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 the 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.

  • Even records:
SELECT EmpID FROM (SELECT rowno, EmpID FROM EmployeeInfo) WHERE MOD(rowno, 2) = 0;
  • Odd records:
SELECT EmpID FROM (SELECT rowno, EmpID FROM EmployeeInfo) WHERE MOD(rowno, 2) = 1;

Q19. Write a query to retrieve employee details from the 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.

  • Two minimum salaries:
SELECT DISTINCT Salary FROM EmployeePosition E1 
WHERE 2 >= (SELECT COUNT(DISTINCT Salary) FROM EmployeePosition E2 
WHERE E1.Salary >= E2.Salary) ORDER BY E1.Salary DESC;
  • Two maximum salaries:
SELECT DISTINCT Salary FROM EmployeePosition E1 
WHERE 2 >= (SELECT COUNT(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, EmployeeInfo 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 EmployeePosition
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.

  • First record:
SELECT * FROM EmployeeInfo WHERE EmpID = (SELECT MIN(EmpID) FROM EmployeeInfo);
  • Last record:
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(EmpID) < 2;

Q29. Write a query to retrieve EmpPosition 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);

Q31. Write a query to get the 10 highest salaries from the EmployeePosition table.

SELECT DISTINCT Salary 
FROM EmployeePosition 
ORDER BY Salary DESC 
LIMIT 10;

Leave a Comment