SQL Query Interview Questions

Let us try to answer common interview questions using below two tables – Employee and Incentive

SQL Basics

1. Get all employee details from the employee table?

SELECT * FROM EMPLOYEE;

 

2. Get First_Name, Last_Name from employee table?

SELECT FIRST_NAME, LAST_NAME FROM EMPLOYEE ;

 

3. Get First_Name from employee table using alias name “Employee Name”?

SELECT FIRST_NAME “EMPLOYEE NAME” FROM EMPLOYEE;

 

4. Get First_Name from employee table in upper case?

SELECT UPPER (FIRST_NAME) FROM EMPLOYEE;

5. Get FIRST_NAME from employee table in lower case?

SELECT LOWER (FIRST_NAME) FROM EMPLOYEE;

 

6. Get unique DEPARTMENT from employee table?

SELECT DISTINCT DEPARTMENT FROM EMPLOYEE;

 

7. Select first 3 characters of FIRST_NAME from EMPLOYEE?

SELECT SUBSTR (FIRST_NAME, 0, 3) FROM EMPLOYEE;

 

8. Get position of 'o' in name 'John' from employee table?

SELECT INSTR (FIRST_NAME,'o') FROM EMPLOYEE WHERE FIRST_NAME='John';

 

9. Get FIRST_NAME from employee table after removing white spaces from right side?

SELECT RTRIM (FIRST_NAME) FROM EMPLOYEE;

10. Get FIRST_NAME from employee table after removing white spaces from left side?

SELECT LTRIM (FIRST_NAME) FROM EMPLOYEE;

 

11. Get length of FIRST_NAME from employee table?

SELECT LENGTH (FIRST_NAME) FROM EMPLOYEE;

 

12. Get First_Name from employee table after replacing 'o' with '$'?

SELECT FIRST_NAME, REPLACE (FIRST_NAME,'o','$') FROM EMPLOYEE;

 

13. Get First_Name and Last_Name as single column from employee table separated by a '_'?

SELECT FIRST_NAME|| '_' ||LAST_NAME FROM EMPLOYEE;

 

14. Get FIRST_NAME, Joining year, Joining Month and Joining Date from employee table?

SELECT FIRST_NAME, 
	TO_CHAR(JOINING_DATE,'YYYY') JOINYEAR ,
	 TO_CHAR(JOINING_DATE,'MON'),
	  TO_CHAR(JOINING_DATE,'DD')          FROM         EMPLOYEE;

 

SQL Order By

15. Get all employee details from the employee table?

SELECT * FROM EMPLOYEE;

 

16. Get First_Name, Last_Name from employee table?

SELECT FIRST_NAME, LAST_NAME FROM EMPLOYEE ;

 

17. Get First_Name from employee table using alias name “Employee Name” ?

SELECT FIRST_NAME “EMPLOYEE NAME” FROM EMPLOYEE;

 

18. Get First_Name from employee table in upper case?

SELECT UPPER (FIRST_NAME) FROM EMPLOYEE;

 

19. Get FIRST_NAME from employee table in lower case?

SELECT LOWER (FIRST_NAME) FROM EMPLOYEE;

 

20. Get unique DEPARTMENT from employee table?

SELECT DISTINCT DEPARTMENT FROM EMPLOYEE;

 

21. Select first 3 characters of FIRST_NAME from EMPLOYEE?

SELECT SUBSTR (FIRST_NAME, 0, 3) FROM EMPLOYEE;

 

22. Get position of 'o' in name 'John' from employee table?

SELECT INSTR (FIRST_NAME,'o') FROM EMPLOYEE WHERE FIRST_NAME='John';

 

23. Get FIRST_NAME from employee table after removing white spaces from right side?

SELECT RTRIM (FIRST_NAME) FROM EMPLOYEE;

 

24. Get FIRST_NAME from employee table after removing white spaces from left side?

SELECT LTRIM (FIRST_NAME) FROM EMPLOYEE;

 

25. Get length of FIRST_NAME from employee table?

SELECT LENGTH (FIRST_NAME) FROM EMPLOYEE;

 

26. Get First_Name from employee table after replacing 'o' with '$'?

SELECT FIRST_NAME, REPLACE (FIRST_NAME,'o','$') FROM EMPLOYEE;

 

27. Get First_Name and Last_Name as single column from employee table separated by a '_'?

SELECT FIRST_NAME|| '_' ||LAST_NAME FROM EMPLOYEE;

 

28. Get FIRST_NAME, Joining year, Joining Month and Joining Date from employee table?

SELECT FIRST_NAME, 
	TO_CHAR(JOINING_DATE,'YYYY') JOINYEAR ,
	 TO_CHAR(JOINING_DATE,'MON'),
	   TO_CHAR(JOINING_DATE,'DD')     FROM        EMPLOYEE;

 

29. Get all employee details from the employee table order by First_Name Ascending?

SELECT * FROM EMPLOYEE ORDER BY FIRST_NAME ASC

 

30. Get all employee details from the employee table order by First_Name descending?

SELECT * FROM EMPLOYEE ORDER BY FIRST_NAME DESC;

 

31. Get all employee details from the employee table order by First_Name Ascending and Salary descending?

SELECT * FROM EMPLOYEE ORDER BY FIRST_NAME ASC, SALARY DESC;

 

SQL Where Condition

32. Get employee details from employee table whose employee name is “John”?

SELECT * FROM EMPLOYEE WHERE FIRST_NAME='John';

 

33. Get employee details from employee table whose employee name are “John” and “Roy”?

SELECT * FROM EMPLOYEE WHERE FIRST_NAME IN ('John', 'Roy');

 

34. Get employee details from employee table whose employee name are not “John” and “Roy”?

SELECT * FROM EMPLOYEE WHERE FIRST_NAME NOT IN ('JOHN', 'ROY');

 

 SQL Wild Card Search

35. Get employee details from employee table whose first name starts with 'J'?

SELECT * FROM EMPLOYEE WHERE FIRST_NAME LIKE 'J%';

 

36. Get employee details from employee table whose first name contains 'o'?

SELECT * FROM EMPLOYEE WHERE FIRST_NAME LIKE '%o%';

 

37. Get employee details from employee table whose first name ends with 'n'?

SELECT * FROM EMPLOYEE WHERE FIRST_NAME LIKE '%n';

 

SQL Pattern Matching

38. Get employee details from employee table whose first name ends with 'n' and name contains 4 letters?

SELECT * FROM EMPLOYEE WHERE FIRST_NAME LIKE '___n'; (Underscores)

 

39. Get employee details from employee table whose first name starts with 'J' and name contains 4 letters?

SELECT * FROM EMPLOYEE WHERE FIRST_NAME LIKE 'J___'; (Underscores)

 

40. Get employee details from employee table whose Salary greater than 600000?

SELECT * FROM EMPLOYEE WHERE SALARY >600000;

 

41. Get employee details from employee table whose Salary less than 800000?

SELECT * FROM EMPLOYEE WHERE SALARY <800000;

 

42. Get employee details from employee table whose Salary between 500000 and 800000?

SELECT * FROM EMPLOYEE WHERE SALARY BETWEEN 500000 AND 800000;

 

SQL DATE Functions

43. Get employee details from employee table whose joining year is “2013”?

 SELECT * FROM EMPLOYEE
 WHERE TO_CHAR (JOINING_DATE, 'YYYY')='2013';

 

44. Get employee details from employee table whose joining month is “January”?

SELECT * FROM EMPLOYEE WHERE TO_CHAR(JOINING_DATE,'MM')='01' 

OR

SELECT * FROM EMPLOYEE WHERE TO_CHAR(JOINING_DATE,'MON')='JAN';

 

45. Get employee details from employee table who joined before January 1st 2013?

SELECT * FROM EMPLOYEE WHERE JOINING_DATE <TO_DATE('01/01/2013','DD/MM/YYYY');

 

46. Get employee details from employee table who joined after January 31st?

SELECT * FROM EMPLOYEE WHERE JOINING_DATE >TO_DATE('31/01/2013','DD/MM/YYYY');

 

47. Get Joining Date and Time from employee table?

SELECT TO_CHAR(JOINING_DATE,'DD/MM/YYYY HH:MI:SS') FROM EMPLOYEE;

 

48. Get Joining Date,Time including milliseconds from employee table?

SELECT TO_CHAR(JOINING_DATE,'DD/MM/YYYY HH:MI:SS.FF') FROM EMPLOYEE ;

 

Here          column             Data          Type should            be            “TimeStamp”

49. Get difference between JOINING_DATE and INCENTIVE_DATE from employee and incentives table?

SELECT FIRST_NAME,INCENTIVE_DATE - JOINING_DATE
FROM EMPLOYEE A INNER JOIN INCENTIVES B
        ON A.EMPLOYEE_ID=B.EMPLOYEE_REF_ID;

 

50. Get database date?

SELECT SYSDATE FROM DUAL;

 

SQL Escape Characters

51. Get names of employees from employee table who has '%' in Last_Name. Tip : Escape character for special characters in a query.?

Select FIRST_NAME FROM EMPLOYEE WHERE LAST_NAME LIKE '%\%%';

52. Get Last Name from employee table after replacing special character with white space?

Select           translate(LAST_NAME,'%','        ')        from          employee

 

 SQL Group By Query

53. Get department,total salary with respect to a department from employee table?

SELECT DEPARTMENT,SUM(SALARY) TOTAL_SALARY FROM EMPLOYEE GROUP BY DEPARTMENT;

 

54. Get department, total salary with respect to a department from employee table order by total salary descending?

SELECT DEPARTMENT, SUM (SALARY) TOTAL_SALARY
 FROM EMPLOYEE GROUP BY DEPARTMENT
ORDER BY TOTAL_SALARY DESC;

 

SQL Mathematical Operations using Group By

55. Get department, no of employees in a department, total salary with respect to a department from employee table order by total salary descending?

SELECT DEPARTMENT,COUNT(FIRST_NAME),SUM(SALARY) TOTAL_SALARY
 FROM EMPLOYEE GROUP BY DEPARTMENT ORDER BY TOTAL_SALARY DESC;

 

56. Get department wise average salary from employee table order by salary ascending?

SELECT DEPARTMENT,AVG(SALARY) AVGSALARY
FROM EMPLOYEE GROUP BY DEPARTMENT ORDER BY AVGSALARY ASC;

 

57. Get department wise maximum salary from employee table order by salary ascending?

SELECT DEPARTMENT,MAX(SALARY) MAXSALARY
FROM EMPLOYEE GROUP BY DEPARTMENT ORDER BY MAXSALARY ASC;

 

58. Get department wise minimum salary from employee table order by salary ascending?

SELECT DEPARTMENT,MIN(SALARY) MINSALARY
FROM EMPLOYEE GROUP BY DEPARTMENT ORDER BY MINSALARY ASC;

 

59. Select no of employees joined with respect to year and month from employee table?

SELECT TO_CHAR (JOINING_DATE,'YYYY') JOIN_YEAR,
TO_CHAR (JOINING_DATE,'MM') JOIN_MONTH,COUNT(*) TOTAL_EMP
FROM EMPLOYEE GROUP BY TO_CHAR (JOINING_DATE,'YYYY'),TO_CHAR(JOINING_DATE,'MM');

 

60. Select department,total salary with respect to a department from employee table where total salary greater than 800000 order by Total_Salary descending?

SELECT DEPARTMENT,SUM(SALARY) TOTAL_SALARY
FROM EMPLOYEE GROUP BY DEPARTMENT
 HAVING SUM(SALARY) >800000 ORDER BY TOTAL_SALARY DESC;