Infolinks

Showing posts with label ORACLE. Show all posts
Showing posts with label ORACLE. Show all posts

Sunday, 15 July 2012

Oracle Query Tuning

If query taking long time then First will run the query in Explain Plan, The explain plan process stores data in the PLAN_TABLE.
it will give us execution plan of the query like whether the query is using the relevant indexes on the joining columns or indexes to support the query are missing.
If joining columns doesn’t have index then it will do the full table scan if it is full table scan the cost will be more then will create the indexes on the joining columns and will run the query it should give better performance and also needs to analyze the tables if analyzation happened long back. The ANALYZE statement can be used to gather statistics for a specific table, index or cluster using
ANALYZE TABLE employees COMPUTE STATISTICS;
If still have performance issue then will use HINTS, hint is nothing but a clue. We can use hints like
  • ALL_ROWS
    One of the hints that 'invokes' the Cost based optimizer
    ALL_ROWS is usually used for batch processing or data warehousing systems.
(/*+ ALL_ROWS */)
  • FIRST_ROWS
    One of the hints that 'invokes' the Cost based optimizer
    FIRST_ROWS is usually used for OLTP systems.
(/*+ FIRST_ROWS */)
  • CHOOSE One of the hints that 'invokes' the Cost based optimizer
    This hint lets the server choose (between ALL_ROWS and FIRST_ROWS, based on statistics gathered.
  • HASH Hashes one table (full scan) and creates a hash index for that table. Then hashes other table and uses hash index to find corresponding records. Therefore not suitable for < or > join conditions.
/*+ use_hash */
Hints are most useful to optimize the query performance.

SQL*LOADER

SQL*Loader (sqlldr ) is the utility to use for high performance data loads. The data can be loaded from any text file and inserted into the database.
During processing, SQL*Loader writes messages to the log file, bad rows to the bad file, and discarded rows to the discard file.
The Control File
The SQL*Loader control file contains information that describes how the data will be loaded. It contains the table name, column data types, field delimiters, etc. It simply provides the guts for all SQL*Loader processing.
SQL*Loader Options
SQL*Loader provides the following options, which can be specified either on the command line or within a parameter file:
  1.  bad – A file that is created when at least one record from the input file is rejected. The rejected data records are placed in this file. A record could be rejected for many reasons, including a non-unique key or a required column being null.
  2.  bindsize – [256000] The size of the bind array in bytes.
  3.  columnarrayrows – [5000] Specifies the number of rows to allocate for direct path column arrays.
  4.  control – The name of the control file. This file specifies the format of the data to be loaded.
  5. data – The name of the file that contains the data to load.
  6.  direct – [FALSE] Specifies whether or not to use a direct path load or conventional.
  7. discard – The name of the file that contains the discarded rows. Discarded rows are those that fail the WHEN clause condition when selectively loading records.
  8.   discardmax – [ALL] The maximum number of discards to allow.
  9. errors – [50] The number of errors to allow on the load.
  10. external_table – [NOT_USED] Determines whether or not any data will be loaded using external tables. The other valid options include GENERATE_ONLY and EXECUTE.
  11. file – Used only with parallel loads, this parameter specifies the file to allocate extents from.
  12. load – [ALL] The number of logical records to load.
  13.  log – The name of the file used by SQL*Loader to log results.
  14.  multithreading – The default is TRUE on multiple CPU systems and FALSE on single CPU systems.
  15.  parfile – [Y] The name of the file that contains the parameter options for SQL*Loader.
  16.  parallel – [FALSE] Specifies a filename that contains index creation statements.
  17.  readsize – The size of the buffer used by SQL*Loader when reading data from the input file. This value should match that of bindsize.
  18.  resumable – [N] Enables and disables resumable space allocation. When “Y”, the parameters resumable_name and resumable_timeout are utilized.
  19.  resumable_name – User defined string that helps identify a resumable statement that has been suspended. This parameter is ignored unless resumable = Y.
  20.  resumable_timeout – [7200 seconds] The time period in which an error must be fixed. This parameter is ignored unless resumable = Y.
  21.  rows – [64] The number of rows to load before a commit is issued (conventional path only). For direct path loads, rows are the number of rows to read from the data file before saving the data in the datafiles.
  22.  silent – Suppress errors during data load. A value of ALL will suppress all load messages. Other options include DISCARDS, ERRORS, FEEDBACK, HEADER, and PARTITIONS.
  23.  skip – [0] Allows the skipping of the specified number of logical records.
  24.  skip_unusable_indexes – [FALSE] Determines whether SQL*Loader skips the building of indexes that are in an unusable state.
  25.  skip_index_maintenance – [FALSE] Stops index maintenance for direct path loads only.
  26.  streamsize – [256000] Specifies the size of direct path streams in bytes.
  27.  userid – The Oracle username and password.

  1. To drive the concept of the SQL loader home.., let us assume that we have text file which is the usual emp table.
  2. Now open the cmd prompt and type sqlldr.
  3. later the things should look some thing like this.
sqlldr# userid=username/pwd@ora
load data
in file ‘C:\emp.txt’
into table emp_oracle
fields terminated by “,”optionally enclosed by’ ” ’
(empno,ename,job,mgr,sal,comm,deptno)

Explain Plan,Plan Table

Explain Plan : It is a statement that allows you to have oracle generate execution plan for any sql statement with out actually executing it.You will be able to examine the execution plan by querying the plan table.
Plan Table : A Plan table holds execution plans generated by the explain plan statements.Create Plan table by  running utlxplan.sql located in
$oracle_home/rdbms/admin.
Explain Plan Syntax :
Explain plan [set statement_id=<string in single quotes>]
[into <plan table name>]
for
<sql statements>;
Example to Explain Plan:
Sql> Explain plan set statement_id=’demo’ for
select a.customer_name,a.customer_number,b.invoice_number,
b.invoice_type,b.invoice_data,b.total_amount,
c.line_number,c.part_number,c.quantity,c.unit_cost
from customers a, invoices b, invoice_items c
where c.invoice_id=:b1
and c.line_number=:b2
and b.invoice_id=c.invoice_id
and a.customer_id=b.customer_id

Sql> @ explain.sql
Enter statement_id : Demo

materialized view

A materialized view is a database object that contains the results of a query. They are local copies of data located remotely, or are used to create summary tables based on aggregations of a table's data.Materialized view and the Query rewrite feature is added from ORACLE 8i.
A materialized view log is a schema object that records changes to a master table's data so that a materialized view defined on the master table can be refreshed incrementally.
  • If you delete any record from your Materialized view it is goanna impact your Source table once it is refreshed.
Examples to the Simple Materialized view’s is given below .
Eg 1 :Create materialized_view MV refresh as select * from emp;
Execute dbms_mview.refresh(‘MV’);
Refresh Complete : To perform a complete refresh of a materialized view, the server that manages the materialized view executes the materialized view's defining query, which essentially recreates the materialized view. To refresh the materialized view, the result set of the query replaces the existing materialized view data. Oracle can perform a complete refresh for any materialized view. Depending on the amount of data that satisfies the defining query, a complete refresh can take a substantially longer amount of time to perform than a fast refresh.
Create Materialized_view MV Refresh complete as select * from emp;
execute DBMS_mview.refresh(List=>’MV’,Method=>’c’);
Refresh Fast :To perform a fast refresh, the master that manages the materialized view first identifies the changes that occurred in the master since the most recent refresh of the materialized view and then applies these changes to the materialized view. Fast refreshes are more efficient than complete refreshes when there are few changes to the master because the participating server and network replicate a smaller amount of data.
Create Materialized_view MV Refresh fast as select * from emp;
execute DBMS_mview.refresh(list=>’MV’,Method=>’F’);
Primary Key Materialized Views :
The following statement creates the primary-key materialized view on the table emp located on a remote database.
SQL> 	CREATE MATERIALIZED VIEW mv_emp_pk
   	REFRESH FAST START WITH SYSDATE 
	NEXT  SYSDATE + 1/48
	WITH PRIMARY KEY 
	AS SELECT * FROM emp@remote_db;


Note: When you create a materialized view using the FAST option you will need to create a view log on the master tables(s) as shown below:


SQL> CREATE MATERIALIZED VIEW LOG ON emp;Materialized view log created.





Rowid Materialized Views :


The following statement creates the rowid materialized view on table emp located on a remote database:


SQL>CREATE MATERIALIZED VIEW mv_emp_rowid REFRESH WITH ROWID    AS SELECT * FROM emp@remote_db;


Materialized view log created.





Creating Materialized Aggregate Views :


CREATE MATERIALIZED VIEW sales_mv BUILD IMMEDIATE REFRESH FAST ON COMMIT AS SELECT t.calendar_year, p.prod_id, SUM(s.amount_sold) AS sum_sales FROM times t, products p, sales s WHERE t.time_id = s.time_id AND p.prod_id = s.prod_id GROUP BY t.calendar_year, p.prod_id;


Creating Materialized Join Views :


CREATE MATERIALIZED VIEW sales_by_month_by_state
     TABLESPACE example
     PARALLEL 4
     BUILD IMMEDIATE
     REFRESH COMPLETE
     ENABLE QUERY REWRITE
     AS SELECT t.calendar_month_desc, c.cust_state_province,
        SUM(s.amount_sold) AS sum_sales
        FROM times t, sales s, customers c
        WHERE s.time_id = t.time_id AND s.cust_id = c.cust_id
        GROUP BY t.calendar_month_desc, c.cust_state_province;





Periodic Refresh of Materialized Views:


CREATE MATERIALIZED VIEW emp_data 
   PCTFREE 5 PCTUSED 60 
   TABLESPACE example 
   STORAGE (INITIAL 50K NEXT 50K)
   REFRESH FAST NEXT sysdate + 7 
   AS SELECT * FROM employees; 





Automatic Refresh Times for Materialized Views


CREATE MATERIALIZED VIEW all_customers
   PCTFREE 5 PCTUSED 60 
   TABLESPACE example 
   STORAGE (INITIAL 50K NEXT 50K) 
   USING INDEX STORAGE (INITIAL 25K NEXT 25K)
   REFRESH START WITH ROUND(SYSDATE + 1) + 11/24 
   NEXT NEXT_DAY(TRUNC(SYSDATE), 'MONDAY') + 15/24 
   AS SELECT * FROM sh.customers@remote 
         UNION
      SELECT * FROM sh.customers@local;

EXCEPTION HANDLING

Types of Exceptions
1. Pre Defined Exceptions
2. User Defined Exceptions
Pre Defined Exceptions
These exceptions are used to handle some logical errors known to the system are pre defined. The following are some of the important pre defined Exceptions
  1. no_data_found
This Exception raises when there is no rows to be retrieved from a table
according to given condition
  1. dup_val_on_index
This Exception raises when ever you try to store duplicate values into a
table, which has been indexed (unique indexed)
  1. cur_already_open
This Exception raises when ever your program attempts to open an already
opened cursor.
A cursor must be closed before it can be re opened. A cursor for loop
automatically opens the cursor to which it refers. So Your program can not open that cursor inside the loop
  1. invalid_cursor
This Exception raises when ever your program attempts an illegal cursor operation, such as closing an un opened cursor
  1. zero_divide
This Exception raises when ever your program attempts to divide a number
by zero
  1. program_error
This Exception raises when ever PL/SQL has internal problem
  1. storage_error
This Exception raises when ever PL/SQL runs out of memory
  1. too_many_rows
This Exception raises when ever a select statement returns more than one
row
  1. login_denied
This Exception raises when ever your program attempts to logon to oracle
with an invalid user name and/or password
  1. value_error
This Exception raises when ever an arithmetic conversion or size constraint error occurs
For ex, when your program selects a column value into a variable. If the value is longer than the declared length of the variable, pl/sql abords the assignment and raise the exception value_error
Programs On Exceptions
1. Write a program for handling an error when we try to open an already opened cursor
declare
cursor c1 is select * from emp;
x emp%rowtype;
begin
open c1;
fetch c1 into x;
dbms_output.put_line(x.empno||' '||x.ename);
--open c1;
close c1;
exception
when cursor_already_open then
dbms_output.put_line(' sorry friend, cursor already opened');
end;
/
2. Write a program to display a message when no data found in the main table
declare
e emp%rowtype;
begin
select * into e from emp where empno=&empno;
dbms_output.put_line('Empno Ename job');
dbms_output.put_line(e.empno||' '||e.ename||' '||e.job);
exception
when No_data_found then
dbms_output.put_line(' sorry friend, no data found');
end;
/
3. Write a program for handling an error when we insert a duplicate value into a column.
declare
n sample%rowtype;
begin
insert into sample values(&sno,'&sname','&class');
dbms_output.put_line('Record Inserted....');
exception
when dup_val_on_index then
dbms_output.put_line(' sorry friend, you entered a duplicate value');
end;
/
Note : create a table with unique constraint, for dup_val_on_index,
 



  • create table sample(sno number(3),sname varchar2(10));


  • CREATE UNIQUE INDEX IDX_SAM ON SAMPLE(SNO);


4. Write a program for handling an error when we try to close an un opened cursor


declare


cursor c1 is select * from emp;


begin


--open c1;


dbms_output.put_line(' cursor is Opened');


close c1;


exception


when invalid_cursor then


dbms_output.put_line(' sorry friend, No such cursor is opened');


end;


/


Note : if u remove comment for open c1 then output is cursor is opened, other wise the you will get “sorry friend, no such cursor is opened ..”, since you r trying to close an unopened cursor


5. Write a program for handling an error when we try to divide a value with zero


declare


a number:=&a;


b number:=&b;


c number;


begin


c:=a/b;


dbms_output.put_line(c);


exception


when zero_divide then


dbms_output.put_line('Zero Divide error');


end;


/


6. Write a program for handling an error when ever the program retrieves too many rows


declare


e emp%rowtype;


begin


select * into e from emp;


exception


when too_many_rows then


dbms_output.put_line(' sorry friend, too many rows are selected');


end;


/


User Defined Exceptions


A user can define or create exceptions, which must be raised automatically, because the system does not knows when to execute the user defined errors. There are 3 steps while working with user defined exceptions


1. Creating Exception


2. Raising Exception


3. Handling Exception


The main purpose of user defined exceptions is, we can display a message in predefined error format in the instances not known to the system or that are not predefined


Syntax :


Declare


<Exception Name> Exception;


begin


Raise Exception;


Exception


When <Exception name> then


Message


End;


/


Raise_Application_Error




This is a keyword, which is used to generate an error message in pre defined error format for the given user defined error


Syntax :


Raise_Application_Error(Error number, ‘ message ‘);




Example :


Raise_Application_Error(-20173, ‘ Some Error ‘);


Note : Error Number for (user defined Exceptions) must be in between –20001 to -20999




1. Write a program for creating an exception and raising it when the basic is <3000, while inserting rows into a table and also handle it to perform any other action


Declare


invalid_salary exception;


s employee.sal%type:=&sal;


begin


if s<3000 then


raise invalid_salary;


else


insert into employee(sal) values (s);


dbms_output.put_line(' Record Inserted.....');


end if;


exception


when invalid_salary then


dbms_output.put_line(' salary must be >3000 ');


end;


/


2. Write a program for creating an exception and raising it when ever you try to insert any negative number into a table


Declare


invalid_number exception;


e employee.empno%type:=&empno;


begin


if e<0 then


raise invalid_number;


else


insert into employee(empno) values (e);


dbms_output.put_line(' Record Inserted.....');


end if;


exception


when invalid_number then


dbms_output.put_line(' Number must be positive ');


end;


/

FUNCTIONS

A function is a sub program that executes a set of statements and returns a value to the main program. The basic difference between Functions and procedures is, function returns a value to the calling program or main program where as a procedure does not return any value
Syntax of A Function
Create [ Or Replace ] Function < Function Name > ( Argument1 [Mode] <Data type>, Argument2 [Mode] <data type>,---) return data type
Is / As
Local Variables Declarations;
------------
Begin
Executable Statements;
---------------
[Exception
Exception Handling; ]
End;
/
Example : 1
Write a program to find sum of two numbers
Sol :
ed function1
Create or replace function addition(x integer, y integer) return integer
is
Begin
Return x+y;
End;
/
mainfunction1
Declare
A integer :=&a;
B integer :=&b;
C integer;
Begin
C:=addition(a,b);
Dbms_output.put_line('sum of two numbers is '|| c );
End;
/
Example : 2
wirte a function which accepts item number and quantity and then return the total amount by fetching rate from the item table
create or replace function itembill(q in number,r in number) return number
is
begin
return q*r;
end;
/
ed mainfunction2
declare
q1 number:=&quantity;
r1 number;
res number;
begin
select rate into r1 from item where itno=&itno;
res:=itembill(q1,r1);
dbms_output.put_line(' Total Amount is '||res);
exception
when no_data_found then
dbms_output.put_line(' No Such Item Exits');
end;
/

PROCEDURES

A procedure is a set of instructions(usually combining sql and plsql commands) saved for calling and repeated execution
A procedure is a sub program that performs a specific action. A procedure can be called from any pl/sql block. A procedure has two parts one is Specification and Other is Body
The procedure specification begins with the keyword procedure followed by procedure name and an option list of arguments enclosed with in the parenthesis.
The procedure body begins with is or as and ends with an end statement.
It consists of three parts
1. Declarative Part
2. Execution Part
3. Exception Handling Part

Syntax of Procedure


Create [ Or Replace ] Procedure < Procedure Name > ( Argument1 [Mode] <Data type>, Argument2 [Mode] <data type>,---)
Is / As
Local Variables Declarations;
------------
Begin
Executable Statements;
---------------
[Exception
Exception Handling; ]
End;
/
where Mode refers to the type of arguments such as In , Out or InOut
Modes In Procedures And Functions
  1. IN : the In parameter lets the user to pass values to the called sub programs with in the sub program, the IN parameter acts like a constant. There fore it cannot be modified.
  1. OUT : The Out parameter lets the user returned values to the calling block. Inside the sub program the out parameter acts like a un initialized variable. Its value can not be assigned to another variable or itself
  1. INOUT : the INOUT parameter lets the user pass initial values to the called sub program and return updated values to the calling block
Note : the default mode of an argument is "IN"
Show Errors :
This Command is used to show the errors that are occurred during the procedure creation
Ex : when ever we create a procedure and executed, if it show procedure created with compilation errors, the we can see those errors using the following statement
show Errors
Example :1
Write a procedure to show a simple message
Sol. Steps
  1. Write the sub Program
Ed subprocedure1
Create or replace procedure disp is
Begin
Dbms_output.put_line('This is a Sub Program');
End;
/
  1. Next, Compile the above procedure to find errors in the procedure, with the following statement
@ subprocedure1
  1. Next, Write the main program
Begin
Dbms_output.put_line('This is Main Program');
Disp;
Dbms_output.put_line('Again Continuing the Main Program');
End;
/
  1. Next, Run the above main procedure with the following statement
@mainprogram1
output :
SQL> @ mainprogram1
This is Main Program
This is a Sub Program
Again Continuing the Main Program
PL/SQL procedure successfully completed.
Example :2
Write a procedure to show a message when updated any row in a particular table
Sol. Steps
  1. Write the sub Program
Ed subprocedure2
Create or replace procedure upd(d number)
is
Begin
update employee set sal=sal+(sal*0.2) where empno=d;
dbms_output.put_line(sql%rowcount||' Record Updated...');
End;
/
  1. Next, Compile the above procedure to find errors in the procedure, with the following statement
@ subprocedure2
  1. Next, Write the main program
Begin
upd(&empno);
End;
/
  1. Next, Run the above main procedure with the following statement
@mainprogram2
output :
SQL> @ mainprogram2
Enter value for empno: 7369
old 2: upd(&empno);
new 2: upd(7369);
1 Record Updated...
PL/SQL procedure successfully completed.
Example 3:
Create a procedure which adds the given three numbers using “in” and “out” parameters
Sol. Steps
  1. Write the sub Program
Ed subprocedure3
Create or replace procedure sum_numbers(n1 in number, n2 in
number, res out number)
is
Begin
res:=n1+n2;
End;
/
  1. Next, Compile the above procedure to find errors in the procedure, with the following statement
@ subprocedure3
  1. Next, Write the main program
Declare
a number;
b number;
c number;
Begin
a:=&a;
b:=&b;
sum_numbers(a,b,c);
dbms_output.put_line('sum of three numbers is '||c);
End;
/
  1. Next, Run the above main procedure with the following statement
@mainprogram3
output :
SQL> @ mainprogram3
Enter value for a: 10
old 6: a:=&a;
new 6: a:=10;
Enter value for b: 20
old 7: b:=&b;
new 7: b:=20;
sum of three numbers is30
PL/SQL procedure successfully completed.
Example 4:
Create a procedure which Updates the sal with the increment value that you give according to the given emp number
Sol. Steps
  1. Write the sub Program
Ed subprocedure4
Create or replace procedure incr(eno employee.empno%type,s
out number, i number)
Is
Begin
Update employee set sal=sal+i where empno=eno;
Dbms_output.put_line('Record Updated----');
End;
/
  1. Next, Compile the above procedure to find errors in the procedure, with the following statement
@ subprocedure4
  1. Next, Write the main program
Declare
e number:=&empno;
Incr_value number:=&increment;
s employee.sal%type;
Begin
Select sal into s from employee where empno=e;
Incr(e,s,incr_value);
End;
/
  1. Next, Run the above main procedure with the following statement
@mainprogram4
output :
SQL> @ mainprogram4
Enter value for empno: 7369
old 2: e number:=&empno;
new 2: e number:=7369;
Enter value for increment: 48
old 3: incr_value number:=&increment;
new 3: incr_value number:=48;
Record Updated----
PL/SQL procedure successfully completed.

TRIGGER

Trigger is a database object ie used to execute an action basing on an even. Triggers are event-based programs, which are executed when an event occurs or raises or fires
Types of Triggers
Trigger Type is defined by the type of triggering transaction and by the level at which the trigger is executed
Triggers Are Of Two Types
1. Row Level Triggers
2. Statement Level Triggers
Row Level Triggers
A row trigger is fired each time a row in the table is affected by the triggering statement. For example, if an UPDATE statement updates multiple rows of a table, a row trigger is fired once for each row affected by the update statement. If the triggering statement affects no rows, the trigger is not executed at all. Row triggers should be used when some processing is required whenever a triggering statement affects a single row in a table.
Row level triggers are created using the "For Each Row" Clause in the Create Trigger Command
Statement triggers
A statement trigger is fried once on behalf of the triggering statement, independent of the number of rows the triggering statement affects (even if not rows are affected) statement triggers should be used when a triggering statement affects rows in a table but the processing required is completely independent of the number of rows affected
Statement level triggers are the default type of trigger created via Create Trigger Command
Syntax:
create or replace trigger <trigger_name>
{before/after/instead of}
{insert/update/delete}
[ of <column name> on <table name> ]
[ for each row [when <condition>] ]
Declare
variables declarations
-----------------
begin
Executable statements
-------------
Exception
Exception statements
---------------
end;
/
Syntax Explanation :
or replace : Recreates the trigger if it already exists. this option can be used to change the definition of an existing trigger without requiring the user to drop the trigger first
Trigger Name : is the name of the trigger to be created
Before : Indicates that oracle fires the trigger before executing the trigger statement
After : Indicates that oracle fires the trigger After executing the trigger statement
Insert : Indicates that oracle fires the trigger whenever an INSERT statement adds a row to a table.
Delete : Indicates that oracle fires the trigger whenever a DELETE statement removes a row from the table.
Update : Indicates that oracle fires the trigger whenever an UPDATE statement changes a value in one of the columns specified in the OF clause. if the OF clause is omitted, the oracle fires the trigger whenever an UPDATE statement changes a value in any column of the table.
for Each Row : Designates the trigger to be a row trigger. the oracle engine fires a row trigger once for each row that is affected by the triggering statement and meets the optional trigger constraint defined in the When clause. if this clause is omitted the trigger is a statement trigger.
When : specifies the trigger restriction. the trigger restriction contains a SQL condition that must be satisfied for the oracle to fire the trigger.
Basing on the above 2 types of triggers, they are further classified into 3 types
1. DML Triggers
2. DDL Triggers and
3. Instead of Triggers
1. DML Triggers
These triggers are executed before or after. we apply any dml operations on a table.
When we create a table. the trigger definition is stored in the database, which is identified with the trigger name. the code in the trigger is processed when we apply any command on the database or table
Examples :
Steps for Creating a Trigger
1. First Create a trigger, next set the server on with the following statement (set Serveroutput on)
2. Run that Trigger with the following statement
@ <trigger name>
3. perform some action (ie either insert or update or delete etc)
Statement Level Triggers
1. Create A Trigger, Which Displays A Message When Ever You Insert A New Row In To Sample1 Table
Create or replace trigger instrig1 before insert on sample1
Begin
dbms_output.put_line('one record inserted successfully.....');
End;
/
2. Create A Trigger, Which Displays A Message When Ever You Update An Existing Row In The Table Sample1
Create or replace trigger updtrig1 before update on sample1
Begin
dbms_output.put_line('one record updated successfully.....');
End;
/
  1. Create A Trigger, Which Displays A Message When Ever You Delete A Row From The Table Sample1
Create or replace trigger deltrig1 before delete on sample1
Begin
dbms_output.put_line('record(s) deleted successfully.....');
End;
/
Row Level Triggers
1. Create A Trigger, Which Displays A Message When Ever You Insert A New Row Into A Table Sample1
Create or replace trigger instrig2 before insert on sample1
for each row
Begin
dbms_output.put_line(:new.sno||' record inserted successfully.....');
End;
/
  1. Create a trigger, which displays a message when ever you update a row in the table sample1
Create or replace trigger updtrig2 before update on sample1 for each row
Begin
dbms_output.put_line(:old.sno||' record updated to '||:new.sno);
End;
/
  1. Create A Trigger, Which Displays A Message When Ever You Delete A Row From The Table Sample1
Create or replace trigger deltrig2 after delete on sample1 for each row
Begin
dbms_output.put_line(:old.sno||' record deleted successfully.....');
End;
/
DDL TRIGGERS
  1. Create A Trigger, Which Displays An Error Message When Ever You Create a New Table which starts with Letter ‘A’
Create or replace trigger ctrig1 before create on scott.schema
Begin
if dictionary_obj_name like 'a%' then
raise_application_error(-20001,'object name can not start with a');
End if;
End;
/
  1. Create A Trigger, Which Displays An Error Message When Ever You try to drop any Table
Create or replace trigger prevent_drop after drop on scott.schema
Begin
if ora_dict_obj_type='table' then
raise_application_error(-20001,'object can not be dropped');
End if;
End;
/
  1. Create A Trigger, Which Displays An Error Message When Ever You try to Alter any Table
create or replace trigger prevent_alter before alter on scott.schema
begin
if ora_dict_obj_type='TABLE' then
Raise_Application_Error(-20001,'Object Can not be altered');
end if;
end;
/
INSTEAD OF TRIGGER
Create a trigger, which inserts the given values in to the relevant table through a Composite view
Create or replace trigger instrig instead of
Insert on empdept for each row
Begin
if :new.deptno is not null and :new.dname is not null then
insert into department(deptno,dname) values (:new.deptno,:new.dname);
end if;
if :new.empno is not null and :new.ename is not null then
insert into employee(empno,ename) values (:new.empno,:new.ename);
end if;
End;
/

CURSORS

Cursor is a location in the memory where the SQL statement is processed. That is when ever we send an SQL statement; the system allocates some memory by giving a name. After processing the statement it automatically cleans that memory. There are four steps, which are processed by the system whenever we send an SQL statement they are

1.Creating cursor : This step allocates memory 2.Opening cursor :
In this step process the sql statement
3.Fetching cursor:
The values satisfied by the sql statement are fetched from the
table into cursor row by row
4.Closing cursor : This statement close the memory allocated for the cursor
Types Of Cursors
There Are Two Types Of Cursors
1. Implicit cursors or simple cursors
2. Explicit Cursors
Implicit Cursors
Oracle implicitly opens a cursor to process each sql statement not associated with explicitly declared cursor. Pl/sql lets you refer to the most recent implicit cursors as the sql cursor.
Implicit cursors have four attributes, you can use in your program
  1. sql%rowcount
It returns the number of rows affected by an insert, update or delete
  1. sql%found
This attributes evaluates to true, if an insert or update or delete
affected to one or more rows. It evaluates to false if no row is affected
  1. sql%notfound
This attributes is logical opposite to sql%found. It evaluates to true,
if an insert or update or delete does not affected to any row. It evaluates to
false if any one row is affected
  1. %isopen
It checks whether the cursor has been opened or not
Example : 1
write a program to check whether there is atleast one row satisfying the given select statement or not using implicit cursors
Declare
e emp%rowtype;
Begin
select * into e from emp where empno=&empno;
if sql%found then
dbms_output.put_line('~~~~~~~~~~~~~~~~~~~~');
dbms_output.put_line(' Record Found ');
dbms_output.put_line('~~~~~~~~~~~~~~~~~~~~');
End if;
End;
/
Example : 2
write a program to change the ename column values to upper case
using implicit cursors
begin
update employee set ename=upper(ename);
if sql%found then
dbms_output.put_line('~~~~~~~~~~~~~~~~~~~~');
dbms_output.put_line(sql%rowcount || ' Records Updated ');
dbms_output.put_line('~~~~~~~~~~~~~~~~~~~~');
else
dbms_output.put_line('~~~~~~~~~~~~~~~~~~~~');
dbms_output.put_line(' Sorry Changes Are not Made ');
end if;
end;
/
Example : 3
write a program to update the salary of those employees to 20% whose salary is greater than the given salary, using implicit cursors
begin
update employee set sal=sal+(sal*20)/100 where sal>&sal;
if sql%found then
dbms_output.put_line('~~~~~~~~~~~~~~~~~~~~');
dbms_output.put_line(sql%rowcount || ' Records Updated ');
dbms_output.put_line('~~~~~~~~~~~~~~~~~~~~');
end if;
if sql%notfound then
dbms_output.put_line('~~~~~~~~~~~~~~~~~~~~');
dbms_output.put_line(' Sorry No Record is Satisfied ');
end if;
end;
/
Explicit Cursors
The user creates these cursors for applying 2 or more DML operations on a table. using a cursor we can fetch all the row values from the table into a variable for printing them or performing some action with them
In an explicit cursor, there are four steps related to execute cursors by the user. It is also used to compare a variable value with all values of the column.
The four steps are
1. Declaring a Cursor
2. Opening a Cursor
3. Fetching a Cursor
4. Closing a Cursor
Declaring a Cursor
A cursor is defined in the declarative part of the PL/SQL block by naming it and associating it with a query
Syntax :
Declare
Cursor <cursor_name> is <select Statement>
Opening a Cursor
The cursor must be initialized or opened with the open statement. Opening the cursor excutes the query and identifies the result set which consists of all the rows that meets the query search criteria
Syntax :
Declare
Cursor <cursor_name> is <select Statement>
Begin
Open <cursor Name>
-----------
End;
Fetching With a Cursor
This statement is used to retrieve the cursors current row. Fetching can be excuted repeatedly until all rows have been retrieved
Syntax :
Declare
Cursor <cursor_name> is <select Statement>
Begin
Open <cursor Name>
Fetch <cursor Name> into <variable1>,<variable2>,----
-----------
End;
Closing a Cursor
When the last row has been processed, the cursor is closed with the close statement. The close statement disable the cursor

Syntax :
Declare
Cursor <cursor_name> is <select Statement>
Begin
Open <cursor Name>
Fetch <cursor Name> into <variable1>,<variable2>,----
-----------
Close <cursor Name>
End;
Examples
1. Write a program using an explicit cursor, which displays all the rows from the table
Declare
Cursor c1 is select * from emp;
e emp%rowtype;
Begin
Open c1;
Loop
Fetch c1 into e;
Dbms_output.put_line(e.empno||' '||e.ename||' '||e.sal);
Exit when c1%notfound;
End loop;
Close c1;
End;
/
2.Write a program using an explicit cursor, to insert values into a table empc from emp table, whose experience is >=23 years
Declare
cursor c2 is select * from emp where (sysdate-hiredate)/365>=23;
e emp%rowtype;
x number:=0;
begin
open c2;
loop
fetch c2 into e;
exit when c2%notfound;
insert into empc values(e.empno,e.ename,e.job,e.sal,e.hiredate);
x:=x+sql%rowcount;
end loop;
dbms_output.put_line(x||' Records inserted Successfully....');
close c2;
end;
/
3.Write a program using an explicit cursor, to display first 5 records
Declare
cursor c3 is select * from emp;
e emp%rowtype;
x number;
begin
open c3;
loop
fetch c3 into e;
exit when c3%rowcount>5;
dbms_output.put_line(e.empno||' '||e.ename||' '||e.sal);
end loop;
close c3;
end;
/
Cursor For Loop
A cursor for loop implicitly declares its loop index as a %rowtype, opens a cursor, repeatedly fetches the rows of values from the result set into fields in the records and closes the cursor when all rows have been processed or exit command is encountered
Syntax :
Declare
Cursor <cursor_name> is <select statement>
Begin
For <index> in <cursor name> loop
---------
end loop;
end;
/
Example : 1
Write a program to display all employee’s details working in the accepted department number using cursor for loop
declare
cursor c1 is select * from emp where deptno=&deptno;
e c1%rowtype;
begin
dbms_output.put_line('****************');
for e in c1 loop
dbms_output.put_line(e.empno||' '||e.ename||' '||e.job||' '||e.sal);
end loop;
dbms_output.put_line('****************');
end;
/
Example : 2
Write a program to display all employee’s details working in the accepted department number and also display all department details for the given department number using cursor for loops
declare
cursor c1 is select * from emp where deptno=&deptno;
e c1%rowtype;
cursor c2 is select * from dept where deptno=&deptno;
d c2%rowtype;
begin
dbms_output.put_line(' ****Employees Details ***** ');
for e in c1 loop
dbms_output.put_line(e.empno||' '|| e.ename||' '||e.job||' '||e.sal);
end loop;
dbms_output.put_line(' ****Department Details ***** ');
for d in c2 loop
dbms_output.put_line(d.dname||' '||d.loc
||' '||d.deptno);
end loop;
end;
/

Partitions

Partitions

Partition is a data base object, it allows VLDB objects (Very Large Databases such as tables, indexes etc). By splitting large database objects into smaller, more manageable units. You can divide the rows of a single table into different parts called as partitioning a table
Advantages of Using Partitions
1.Reducing data base Retrieve time
By splitting the large data base objects into smaller, more manageable partitions, allows you to perform any type of action on individual units of the database
2.Backup and Recovery
Partitioning improves performance of Backup and Recovery operations. Smaller size of partitions offers more options for backing up and recovering from the partitions
3.Query Performance
Partitions can be created on ranges of certain columns. This focus reduce the search time, as a queries require a full table scan to search for the given criteria. This can be resolved by using partitions on a table
4.Partition Transparency
Partition implementation is transparent to the end users and developers. They do not need to be aware of the physical implementation of the database objects
Syntax :
Create table <table name> (columns specifications,-------)
Partition by Range (column name)
(partition <partition name> values less than (some value),
partition <partition name> values less than (some value),
----------------------------
partition <partition name> values less than (max value));
Example :
create table partitiondemo(sno number(3),sname varchar2(10))
partition by range (sno)
(partition p1 values less than(200),
partition p2 values less than(300),
partition p3 values less than(600),
partition p4 values less than(999))
1. select * from partitiondemo;
SNO SNAME
--------- ----------
100 Nithya
105 Saloni
109 Namitha
199 Aruna
204 Priya
250 Mahesh
306 Nandhini
450 Pooja
560 Uma
328 Janshi
900 Rani
904 Kavya
2. select * from partitiondemo partition(p1);
SNO SNAME
--------- ----------
100 Nithya
105 Saloni
109 Namitha
199 Aruna
3. select * from partitiondemo partition(p2);
SNO SNAME
--------- ----------
204 Priya
250 Mahesh
4. select * from partitiondemo partition(p3);
SNO SNAME
--------- ----------
306 Nandhini
450 Pooja
560 Uma
328 Janshi
900 Rani
904 Kavya
5. select * from partitiondemo partition(p3);
SNO SNAME
--------- ----------
900 Rani
904 Kavya
Now, insert some values again
insert into partitiondemo values(125,'Sound');
select * from partitiondemo partition(p1);
SNO SNAME
--------- ----------
100 Nithya
105 Saloni
109 Namitha
199 Aruna
125 Sound

Clusters

Clusters

Tables that are frequently accessed together may be physically stored together. Clustering is a method of storing tables that are closely related and are often joined together, into the same area on the disk.
A cluster is created to hold the tables. A cluster contains the values of a table according to the column that we define. When ever we insert values into the table the same values are automatically stored in the database object name cluster.
Cluster can be created by using create cluster command. To create a cluster use the following syntax
Syntax :
Create cluster <cluster name> (columns specifications,------)
To create any cluster
  1. First create a cluster with the column names that you want
  2. Next create an index on that cluster. Otherwise the cluster will not work properly
  3. Next create a table with the cluster specification and list of columns
  4. Now insert some values into that table, if you see the cluster the same values will be available from the cluster also
Note :
  1. We can not insert or update or delete any row from the cluster
  2. We can not even drop the cluster until the source table is present
  3. To drop the cluster, first we have to drop the source table and then we can drop that particular cluster
Creation of cluster for a table
First create the cluster,
create cluster sampleclust (sno number(3), sname varchar2(10));
Next create the index,
create index idx_sampleclust on cluster sampleclust;
Attaching the cluster to the table,
create table sample2 (sno number(3),sname varchar2(10)) cluster
sampleclust(sno,sname)
Example :
  1. To display details of the Table
select * from sample2;
SNO SNAME
--------- ----------
100 Nithya
101 Saloni
102 Aruna
  1. To display details of the Table
select * from cluster sampleclust;
SNO SNAME
--------- ----------
100 Nithya
101 Saloni
102 Aruna
  1. Even if you delete any row in the table, that will not affected to the cluster
    1. delete from sample2 where sno=100;
    2. select * from sample2;
SNO SNAME
--------- ----------
101 Saloni
102 Aruna
3. select * from cluster sampleclust;
SNO SNAME
--------- ----------
100 Nithya
101 Saloni
102 Aruna
4. delete from sampleclust where sno=102
ERROR at line 1:
ORA-00942: table or view does not exist
Note : We can not delete any data from the cluster
Drop cluster
This command is used to drop any cluster
Syntax : drop Cluster <cluster name>
To drop any cluster
Drop table sample2;
Drop cluster sampleclust;

Sequences

Sequences

The sequence is a database object, which is used to generate unique integers for use as primary key for a column. There are two steps while working with a sequence
  1. Creating Sequence
  2. Attaching or Binding Sequence

Creating Sequence

Since a sequence is a database object, it contains the values with which it generates starting, increment value and maximum value etc.
Syntax :
Create sequence <sequence name >Start with <value>
Increment by <value>Max value / [no max value] <value>
Min value / [no min value] <value>Cycle/no cycle
Cache/no cache <value>Order/no order
Start with : it is a keyword to specifying the starting value in the sequence creation
Increment by : this parameter accepts the number by which we want to increment the value in the sequence. If you want to decrement you must specify the negative value. By default the increment value is ‘1’ (not zero)
Max value : specifies the maximum value or limit that the sequence can generate
No max value : if you cannot specify the maximum limit, use this parameter. The default value is No max value.(not compulsory)
Min value : specifies the sequences minimum value or limit. It represent the number upto which you want to decrement the value.
No Min value : The default value is No Max value.(not compulsory)
Cycle : specifies that the sequence continues to generate value after reaching either its maximum or minimum value.
No cycle : it represents that the sequence should stop generation of numbers when the max value is reached. This is the default one.
Cache : it specifies how many values of the sequence oracle pre allocates and keeps in the memory for the fast access. This keyword generates ‘n’ numbers immediately after creating sequence. Where ‘n’ is the number followed by cache. Default is ‘20’
No cache : this is the default one.
Order : generates the sequences in the order of request
No order : this is the default one
Attaching Sequence
If sequence is created it can be attached to the table for storing the value of the sequence into the table or for generating the column values according to the parameters in the sequence.
There are two ways the sequence can be attached
  1. Insert Command
  2. Update Command
currval : specifies sequence current value
nextval : specifies sequence next value
Example :

Using Insert Command

Syntax :
Insert into <table name>(columns list) values(sequencename.nextval,……);
First create a dummy table
  • create table temporary(sno number(3), name varchar2(10));
Next, create a sequence with the following way
  • create sequence myseq start with 100 increment by 1 maxvalue 110  min value 100;
  • insert into temporary values(myseq.nextval,'&sname');
similarly insert values in to the temporary table, when the sequence.next Val reaches to 110, it automatically gives an error message.
ERROR at line 1:
ORA-08004: sequence MYSEQ.NEXTVAL exceeds MAXVALUE and cannot be instantiated
  • select * from temporary;
SNO NAME
--------- ----------
100 one
101 two
102 three
103 four
104 five
105 six
106 seven
107 eight
108 nine
109 ten
110 eleven

Using Update Command

Syntax :
Update <table name> set <column Name>=<sequencename.nextval>, ------;
create a sequence with the following way
create sequence myseq1 start with 1 increment by 1 maxvalue 10
min value 1;
Updating only one value
update sample set sno=myseq1.nextval where sname=’kk’;
Updating Multiple Values
update sample set sno=myseq1.nextval;
select * from sample;
EMPNO ENAME SAL
--------- ---------- ---------
1 Nithya 5600
2 Saloni 5400
3 Pooja 5400
4 Aruna 7600
5 Sound 5600
6 Mahesh 4500
7 Nandhini 3500
Note :
  1. If you are updating a table using a sequence, which contains more rows than the sequence can generate, we will get an error message
  2. For example, if a table contains 12 rows, but the sequence can generate maximum of 11 rows only. So we get error message

Altering a Sequence

A sequence can be altered using the Alter Command. But we can not alter the start with value.
Syntax :
Alter sequence <sequence name > Increment by <value>
Maxvalue /NoMaxValue <value>Minvalue /NoMinValue <value>
Cycle / NoCycle Cache/NoCache <value>
Order/noorder

Example

create sequence myseq2 start with 100 increment by 1 maxvalue 110
minvalue 100;
we altered the above sequence
Alter sequence myseq2 increment by 2 maxvalue 120;
Update sample set sno=myseq2.nextval;
select * from sample;
EMPNO ENAME SAL
--------- ---------- ---------
100 Nithya 5600
102 Saloni 5400
104 Pooja 5400
106 Aruna 7600
108 Sound 5600
110 Mahesh 4500
112 Nandhini 3500
Again Altering the above sequence
Alter sequence myseq1 nomaxvalue;
update sample set sno=myseq1.nextval;
now you can update the values as many times as you want, since there is no maximum limit to stop the generation of numbers
Drop sequence :
This command is used to drop any sequence

Syntax : drop sequence <sequence name>

Example : drop sequence myseq1;
Note :
Even though u altered the sequence, it will generate numbers from where it has left before altering the sequence and continues according to ur altered form
RowID:
When ever we insert a row, the system automatically generates Rowid’s and appends the rowid column and stores the values in it. each rowid is unique from one another.
To see Rowid’s of emp table, give the following command
Select rowid from emp;
Select rowid from dept;

To delete any Duplicate Rows (Using Row id’s)

If any duplicate rows are present in any table, then with the help of rowid, we can able to delete that particular records
select rowid, eno, ename, sal from sample;
ROWID SNO NAME
-------------------------------------- ----------
AAAAcUAACAAAAEbAAA 1 Nithya
AAAAcUAACAAAAEbAAB 2 Saloni
AAAAcUAACAAAAEbAAC 1 Mahesh
AAAAcUAACAAAAEbAAD 4 Aruna
AAAAcUAACAAAAEbAAE 2 Saloni
AAAAcUAACAAAAEbAAF 2 Saloni
Delete from customer where rowid=’ AAAAcUAACAAAAEbAAF’;
Delete from customer where rowid=’ AAAAcUAACAAAAEbAAE’

VIEWS

Views

A view is like a window through which data on a table can be viewed or changed. It is a virtual table that is – it does not have any data of its own, but derives the data from the table it is associated with. It manipulates data in the underlying base table.
Syntax :
Create [or Replace] View <view name>
[ ( column1, column2,----)] as Select <select list>
Replace Option :
This option is used to replace the definition of existing view with new definition. It can be replaced with another view only, but not with a table.
Advantages of Views
  1. Views restrict access to the database. Selecting from a view can display a restricted portion of the data base
  2. Views allows users to make simple queries to retrieve the results from complicated queries
  3. As a view does not store any data, the redundancy problem does not arise
  4. One view can be used to transparently retrieve data from several tables.
  5. Views also allow the same data to be seen by different users in different ways
Types of View
There are two types of view
1. Simple View
2. Composite View
Simple View
When a view is create basing on a single table then it is called a simple view
Syntax :
Create [or replace ] view <view name> as Select <select list> from <table1>;
Examples :
  1. Create a view, which shows all employees details whose salary is between 2000 and 3000
  • Create view empsal as select * from emp where sal>=2000 and sal<=3000;
To see the details of a view, give the following command
Select * from <view name>
Ex : Select * from sample;
2. write a query which Creates a view, that shows all employees details who are belonging to dept 20
  • Create or replace view empsal as select * from emp where deptno=20
Ex : Select * from empsal;
3. Create a view, which shows all employees details whose salary is between 2000 and 3000 and who are belonging to dept 20
  • Create or replace view empsal as select * from emp where deptno=20 and Sal between 2000 and 3000;
We can change column names in a view at the time of creation
Create a view, which shows all employees details who belong to dept 20
  • create or replace view emp20 as select * from emp where deptno=20
  • create or replace view dept20(emp_no, emp_name, salary) as select empno, ename, sal from emp;
select * from dept20;
EMP_NO EMP_NAME SALARY
----------- ---------- --------------
7369 SMITH 800
7499 ALLEN 1600
7521 WARD 1250
7566 JONES 2975
7654 MARTIN 1250
We can Create a view, using another view
  • create or replace view emp21 (ename,sal) as select empname,basic from emp20;
select * from emp21;
ENAME SAL
----------- -----
SMITH 800
JONES 2975
Mahesh 3000
ADAMS 1100
FORD 3000
Note :
  1. If column names are not given then it defaultly takes all the given column names in the table
  2. Number of columns in view ie in Bracket must be equal to number of columns in the select statement
Manipulating the Base Tables Through Views
The insert, update and delete commands can also be used with views. Using these commands with views is an indirect way of manipulating tables. Since a view nothing but a virtual table. Any manipulations ie deletion, updation will automatically affects the main table.
Example :
Create a view, which shows All students details Whose avg marks is >=60
  • Create or replace view studview as select * from students where average>=60;
  • select * from studview
  • Update students set roll=100 where roll=1;
  • Select * from studview;
  • Insert into students values(101,'Nayana Tara');
  • Select * from students;
  • delete from studview where roll=101;
  • select * from studview;
  • Select * from students;
Note : What ever operations you perform on views will be affected to the table that is associated with the view
To Create Read Only Views
We can create read only view. When a view is created with any arithmetic expression or with any group by functions then it cannot be modified or changed with insert or update or delete statements. So this can be called as a Read Only Views
Example :
Creates a view, with all department numbers and number of Employee’s working under each department
  • create view deptgroup as select deptno,count(*) "no of emps" from emp group by deptno
  • select * from deptgroup;
EPTNO no of emps
----- ----------
10 3
20 5
30 6
Note : When Ever We Apply Any Dml Operation On This View, We Will Get An Error Message
  • insert into deptgroup values(40,2);
*ERROR at line 1:
ORA-01732: data manipulation operation not legal on this view
  • Delete from deptgroup where deptno=10
ERROR at line 1:
ORA-01732: data manipulation operation not legal on this view
Composite View
A view created by retrieving data from 2 or more tables. A composite view can not be updated or we can not apply any DML operations except select statement. Because it contains different columns from different tables.
Syntax :
Create or replace view <view name> as select <fields list> from <table1>,<table2>,……..
1. create view which should show all columns in emp and dept table whose deptno in emp is equal to deptno in dept table
  • create or replace view empdept as select empno, ename, sal, dname, loc, dept.deptno from emp, dept where emp.deptno=dept.deptno
select * from empdept;
insert into empdept values(1000,'hello',5000,'Production','tirupati',20)
ERROR at line 1:
ORA-01776: cannot modify more than one base table through a join view
Note : Since here two table values are included. So at a time we cann’t insert or modify two tables data at a time

But if you give like this ,

update empdept set empno=1000 where empno=8900;
update empdept set empno=2000,ename='jj',sal=1000 where empno=1000
update empdept set dname='cmp',loc='tpt',deptno=30 where empno=2000
Note : Records will be updated, since you are updating values only on a single table related columns, if you apply any operations on both table columns then can not perform any operation
select * from empdept;
Dropping a view
Drop View : This command is used to drop any view from the database
Drop view <view name>
Example : Drop View empdept;
Note :
  1. To show all Views details (both user defined and pre defined views)
  • Select * from all_Views;
  1. To show all User Defined Views details
  • Select * from user_Views;
  • Select view_name from all_Views;

Joins

Joins

One of the most important features of SQL is the ability to define relationships between multiple tables and draw information from them in terms of these relationships, all within a single command.
With joins we can combine columns from different tables. The connection between tables is established through the WHERE clause
Table and column alias names
The full name of a column of a table actually consists of the table name followed by a dot and then the column name e.g. emp.empno, emp.ename etc
User can omit the table names if one is querying only single table at a time. Even when querying on multiple tables one can still be able to omit the table names provided that all the column names are different.
Types of Joins
There are five types of joins, they are
1. Equi Join ( = )
2. Non Equi Join ( < , > , >=, <=, !=)
3. Outer Join ( + )
4. Self Join
5. Cross Join (Introduced in Orace 9i)
General Syntax :
Select < select list > from <table1>,<table2>,-----, <table N>
Where <table1.column1>=<table2.column2> and ------
Equi Join
When two tables joined together using equality operator, then it is called Equi Join. Table prefixes are utilized to prevent ambiguity and the Where clause specifies the columns being joined
Example :
List the employee numbers, names, department numbers and dept names
  • Select empno,ename,emp.deptno,dname from emp,dept
where emp.deptno=dept.deptno;
Here, the deptno column exists in both the tables. To avoid ambiguity, the column name should be qualified with the table name ( or with an alias of table name)
Both the table names need to be specified(emp and dept) the where clause defines the joining condition ie joining the deptno of emp table to the deptno of dept table. Here, it checks for the equality of values in these columns
Using Table Aliases
It can be very tedious to type table names repeatedly. Temporary labels ( or aliases) can be used in the FROM clause. These temporary names are valid only for the current select statement. Table aliases should also be specified in the select clause. Table aliases can be up to 30 characters in length, but the shorter they are the better.
Example : List Employee Numbers, Names, Department Numbers, And Department Names From Emp And Dept Tables Using Aliases
  • select e.empno, e.ename, e.deptno, d.dname from emp e, dept d
where e.deptno=d.deptno;
Non Equi Join
Using this we can retrieve data from two or more tables by specifying a condition on the common column with any non-equi join operators (>, <, >=, <=,!=)
Example :
Write a query to display those employees details whose salary of emp1 table >= salary of emp table employees
  • select eno,ena,emp1.sal,emp.sal from emp1,emp where emp1.sal>=emp.sal
Outer Join
It is used to retrieve the common data from both tables and all values from the table having outer join operator (+)
There are 2 types of outer joins
  1. Left Outer Join
  2. Right Outer Join
Left Outer Join
It is used to retrieve all rows from the table having the outer join
operator which is left side to the outer join and common values from another
table
Example :
Display the list of employees working in each department. Display the department information even if no employee belongs to that department
  • select empno,ename,sal,emp.deptno,dname,loc from emp, dept where
emp.deptno(+)=dept.deptno
Right Outer Join
It is used to retrieve all rows from the table having the outer join
operator which is right side to the outer join and common values from another
table
Example :
Display the list of employees working in each department. Display the employee information even if no such department belongs to the dept table
  • select empno,ename,sal,emp.deptno,dname,loc from emp, dept where
emp.deptno=dept.deptno(+)
if the symbol (+) is placed on the other side of the equation then all the employee details with no corresponding department name and location , will be displayed with NULL values in Dname and Loc columns
Rules to place ( + ) Operator
1. The outer join symbol (+) can not be on both the sides
2. We can not outer join the same table to more than one other table in a single Select statement
3. A condition involving an outer join may not use the IN operator or be linked to another condition by the OR operator
Self Join
To join a table to itself means that each row of the table is combined with itself with every other row of the table. The self join can be viewed as a join of two copies of the same table. The table is not actually copied, but SQL performs the command as though it were.
Example : list out the names of the manager with the employees in the emp table
  • Select worker.ename, manager.ename ‘Manager’ from emp worker, emp manager where worker.mgr=manager.empno;
Cross Join (oracle 9i concept)
A cross joins returns what’s known as a Cartesian product. This means that the join combines every row from the left table with every row in the right table. This type of join can be used in situations where it is desired, to select all possible combinations of rows and columns from both tables. This kind of join is usually not preferred as it may run for a long time and produce a huge result set that may not be useful
Syntax
Select <select list > from table1 alias cross join table2 alias
Example :
Display the list of employees working in each department. Display the employee information even if no such department belongs to the dept table and also display the department details even if no employee belongs to that department
  • select empno,ename,sal,emp.deptno,dname,loc from emp Cross join dept

Constraints

 Constraints:

These are the conditions or rules that we impose on any column for entering valid data into the table. Constraints are a part of the table definition that are used to limit the values entered into its columns.
Note : Constraints can be imposed in two ways

(i) Table Level : Imposing constraints on a table level by specifying the constraint type at the end of the columns is called Table Level Constraints. In Table Level, the Constraint key word followed by Constraint name must be defined otherwise error comes.
(ii) Column Level :
Imposing constraints on a column by specifying the constraint type beside the column name is called Column Level Constraints
Constraints can be defined in two ways
1. In the Table Definition itself
2. Using Alter Command
The following are a list of constraints
  1. NOT NULL CONSTRAINT
  2. UNIQUE CONSTRAINT
  3. PRIMARY KEY CONSTRAINT
  4. CHECK CONSTRAINT
  5. DEFAULT
  6. REFERENCES (FOREIGN KEY CONSTRAINT )
NOT NULL
The Not Null constraint specifies that a column can not contain Nulls. To satisfy this constraint every row in the table must contain a value for the column.
if you do not specify not null, the column can contain nulls by default
Syntax:
Not Null in Column Level
Ø Create table <Table Name> ( Column <Data type><size> Not Null,
Column2 <Data Type><size> ,-------);
Adding Not Null Using Alter Command for Existing Table
Ø Alter Table <Table Name>
Modify (<Column Name > <data type> (size) not null)
Note :
  1. Column must be empty to impose the not Null constraint with alter table statement.
  2. Table Level is not applicable for Not Null constraint
Example :
Create table sample1(sno number(3), sname varchar2(20) not null);
Ø insert into sample1 values(&sno,'&sna');
Enter value for sno: 100
Enter value for sna: Mahesh
Enter value for sno: 101
Enter value for sna: (nothing is entered)

Error Comes

mandatory (NOT NULL) column is missing or NULL during insert
Unique Constraint
This constraint does not allow duplicate values into any column.
Syntax: (For Column Level)
Create Table <Table Name>(Column1 <Data Type><Size> Unique,
Column2 <Data Type><Size>,--------);
Syntax: (For Table Level)
Create Table <Table Name>(Column1 <Data Type><Size>,
Column2 <Data Type><Size>,--------,
Constraint <Constraint Name> Unique <Column Name>);
( Or )

Example : Imposing unique constraint Table Level

create table customer(cno number(3),cname varchar2(20),
constraint con_un unique(cno));
Ø insert into customer values(&cno,'&cname');
Enter value for cno: 100
Enter value for cname: Mahesh
Enter value for cn: 100
Enter value for c: Nithya
Error Comes
unique constraint (SCOTT.CON_UN) violated
Example : Imposing unique constraint Column Level
create table customer1(cno number(3) unique ,cname varchar2(20));
Ø insert into customer1 values(&cno, '&cname');
Enter value for cno: 100
Enter value for cname: Mahi
Enter value for cn: 100
Enter value for c: Prasad
Error Comes
unique constraint (SCOTT.SYS_C00385) violated
Default Constraint :
The default constraint is used to specify a default value for the column, when you want to given default values. You can give default value as NULL or some other value.
Note : Only Column level definition can be applied to “Default constraint”
Syntax : for column level
Create Table <Table Name>(Column1 <Data Type><Size> default <value>,
Column2 <Data Type><Size>,--------);
Example :
Ø create table sample2(sno number(3) default 10,sname varchar2(20), phone
varchar2(10) default null)
Ø insert into sample2 values(1,'Mahesh','11111')
Ø insert into sample2 values(2,'Prasad','22222')
Ø insert into sample2(sno, sname) values (3,'Nithya')
Ø insert into sample2(sno, sname) values (4,'Saloni')
Ø select * from sample2;
SNO SNAME PHONE
--------- -------------------- ----------
1 Mahesh 11111
2 Prasad 22222
3 Nithya
4 Saloni
Example : 2
create table sample3(sno number(3) default 100, sname varchar2(10) not null);
Ø insert into sample3 values(200,'Nithya')
Ø insert into sample3 values(201,'Aruna')
Ø insert into sample3(sname) values('Mahesh')
Ø insert into sample3(sname) values('Saloni')
select * from sample3;
SNO SNAME
--------- ----------------
200 Nithya
201 Aruna
100 Mahesh
100 Saloni
Check Constraint
The check constraint explicitly defines a condition. It will put restrictions on the range of the column values and the values to be accepted into the column. The condition of a check constraint can refer to any column in the table. But it can not refer to any column in other tables.
Syntax : (column Level)
Create table <table name> (<column1> <datatype> [size] check(condition),
<column2><data type> <size>,-----);
Example:
Create table items(itemno number(3) check(itemno>=100),itemname varchar2(10));
Ø Insert into items values (100,'Rice')
Ø insert into items values(101,'Paste')
Ø insert into items values(99,'Chacolate') à Error comes
Syntax : (Table Level)
Create table <table name>
(<column1> <datatype> [size],
<column2><data type> <size>,
-------------------
constraint <constraint name> check(<column name> with condition));
Example :
create table item1(itno number(3), Itname varchar2(10),
Constraint con_check check (itno>=100));
Ø Insert into item1 values (100,'Rice')
Ø insert into item1 values(101,'Paste')
Ø insert into item1 values(99,'Chacolate') à Error comes
Primary Key Constraint
It avoids null values and also it does not allows duplicate values. Ie A Primary key is nothing but combination of not null and unique constraints
To Impose Primary Key On Any Column, You Can Use Either Table Level Or Column Level
Syntax: Table Level
create table <tablename>(column1 <datatype><size>,
column2 <datatype><size>,--------,
constraint <constraint name> primary key <column name>);
Ex:
create table items1(itemno number(3),itemname varchar2(20),
constraint con_pk primary key(itemno));
Ø insert into items1 values(100,’Rice’);
Ø insert into items1 values(100,’paste’);

Error Comes

unique constraint (SCOTT.CON_PK) violated
Syntax : Column Level
create table <tablename>(column1 <datatype><size> primary key ,
column2 <datatype><size>,--------);
Example :
create table items2(itemno number(3) primary key, itemname varchar2(20));
Ø insert into items2 values(100 ,'Wheat');
Ø insert into items2 values(100 ,'Rice');

Error Comes

unique constraint (SCOTT.CON_PK) violated
Example 2
Ø insert into item values(null ,'Wheat');

Error Comes

mandatory (NOT NULL) column is missing or NULL during insert
Foreign Key Constraint
This is used to establish relationship between 2 or more tables for inserting common or similar values into the related columns of the table.
The table from which we are establishing relation is called child table. Because the related column in the child table depends on another table for its values.
The table on which other tables are depending on the table, to which we are establishing a relation is called Mother table.
If two tables are having this relationship then the system checks whether the new value that we are inserting is related column of child table exists in the mother table or not.
There are five major restrictions related to foreign key
  1. The Mother table column related must have primary key
  2. We can not drop the mother table with out child table provided.
  3. The data type and the size of the related columns in both the tables must be same and name can be different
  4. We can establish relation to any number of tables
  5. We can not impose foreign key which are having values.
Syntax : (Column Level)
Create Table <Table Name>
(<column1><data type><size>
References <Mother Table>(<column name>),
<column2><data type><size>,
--------------
<columnN><data type><size>);
Syntax : (Table Level)
Create Table <Table Name>
(<column1><data type><size>
<column2><data type><size>,
--------------
<columnN><data type><size>,
constraint <constraint Name>
foreign key(child column)
References <Mother Table> (<column name>));
Making relationships between two tables
1. First Create the Mother Table or Parent Table
create table bankmast
(accno number(3) primary key,
accna varchar2(10) not null,
balance number(8,2));

2. Next Create the Child Table

create table banktrans
(acno number(3) references bankmast(accno),
trmode char(1),trdate date,
amount number(8,2));
3. Next insert the values in to mother table first,
Ø insert into bankmast values(&accno,'&accna',&bal);
select * from bankmast;
ACCNO ACCNA BALANCE
--------- ---------- -----------
100 Priya 5000

101 Mahesh 6500

102 Saryu 4500
103 Nandhini 7600
4. Next insert values into child table
Ø insert into banktrans values(&acno,'&trmode','&trdate',&amt);
Ø insert into banktrans values(100,'D','5-May-05',5000);
Ø insert into banktrans values(102,'W','15-Apr-05',2000);
Ø insert into banktrans values(104,'D','10-Feb-05',10000);
ERROR : (since there is no such account number present in the mother table)
ORA-02291: integrity constraint (SCOTT.SYS_C00674) violated - parent key not found
SQL> select * from banktrans;
ACNO T TRDATE AMOUNT
--------- --- ------------- ------------
100 d 05-MAY-05 5000
102 w 01-JUN-05 2000
Dropping Relationship Tables
Note :
  1. if you try to drop the mother table when child table is present, it will give an error message
drop table bankmast;
ERROR :
ORA-02449: unique/primary keys in table referenced by foreign keys
  1. To drop the mother table, First Drop the Child table and then drop the mother table
Drop table banktrans;
Drop table bankmast;

Sub Queries Or Nested Queries

Sub Queries Or Nested Queries

A query within another query is called a sub query. We can define any number of sub queries with in a query. But the system executes the inner most query first. If we are using relational operators between the queries then the sub query must return a single value.
The Following Points should be kept in Mind While using SubQueries
  1. The inner Query must be enclosed in parentheses
  2. The Inner query must be on the right hand side of the condition
  3. The sub query may not have an order by clause
  4. The Order By clause appears at the end of the main select statement
  5. Sub queries are always executed from the most deeply nested to the least deeply nested
Note : If we use operators “in” and “not in” then the sub query followed by the operators can return more than one value.
Example Queries
1.List the employee’s Details who belong to the department of “Ward”
In general, first we have to know; what the department of “Ward” and then we can display all the employee’s details belonging to “Ward”
select * from emp where ename='WARD';
EMPNO ENAME JOB MGR HIREDATE SAL COMM DEPTNO
--------- ---------- --------- --------- ------------ --------- --------- ---------
7521 WARD salesman 7698 22-FEB-81 1250 500 30
  • select * from emp where deptno=30;
EMPNO ENAME JOB MGR HIREDATE SAL COMM DEPTNO
--------- ---------- -------------- ----------- ------------- --------- --------- ---------
7499 ALLEN salesman 7698 20-FEB-81 1600 300 30
7521 WARD salesman 7698 22-FEB-81 1250 500 30
7654 MARTIN salesman 7698 28-SEP-81 1250 1400 30
7698 BLAKE manager 7839 01-MAY-81 2850 30
7844 TURNER salesman 7698 08-SEP-81 1500 0 30
Using Sub Queries,
  • select * from emp where deptno=(select deptno from emp where ename='WARD');
EMPNO ENAME JOB MGR HIREDATE SAL COMM DEPTNO
--------- ---------- -------------- ----------- ------------- --------- --------- ---------
7499 ALLEN salesman 7698 20-FEB-81 1600 300 30
7521 WARD salesman 7698 22-FEB-81 1250 500 30
7654 MARTIN salesman 7698 28-SEP-81 1250 1400 30
7698 BLAKE manager 7839 01-MAY-81 2850 30
7844 TURNER salesman 7698 08-SEP-81 1500 0 30
2.List the employee’s details whose salary is greater than the average salary of all the employees
In general, first we have to know; average sal of all employee’s and then we can display all the employee’s details whose salary is > average salary of all employees
  • select avg(sal) from emp;
AVG(SAL)
---------
2161.3636
  • select * from emp where sal>=2161;
EMPNO ENAME JOB MGR HIREDATE SAL COMM DEPTNO
--------- ---------- ------------- --------- ------------- --------- --------- -----------
7566 JONES Manager 7839 02-APR-81 2975 20
7698 BLAKE Manager 7839 01-MAY-81 2850 30
7782 CLARK Manager 7839 09-JUN-81 2450 10
7788 SCOTT Analyst 7566 19-APR-87 3000 20
7839 KING President 17-NOV-81 5000 10
Using Sub Queries,
  • select * from emp where sal>(select avg(sal) from emp);
EMPNO ENAME JOB MGR HIREDATE SAL COMM DEPTNO
--------- ---------- ------------- --------- ------------- --------- --------- -----------
7566 JONES Manager 7839 02-APR-81 2975 20
7698 BLAKE Manager 7839 01-MAY-81 2850 30
7782 CLARK Manager 7839 09-JUN-81 2450 10
7788 Mahesh Analyst 7566 14-FEB-78 3000 20
7839 KING President 17-NOV-81 5000 10
3.Write a Query to find the details of the department whose manager’s empcode ‘7698’
In general, first we have to know; department number whose manager empcode is “7698” and then we can display all the department details of that employee
  • select * from dept;
DEPTNO DNAME LOC
--------- -------------- -----------------
10 ACCOUNTING NEW YORK
20 RESEARCH DALLAS
30 SALES CHICAGO
40 OPERATIONS BOSTON
  • select deptno from emp where mgr='7698'
DEPTNO
---------
30
30
30
30
Using sub Query,
select * from dept where deptno=(select deptno from emp where mgr='7698')
ERROR at line 1:

ORA-01427: single-row subquery returns more than one row

  • select * from dept where deptno in (select deptno from emp where mgr='7698')
DEPTNO DNAME LOC
--------- -------------- -------------
30 SALES CHICAGO
or you can give, like this
  • select * from dept where deptno=(select distinct deptno from emp where mgr='7698');
DEPTNO DNAME LOC
--------- -------------- -------------
30 SALES CHICAGO
4.List the details of all the employee’s who earn lowest salary in each department
  • select * from emp where sal in(select min(sal) from emp group by deptno)
EMPNO ENAME JOB MGR HIREDATE SAL COMM DEPTNO
-------- ---------- --------- --------- ------------- --------- --------- ---------
7369 SMITH Clerk 7902 17-DEC-80 800 20
7521 WARD Salesman 7698 22-FEB-81 1250 500 30
7654 MARTIN Salesman 7698 28-SEP-81 1250 1400 30
7782 CLARK Manager 7839 09-JUN-81 2450 10
Co related Sub query:
A sub query is executed once for the parent statement whereas the correlated sub query is executed once for each row of the parent query.
Example:
Find all employees who earn more than the average salary in their department.
SELECT last-named, salary, department_id FROM employees A WHERE salary > (SELECT AVG (salary) FROM employees B WHERE B.department_id =A.department_id Group by B.department_id)