Single Row Sub Query

=>    If Inner Query returns only one value then the sub query is called as "Single Row Sub Query".

    Syntax:
    =>    Select <collist> from <tableName> where colname OP ( Select statement);

=> OP can be { =, >, <, >=, <=, <> or != }
=> Use sub query when where clause is based on unknown condition.

    Examples:

    1.Display the employees who's job = job of smith ?
    =>    Select e.ename, e.sal, e.job from emp e where job = ( select JOB from emp where ename = 'SMITH');

    2. Select the name of the employee who earns maximum salary ?
    =>    Select e.ename, e.sal, e.job from emp e where sal > ( select MAX(sal) from emp );

    3. Display the employee record who has max experience ?
    =>    Select * from emp where hiredate > ( select MIN( hiredate) from emp );

    4. Display all records except last record ?
    =>    Select * from emp where rowid < ( Select max(rowid) from emp );