Retrieving Data Using the SQL SELECT Statement

Scalar Queries

=>    Sub Queries follows select clause are called Sub Queries.
=>    A scalar query 1 value per row.

    Syntax:
    =>    Select (subQuery1), (subQuery2), .... From tableName;

=>    Sub Query can be used in
    a)    Where
    b)    From
    c)    Select etc..

    Examples:
    1. Display the no of records in Emp and Dept ?
    =>    Select ( select count(*) from emp ) as emp,
            ( select count(*) from dept ) as dept
        from dual;
   
    2. Display the following report ?
        ename    sal    deptno    dept_sumsal
   
    =>    Select ename, sal, deptno, (select sum(sal) from emp e where e.deptno = deptno) as dept_sumsal from emp;

Inline Views

=> Sub Query following From clause is called "Inline View".
=> The result of the Sub Query acts as a table for Outer Query.

=> Inline views are used in the following scenarios:
    (i)    To use column alias in where clause.
    (ii)    To use Window functions.
    (iii)    To use result of one process in another process.

    Syntax:
    =>    Select * from <SubQuery> [ where condition ];

    Examples:
    =>    Select * from ( Select ename, sal from emp e );

    1. Display 5th record from emp ?
    =>    Select * from ( select rownum, empno, ename, sal from emp) where rownum = 5;

    2. Get the top 3 max salaries from emp ?
    =>    Select * from ( select rownum, empno, ename, sal from emp order by sal desc) where rownum <4;

Co-related Sub Query

=>    If Sub Query references values of Outer Query then it is called as "Co-related Sub Query".
=>    In the co-related Sub QUery, execution starts from Outer Query and Inner Query will be executed as many times as no of records exists in the table.

    Examples:
    1. Select the TOP 3 Maximum salaries ?
    =>    Select distinct sal from emp e1
            where 3 > ( Select count( distinct sal ) from emp e2 where e1.sal < e2.sal ) Order By sal Desc;
   
    2. Select the TOP 3 Minimum salaries ?
    =>    Select distinct sal from emp e1
            where 3 > ( Select count( distinct sal ) from emp e2 where e1.sal > e2.sal );
   

Multi Row Sub Query

=>    If Inner query returns more than one column then Sub Query is called "Multi Row Sub Query".
=>    No of Columns returned by Inner Query = No of columns used in where clause of Outer Query.
=>    Corresponding columns datatype must be same.

    Syntax:
    =>    Where (col1, col2) OP ( Select column1, column2 from tableName );

=>    OP can be { =, IN, NOT IN }

    Examples:
    1. Display emp records earning max sal in their dept ?
    =>    Select * from emp where (deptno, sal) in ( select deptno, MAX(sal) from emp GROUP BY deptno);

Nested Sub Query

=>    A query embedded in another Query or Sub Query.
=>    Queries can be nested upto 255 level.

    Syntax:
    =>    Select ....
                 ( select ....
                     ( select ....


    Examples:
    1. Select 2nd max salary ?
    =>    Select MAX( sal ) from emp where sal < ( Select MAX( sal ) from emp );

    2. Find the Location of the employee who is earning 2nd max sal ?
    =>    Select loc from dept where deptno in ( select deptno from emp where sal = (
        Select MAX( sal ) from emp where sal < ( Select MAX( sal ) from emp ) ) );

Multi Row Sub Query

=>    If sub query returns more than one value then the sub query is called 'Multi Row Sub Query'.

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

=> OP can be { IN, NOT IN, ALL, EXISTS, ANY }

    Examples:

    1. Get the 5th record from emp ?
    =>    Select * from emp where empno in ( select DECODE( rownum, 5, empno) from emp );
       
    2. Display Alternate records from emp ?
    =>    Select * from emp where empno in ( select DECODE( MOD(rownum,2) , 1, empno ) from emp );

    3. Display names of employee's who are earning MIN and MAX salary ?
    =>    Select * from emp where sal IN ( select MAX(sal) from emp UNION select MIN(sal) from emp);

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 );

Sub Queries

=>    A Query embedded in Another Query is called a Sub Query.
=>    There are 2 Queries:
        a) Parent Query     or    Outer Query    or    Main Query
        b) Child Query     or    Inner Query    or    Main Query
=>    When it is executed 1st Inner Query is executed then Outer Query.
=>    The result of inner query acts as input for Outer Query.
=>    Outer Query can be Insert / Update / Delete / Select.
    Inner Query must always be Select.


Types of Sub Queries:

    1. Single Row Sub Query
    2. Multi Row Sub Query
    3. Nested Sub Query
    4. Multi Column Sub Query
    5. Co-related Sub Query

FAQ

1. What are the different types of Integrity Constraints available in RDBMS ?
A. There are 3 types of Integrity Constraints (IC), they are
    a) Entity Integrity Constraint
        - PRIMARY KEY
        - UNIQUE
    b) Domain Integiry Constraint
        - NOT NULL
        - CHECK
    c) Referential Integrity Constraint
        - FOREIGN KEY

2. What does Self referential Integrity means ?
A. Self referential Integrity means, A foreign Key of a table is refering to Primary Key of the same table.
    ex: EMP
        empno, ename, job, sal, MGR, deptno.
            Foreign Key ( MGR ) CONSTRAINT fk_mgr REFERENCES emp( empno )

    Here MGR can contain either EMPNO or NULL.
    This kind of relationship is called as Self Referential Integrity.

3. What is the use of default keyword ?
A. Default key is used to insert default values in place of NULL.
    ex: create table emp
        ( empno number(4) primary key,
          ename varchar2(20) unique,
          sal number(7,2) check (sal > 2000)   default 0,
          hiredate date   default sysdate,
          ..... );

    Here when the user doesn't provide hiredate, instead of NULL, sysdate will be stored as Hiredate.
    And when sal <=2000, instead of NULL, 0 will be stored.

    This improves performance, because
    4+ NULL = NULL,
    but 4+0 =4.

4. How many ways can we declare constraints ?
A. Constraint can be decalred in 2 ways,
    (i) Column Level
    (ii) Table Level.

5. When should we use Column Level or Table Level ?
A.  Column Level Constraints:
    - Use column level constraints to declare a constraint for a single column.
    - Here constraints are declared immediately after declaring the column.
    - we can't combine multiple columns for a single constraint.

     Table Level Constraints:
    - Use table level constraints to declare a constraint for combination of columns.
    - Table level constraints are declared after declaring all the columns.

    Examples:

    (i) Column Level Constraints.
        Table Name:    ACCT_MASTER
        Columns:        ACCNO, ACC_NAME, BALANCE.
        Rules:       
            a) Accno should not be repeated and should not be NULL.
            b) Name should not be NULL.
            c) Balance > 1000 and should not be NULL.

        Create table ACCT_MASTER
        (
            ACCNO  number(4) PRIMARY KEY,
            ACC_NAME  varchar2(20) NOT NULL,
            BALANCE number(7,2) CHECK (BALANCE > 1000) NOT NULL
        );

    (ii) Table Level Constraints.
        Table Name:    REGISTRATION
        Columns:        STUDENT_NO, COURSE_ID, DATE_OF_COMPLETION.
        Rules:
            a) A record can be identified using STUDENT_NO, COURSE_ID. Because a student can do multiple courses.
            b) DATE_OF_COMPLETION must not be null. If no input is provided, insert system date.

        Create table REGISTRATION
        (
            STUDENT_NO number(4),
            COURSE_ID number(4),
            DATE_OF_COMPLETION date DEFAULT sysdate,
            PRIMARY KEY (STUDENT_NO, COURSE_ID)
        );

6. Which constraint is not possibel at Tabel Level ?
A. NOT NULL is not possible.

7. Discuss about Primary Key - Foreign Key relation ship ?
A.    - One Foreign Key can refer to One Primary Key, but not multiple Primary Key's.
    - Two or More Foreign Keys can refer to One Primary Key.
    - A Composite Primary Key must be referenced by Composite Foreign Key only.

8. What are the Precautions to be taken before adding a constraint externally ?
A.    - Primary Key can't be added to a column that contains NULL's or Duplicate records.
    - To Add a check constraint, ensure the Rules is being followed by the column.
        ex: Check ( sal > 3000 ).
            - Ensure that none of the sal <=3000. Else the Constraint will be failed ( Voilated ).
            - But we can aviod it using " NOVALIDATE ", this can be used only with Check constraint.
            - This doesn't effect the existing data, but check only for the Future data.
   
    =>    CHECK ( SAL > 3000 )  NOVALIDATE;

9. What are the Precautions to be taken before dropping a constraint ?
A.    - Before dropping Primary Key, Drop its respective Foreign Key also.
    - Even a table cannot be dropped if it is participating in a Primary Key - Foreign Key relation ship.
    - Even a table cannot be truncate if it is participating in a Primary Key - Foreign Key relation ship.

    - We can use " CASCADE " keyword to achieve these operations.

    Syntax:
        Alter table <table_name> DROP CONSTRAINT <constraint_name> CASCADE;
        or
        DROP table <table_name> CONSTRAINT CASCADE;
   
    Ex:
        (i) Alter table DEPT drop PRIMARY KEY CASCADE;
        (ii) Drop table emp CASCADE CONSTRAINTS;
   
10. How can we drop a constraint with out Constraint Name ?
A.    Oracle provides a table called " USER_CONSTRAINTS ", it maintains complete information about constraints declared in all the table.
    By default, Oracle provides names for each constraint.

    Syntax:
        Select constraint_name, constraint_type from USER_CONSTRAINTS
        where table_name = <table_name>
   
    CONSTRAINT CODES used in Oracle are
        Primary Key    P
        Foreign Key    R
        Unique        U
        Check        C
        Not Null        C

    Examples:
        Select constraint_name, constraint_type,constraint_code from USER_CONSTRAINTS
        where table_name = 'EMP';
   
    - Get the constraint name and drop it.

11. How to Disable / Enable Constraints ?
A.    - The constraints are disabled and enabled when there is huge amount of data to be copied from one table to another table.
    - If constraint is disabled, it still exists in the database but it will not work till it is enabled.
    - Copying data with constraints consume more time. Hence first we disable the constraints the ccopy the data, then enable the constraints.
        This must be done only when the data is valid data.

    Syntax:
        Alter table <table_name>  disable / enable Constraint <constraint_name>
   
    Examples:
        (i)    Enabling Primary Key:
                Alter table emp enable primary key;

                    - But ensure that there are no duplicate records and no NULL values in the column.

            Disable primary key:
                Alter table emp disable primary key.

                    - Primary key can't be disabled if it is referenced by any foreign key.
                    - Hence in this case we need to use CASCADE operation.

                Alter table emp disable primary key CASCADE;
           
                    - When primary key is enabled, the dependent foreign keys are not enabled automatically. Hence they must be done manually.
       
        (ii)    Enabling Check constraint:
                Alter table emp enable constraint ck_sal;
       
                - To Enable a check constraint,
                    - Ensure that none of record voilates the integrity rules
                    - But we can aviod it using " NOVALIDATE ", this can be used only with Check constraint.
                    - This doesn't effect the existing data, but check only for the Future data.
       
                Alter table emp enable NOVALIDATE constraint ck_sal;

            Disable Check constraint:
                Alter table emp disable constraint ck_sal;
           
        (iii)    Enabling Unique:
                Alter table emp enable constraint unique_ename;
           
                    - Ensure that there are no null records placed in column.
               
            Disabling Unique:
                Alter table emp disable constraint unique_ename;
               
                    - Unique constraint can't be disabled if it is referenced by any foreign key.
                    - Hence in this case we need to use CASCADE operation.

                Alter table emp disable constraint ck_sal CASCADE;
           
                    - When Unique constraint is enabled, the dependent foreign keys are not enabled automatically. Hence they must be done manually.

        (iv)    Enabling Foreign Key:
                Alter table emp enable constraint fk_deptno;
           
                    - Ensure that there are no values which doesn't exists in parent table.
           
            Disable Foreign Key:
                Alter table emp disable constraint fk_deptno;
                   

FOREIGN KEY

    - It is used to establish relationship between 2 tables.
    - Values of foreign key should match with values of primary key/ unique keys.
    - It allows NULL values, duplicate values.
    - A parent child relationship will be decalred when once the foreign key constraint is declared.
        This relationship is also called as 'Master/ Detail relationship', 'Master/ Transaction relationship' , 'Master/ Salve relationship'.
    - The table holding Primary Key is called Parent or Master.
    - The table holding Foreign Key is called Child or Details Or Transaction or Slave.
    - By default, Oracle creates one- many realtion ship for every parent-child relationship.
    - To establish one-to-one relationship, decalre foreign key with Unique constraints.
   
    Syntax:
        column_name datatype [size] references table_name ( column_name );
   
    Examples:

    (i) Creating a Check Constraint.
       
        Column Level Check declaration.
            create table emp
            (empno number(4), ..., deptno number(4) CONSTRAINT fk_deptno REFERENCES dept( deptno ), ........  );
       
        Table level Check Constraint.
            create table emp
            (empno number(4), ...... deptno number(4), .......,
             foreign key (deptno) CONSTRAINT fk_deptno REFERENCES dept( deptno ),...............);
       
        Using Alter
            create table emp
            (empno number(4), ...... deptno number(4), .......)

            Alter table emp add (  foreign key (deptno) CONSTRAINT fk_deptno REFERENCES dept( deptno ) );
   
    (ii) Drop Check constraint.
       
        Alter table emp drop CONSTRAINT fk_deptno;

CHECK

    - It is used for applying Business rules on a paticular column.
        Like sal>2000, deptno is not null, lenght of username > 5, length of password >= 8 etc...
    - It allows NULL values.
       
    Syntax:
        column_name datatype [size] CONSTRAINT <constraint_name> CHECK <condition>;

    Examples:
   
    (i) Creating a Check Constraint.
       
        Column Level Check declaration.
            create table emp
            (empno number(4), ..., sal number(7,2) CONSTRAINT check_sal check (sal > 3000),............);
       
        Table level Check Constraint.
            create table emp
            (empno number(4), ...... sal number(7,2), .......,
             CONSTRAINT check_sal check (sal > 3000),...............);
       
        Using Alter
            create table emp
            (empno number(4), ...... sal number(7,2), .......)

            Alter table emp add (  CONSTRAINT check_sal check (sal > 3000) );
   
    (ii) Drop Check constraint.
       
        Alter table emp drop CONSTRAINT check_sal;

NOT NULL

    - It allows duplicate records, but doesn't allow NULL values.

    Syntax:
        column_name datatype [size] CONSTRAINT <constraint_name> NOT NULL;
   
    Examples:

    (i) Creating a NOT NULL constraint.

        Column Level NOT NULL declaration.
            create table emp
            (empno number(10), ename varchar2(20), ........... deptno number(4) CONSTRAINT notnull_deptno NOT NULL);
       
        Table Level NOT NULL decalaration.
            - Not Possible -
       
        Using Alter
            - Not Possible -
   
    (ii) Dropping a NOT NULL constraint.
       
        Alter table emp drop CONSTRAINT notnull_deptno;

UNIQUE

    - It doesn't accept duplicate values.
    - It allows NULL values.

    Syntax:
        column_name datatype [size] CONSTRAINT <constraint_name> unique;
   
    Examples:
   
    (i) Creating a UNIQUE constraint.

        Column Level Unique Declaration.
            create table emp
            ( empno number(4), ename varchar2(20) CONSTRAINT unique_ename UNIQUE ename, ......);
       
        Table Level Unique Declaration.
            create table emp
            (empno number(4), ename varchar2(20), ..... ,
                CONSTRAINT unique_ename UNIQUE ename );
           
        Using Alter
            create table emp
            (empno number(4), ename varchar2(20), ..... );

            Alter table emp add ( CONSTRAINT unique_ename UNIQUE ename );
       
    (ii) Dropping a UNIQUE constraint.

        To drop a UNIQUE constraint, we need to know the name used for the constraint.

        Alter table emp drop constraint unique_ename;

PRIMARY KEY

    - It doesn't accept NULL values.
    - It doesn't allow duplicate values.
    - We can have only one primary key per table, but we can have combination of columns as a primary key.
        This kind of primary key is called as Composite Key.

    Syntax:
        column_name datatype [size] priamary key;
   
    Examples:   
   
    (i). Creating a Primary Key constraint.

        Column Level Primary Key declaration.
            create table emp
            ( empno number(4) primary key, .... );
       
        Table Level Priamary Key declaration.
            create table emp
            ( empno number(4), ename varchar2(20), ..... ,
                primary key (empno) ,.... );
       
        Using Alter
            create table emp
            ( empno number(4), ename varchar2(20), ..... );

            Alter table emp add (primary key (empno));

        Creating primary key with name.
            create table emp ( empno number(4) constraint pk_emp primary key(empno), ....);

    (ii). Dropping a Primary Key Constraint.
       
        Alter table emp drop primary key;

Integrity Constraints

=> Integrity constraints are used

- to implement business rules,
- to validate data,
- to ensure data integrity etc..

=> Constraint means rules, rules are used to maintain data integrity.

=> Oracle has the following constraints:

1. PRIMARY KEY
2. UNIQUE
3. NOT NULL
4. CHECK
5. FOREIGN KEY

FAQ

CROSS JOIN

CROSS JOIN

=>    It returns x-product of 2 tables.
=>    Each record of table 1 is combined with each record of table 2.

Syntax:
=>    Select <collist> from <tablelist>
=>    Select <collist> from <table 1> CROSS JOIN <table 2>

Examples:

1. Select e.ename , d.dname from emp e , dept d;
2. Select e.ename, d.dname from emp e CROSS JOIN dept d;

NATURAL JOIN

=>    It is similar to EQUI JOIN, but join can be performed if it has same column name.

Syntax:
=>    Select <collist> FROM <tablename> NATURAL JOIN <tablename>

Examples:

1. Display employee name and respective dept name ?
=>    Select e.ename, d.dname from emp e NATURAL JOIN dept d;

OUTER JOIN

    =>    Equi JOIN returns only matching records but doesn't return un-matched records.
    =>    To get the un-matched records also we need to perform Outer Join.
    =>    Outer join is of 3 types:
            (a)    Left Outer
            (b)    Right Outer
            (c)    Full Outer

    a) Left Outer Join:
    =>    It returns all the records from Left side table and matching records from Right side table.
   
    Syntax:
    =>    Select <collist> from <tablelist>  where <cond1> = <cond2> (+)
    =>    Select <collist> from <table1>  LEFT OUTER JOIN <table2> ON <join condition>

    Examples:

    1. Display all the employee records and their respective dept names ?
    =>    Select e.ename, d.dname from emp e, dept d where e.deptno = d.deptno (+);
            (or)
    =>    Select e.ename, d.dname from emp e LEFT OUTER JOIN dept d ON ( e.deptno = d.deptno )
            (or)
    =>    Select e.ename , d.dname from emp e LEFT OUTER JOIN dept d USING( deptno );

   
    b) Right Outer Join:
    =>    It returns all the records Right side table and matching records from Left side table.

    Syntax:
    =>    Select <collist> from <tablelist>  where <cond1> (+)= <cond2>
    =>    Select <collist> from <table1>  RIGHT OUTER JOIN <table2> ON <join condition>

    Examples:

    1. Display all the dept records and their respective employees ?
    =>    Select d.dname,e.ename from dept d, emp e where d.deptno (+)= e.deptno ;
            (or)
    =>    Select d.dname,e.ename from dept d RIGHT OUTER JOIN emp e ON ( d.deptno = e.deptno )
            (or)
    =>    Select d.dname,e.ename from dept d RIGHT OUTER JOIN emp e USING( deptno )

   
    c) Full Outer Join:
    =>    It return all records from both the tables.
    =>    It doesn't support NON-ASCII JOIN.

    Syntax:
    =>    Select <collist> from <table1> FULL OUTER JOIN <table2> ON <join condition>

    Examples:

    1. Display all the employee records and dept records.
    =>    Select e.ename, d.dname from emp e
            FULL OUTER JOIN dept d USING( deptno );

SELF JOIN

    =>    A self join is performed on the tables having self referential integrity.
    =>    To perform self join, same table  must be listed twice with different alias.

    Syntax:
    =>    Select <collist>
        From <tablename>  as t1, <tablename> as t2
        where <join condition>
   
    Examples:
   
    1. Display employee name and his respective manager name ?
    =>    Select e.ename, m.ename from emp e, emp m where e.mgr = m.empno;
            (or)
        Select e.ename, m.ename from emp e JOIN emp m ON e.mgr = m.empno;

    2. Display the names of the employee who earns more than his manager ?
    =>    Select e.ename, m.ename from emp e, emp m where e.mgr = m.empno and e.sal > m.sal;
            (or)
        Select e.ename, m.ename from emp e JOIN emp m ON e.mgr = m.empno and e.sal > m.sal;

    3. Display the Manager of Blake ?
    =>    Select e.ename from emp e, emp m where e.mgr = m.empno and m.ename = 'BLAKE';
            (or)
        Select e.ename from emp e JOIN emp m ON e.mgr = m.empno and m.ename = 'BLAKE';

NON EQUI JOIN

   =>    Use NON equi join when two or more tables are not sharing the common column.   
    Syntax:
    =>    Select <collist> from <tablelist> where <join condition> [ and <join condition>.... ]

    Examples:

    1. Display employee names and grades ?
    =>    Select e.ename, s.grade from emp e, salgrade s where e.sal between s.lowsal and s.highsal;
            (or)
    =>    Select e.ename, s.grade from emp e JOIN salgrade s ON e.sal between s.lowsal and s.highsal;

    2. Display employee names working in Newyork location and having grade >=2 ?
    =>    Select e.ename, d.loc, g.grade from emp e, dept d, salgrade g where e.depto = d.deptno
            and ( e.sal between s.lowsal and s.highsal ) and d.loc = 'NEWYORK'  and g.grade >=2;

        (or)
   
    =>    Select e.ename, d.loc, g.grade from emp e
            JOIN dept d ON ( e.depto = d.deptno  AND d.loc = 'NEWYORK' )
            JOIN salgrade g ON ( e.sal between s.lowsal and s.highsal AND g.grade >=2)

EQUI JOIN or INNER JOIN

    => Use EQUI join when two or more tables are sharing the common column.

    Syntax:
    =>    Select <collist> from <tablelist> where <join_condition>
    =>    Select <collist> from <table 1> JOIN  <table 2> ON <join_condition>
    =>    Select <collist> from <table 1> JOIN  <table 2> USING (common_column)
            -- In USING() column name must be same in both tables.

    Examples:

    1. Display employee names and their respective dept name ?
    =>    Select e.ename , d.dname from emp e, dept d where e.deptno = d.deptno;
            (or)
    =>    Select e.ename , d.dname from emp e JOIN dept d ON e.deptno = d.deptno;
            (or)
    =>    Select e.ename , d.dname from emp e JOIN dept d USING( deptno );

    2. Display names of the employees who are working in Newyork.
    =>    Select e.ename, d.loc from emp e, dept d where e.deptno = d.deptno and d.loc = 'NEWYORK';
            (or)
    =>    Select e.ename , d.loc from emp e JOIN dept d ON e.deptno = d.deptno AND d.loc = 'NEWYORK';
            (or)
    =>    Select e.ename , d.loc from emp e JOIN dept d USING( deptno ) where d.loc = 'NEWYORK';

JOINS

=>    Join is a data retrieval operation. This operation is performed to get data from multiple tables.

Types of JOINS:

(i) EQUI JOIN or INNER JOIN
(ii) NON EQUI JOIN
(iii) SELF JOIN
(iv) OUTER JOIN
    a)    LEFT OUTER
    b)    RIGHT OUTER
    c)    FULL OUTER
(v) NATURAL JOIN.  
(vi) CROSS JOIN.

Multi Row Function

Multi Row Function Tips

=>    Will process group of records and returns one value from the group.
=>    These functions are also called as "Aggregate Functions"  or  "Group Functions".

(i)    Max():

=>    It returns maximum value of a given expression.

Syntax:
=>    MAX( expr )

Examples:

1.    Display the maximum salary paid to the employee.
=>    Select MAX( sal ) from emp;

2.    Display the Maximum experienced employee.
=>    Select MAX( sysdate - hiredate ) from emp;

3.    Display the maximum salary of the employee who is working in 30th dept.
=>    Select Max( sal ) from emp where deptno = 30;

(ii)    Min():

=>    It returns minimum value of the given expression.

Syntax:
=>    MIN( expr )

Examples:

1.    Display the minimum salary paid to the employee.
=>    Select MIN( sal ) from emp;

2.    Display the minimum experienced employee.
=>    Select MIN( sysdate - hiredate ) from emp;

3.    Display the minimum salary of the employee who is working in 30th dept.
=>    Select MIN( sal ) from emp where deptno = 30;

(iii)    SUM():

=>    It returns sum of the expression.

Syntax:
=>    SUM( expr )

Examples:

1. Get the total paid to each Dept ?
=>    Select SUM( sal ) from Emp Group By dept;

(iv)    AVG():

=>    It returns the Average of the expression.

Syntax:
=>    AVG( expr )

Examples:

1. Get the Average sal of all the employees ?
=>    Select AVG( sal ) from Emp;

(v)    COUNT():

=>    It returns the total no of records present in a column.
=>    Count ignores NULL values.
=>    Count will count duplicate values also.
        To ignore it, use DISTINCT keyword.
            " COUNT( DISTINCT empno ) "

Syntax:
=>    COUNT( expr )

Examples:

1.  Count the no of employees working in dept no 30.
=>    Select count(*) from emp where deptno = 30;

OLAP Functions

OLAP Functions Tips

=> OLAP functions are also called as "Analytical Functions" or "Window Functions".

We can perform the following operations:
    (i)    Rank the records.
    (ii)    Get the record number.

(i)    RANK  &  DENSE_RANK

=> Ranking will be done based on some value.
=> These function accepts only sorted set of values.
=>    Rank() generate gaps,
    Dense_Rank() doesn't generate gaps.

    i.e    Rank:        { 1,2,3,3,5,6,.... }    4 is skipped.
        Dense_Rank:    { 1,2,3,3,4,5,6,.... }    4 is involved.

Syntax:
=>    RANK() over (expr)
=>    DENSE_RANK() over (expr)

Examples:

1.    Display Ranks of the employees based on their Salaries ?
=>    Select ename, sal, RANK() OVER( ORDER BY SAL) as "Rank" from Emp;

2.    Display Ranks of the employees based on their Hiredate ?
=>    Select ename, sal, RANK() OVER( ORDER BY hiredate) as "Rank" from Emp;

(ii)    ROW_NUMBER

=> Returns the record number.

Syntax:
=>    ROW_NUMBER() OVER (expr)

Examples:
=>    Select ename, sal, ROW_NUMBER() over (ORDER BY sal DESC) as "Row Number" from Emp;

Special Function

1. What can we do with Special Functions ?

    We can do the following:
    (i)    Replace NULL values.
    (ii)    Use If-else condition.
    (iii)    Find the Greatest and Least.
    (iv)    Change ASCII value to Character value and viceversa.

(i)    Replace NULL values.

    (I) NVL:

    If expr1 is not null, it will return expr2.
    If expr1 is null, return expr2.

    Syntax:
    => NVL( expr1, expr2 )

    Examples:

    => NVL( 101, 102 )        = 101
    => NVL( null, 100 )        = 100
    => NVL( null, null )        = null
    => Select Sal + NVL( comm, 0 ) as "Total Salary" from Emp;       
                    = 100

    (II) COALESCE:

    Returns the first non null value.

    Syntax:
    => COALESCE( expr1, expr2, expr3, ....)

    Examples:

    => COALESCE( null, 100, 200 )    = 100
    => COALESCE( 100, 200 )        = 100
    => COALESCE( 123, 200, null )    = 123

(ii)    Use If-else condition.

    (I) NVL2:

    If expr1 is not null, it returns expr2.
    If expr1 is null, it returns expr3.

    Syntax:
    => NVL2( expr1, expr2, expr3 );

    Examples:
    => NVL2( 10,20,30 )        = 20.
    => NVL2( null,20,30 )        = 30.
    => Select NVL2( comm, comm, 0 ) from emp;

    (II) DECODE:

    Works same as If-then-else.

    Syntax:
    => DECODE( expr1, value1, return expr1,
                    value2, return expr2,
                    value3, return expr3, .....
                    , return Default expr)
   
    If default expr is not provided, it returns NULL.

    Examples:
    => DECODE( 3+2, 5, return 5, 6, return 6)        = 5

    => Select DECODE( JOB, 'CLERK', 'WORKER',
                        'MANAGER', 'BOSS',
                        'PRESIDENT', 'BIG BOSS',
                        ,' EMPLOYEE')
        From EMP;

(iii)    Find the Greatest and Least.

   

    (I)    GREATEST()

        Returns greatest value among the given value.

        Syntax:
        =>    GREATEST ( expr1, expr2, expr3, ....)

        Examples:
        => GREATEST ( 10,20,30 )        = 30
        => GREATEST ( 10,30,12 )        = 30
        => GREATEST ( 30,45,5 )            = 45

    (II)    LEAST()

        Returns least value among the given values.

        Syntax:
        =>    LEAST ( expr1, expr2, expr3, ....)

        Examples:
        => LEAST ( 10,20,30 )        = 10
        => LEAST ( 10,30,12 )        = 10
        => LEAST ( 30,45,5 )        = 5

(iv)    Change ASCII value to Character value and viceversa.

    (I) CHR()

        Converts the ASCII value to the respective character.

        Syntax:
        =>    CHR( ASCII_value )

        Examples:
        =>    CHR(65)        = A
        =>    CHR(97)        = a
   

    (II) ASCII()

        Converts the CHAR value to the respective ASCII value.

        Syntax:
        =>    ASCII( character )

        Examples:
        =>    ASCII( 'A' )    = 65
        =>    ASCII( 'a' )        = 97

Conversion Function

1.  What can we do with Conversion Functions ?

    We can do the following:
    (i)    Convert one datatype to another datatype.
    (ii)    There are 3 types of conversion

        - converting NUMBER, DATE to CHARACTER.    [ TO_CHAR() ]
        - converting NUMBER, CHARACTER to DATE.    [ TO_DATE() ]
        - converting CHARACTER to NUMBER.        [ TO_NUMBER() ]

(I) TO_CHAR():

Syntax:
=>    TO_CHAR( date  [, format] )
=>    TO_CHAR( number  [, format] )

Examples:
=>    TO_CHAR( SYSDATE, 'YYYY' )        = 2012
=>    TO_CHAR( SYSDATE, 'MON' )        = FEB
=>    TO_CHAR( SYSDATE, 'DD' )        = 13
=>    TO_CHAR( SYSDATE, 'DAY' )        = MONDAY
=>    TO_CHAR(123456, '9,99,999')        = 1,23,456

(II) TO_DATE():

Syntax:
=>    TO_DATE( charString [,format] )
=>    TO_DATE( number [,format] )

Examples:
=>    TO_DATE('12-02-12','DD-MM-YY')                    = 12-FEB-12
=>    TO_DATE('17-FEBRUARY-2012','DD-MONTH-YYYY')    = 17-FEB-12
=>    TO_DATE(123, 'J')                                = 03-MAY-12        { '01-JAN-12' + 123 }

(III) TO_NUMBER():

Syntax:
=>    TO_NUMBER( charString [,format] )

Examples:
=>    TO_NUMBER( '1,000' , '9,999')            = 1000
=>    TO_NUMBER( '$1,23,456' , 'L9,99,999')    = 123456

Date Functions

1.    What can we do with Oracle date functions ?

    We can get
    (i)    current system date (date, date & time)
    (ii)    current session date (date, date & time)
    (iii)    Add months to a date.
    (iv)    Extract day, month, year from a date.
    (v)    Last day of the Month. (monday, tuesday etc..)
    (vi)    Next day.
    (vii)    Difference between two date's and return months, days etc..
    (viii)    ROUND, TRUNC the date to apprx value.   

(i)    current system date (date, date & time)    &    current session date (date, date & time)

=>    CURRENT_DATE            12-FEB-12
=>    CURRENT_TIMESTAMP        12-FEB-12 08.44.09.943000 PM +05:30
=>    DBTIMEZONE                +00:00
=>    LOCALTIMESTAMP            12-FEB-12 08.45.48.553000 PM
=>    SESSIONTIMEZONE            +05:30
=>    SYSDATE                    12-FEB-12
=>    SYSTIMESTAMP                12-FEB-12 08.46.40.528000 PM +05:30

(ii)    Add months to a date.

    We can perform :
x    Date1 + Date2        Invalid
    Date1 - Date2        valid
    Date1 + number    valid
    Date1 - number    valid

    To add months to a date, we can use "ADD_MONTHS" function.
   
=>    SYSDATE + 5
=>    SYSDATE -10
=>    SYSDATE + '12-DEC-12'        NOT VALID
=>    Select SYSDATE + HIREDATE From EMP;
=>    Select sysdate, ADD_MONTHS(sysdate, 2) From Dual;            -- 12-FEB-12, 12-APR-12
=>    Select sysdate, ADD_MONTHS(sysdate, -4) From Dual;            -- 12-FEB-12, 12-OCT-11

(iii)    Extract day, month, year from a date.

=>    Select EXTRACT (day from sysdate) From Dual;        12
=>    Select EXTRACT (month from sysdate) From Dual;        DEC
=>    Select EXTRACT (year from sysdate) From Dual;        2012

(iv)    Last day of the Month. (monday, tuesday etc..)

=>    Select last_day( sysdate ) from dual;        29-FEB-12
=>    Select last_day( sysdate ) +1 from dual;        01-MAR-12

(v)    Next Day

=>    Select next_day( sysdate, 'monday' ) from dual;            13-FEB-12
=>    Select next_day( last_day(sysdate) -7, 'sunday') from dual;    26-FEB-12

(vi)    Difference between two date's and return months, days etc..

=>    Select MONTHS_BETWEEN( '12-DEC-12', '01-JAN-12') From Dual;        11 months.


Note:

1. In date function, Months_Between and Extract (day, year) returns Number as output.

Numeric Function

1. What can we do with Numeric Functions ?

    We can do the following:
    (i)    Make a negative no positive.
    (ii)    Get the sign of the Number.
    (iii)    Find the power of required no.
    (iv)    Find the Sqrt of a number.
    (v)    Get the remainder of 2 numbers.
    (vi)    Get the customized Apprx value.

(i)    Make a negative number positive.

       
=>    ABS( -10)             10
=>    ABS( 20)             20
=>    ABS( -30)             30
=>    ABS( -10)             10
   
    Mostly used in case of date functions.

    Ex:    Select ABS ( MONTHS_BETWEEN ( SYSDATE - HIREDATE ) ) from Emp;

(ii)    Get the sign of the Number.

=>    SIGN( 10 )            = 1;
=>    SIGN( -10 )        = -1;
=>    SIGN( 0 )            = 0;

(iii)    Find the power of required no.

=>    POWER( 3,2)        = 9
=>    POWER(12,2)        = 144
=>    POWER(101,0)        = 1
=>    POWER(2, -1)        = 0.5

(iv)    Find the Sqrt of a number.

=>    SQRT( 4 )            = 2
=>    SQRT( 16 )        = 4
=>    SQRT( 9 )            = 3

(v)    Get the remainder of 2 numbers.

=>    MOD( 10,5 )        = 0
=>    MOD( 10,3 )        = 1
=>    MOD( 10,4 )        = 2
=>    MOD( 10,7 )        = 3

(vi)    Get the customized Apprx value.

CEIL:

=>    CEIL( 10.1 )            = 11
=>    CEIL( 9.9 )                = 10

FLOOR:

=>    FLOOR( 10.1 )            = 10
=>    FLOOR( 9.9 )            = 9

ROUND:

If the decimal lies in [0,4] then Oracle Uses FLOOR function.
If the decimal lies in [5,9] then Oracle Uses CEIL function.

=>    ROUND( 101.2 )        = 101
=>    ROUND( 101.234,  2)    = 101.23
=>    ROUND( 101.2,-2 )        = 100
=>    ROUND( 101.2,-3 )        = 0
=>    ROUND( 103.73 )        = 104

If MODE = 'year',    date lies in [JAN - JUN] then Oracle sets to  "01-JAN of current year".
                date lies in [JUL - DEC] then Oracle sets to  "01-JAN of next year".

If MODE = 'month',    date lies in [01 - 15] then Oracle sets to  "01-current month".
                date lies in [16 - end] then Oracle sets to  "01-next month".

If MODE = 'day',    date lies in [SUNDAY - WEDNESDAY] then Oracle sets to "Current Sunday of the Week".
                date lies in [THRUSDAY-SUNDAY] then Oracle sets to "Next Sunday of the Week".


=>    ROUND( '12-FEB-12', 'year')        = 01-JAN-12
=>    ROUND( '12-OCT-12', 'year')            = 01-JAN-13
=>    ROUND( '30-JUN-12', 'year')        = 01-JAN-12
=>    ROUND( '01-JUL-12', 'year')            = 01-JAN-13

=>    ROUND( '12-FEB-12', 'month')        = 01-FEB-12
=>    ROUND( '18-OCT-12', 'month')    = 01-NOV-12
=>    ROUND( '15-JUN-12', 'month')        = 01-JUN-12
=>    ROUND( '16-JUN-12', 'month')        = 01-JUL-12

=>    ROUND( '13-FEB-12', 'day')   [monday]        = 12-FEB-12           
=>    ROUND( '17-FEB-12', 'day')   [friday]        = 19-FEB-12
=>    ROUND( '12-FEB-12', 'day')   [sunday]        = 12-FEB-12


TRUNC:

=>    TRUNC(10.2)                        = 10
=>    TRUNC(10.234,2)                    = 10.23
=>    TRUNC(1123.923,-3)                = 1000

If MODE = 'year',    Oracle sets date to "01-JAN- current year"
If MODE = 'month',    Oracle sets date to "01-current month"
If MODE = 'day',    Oracle sets date to "starting day of the current week"

=>    TRUNC(to_date('31-DEC-12'),'year')        = 01-JAN-12
=>    TRUNC(to_date('30-DEC-12'),'month')    = 01-DEC-12
=>    TRUNC(to_date('14-FEB-12'),'day')        = 12-FEB-12

Character Functions

1. SUBSTR

    Syntax:    SUBSTR( string, position, length );

    ex:        Select substr( 'shashi', 2) from dual;    -- hashi
            Select substr( 'shashi', 0, 1) from dual;    -- s
            Select substr( 'shashi', 1, 1) from dual;    -- s
            Select substr( 'shashi', 0, 2) from dual;    -- sh
            Select substr( 'shashi', -1, 1) from dual;    -- i
            Select substr( 'shashi', -2, 2) from dual;    -- hi
            Select substr( 'shashi', -2, 6) from dual;    -- hi    only remaining characters are printed.
   
    Logic:    length is optional in SUBSTR.
                If length is not provided, starting from the position, entire string will be printed.
            If position = -ve, Oracle starts from backward.
            If position is greater than the length, nothing is printed.
            If position = -ve and length > remaining characters, Only the string is printed but not the null values.
           

2. INSTR

    Syntax:    INSTR( String, search_string, start, occurance );

    ex:        Select instr( 'shashi', 'h') from dual;        -- 2
            Select instr( 'shashi', 's') from dual;        -- 1
            Select instr( 'shashi', 's',2) from dual;    -- 4
            Select instr( 'shashi', 'h',2) from dual;    -- 5
            Select instr( 'shashi', 's',1,2) from dual;    -- 4
            Select instr( 'shashi', 's',-1,2) from dual;    -- 1
            Select instr( 'shashi', 'h',1,2) from dual;    -- 5
            Select instr( 'shashi', 's',6,2) from dual;    -- 0   
   
    Logic:    start, occurance are optional in INSTR.
                If start is not provided, it starts from the begging.
                If occurance is not provided, it returns the first occurance.
            If start is -ve, Oracle starts from backward.

3. REPLACE

    Syntax:    REPLACE( String, search_string, replacement_string );

    ex:        Select replace('shashi', 'h') from dual;        -- sasi
            Select replace('shashi', 'h', 'o') from dual;        -- soasoi
   
    Logic:    replacement_string is optional.

4. TRANSLATE

    Syntax:    TRANSLATE( 'expression', 'fromString', 'toString');

    ex:        Select translate('shashikanth', 'ha', 'mn') from dual;        -- smnsmiknntm
            Select translate('shashikanth', 'shas', 'kota') from dual;    -- kotkoiktnto        here s = { k,a }, but s accepts only k.

Logic:    toString is optional.
                If toString is not provided, blank characters are placed in place of fromString characters.
            It is a character based replacement.   
                If a character is assigned a value, it acts as a constant value.

Functions in Oracle

Character Functions:

    1. SUBSTR    ( mainString, start, length )
    2. INSTR( mainString, searchString, start, occurance)
    3. REPLACE( mainString, searchString, replaceString )
    4. TRANSLATE( mainString, charString, translateString )
More about Character Functions

Numeric Functions:

    1. ABS( number )
    2. SIGN( number )
    3. SQRT( number )
    4. POWER( number1,number2 )
    5. MOD( number1,number2 )
    6. CEIL( decimal_number )
    7. FLOOR( decimal_number )
    8. ROUND
        a) ROUND( decimal_number, number )
        B) ROUND( date, mode )
    9. TRUNC
        a) TRUNC( decimal_number, number )
        b) TRUNC( date, mode )
More about Numeric Functions

Date Functions:

    1. ADD_MONTHS( date, months )
    2. MONTHS_BETWEEN ( date1, date2 )
    3. EXTRACT( MODE from date )
    4. LAST_DAY( date )
    5. NEXT_DAY( date )
More about Date Functions

Conversion Functions:

    1. TO_CHAR( date, format )
    2. TO_CHAR( number, format )
    3. TO_DATE( string, format )
    4. TO_DATE( number, format )
    5. TO_NUMBER( string, format )
More about Conversion Functions

Special Functions:

    1. NVL( value1, value2);
    2. COALESCE( expr1, expr2, expr3, ..... )
    3. NVL2( expr1, expr2, expr3 )
    4. DECODE( expr1, value1, return value1, value2, return value2, ..... default value )
    5. GREATEST( value1, value2, value3, .... )
    6. LEAST( value1, value2, value3, .... )
    7. CHR( number )
    8. ASCII( character )
More about Special Functions

OLAP Functions or Analytical Functions or Window Functions:

    1. RANK() OVER ( expr )
    2. DENSE_RANK() OVER ( expr )
    3. ROW_NUMBER() OVER ( expr )
More about OLAP functions

Multi Row Functions:

    1. MAX( column )
    2. MIN( column )
    3. SUM( column )
    4. AVG( column )
    5. COUNT( column )
More about Multi Row Functions

Tips:

1. Combination of Multi Row Functions and Single Row Function is not possible in Select clause.
2. In Character Functions, LENGTH and INSTR returns a Number.
    In Date Functions, MONTHS_BETWEEN and EXTRACT( year, day ) retuns a Number.
3. All the above Functions return NULL values except
    NVL, COALESCE, NVL2, REPLACE, CONCAT
4. Functions which doesn't accept any arguments are called 'PSEUDO CODE' functions.
    =>    Sequence Pseudo Columns { CURRVAL, NEXTVAL }
    =>    ROWID
    =>    ROWNUM
5. Valid and Invalid Operations
    =>    sysdate + 10                is valid.
    =>    '01-JAN-12' + 20            is In Valid.
    =>    sysdate + (sysdate + 10)        is In Valid.