Single Row Subquery

1. More salary than the avg(sal) from emp? 

Answer:

  select ename, sal  from emp  where sal>(select avg(sal) from emp);

2. employees in sales department from emp, dept tables.  

Answer:

    select ename from emp  where deptno=(select  deptno from dept where dname=’SALES’);

3. employees who are working in “SMITH” department number from emp table?

Answer: 

select ename  from emp where deptno=(select deptno from emp where name=’SMITH’);

4.   Senior most employee details from emp table? 

    Answer: 

 select * from emp  where hiredate=(select min(hiredate) from emp); 

5.   Write a query to display highest sal details emp table?

 Answer: 

 select * from emp  where sal=(select max (sal) from emp); 

6.   Highest paid emp department, dname from emp, dept tables?                

  Answer: 

select dname from dept  where deptno=(select deptno from emp where sal=(select max(sal) from emp));

7.   Write a query to display second max(sal) from emp table?

 Answer: 

   select max(sal) from emp  where sal<(select max(sal) from emp);

8.   Second highest sal employee details from emp table? 

 Answer: 

 select * from emp where sal=(select max(sal) from emp where sal<(select max(sal) from emp));

9.   Write a query to display the employees who are working under ‘BLAKE’ from emp table using ‘empno, mgr’ columns ?  

 Answer:

select * from emp  where mgr=select empno from emp where ename=’BLAKE’); 

10.   Write a query to display lowest average salary job from emp tables?  

  Answer: 

 select job, avg(sal) from emp group by job having avg(sal)= (select min(avg(sal)) from emp group by job); 

                     or

 select deptno, min(sal) from emp group by deptno  having  min(sal)>(select min(sal) from emp where deptno=30);

11.    job, avg(sal) of the employees whose job avg(sal) more than the clerk’s job avg(sal) from emp table? 

  Answer: 

    select avg(sal), job from emp group by job having avg(sal) >(select avg(sal) from emp where job=’CLERK’);

12.   Employees in sales department from emp, dept tables?

  Answer: 

   select ename, dname  from emp e, dept d where e.deptno=d.deptno and d.deptno=(select deptno from dept dname=’SALES’);

13.   who are getting more salary than the ‘BLAKE’ salary from emp table?

 Answer: 

  select * from emp where sal> (select sal from emp  where ename=’BLAKE’);