PL/SQL Introduction

• Procedural language extension to sql with design feature of programming language

• Data manipulation and query statements of sql are included with in procedural units of code.

• It is mainly used to reduce the network traffic

• Pl/sql statement is used to group the set of sql statement into single block and send the entire block to the server in a single call (here set of sql statement can execute at a single time)

• It was developed by Oracle Corporation in the early 90’s to enhance the capabilities of SQL.

PL/sql Engine

• Oracle uses a PL/SQL engine to processes the PL/SQL statements. 
• A PL/SQL code can be stored in the client system (client-side) or in the database (server-side).

The program unit is stored in a database. When an application calls a procedure stored in the database, Oracle loads the compiled program unit into the shared pool in the system global area (SGA). The PL/SQL and SQL statement executors work together to process the statements within the procedure.

Reference : http://docs.oracle.com/cd/B19306_01/server.102/b14220/sqlplsql.htm 

Using Group by clause along with ROLLUP or CUBE operators

ROLLUP

        Use the ROLLUP operator to Produce the sub total values, 

        ROLLUP is an Extension GROUP BY clause       

SELECT Department_id, SUM (salary)

FROM   Employees

WHERE  department_id IN (10,20,30)

GROUP BY ROLLUP(Department_id);

- - - - - - - - - - - - - - - - - - - - - 

Department_id     SUM(salary)

- - - - - - - - - - - - - - - - - - - - - 

           10    4400

           20    19000

           30    24900

                  48300

- - - - - - - - - - - - - - - - - - - - -  

SELECT 4400+19000+24900 FROM dual;

48300

SELECT Department_id "Dep_id", job_id,  SUM (salary)

FROM    Employees

WHERE department_id IN (90,20,30)

GROUP BY ROLLUP(Department_id, Job_id);

- - - - - - - - - - - - - - - - - - 

Dep_id    Job_id       SUM(salary)

- - - - - - - - - - - - - - - - - - 

    20    MK_MAN        13000

    20    MK_REP         6000

    20                 19000

    30    PU_MAN        11000

    30    PU_CLERK      13900

    30                 24900

    90    AD_VP         34000

    90    AD_PRES       24000

    90                 58000

                       101900

- - - - - - - - - - - - - - - - - - 

CUBE

     The cube operator is used to produce results sets that are typically used for cross-tabular reports. 

This means Rollup produces only one possible subtotaling where as Cube produces subtotal for all possible conditions of grouping specified in the group by clause and a grand total

SELECT Department_id "Dept_id", SUM (salary)

FROM    Employees

WHERE department_id IN (10,20,30)

GROUP BY CUBE(Department_id);

- - - - - - - - - - - - 

Dept_id   SUM(salary)

- - - - - - - - - - - - 

           48300

    10    4400

    20    19000

    30    24900

- - - - - - - - - - - - 

The following query produces subtotaling results based on job,based on deptno and based on the individual jobs(MK_MAN or MK_REP or PU_MAN or  AD_ASST or PU_CLERK etc in dept 10, 20 and 30)

SELECT Department_id "Dept_id", job_id, SUM (salary)

FROM    Employees

WHERE department_id IN (10,20,30)

GROUP BY CUBE(Department_id, job_id );

- - - - - - - - - - - - - - - - - - - -

Dept_id       Job_id        SUM(salary)

- - - - - - - - - - - - - - - - - - - -

                         48300

              MK_MAN    13000

              MK_REP      6000

              PU_MAN    11000

              AD_ASST     4400

              PU_CLERK   13900

    10                    4400

    10       AD_ASST      4400

    20                  19000

    20       MK_MAN      13000

    20       MK_REP       6000

    30                  24900

    30        PU_MAN    11000

    30       PU_CLERK    13900

- - - - - - - - - - - - - - - - - - - -

Joins in oracle

                  SQL Joins are used to relate information in different tables. A Join condition is a part of the sql query that retrieves rows from two or more tables. 

SELECT * FROM EMPLOYEES;

 SELECT * FROM DEPARTMENTS;

SELECT * FROM LOCATIONS;

Types of Joins :

   Equiv Join
   Outer Join
         Right Outer Join
         Left Outer Join
         Full Outer Join
   Inner Join
   Cross Join

Equiv Join :


SELECT
Employees.First_name
,    Employees.Last_name
,    Employees.Department_id
,    Departments.Department_name
,    Departments.Location_id
FROM
Employees,
Departments
WHERE
Employees.Department_id = Departments.Department_id;


Equiv Join by using table Alias name:

SELECT
E.First_name
,   E.Last_name
, E.Department_id
,   D.Department_name
, D.Location_id
FROM
Employees   E
, Departments D
WHERE
E.Department_id = D.Department_id;


select * from employees;


Course students

- - - - - - - -        - - - - - - - -
c_id    c_name s_name   c_id
- - - - - - - -        - - - - - - - -
10 Java name1 20
20 Oracle name2 10
30 dotnet name3 40
40 testing name4 30
50 php name5 60
- - - - - - - -        - - - - - - - -

Join condition : Students.c_id  = course.c_id

Join  :  It will give you matched records only.

- - - - - - - - - -
s_name c_name
 - - - - - - - - - -
name1      oracle
name2 Java
name3 testing
name4 dotnet  
 - - - - - - - - - -

Outer Join   :  It will give you matched records + unmatched records

Types :
 
       Right outer join
left outer join
full outer join


Right outer join

 - - - - - - - - -
s_name c_name
- - - - - - - - -
name1   oracle
name2 Java
name3 testing
name4 dotnet
  -     php
 - - - - - - - - -

Left outer join

 - - - - - - - - - - -
s_name c_name
- - - - - - - - - - -
name1      oracle
name2 Java
name3 testing
name4 dotnet
  name5        -
 - - - - - - - - - - -

Full outer join

 - - - - - - - - - - -
  s_name c_name
 - - - - - - - - - - -
name1      oracle
name2 Java
name3 testing
name4 dotnet
    -        php
name5 -
 - - - - - - - - - - -

Outer Join : Right Outer Join


SELECT
E.First_name
, E.Last_name
, D.Department_id
, D.Department_name
, D.Location_id
FROM
Employees E
,    Departments D
WHERE
E.Department_id (+) = D.Department_id;



Outer Join  :  Left Outer Join


SELECT
E.First_name
, E.Last_name
, E.Department_id
, D.Department_name
, D.Location_id
FROM  
Employees E
, Departments D
WHERE
E.Department_id = D.Department_id(+);


Outer Join : Full Outer Join

         Discuss later


Inner Join :


Select * from Employees;

select
E1.Employee_id
, E1.First_name
, E1.Manager_id
, E2.First_name "MANAGER_NAME"
From
Employees E1
, Employees E2
Where
E1.Manager_id = E2.Employee_id;


Perform Join By using KEY WORDS and ON clause

We can written this above concepts by using following methods

  Instead of WHERE clause we can use ON clause
  No need to include + sign
  Use Following Key words


Key words:


     Join
  Right Outer Join
  Left  Outer Join
  Full  Outer Join
  Inner Join
  Cross Join


Join


SELECT
E.First_name
, E.Last_name
, E.Department_id
, D.Department_name
, D.Location_id
FROM
Employees E   JOIN
Departments D
ON
E.Department_id = D.Department_id;


Right outer Join


SELECT
E.First_name
, E.Last_name
, E.Department_id
, D.Department_name
, D.Location_id
FROM
Employees E RIGHT OUTER JOIN
Departments D
ON
E.Department_id = D.Department_id;


Left outer Join


SELECT
E.First_name
, E.Last_name
,  E.Department_id
, D.Department_name
, D.Location_id
FROM
Employees E LEFT OUTER JOIN
Departments D
ON  
E.Department_id = D.Department_id;


Full outer Join


SELECT
E.First_name
, E.Last_name
,  E.Department_id
,  D.Department_name
,  D.Location_id
FROM
Employees E FULL OUTER JOIN
Departments D
ON
E.Department_id = D.Department_id;


Inner Join or Self Join


     Selecting data with in a table.

SELECT
E1.Employee_id
,  E1.First_name
,  E1.Manager_id
,  E2.First_name "MANAGER_NAME"
From
Employees E1 INNER JOIN
Employees E2
ON
E1.Manager_id = E2.Employee_id;


Cross Join


Cross join is a Cartesian product no of rows in the first table join with no of rows in the second table
Cartesian product is form when you ignore the where clause or valid join condition
Cross join is a wrong join
To avoid the cross join we use the where clause or valid join condition

select
E1.Employee_id
,   E1.First_name
,   E1.Manager_id
,   E2.First_name "MANAGER_NAME"
From
Employees E1 CROSS JOIN
Employees E2;

n1 x3
n2 x1
n3 x2

n1   x1
n1   x2
n1   x3
n2   x1
n2   x2
n2   x3
n3   x1
n3   x2
n3   x3


Data From 3 tables  :

Relation
      Employees and Departments table  - Department_id column.
Departments and Locations table - location_id column.

SELECT
E.First_name     ,
E.Last_name      ,
E.Department_id  ,
D.Department_name,
D.Location_id    ,
L.Street_address ,
L.city
FROM
Employees   E ,
Departments D ,
Locations   L
WHERE
E.Department_id = D.Department_id AND
D.Location_id   = L.Location_id;

If you are going to fetch data from 50 tables. You need to write 49 condition ( i.e. n-1 condition)


Additional Examples For Outer Joins


CREATE TABLE COURSE
                 (
                     CID    NUMBER(3)    ,
                     CNAME  VARCHAR2(20)
                  );

INSERT INTO COURSE VALUES (10,'ORACLE');
INSERT INTO COURSE VALUES (20,'DOTNET');
INSERT INTO COURSE VALUES (30,'JAVA');
INSERT INTO COURSE VALUES (40,'UNIX');
INSERT INTO COURSE VALUES (50,'TESTING');


SELECT * FROM COURSE;


CREATE TABLE STUDENTS
(
SNAME  VARCHAR2 (20),
CID    NUMBER (3)
);

INSERT INTO STUDENTS VALUES ('SENTHIL', 20);
INSERT INTO STUDENTS VALUES ('MURUGAPPAN', 10);
INSERT INTO STUDENTS VALUES ('RAMESH', 30);
INSERT INTO STUDENTS VALUES ('KARTHI', 40);
INSERT INTO STUDENTS VALUES ('RAVI', 60);

SELECT * FROM STUDENTS;

Equi Join


SELECT
S.SNAME  ,
    C.CID    ,
    C.CNAME
FROM
STUDENTS S ,
COURSE   C
WHERE
S.CID = C.CID;


OUTER JOIN TYPES:

  RIGHT OUTER JOIN
  LEFT  OUTER JOIN
  FULL  OUTER JOIN

RIGHT OUTER JOIN
   
     It Shows Matched as well as unmatched Records on Right side table
       [By including + on right side]


SELECT
S.SNAME  ,
C.CID    ,
C.CNAME
FROM
STUDENTS S ,
COURSE C
WHERE
S.CID (+) = C.CID;


LEFT OUTER JOIN

   It Shows Matched as well as unmatched Records on Left side table
   [By including + on Left side]


SELECT
S.SNAME ,
C.CID   ,
C.CNAME
FROM
STUDENTS S ,
COURSE C
WHERE
S.CID = C.CID (+);

Restriction and sorting [ WHERE clause ]

SELECT * 
FROM employees;

Number

SELECT * 
FROM   employees
WHERE  department_id = 90;

String:

SELECT * 
FROM   employees
WHERE  First_name = 'steven';

no date fount

*data is case sensitive in oracle

SELECT * 
FROM   employees
WHERE  First_name = 'Steven';

Date:

Date Format  :  DD-MON-YY     e.g. 14-JUN-12

SELECT first_name, Hire_date FROM employees
WHERE hire_date = '08-MAR-00';

Operators :

       =   ,    >  ,  >=   ,   <   ,    <=   ,   <>    (or)    !=       (or)      ^=
     
=

SELECT * FROM employees
WHERE salary = 24000;

>

SELECT * FROM employees
WHERE salary > 17000;

<

SELECT * FROM employees
WHERE salary >= 17000;


!=   [or]  < >  [or]  ^=


SELECT * FROM employees
WHERE first_name != 'Steven';

Other operators

      BETWEEN ...  AND  ....     
      NOT BETWEEN ...  AND  ....     
      IN  
      NOT IN  
      LIKE  
      NOT LIKE
      IS NULL 
      IS NOT NULL     

BETWEEN ...  AND  ....     


SELECT * FROM employees
WHERE salary BETWEEN 5000 AND 10000;


NOT  BETWEEN ...  AND  ....


SELECT * FROM employees
WHERE salary not between 5000 and 10000;


IN


SELECT * FROM employees
WHERE salary IN (24000, 10000, 17000);


NOT IN


SELECT * FROM employees
WHERE salary NOT IN (24000, 10000, 17000);


LIKE


     Pattern matching operator

     % -> something or zero  
     _  -> position


Query to fetch all the first name which starts with S


SELECT * FROM employees
WHERE first_name LIKE 'S%';


Query to fetch all the first name which starts with S and end with n


SELECT * FROM employees
WHERE first_name LIKE 'S%n';


Query to fetch all the first name which contain u as second character


SELECT * FROM employees
WHERE first_name LIKE '_u%';


Query to fetch all the first name which contain u as second character and i as fourth character


SELECT * FROM employees
WHERE first_name LIKE '_u_i%';


Query to fetch all the mail_id which contain _ at third position 


SELECT * FROM employees
WHERE mail_id LIKE '__\_%' ESCAPE '\';

                                  [ or ]

SELECT * FROM employees
WHERE mail_id LIKE '__$_%' ESCAPE '$';


NULL


SELECT * FROM employees
WHERE commission_pct IS NULL;


IS NOT NULL


SELECT * FROM employees
WHERE commission_pct IS NOT NULL;


AND and OR logical operator

AND operator

SELECT * FROM employees
WHERE  First_name = 'Steven'  AND 
       Salary = 24000;


OR operator


SELECT * FROM employees
WHERE  First_name = 'Steven'  OR
       Salary = 24000;


AND and OR operator


SELECT * FROM employees
WHERE  (First_name = 'Steven' OR First_name='Lex') AND 
       Salary > 15000;

ASC and DESC

          Ascending      ASC     [ Default ]
          Descending    DESC


SELECT * FROM employees;

Sorting based on ASC

SELECT * FROM employees
ORDER BY department_id ASC;

Sorting based on DESC

SELECT * FROM employees
ORDER BY department_id DESC;

Sorting based on ASC (default)

SELECT * FROM employees
ORDER BY by department_id;

Sorting based on alias name

SELECT first_name, hire_date, salary as a
FROM employees
ORDER BY a DESC;

Sorting based on multiple columns

SELECT first_name, department_id, salary
FROM employees
ORDER BY department_id, salary ASC;


Thanks for reading!!!!