Top 20 SQL Queries Interview Questions and Answers for Software Testing professionals - SET 3

 

1.     List all the employee details

SQL > Select * from employee;

2.     List all the department details

SQL > Select DEPARTMENT_ID from Employee;

3.     List all job details

SQL > Select JOB_ID from Employee

4.     List all the locations

SQL > Select loc from Employee;

5.List the latest updated record

select TOP 1 * from employee ORDER BY HIREDATE ASC

OR

select * from Employee where HIREDATE=(select max(HIREDATE) from Employee)

 

6.List out first name, last name, salary, commission for all employees

select LAST_NAME,FIRST_NAME, HIREDATE from employee

 

7. List out employee_id,last name,department id for all  employees and rename employee id as “ID  of the employee”, last name as “Name of the employee”, department id as  “department  ID”

 

Select employee_id “id of the employee”, last_name “name", department id as “department id” from employee;

 

8. List out the employees anual salary with their names only.

select first_name as Employee_names, (salary*12) as Anuual_Salary from employee

 

9. List the details about “SMITH”

Select * from employee where first_name=smith

 

10. List out the employees who are working in department 20

Select * from employee where department=20

 

11. List out the employee who are earning salary between 3000 to 4500

Select * from employee where salary between 3000 and 4500

 

12. List out the employees who are working in department 10 or 20

select * from employee where DEPARTMENT_ID in (10,20)

 

13. Find out the employees who are not working in department 10 or 30

select * from employee where DEPARTMENT_ID NOT in (10,20)

 

14. List out the employees whose name start with “S” and end with “H”

Select * from employee where last_name like ‘S%H’

 

15.List out the employees whose name length is 4 and start with “S”

Select * from employee where last_name like ‘S___’

 

16.     List out the employees who are working in department 20 and 30 and draw the salaries more than 3500

select * from employee where DEPARTMENT_ID in (20,30) AND salary=3500

17. List out the employees who are not receiving commission

select * from employee where COMM is NULL