Clustered and Non-Clustered Indexes

Clustered indexes :-
---------------------
Clustered indexes sort and store the data rows in the table based on their key values. There can only be one clustered index per table, because the data rows themselves can only be sorted in one order.

In SQL Server, indexes are organized as B-trees. Each page in an index B-tree is called an index node. The top node of the B-tree is called the root node. The bottom level of nodes in the index is called the leaf nodes. Any index levels between the root and the leaf nodes are collectively known as intermediate levels. In a clustered index, the leaf nodes contain the data pages of the underlying table. The root and intermediate level nodes contain index pages holding index rows. Each index row contains a key value and a pointer to either an intermediate level page in the B-tree, or a data row in the leaf level of the index. The pages in each level of the index are linked in a doubly-linked list.

Clustered indexes have one row in sys.partitions, with index_id = 1 for each partition used by the index. By default, a clustered index has a single partition. When a clustered index has multiple partitions, each partition has a B-tree structure that contains the data for that specific partition. For example, if a clustered index has four partitions, there are four B-tree structures; one in each partition.

Depending on the data types in the clustered index, each clustered index structure will have one or more allocation units in which to store and manage the data for a specific partition. At a minimum, each clustered index will have one IN_ROW_DATA allocation unit per partition. The clustered index will also have one LOB_DATA allocation unit per partition if it contains large object (LOB) columns. It will also have one ROW_OVERFLOW_DATA allocation unit per partition if it contains variable length columns that exceed the 8,060 byte row size limit. For more information about allocation units, see Table and Index Organization.

The pages in the data chain and the rows in them are ordered on the value of the clustered index key. All inserts are made at the point where the key value in the inserted row fits in the ordering sequence among existing rows. The page collections for the B-tree are anchored by page pointers in the sys.system_internals_allocation_units system view.

For a clustered index, the root_page column in sys.system_internals_allocation_units points to the top of the clustered index for a specific partition. SQL Server moves down the index to find the row corresponding to a clustered index key. To find a range of keys, SQL Server moves through the index to find the starting key value in the range and then scans through the data pages using the previous or next pointers. To find the first page in the chain of data pages, SQL Server follows the leftmost pointers from the root node of the index.




Non-clustered Index Structures :-
------------------------------------
Non-clustered indexes have the same B-tree structure as clustered indexes, except for the following significant differences:

The data rows of the underlying table are not sorted and stored in order based on their non-clustered keys.

The leaf layer of a nonclustered index is made up of index pages instead of data pages.

Nonclustered indexes can be defined on a table or view with a clustered index or a heap. Each index row in the nonclustered index contains the nonclustered key value and a row locator. This locator points to the data row in the clustered index or heap having the key value.

The row locators in nonclustered index rows are either a pointer to a row or are a clustered index key for a row, as described in the following:

If the table is a heap, which means it does not have a clustered index, the row locator is a pointer to the row. The pointer is built from the file identifier (ID), page number, and number of the row on the page. The whole pointer is known as a Row ID (RID).

If the table has a clustered index, or the index is on an indexed view, the row locator is the clustered index key for the row. If the clustered index is not a unique index, SQL Server makes any duplicate keys unique by adding an internally generated value called a uniqueifier. This four-byte value is not visible to users. It is only added when required to make the clustered key unique for use in nonclustered indexes. SQL Server retrieves the data row by searching the clustered index using the clustered index key stored in the leaf row of the nonclustered index.

Nonclustered indexes have one row in sys.partitions with index_id >0 for each partition used by the index. By default, a nonclustered index has a single partition. When a nonclustered index has multiple partitions, each partition has a B-tree structure that contains the index rows for that specific partition. For example, if a nonclustered index has four partitions, there are four B-tree structures, with one in each partition.


Depending on the data types in the nonclustered index, each nonclustered index structure will have one or more allocation units in which to store and manage the data for a specific partition. At a minimum, each nonclustered index will have one IN_ROW_DATA allocation unit per partition that stores the index B-tree pages. The nonclustered index will also have one LOB_DATA allocation unit per partition if it contains large object (LOB) columns . Additionally, it will have one ROW_OVERFLOW_DATA allocation unit per partition if it contains variable length columns that exceed the 8,060 byte row size limit. For more information about allocation units, see Table and Index Organization. The page collections for the B-tree are anchored by root_page pointers in the sys.system_internals_allocation_units system view.

Loops in Stored Procedures

Whenever we execute a procedure each query or statement is executing once. 
Loop is a programming concept which is used to execute a group of statements repeatedly for the specific no of times.

3 Types of Loops :-
- Simple Loop
- For Loop
- While Loop

Correlated Subquery

Subquery which is depending on outer query output is called correlated subquery. First outer query is executed, based on the output, subquery is executed again based on the output, outer query is executed 2nd time.

In other words, a correlated subquery is a subquery that references a column from a table in the outer query, and uses that value as a condition in the subquery. The subquery is executed for each row returned by the outer query, so it is dependent on the data in the outer query.

1) Find the employees whose salary is higher than the average salary of their department.

select * from emp where sal >

(select avg(sal) from emp 

where deptno = emp.deptno)

2) Find the customers who have made more than one purchase in the past month.

SELECT *

FROM customers c

WHERE (

    SELECT COUNT(*)

    FROM orders o

    WHERE o.customer_id = c.customer_id

    AND o.order_date >= DATEADD(month, -1, GETDATE())

) > 1

Subquery

What is Subquery? Explain the Properties of a Subquery?
A query which is nested within another query is called subquery.
If you want to fetch data from one table based on the input value of other table we write subqueries.
Here inner query is executed first, output will be passed to outer query, based on that outer query will be executed. To write a subquery we need a common column.
Table from which you expect output, write an outer query on that.
Table from which you expect input, write an inner query on that.

Two types of subqueries :
- Single row subquery: Single-row subqueries are used with Comparison Operators.
- Multi row subquery: Multiple-row subqueries are used with Logical Operators.

Single row subquery :
If the subquery generates single output value then its called Single row subquery.
equal to operator is used in the single row subquery.

Syntax :
Select dname from dept where deptno = (select deptno from emp where empno = 7654);


Multi row subquery :
If the subquery generates more than one output value then its called Multi row subquery.
In operator is used in Multi row subquery.

Syntax :
Select * from emp where deptno IN (Select deptno from dept where dname IN ('Accounting', 'Research'));

SELECT * FROM EMP WHERE SAL < (SELECT MAX(SAL) FROM EMP);
 

Cursors

A cursor is a pointer to this temporary work area (context area) created in the system memory, when a SQL statement is executed. Temporary work area is used to store the data retrieved from the database and manupulate this data. It can hold more than one row but can process one row at a time.

There are two different Types of cursors
- Implicit Cursors 
- Explicit Cursors

Implicit Cursors : Implicit cursors are automatically created by oracle whenever SQL statement is executed. At the time of DML operations like insert, update and delete these cursors hold the data that needs to be inserted.
Here when you write a query completely table data will be processed in the cursor area.

Explicit Cursors : Explicit Cursors are created by users. They are used to display multiple records using program or procedure. 
Here when you write a query only the requred columns data will be processed in the cursor area.
It should be defined in the declaration section of the PLSQL block and created on select statement.
Fetch operation can get only 1 record because pointer is on the first record inorder to fetch all the records 'Loop' is used.

4 steps to write a cursor
- Declare Cursor
- Open Cursor
- Fetch data from Cursor
- Close Cursor

Cursor Properties
- <cursor name> %ISOPEN 
- <cursor name> %FOUND
- <cursor name> %NOT FOUND
- <cursor name> %ROWCOUNT

PLSQL Functions

REGEXP_REPLACE Function :

It will allow you to replace a sequence of characters in a string with another set of characters using regular expression pattern matching.

SELECT regexp_replace('Removing_Special - Characters .,_-$%&^& ',
'[^0-9 A-Za-z]', '');
            OR
Update emp2 set Ename = Regexp_Replace (Ename,'[^A-Z0-9 ]', '');

Triggers in PLSQL

Triggers in PL/SQL:-
-----------------
Trigger is one of the Database Objects which is saved in database. Triggers are automatically or implicitly executed for any DML operation on the table. 
Trigger has two functions ':Old' is used to get old values from the column and ':New' is used to get new values from the column

Trigger of are used for two purposes :-
------------------------------------
To maintain the data uniformity in a business :-
Data is inserted into the database server in any case as upper, lower and mixed case. But the business people will maintain the business data in only one case. Solution is before the data enters into the table as a record this data is taken by a trigger program by our defined trigger logic. 

To maintain audit information of table data :-
A table contains millions of records its difficult to keep the track on all inserted, updated and deleted rows. Inorder to maintain the track of all the changes in data an audit table is maintained as a solution. Audit tables will be automatically updated at the time of inserts or deletes. Audit tables are maintained seperately for inserts and updates happen in a table.  

Syntax :

CREATE [OR REPLACE ] TRIGGER trigger_name  
{BEFORE | AFTER | INSTEAD OF }  
{INSERT [OR] | UPDATE [OR] | DELETE}  
[OF col_name]  
ON table_name  
[REFERENCING OLD AS o NEW AS n]  
[FOR EACH ROW]  
WHEN (condition)   
DECLARE 
   Declaration-statements 
BEGIN  
   Executable-statements 
EXCEPTION 
   Exception-handling-statements 
END; 


Triggers are of 3 types :-

- DDL Triggers : Whenever we perform any ddl operations such as create, drop   table then we use ddl triggers.
- DML Triggers : Whenever we perform any dml operations such as insert,     update and delete then we use dml triggers.
- Instead of Triggers : If we want to perform any dml operations on view then   we use instead of triggers.

Triggers are further classified as  :-
- Row Level Trigger : If 100 records are updated in a table then the trigger will be executed 100 number of times then its a Row Level Trigger.
- Statement Level Trigger : If you delete records and if the trigger executed only one time then its a Table Level Trigger.


DML TRIGGERS :-

ROW LEVEL TRIGGER :-
-------------------------
CREATE A TRIGGER TO UPDATE AUTOMATICALLY THE DELETED ROWS INTO TARGET 

CREATE OR REPLACE TRIGGER UPDATE_DELETE
BEFORE DELETE ON EMP1
FOR EACH ROW
BEGIN
INSERT INTO TRIGGERED VALUES (:OLD.EMPNO, :OLD.ENAME,:OLD.JOB,:OLD.MGR,:OLD.HIREDATE,:OLD.SAL,:OLD.COMM,:OLD.DEPTNO,:OLD.DATEOFENTRY);
END;
/


CREATE A TRIGGER TO UPDATE AUTOMATICALLY THE INSERTED ROWS INTO TARGET 

CREATE OR REPLACE TRIGGER UPDATE_INSERT
AFTER INSERT ON EMP1
FOR EACH ROW
BEGIN
INSERT INTO TRIGGERED VALUES (:NEW.EMPNO, :NEW.ENAME,:NEW.JOB,:NEW.MGR,:NEW.HIREDATE,:NEW.SAL,:NEW.COMM,:NEW.DEPTNO,:NEW.DATEOFENTRY);
END;
/


CREATE A TRIGGER TO UPDATE AUTOMATICALLY THE DELETED ROWS INTO TARGET 

CREATE OR REPLACE TRIGGER UPDATE_DELETE
BEFORE DELETE ON CUSTOMERS
FOR EACH ROW
BEGIN
INSERT INTO OLDCUSTOMERS (ID,NAME,AGE,ADDRESS,SALARY)
VALUES (:OLD.ID, :OLD.NAME, :OLD.AGE, :OLD.ADDRESS, :OLD.SALARY);
END;
/


CREATE OR REPLACE TRIGGER SALARY_UPDATE_CHECK
BEFORE UPDATE OF SAL ON EMP
FOR EACH ROW
BEGIN
IF
:NEW.SAL < :OLD.SAL
THEN
RAISE_APPLICATION_ERROR (-20215,'UPDATED SALARY CANNOT BE LESSER THAN CURRENT SALARY');
END IF;
END;

/



STATEMENT LEVEL TRIGGER :-
---------------------------

CREATE TRIGGER STMT_LEVEL_TRIGGER
AFTER UPDATE ON HIGHSCHOOLER
BEGIN
UPDATE HIGHSCHOOLER
SET GRADE = NULL
WHERE GRADE = 13;
END;


CREATE OR REPLACE TRIGGER T1
AFTER INSERT ON STUDENT
BEGIN
UPDATE STUDENT
SET TOT       =   M1+M2+M3,
       PCT      = ( M1+M2+M3)/3,
       RESULT = CASE WHEN M1 >=35 AND M2 >=35 AND M3 >=35 THEN 'PASS' ELSE 'FAIL' END;
END;


CREATE OR REPLACE TRIGGER RESTRICTED_INSERT
 BEFORE INSERT ON EMP
 BEGIN
 IF
 (TO_CHAR(SYSDATE, 'HH24:MI') NOT BETWEEN '10:00' AND '18:00')
 THEN
 RAISE_APPLICATION_ERROR (-20123, 'YOU CAN ADD EMPLOYEE ONLY BETWEEN 10:00 AM AND 6:00 PM.');
 END IF;
 END;

 /


DDL TRIGGERS :-

STATEMENT LEVEL TRIGGER :-
---------------------------------

CREATE OR REPLACE TRIGGER RESTRICT_DROP_TABLE
BEFORE DROP ON DATABASE
BEGIN
RAISE_APPLICATION_ERROR (-20125, 'CANNOT DROP TABLE FROM THIS DATABASE');
END;
/


CREATE OR REPLACE TRIGGER LOGIN_TRIGGER
AFTER LOGON ON DATABASE
 BEGIN
 INSERT INTO LOGIN DETAILS VALUES (USER, SYSDATE, 'LOGIN');
 END;
 /


CREATE OR REPLACE TRIGGER LOGOFF_TRIGGER
BEFORE LOGOFF ON DATABASE
BEGIN
INSERT INTO LOGOFF_DETAILS VALUES (USER, SYSDATE, 'LOGOUT');
END;

/

Procedures in PL/SQL

If a program or query that is stored as a procedure permanently in the database is called "Stored Procedure". It is saved in database as Pre-compiled object, once its compiled it will not be compiled again and again like a program until the logic changes. Procedures are executed explicitly, to execute it we have to use execute command. 

A Procedure is a subprogram unit that consists of a group of PL/SQL statements. Each procedure in Oracle has its own unique name by which it can be referred. 
It contains declaration part (optional), execution part, exception handling part (optional).
The values can be passed into the procedure or fetched from the procedure through parameters.
Keyword 'IS' will be used, when the procedure is nested into some other blocks. If the procedure is standalone then 'AS' will be used. Other than this coding standard, both have the same meaning.


SYNTAX :-
----------
CREATE [OR REPLACE] PROCEDURE procedure_name 
[(parameter_name [IN | OUT | IN OUT] type [, ...])] 
{IS | AS} 
BEGIN 
  < procedure_body > 

END procedure_name; 


THERE ARE 3 TYPES OF PARAMETERS :-
-------------------------------------------
- IN parameter lets you pass a value to the subprogram
- OUT represents the parameter that will be used to return a value outside of       the procedure
- IN OUT parameter passes an initial value to a subprogram and returns an     updated value to the caller.


PROCEDURES ARE OF TWO TYPES :-
---------------------------------------
- Dynamic Procedure
- Static Procedure


STATIC PROCEDURE :-
-----------------
CREATE A PROCEDURE TO DISPLAY NUMBER OF EMPLOYEES FROM DEPTNO 10.

CREATE OR REPLACE PROCEDURE PRO_DEPT_10
IS
O_EMPCOUNT INT;
BEGIN
SELECT COUNT(*) INTO O_EMPCOUNT FROM EMP WHERE DEPTNO=10;
DBMS_OUTPUT.PUT_LINE('NO OF EMPLOYEES FROM DEPARTMENT NO 10 IS' || O_EMPCOUNT);
END PRO_DEPT_10;
/



CREATE A PROCEDURE TO DISPLAY NUMBER OF EMPLOYEES FROM DEPTNO 20

CREATE OR REPLACE PROCEDURE PROC_DEPT_20
IS
O_VARIABLE INT;
BEGIN
SELECT COUNT(*) INTO O_VARIABLE FROM EMP WHERE DEPTNO=20;
DBMS_OUTPUT.PUT_LINE('NO OF EMPLOYEES FROM DEPTNO 20 IS'|| O_VARIABLE);
END PROC_DEPT_20;
/


CREATE A PROCEDURE TO DISPLAY NUMBER OF EMPLOYEES FROM DEPTNO 30

CREATE OR REPLACE PROCEDURE PROC_DEPTNO_30
IS
O_VARIABLE INT;
BEGIN
SELECT COUNT(*) INTO O_VARIABLE FROM EMP WHERE DEPTNO = 30;
DBMS_OUTPUT.PUT_LINE ('NUMBER OF EMPLOYEES FROM DEPTNO 30 IS ' || O_VARIABLE);
END PROC_DEPTNO_30;
/


WRITE A PROCEDURE TO DISPLAY 'MY NAME IS PRASAN KUMAR '

CREATE PROCEDURE PROC_NAME
IS
BEGIN
DBMS_OUTPUT.PUT_LINE ('MY  NAME IS PRASAN KUMAR');
END PROC_NAME;
/

SET SERVEROUTPUT ON 

EXECUTE PROC_NAME



DYNAMIC PROCEDURE :-
---------------------
CREATE A PROCEDURE TO DISPLAY NUMBER OF EMPLOYEES FROM VARIABLE DEPTNO'S.

CREATE OR REPLACE PROCEDURE PROC_EMP_COUNT (V_DEPTNO INT)
IS
EMP_COUNT INT;
BEGIN
SELECT COUNT(*) INTO EMP_COUNT FROM EMP WHERE DEPTNO = V_DEPTNO;
DBMS_OUTPUT.PUT_LINE('NUMBER OF EMPLOYEES FROM V_DEPTNO IS ' || EMP_COUNT );
END PROC_EMP_COUNT;
/

SET SERVEROUTPUT ON
EXECUTE PROC_EMP_COUNT(10)

 
CREATE OR REPLACE PROCEDURE PROC_VARIABLE_DEPT (IN_VARIABLE INT)
IS
O_VARIABLE INT;
BEGIN
SELECT COUNT(*) INTO O_VARIABLE FROM EMP WHERE IN_VARIABLE=DEPTNO;
DBMS_OUTPUT.PUT_LINE('NO OF EMPLOYEES FROM VARIABLE DEPT IS' || O_VARIABLE);
END PROC_VARIABLE_DEPT;
/
SET SERVEROUTPUT ON
EXECUTE PROC_VARIABLE_DEPT(20)
 
 
CREATE OR REPLACE PROCEDURE PROC_VAR_JOB (IN_VARIABLE VARCHAR)
IS
OUT_VARIABLE INT;
BEGIN
SELECT COUNT(*) INTO OUT_VARIABLE FROM EMP WHERE JOB=IN_VARIABLE;
DBMS_OUTPUT.PUT_LINE('COUNT FROM VARIABLE JOBS IS' || OUT_VARIABLE );
END PROC_VAR_JOB;

/
SET SERVEROUTPUT ON
EXECUTE PROC_VAR_JOB (CLERK)

CREATE A PROCEDURE TO POPULATE TOTAL SALARY OF AN EMPLOYEE.

CREATE OR REPLACE PROCEDURE PROC_SAL_COMM (EMP_ID IN NUMBER, TOTAL OUT NUMBER)
IS 
BEGIN
SELECT SAL+(SAL*10/100) INTO TOTAL FROM EMP
WHERE EMP_ID = EMPNO;
END PROC_SAL_COMM;
/
SET SERVEROUTPUT ON
EXECUTE PROC_SALHIKE(7934)


CREATE A PROCEDURE TO GET MIN, MAX, AVG, SUM OF SALARIES OF EMPLOYEES.

CREATE OR REPLACE PROCEDURE PROC_SAL_CAL (
IN_DEPTNO IN NUMBER,
MAX_SAL OUT NUMBER,
MIN_SAL OUT NUMBER,
SUM_SAL OUT NUMBER,
AVG_SAL OUT NUMBER )
IS 
BEGIN
SELECT MAX(SAL), MIN(SAL), SUM(SAL), AVG(SAL) 
INTO MAX_SAL, MIN_SAL, SUM_SAL, AVG_SAL
FROM EMP
WHERE DEPTNO = IN_DEPTNO GROUP BY DEPTNO;
END PROC_SAL_CAL;
/

CREATE A PROCEDURE TO INSERT ROWS INTO THE DEPT TABLE.

CREATE OR REPLACE PROCEDURE ADD_DEPT (
P_DNO IN DEPT.DEPTNO%TYPE,
P_DNM IN DEPT.DNAME%TYPE,
P_LOC IN DEPT.LOC%TYPE
)
IS 
BEGIN
INSERT INTO DEPT VALUES (P_DNO, P_DNM, P_LOC);
DBMS_OUTPUT.PUT_LINE('DEPARTMENT ADDED');
END ADD_DEPT;
/

SET SERVEROUTPUT ON
EXECUTE ADD_DEPT (50,'BISCUIT','OUR LOCATION');


WRITE A PROCEDURE TO FIND MINIMUM VALUE.

CREATE OR REPLACE PROCEDURE FINDMIN 
( X IN NUMBER, Y IN NUMBER, Z OUT NUMBER )
IS
BEGIN
IF X < Y THEN
Z:= X;
ELSE
Z:=Y;
END IF;
END;
/
DECLARE
A NUMBER;
B NUMBER;
C NUMBER;
BEGIN
A:=23;
B:=45;
FINDMIN (A,B,C);
DBMS_OUTPUT.PUT_LINE ('MINIMUM OF (23,45):' || C);
END;

/



WRITE A PROCEDURE TO DISPLAY DEPARTMENT NAME FROM DEPT TABLE.

CREATE OR REPLACE PROCEDURE ADD_DEPT
(P_DNO IN DEPT.DEPTNO%TYPE,
 P_DN OUT DEPT.DNAME%TYPE)
IS
BEGIN
SELECT DNAME INTO P_DN
FROM DEPT
WHERE DEPTNO = P_DNO;
END;
/
DECLARE
P_DN VARCHAR2(20);
BEGIN
ADD_DEPT(30,P_DN);
DBMS_OUTPUT.PUT_LINE(P_DN);
END;

/

* Write a procedure to get min, max, avg, sum of salaries of employees.

Create procedure Proc_Get_Salaries

(

in_deptno in number,

min_sal out number, 

max_sal out number,

avg_sal out number 

sum_sal out number)

AS

begin

Select  min(sal), max(sal), avg(sal), sum(sal) into 

min_sal, max_sal, avg_sal, sum_sal from emp where deptno=in_deptno group by deptno;

End;

Introduction to PLSQL

PLSQL is the combination of SQL statements. To increase the capabilities of SQL PLSQL is introduced. Full form of PLSQL is 'Procedural Structured query language'. 

* PL/SQL programs are divided and written in logical blocks of code. Each block consists of three sub-parts

- Declarations

- Executable Commands

- Exception Handling

* Following is the basic structure of a PL/SQL block

DECLARE 

<declarations section> 

BEGIN 

<executable command(s)>

EXCEPTION 

<exception handling> 

END;

* The PL/SQL single-line comments start with the delimiter -- (double hyphen) and multi-line comments are enclosed by /* and */.

 Example : 

    DECLARE 

   -- variable declaration 

   message  varchar2(20):= 'Hello, World!'; 

   BEGIN 

   /* 

   *  PL/SQL executable statement(s) 

   */ 

   dbms_output.put_line(message); 

   END; 

   /  

* A PL/SQL unit is any one of the following −

 PL/SQL block

 Function

 Package

 Package body

 Procedure

 Trigger

 Type

 Type body