SQL Query Interview Questions

0
1580
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;