Order By Clause

• This clause in a select statement arranges the data on a specified column in ascending or in descending order.
• This clause will work after the execution of select statement.
• By default it arranges the data in ascending order.
• In order to arrange the data in descending order , an option called DESC is used.
• Data is arranged in order over a required column for temporary.
• Order by clause can be specified with one or more columns.

      Examples :-

        Display empno, ename and salary of all the employees arranging salaries in ascending order.

        Ans : Select empno, ename, sal from emp order by sal;

        Display ename, job, salary, deptno, commission, and hiredate of all those employees who are working as CLERK, SALESMAN and they have been hired in the year 81 and they don’t earn any commission. Arrange the data in ascending order of salary.

        Ans : Select ename, job, sal, deptno , comm, hiredate from emp

          Where job IN(‘CLERK’ , ‘SALESMAN’) AND

          Hiredate LIKE ‘%81’ AND

          Comm IS NULL

          Order by sal;

        Display ename, job, sal of all the employees arranging job in ascending order and salary in descending order.

        Ans : Select ename, job, sal from emp

          Order by job, sal desc;

        Display ename, job, sal of all those employees arranging job and sal in ascending order.

        Ans : Select ename, job, sal from emp

          Order by job, sal;

                                                       

IS NULL Operator

• This operator tests for NULL values.
• It is the only operator that can be used to test for NULL’s.
• The negation is IS NOT NULL.

      Examples :-

       Select ename, deptno, comm from emp where comm IS NULL;

       Select ename, deptno, comm from emp where comm IS NOT NULL;

       Select ename, deptno, job, mgr from emp where mgr IS NULL;

       Select ename, deptno, job, mgr from emp where mgr IS NOT NULL;

IN Operator

• This operator is used to test for values in a specified list.
• This operator can be used upon any data type.
• The negation of the operator is NOT IN.

     Examples :-

      Select ename, sal, job from emp where ename IN(‘ALLEN’ , ‘SCOTT’);

      Select ename, sal, job from emp where ename NOT IN(‘ALLEN’ , ‘SCOTT’);

      Select ename, sal, job, deptno from emp where deptno IN(10,30);

      Select ename, sal, job, deptno from emp where deptno NOT IN(10,30);

      Select ename, sal, hiredate from emp where hiredate IN(’02-APR-81’ , ’12-JAN-83’);

      Select ename, sal, hiredate from emp where hiredate NOT IN(’02-APR-81’ , ’12-JAN-83’);

BETWEEN … AND…Operator

• This operator is used to display rows based on a range of values.
• The declared range is inclusive. The lower limit should be declared first.
• The range that you specify contains a lower limit and an upper limit.

Examples :-

          Select ename, sal, job from emp where sal BETWEEN 1000 AND 2000;

          Select ename, sal, job from emp where sal NOT BETWEEN 1000 AND 2000;

          Select ename, sal, job from emp where job BETWEEN ‘MANAGER’ AND ‘SALESMAN’;

         Select ename, sal, job from emp where job NOT BETWEEN ‘MANAGER’ AND ‘SALESMAN’;

         Select ename, sal, job, hiredate from emp where hiredate BETWEEN ’02-APR-81’ AND ’12-JAN-83’;

         Select ename, sal, job, hiredate from emp where hiredate NOT BETWEEN ’02-APR-81’ AND ’12-JAN-83’;

DISTINCT - Supressing Duplicate Rows in Output

Supressing Duplicate Rows : =

• Sometimes, your query results contain duplicate rows. You can eliminate such rows by adding the keyword DISTINCT immediately after the keyword SELECT.
• Multiple columns can be declared after the DISTINCT qualifier.
• The DISTINCT qualifier affects all the selected columns, and represents a DISTINCT combination of the columns.
• When DISTINCT is implemented upon a column which may contain NULL values, all the NULLS in the column together treated as one DISTINCT group.
• A select statement should have one DISTINCT keyword.

Examples :=

    

     Ø  Select DISTINCT deptno from emp;

     

     Ø  Select DISTINCT deptno,mgr from emp;

  Ø  Select DISTINCT deptno,job from emp;

      

LIKE operator in ORACLE

Wild Charcters :-

These characters are used to provide pattern matching which means comparision to data is provided with partial value.

ORACLE provides the following wild characters.

           i)    _(Underscore) -> Represents single character.

          ii)    %(Percentage) -> Represents group of character.

Ø  Inorder to have wild characters for the comparision to partial data, LIKE operator is used.

Ø  If these wild characters are formed in data, then the wild characters can be converted to data using ‘\’ (backslash). It is represented through an option called ‘ESCAPE’ , which is written after like operator.

Examples :-

Display employee number and employee name of all those employees whose names contain 4 characters.

Ans : select empno, ename from emp

          Where ename LIKE ‘----‘;

Display ename, job of all those employees whose name starts with ‘A’.

Ans : select empno, ename from emp

          Where ename LIKE ‘A%’;

Display ename, job of all those employees whose name ends with ‘S’.

Ans : select empno, ename from emp

          Where ename LIKE ‘%S’;

Display ename, job, sal of all those employees whose name contain ‘A’.

Ans : Select ename, job, sal from emp

          Where ename LIKE ‘%A%’;

Display ename, sal of all those employees whose salary is ending with ’00’.

Ans : Select ename,sal from emp

          Where sale LIKE ‘’;

Display ename, hiredate of all those employees who have been hired in the year 81.

Ans : Select ename, hiredate from emp

          Where hiredate LIKE ‘%81’;

Display empno, ename, hiredate of all those employees who have been hired in those months whose name doesn’t starts with ‘A’.

Ans : Select empno, ename, hiredate from emp

          Where hiredate NOT LIKE  ‘_ _ _ _A%’;

Display all those employees whose designation contains ‘_’.

Ans : Select * from dept

          Where dname like ‘%\_%’ ESCAPE ‘\’;

WHERE clause in ORACLE

• This clause is used to provide comparison of data, which restricts the rows from a table.
• A WHERE clause contains a condition that must be met and should directly follow the from clause.
• Comparison to date type data is not case sensitive.
• Character values are case sensate and Date values are format Sensitive (DD-MON-YY).
• The WHERE clause can compares..
        Ø  Values in columns
        Ø  Literal Values
        Ø  Arithmetic Expressions
        Ø  Function
  Examples :-
  
  
  Display all those employees, who are working as CLERK.
  
  
  Ans : Select * from emp where job = ‘CLERK’;
  
  
  Display all those employees, who are working in deptno 20.
  
  
  Ans : Select * from emp where deptno=20;
  
  
  Display all those employees, who have been hired on 3^rd-dec-81.
  
  
  Ans : Select * from emp where hiredate=’3^rd-dec-81’;
  
  
  Display empno,ename,job,sal,deptno for all those employees who are working in deptno 20 and as CLERK.
  
  
  Ans : Select empno, ename, job, sal, deptno from emp
  Where deptno=20 AND job=’CLERK’;
  
  
  Display ename,job,salary,deptno for all those employees who are not working as MANAGER.
  
  
  Ans : Select ename, job, sal, deptno from emp
            Where job !=’MANAGER’;
  
  
  Display ename, job, salary, deptno for all those employees whose salary is >=2000.
  
  
  Ans : Select ename, job, sal, deptno from emp
            Where sal>=2000;
  
  
  Display ename, job, salary for all those employees whose salary is >=1000 and <= 3000.
  
  
  Ans : Select ename, job, sal from emp
            Where sal>=1000 AND sal<=3000;
  
  
  (OR) sal BETWEEN 1000 AND 3000;
  
  
  Display ename, job, salary, deptno, hiredate fro all those employees who have been hired between 01-jan-81 and 31-dec-81 and they are working as clerk, salesman and salary between 1000 and 3000;
  
  
  Ans : Select ename, job, sal, deptno, hiredate
            From emp
            Where hiredate BETWEEN ’01-JAN-81’ AND ’31-DEC-81’
            AND
            Job IN (‘CLERK’ , ‘SALESMAN’)
            AND
            Sal BETWEEN 1000 AND 3000;
  
  
  Display ename, job for all those employees who are not working as clerk, analyst.
  
  
  Ans : select ename, job from emp
            Where job NOT IN (‘CLERK’ , ‘ANALYST’);