Database Testing - SQL - Sub Queries Examples

Sub-Queries

34. Display the employee who got the maximum salary.

Select * from employee where salary=(select max(salary) from employee)

35. Display the employees who are working in Sales department

Select * from employee where department_id = (select department_id from department where name='SALES')

36. Display the employees who are working as "Clerk".

Select * from employee where job_id in (select job_id from job where function1='CLERK')

37. Display the employees who are working in "Boston"

Select * from employee where department_id=(select department_id from department where location_id=(select location_id from location where regional_group='Boston'))

38. Find out no.of employees working in 'Sales" department.

Select * from employee where department_id=(select department_id from department where name='SALES' group by department_id)

39. Update the employees salaries, who are working as Clerk on the basis of 10%.

Update employee set salary=(salary*10/100) where job_id=(select job_id from job where function1='CLERK')

40. Delete the employees who are working in accounting department.

delete from employee where department_id=(select department_id from department where name='ACCOUNTING')

41. Display the second highest salary drawing employee details.

Select * from employee where salary=(select max(salary) from employee where salary <(select max(salary) from employee))

42. Display the 3rd highest salary drawing employee details

Select * from employee a where 3 = (select COUNT(distinct(salary)) from employee b where b.salary >=a.salary)