LITERALS IN ORACLE

• A literal is a constant which represent fixed value.

    Types :

      Ø  Text literal

      Ø  Number literal

      Ø  Integer literal

      Ø  Interval literal

• Literal is printed for each row that is retrieved by select statement.
• Date and character literals must be enclosed in single quote (‘).
• Literals increase the readability of the output.

  Examples :=

      Select ename ‘ is designated as ‘ from emp;

      Select ‘ I am a ‘ boy from dual; (Here I am a is Literal and boy is an alias.

      Select ename, ‘ is designated as ‘ , job from emp;

      Select ename || ‘ is designated as ‘ || job from emp; ( Columns on either side  of || operator are combine to make a single column )

Note :  Where we use a literal, there we will use a concatenation operator (||).

      Select ename ‘ ‘’s designation is ‘|| job || ‘ working for department ‘ || dept from emp;

      Select ename || ‘ ‘’s salary is ‘ || sal || ‘ with commission : ‘ || comm from emp;

 You May Also Like to read ....

•  Column Alias in Oracle

• Having Clause in Oracle

• Group By Clause

Column Alias in ORACLE

• Column alias renames a column heading in a query.
• Its an alternate name for an exiting oracle object.
• The column alias is specified in the SELECT list by declaring the alias after the column name by using the space separator.
• Alias heading appears in UPPER casing by default.
• The alias should be declared in double quotes (“ “).
• Alias is declared after the column with minimum of one space.
• The AS keyword can be used between the column name and alias.
• An alias effectively renames the SELECT list item for duration of the query only.
• An alias cannot be used, any where in the SELECT list for operational purpose.

Note  : Never consider Alias as a column

Example :=

         1)    Select 20 + 10 Mysum from dual;

         2)   Select ename, sal salary, comm commission from emp;

         3)   Select sal “Basic Salary” from emp;

         4)   Select ename, empno, sal AS Salary from emp;

         5)   Select empno AS Numbers,  

Ename AS Name,

Job AS Designation,

Sal AS  Salary,

Hiredate AS JoinDate

From emp;

- - Either use Numbers or Use as “Number” as a alias, never use Number as a alias.

Having Clause In ORACLE

• This clause is used to provide a condition on group functions, which may filter the data based on the data retrieved at group function.

• You must need group by clause while declaring having by clause.

   Example:=  

       

      1)   Display deptno and total sal of those dept whose total sal is greater  than 9000.

Ans : Select deptno, sum(sal) from emp

        Group by deptno

        Having sum(sal)> 9000;

      2)  Display deptno and number of employees working at each dept, only for those departments where more than 3 employees are working.

       Ans :  Select deptno, count(*) from emp

                Group by deptno

                Having count(*)>3;

 3) Calculate designation wise investments only when overall investments on the designation crosses 5000 excluding all such designation having SALES patterns.

        Ans : Select deptno, avg(sal)

                From emp

                where job like 'SALES%'

                Group by deptno

                Having max(sal)>5000;

Group By Clause In ORACLE

• This clause is used to group the data over a specified column by eliminating duplicates.
•   Group by clause always arranged the data in ascending order.
•   The column which is used at group by clause, the same column can be used for the retrieving the data.
•   Group by clause will group the data over a single or multiple columns.

   Examples :=

   1)   Display total salary of all the three departments from the “emp” table.

   Ans : Select sum(sal) from emp

        Group by deptno;

       

   2)   Display deptno and total salary of all the 3 departments from “emp”table.

   Ans : Select deptno,sum(sal) from emp

           Group by deptno;

   3)  Display deptno and total salary, which is being paid to dept 20.

   Ans : Select deptno, sum(sal), count(*)

           From emp

           Where deptno=20

           Group by deptno;

   4)  Display job and total salary being paid on each job.

   Ans : Select job, sum(sal)

           From emp

           Group by job;

   5)  Display deptno,total salary,average salary, minimum salary  and number of employees in each dept.

   Ans :  Select deptno, sum(sal), avg(sal),  min(sal)

           Count(*) from emp

           Group by deptno;

   6)  Display deptno, job and total salary being paid on each group of deptno and job.

   Ans  : Select deptno,job, sum(sal) from emp

           Group by deptno,job;

7) Select deptno,sum(sal) avg(sal) from emp where job='CLERK'
    group by deptno;