Single Row Subquery :-

  • If inner query returns only one value, then the sub query is called “single row subquerry”
  • Operations must be >, >=, <, <=, =, <>.

Example :- Display employee records who is job equal to job of smith.

Select * from emp where job  = (select job from emp where ename= ‘smith’)

Display employee name earning max salary

Select ename from emp where sal  = (select max(sal) from emp)

Ename

King

Display name of the employee having max experence

Select ename from emp where hiredate=(select min(hiredate) from emp)

Display employee records whos job=job of smith and sal must be>smith

Select * from emp  where job=(select job from emp where=’smith’) and sal >(select sal from emp where ename=’smith’)