

Starting Oracle
Data :Any valuable information.
Database :Well arranged data, which is populated with data for
specific reason.
DBMS :Data base management system. It is a software which
allows to create database and manipulate the database. Database
manipulation means adding new data, modifying existing data,
deleting & searching. The language used for DBMS operation is called
SQL (Structured Query Language).
Data base terminology
Tuple/Row : A single row in the table is called as tuple.
Attribute/Column : A column stores an attribute of an entity.
Ex.Name, Age etc.
Table Name : Each table is a given a specific name.
Primary Key : Each table should have one or more columns
that can be used to uniquely identifies the rows. That means given a
value in that column. That should not be duplicate.
Composite Primary Key : When a single column can not be used for
unique identification. Then multiple columns can be used together.
Table Employee
Emp. Name Basic Dept.
No. Sal. No.
101
102
103
Sale
Sn. Month Commissio
No. n

Foreign key/Reference key: - (FK)

NICT Computer’s , Near kolhapure circle , beside Maheswari blind school ,

Nehru nager , belgaum-10
 
In relational model we often store data in different tables and put
them together to get complete information.
Dept
Dept. Dept. HOD
No. Name Name
1 CS
2 EC

Student
Reg. No. Name Marks Dept. No.

Relational algebra : A set of operators used to perform operations on

table is called as relational algebra.
Select : Returns a horizontal subset of the table.
Project : Returns the vertical subset of the table.
Union : It takes two tables and returns all rows that are belong to either
first table, second table or both.
Intersect : It takes two table and return all rows that are belong into
first and second table.
Minus : It takes two tables and returns all rows that are belong into 1 st
but not in second table.
Join : Rows of types are combined based on the given column values.
Both table must have common column usually primary key & foreign
key.

ORACLE
What is Oracle:
A DBMS must able to reliable manage a large amount of data in
multi-user environment so that many users concurrently access the
data.
A database system should provide security and failure recovery.

NICT Computer’s , Near kolhapure circle , beside Maheswari blind school ,

Nehru nager , belgaum-10
 
It should also provide database back up facility.
Oracle is the name of DBMS developed by oracle corporation.
Oracle is RDBMS because it stores and manages data using relational
model.

What is developer 2000?

This a collection of application development tool. This tools are
used to develop application [Front-end portion] that will be run on the
clients.
Oracle comes two flavors:
1) Oracle server.
2) Personal Oracle.

NICT Computer’s , Near kolhapure circle , beside Maheswari blind school ,

Nehru nager , belgaum-10
 
What is Personal Oracle?
Personal oracle is flavors of oracle in which oracle server & client
both run on same machine. Oracle 8 supports up to 15 database
collection.

Starting SQL:
SQL is one of the program supplied by oracle this provides an
environment in which user can directly enter SQL command and get
the result.

Connecting the Oracle:

Oracle is a multiuser database system it identifies users using
username and password. Following users are already provided by
oracle when it is installed.
User Password
Name
Scott Tiger
Demo Demo
System System

Basic Data types

Basic Data types Description
Variable length char values up to width
Varchar2 (len)
length Maximum 4000 char
Fixed length char, Default length 1,
Char (len)
Maximum 2000 char
Floating point no width precision of 38
Number
significant no.
Precision represents maximum significant
Number (precision,
digits allowed, scale is no of decimal
scale)
places.
Date Date value

NICT Computer’s , Near kolhapure circle , beside Maheswari blind school ,

Nehru nager , belgaum-10
 
Note :- in general syntax CAPITAL letters means keywords and
small means user defined oracle objects
Syntax to create simple table

NICT Computer’s , Near kolhapure circle , beside Maheswari blind school ,

Nehru nager , belgaum-10
 
Creating table

CREATE TABLE table

(
column_name1
Datatype,
column_name2
Datatype,
column_name3
Eg. Create table Student with rollno 3 digit , name 20 char , dob ,
per 99.99 format
create table student
(
rollno number(3),
name varchar2(20),
dob date,
per number(5,2)
);

• Displaying table definition

Syntax
DESC
table_name;
Eg. desc student;
• Inserting Rows into a table
Simple insert INSERT INTO table_name VALUES

Eg. Insert into student values (1, ‘Ashok’,’3-may-1983’,72.86);

Inserting into selected column

INSERT INTO table_name (selected column)

Eg. Insert into student (Rollno, per) values (2, 85.86);

Simplified Interactive insert

Insert into student values (&rollno,

NICT Computer’s , Near kolhapure circle , beside Maheswari blind school ,

Nehru nager , belgaum-10
 
It will read the values from keyboard you can use / to repeat last
command.
Getting data from another table:into table1 Select *
Insert

• Selecting Rows from a table

Selecting all tables and all columns
Select * from
Eg. select * from student;
Select column list from
Selecting selective column [Projection]
Eg. select name, dob from student;
Select * from tablename
Selecting based on condition

Relational Operators = , ! = or <> , >= , <= , > , < , AND ,

OR , NOT

Eg. 1) Select * from student where per > = 60;

Eg. 2) Select * from student where Per >=50 AND per<=75;

Using expression in select

select name, (sysdate – dob)/365 from student;
where sysdate is current system date

Column Alias
select name, (sysdate – dob)/365 age from student;

To sort rows
select * from student order by per;
select * from student order by per desc;

BETWEEN.. AND Operator

Select * from student where per Between 50 And 75;
IN Operator

NICT Computer’s , Near kolhapure circle , beside Maheswari blind school ,

Nehru nager , belgaum-10
 
Select * from student Where per in (50,60,65,70);

NICT Computer’s , Near kolhapure circle , beside Maheswari blind school ,

Nehru nager , belgaum-10
 
LIKE Operator
This operator is used to search for value when the extract value
is not known. It select rows that match the given pattern.
Symbol Meaning
% Zero or more
character
_(Undersco Any single char
re)

Q. To select all student whose name starts with ‘C’

Select * from student Where name like ‘C%’;
Is null, is not Null
Q. To list students whose result is not declared
select * from student where per is null;
Q. To list students whose result is declared
select * from student where per is not null;
Integrity Constraints
Constraints are used to implement standard rules such as
uniqueness in primary key, limit value & variable to certain range etc.
NOT NULL : Used to prevent any null value from entering into column
[Automatic for primary key]
Method 1
CREATE TABLE tb1
(
name varchar2 (20) constraint constraint_name NOT
NULL,

);
where constaint_name naming styled is like tablename_colname_type

Method 2
CREATE TABLE tb1
(
name varchar2 (20) NOT NULL,

);

NICT Computer’s , Near kolhapure circle , beside Maheswari blind school ,

Nehru nager , belgaum-10
 

PRIMARY KEY : A Primary key used to uniquely identify rows in a

tuple. It may consist of more than one column then it is called
composite key
UNIQUE CONSTRAINT : Enforces uniqueness in the given column(s).
Oracle automatically creates a unique index for this column.
FOREIGN KEY : used to join one table with other tables, provides
referential integrity. After a foreign key is defined, oracle does not
allows the parent row to be deleted, if it contains child row, for this ON
DELETE CASCADE option can be used.
CHECK : Defined the condition that should be satisfied before
insertion and updation is done.
Eq. CREATE TABLE student
(
----------
----------
age number (2) check (age>=18)
);

Q. Create following table

Student Roll sname branch year
no
Roll Subn Mark
no ame s Subject

Create table student

(
rollno number (2) constraint student_rollno_pk primary
key,
sname varchar2(20) constraint student_sname_nn not null,
branch varchar(5),
year number(1)
);

create table subject

(
rollno number(2) constraint subject_rollno_fk references
student(rollno),
subname varchar2(15) constraint subject_subject_nn not
null,

NICT Computer’s , Near kolhapure circle , beside Maheswari blind school ,

Nehru nager , belgaum-10
 
marks number(3) check (marks<=100)
);

• Alter Table Definition:

ALTER TABLE tablename
[ADD (column
specification)]
[MODIFY (column
specification)]

Adding a new column to a table

Alter table student Add (dob Date);
Modify Column
Alter table subject modify (subname varchar2(20));
Dropping a constraint
Q. list all the table names , attributes and their type
Select constraint_name , constraint_type, table_name from
user_constraints;
Alter table student
Drop constraint student_sname_nn;
Enabling and Disabling Constraints
Alter table subject Constraint Disable Check;

• Dropping Table
Drop Table tablename [Cascade Constraints];

Setting default value : we can assign value by using default.

CREATE TABLE shop
(
dob date default sysdate,
);

Data Manipulation

NICT Computer’s , Near kolhapure circle , beside Maheswari blind school ,

Nehru nager , belgaum-10
 

Delete from table_name

Deleting Rows
[where condition];
Q. delete all student from 3rd year
Delete from student where year=3;
Updating Rows
Update table _name set column = value
[where condition];
If condition is not given, all the rows are updated,
Q. change name of rollno 3 as bharat
Update student set name=’bharat’ where rollno=3;

Commit Command
- Makes all the changes of database permanent.
- Unlocks the rows that are held during transaction.
Rollback
Used to undo changes made since last commit command
Savepoint
Used to split transaction into smaller blocks, so that we can
rollback upto particular block.
SAVEPOINT savepoint_name;
ROLLBACK to savepoint_name;
Autocommit : issues commit automatically after every insert, delete
and update command.
SET AUTOCOMMIT ON | OFF

ARITHMETIC AND DATE FUNCTIONS.

-: Arithmetic Function :-
1) ABS (Values) : Absolute value of the given value.
2) Ceil (Value) : smallest integer larger than or equal to value
Eg. Ceil (1.3) = 2 (-2.3) = 2
3) Floor (Value) : Largest Int. smaller than or equal to value
Eg. Floor (1.3) = 1 Floor (-2.3) = -3

NICT Computer’s , Near kolhapure circle , beside Maheswari blind school ,

Nehru nager , belgaum-10
 
4) Mod (value, divisor): Reminder of the division.
5) Power (Value, Exp) :- Value ^exp
6) Round (Value, precession):Rounds the value to precession.
Eg. Round (1295.356,2)=1295.36 Round (1285.356,
-1)=1290
7) Trunc(Value, precision):
Trunc (1295.356,2) = 1295.35 Trunc (1286.356, -1)
1280
8) SQRT (Value): Square root of a no.

Dual Table
This is a table with one row and one column provided by Oracle.
This table can be used with SELECT to test result of a function.
Eg. SELECT sysdate from dual;

- : Date Functions :-
1) Add Months (date, count): Adds count no of months to date
Eg. Select Rollno, dob, add_month (dob,6) from student
Roll no Dob Add_month
11 15-Mar-83 15-Sep_83
Count can be negative to get previous dates.
2) Months between (date1, date2): gives month between two dates.
Eg. Select Rollno, dob, add_months_between (sysdate, dob) Month
from student;
Roll no Dob Month
12 5-Mar-03 3.603
Consider sysdate as 24-June-03
3) Round (Date, [format]) : Rounds up the fractions.
Select rollno, dob, Round (month_between (sysdates, dob))
Month from student;
Roll no Dob Month
12 5-Mar-03 4

NICT Computer’s , Near kolhapure circle , beside Maheswari blind school ,

Nehru nager , belgaum-10
 

4) Trunc (Date) : Truncates the fractional part

Select Rollno, dob, Trunc (month_between (sysdate,dob),0)
Month from students;
12 15-mar-03 3
5) Last Day (date) : Returns date of the last day of the given date
Select Rollno, dob, Last_day (dob) from student;
Roll no Dob Last_day
(dob)
11 15-Mar-03 31-mar-03

6) Next_Day : Returns the date of next named day of the week

(Sunday, Monday, Tuesday, Wednesday -) after the given
date.
Select sysdate, next_day (sysdate, ‘Fri’) from dual;
Sysdate Next day
25-jun-97 27-jun-97

-: String :-
Concatenating Strings: (||)
select name, name || ‘ ‘ || Rollno from student;
Length (String): returns no of char in the string
Lower (string) : Converts to lower case
Upper (string) :
Initcap (string) : Make first char capital
LPAD (string, length [,fill string])
RPAD (string, length [,fill string])
LTRIM (string.[,charset])
RTRIM (string.[,charset])
SUBSTR (string, pos, length)

NICT Computer’s , Near kolhapure circle , beside Maheswari blind school ,

Nehru nager , belgaum-10
 
INSTR (S1, S2, [pos [,occurance]]) : find the string position S1 in S2. If
occurrence is not given. Then it find first occurrence. Search start at
pos,
ASCII (string): gives ASCII of first char of string
CHR (number): gives char of given ASCII code.
Translate (string, from, to): replace char in from with ‘to’ in string.
Replace (string, search, replace): replaces search with replace.

-: Conversion :-
To-char (value [,format]): Converts value which is of no or date
type to char type.
To-Date (char[,format]): Converts character to date type.
To-Number (char): Converts char type to number type.

: TO-CHAR Function :
This function is used to convert the given DATE or NUMBER to
CHAR, also used to format the date or number. To display formatted
date & time portion of SYSDATE.
Select to _char (SYSDATE, ‘dd-month-yyyy’ hh24 : mi :55)
From dual.

NICT Computer’s , Near kolhapure circle , beside Maheswari blind school ,

Nehru nager , belgaum-10
 

Format Option Meaning

MM Number of month : 10
MON First 3 char OCT
MONTH Complete months name: October
DDD Day of year, since 1 Jan
DD Day of month
D Day of week
DAY Day full spelled : Monday
YYYY A digit year : 1996
YY 2 digit year : 1996
YEAR Year spelling
HH or HH12 Hour of the day : 5
HH 24 Hour of the day : 20
MT Minute of Hour
SS Seconds of Minute
A.M. or P.M. Display A.M. or P.M. depending
on time

-: Other Function :-
DECODE (value, if, then, if, then ---- else)
If value is equivalent to if then substitutes then for value.
Eg.
Select Sno, Sname, course
Decode (status, ‘B’, ‘Break’,
‘C’, Completed
‘S’, studying) status from student;
GREATEST (Value1, Value2, -----) Return greatest of return values
LEAST (Value1, Value2, ------) Return smallest of return values

NICT Computer’s , Near kolhapure circle , beside Maheswari blind school ,

Nehru nager , belgaum-10
 
GROUPING DATA
Group by Used to group data on particular column & perform certain
operation on each group.
Eg. Select branch, count (*)
From student
Group by branch;
Group Functions SUM , AVG , MIN , MAX , COUNT.
Eg. Select name, sum (salary)
From worker
Group by name;
= must be same
Grouping on more than one column It is possible to group rows on
more than one column. In this first column is used to group the data
and then with in that group.
Select subcode, faculty, avg(marks)
From studentsx
Group by subcode, faculty;
Having Clause : If you want to put condition on group then having
clause can be used.
Select course, count (*)
From studentsx
Group by course
Having count (*) >1;
Order by with Group by
select course, avg(totalfee)
from students
group by course
order by avg (totalfee);

NICT Computer’s , Near kolhapure circle , beside Maheswari blind school ,

Nehru nager , belgaum-10
 
JOINING TABLES
If some column name exist in more than one table the column name is
qualified by using.
Tablename.columnname
Product of two tables
While joining the table, if where is not given it result in product,
in which each row of first table is joined with each row of second table.
Select student.rollno, sname, subname,marks from
student,subject;
Joining two tables
Select student.rollno, sname, subname,marks from
student,subject where
Student.rollno=subject.rollno;

or
Select student.rollno, sname, subname,marks from student
t1,subject t2 where
t1.rollno=t2.rollno;

-: Set Operators : -
UNION, UNION ALL, INTERSECT, MINUS.
Consider tables
Rollno positio Cricket
n
Rollno positio football
n
Q. List all students in cricket , football or both
Select rollno from cricket
Union
Select rollno from football;

NICT Computer’s , Near kolhapure circle , beside Maheswari blind school ,

Nehru nager , belgaum-10
 
Q. List all students in both cricket , football
Select rollno from cricket
intersect
Select rollno from football;

Q. select student who are only in cricket team

Select rollno from cricket
minus
Select rollno from football;

Q. select student who are only in football team

Select rollno from football
minus
Select rollno from cricket;

VIEWS
A view is a window through which you access a portion of a
table. View it self does not contain any data, it refers to the data of
table on which it is based.
Creating View
Create or replace view viewname
As Query
[with check option];

Eg. Create view result

As select name, per from student,subject where
student.rollno=subject.rollno;
Once a view is created, a view can be used similar to a table.

Changing base table through view: -

NICT Computer’s , Near kolhapure circle , beside Maheswari blind school ,

Nehru nager , belgaum-10
 
A view can also be used to change the data of base table. A view can
be used to.
- Delete rows from base table.
- Update data of base table.
- Insert rows into base table.
But it should satisfy certain conditions.
With check option This option is used to prevent any changes to base
table through view.

NICT Computer’s , Near kolhapure circle , beside Maheswari blind school ,

Nehru nager , belgaum-10
 
Dropping view
[DROP VIEW view-name;]

SUBQUERIES
A query within another query is called subquery. The outer query
is called as main query and inner query is called as subquery.
Eg.1) select subcode, faculty, marks
From studmarks
Where sno = (select sno from students where sname = ‘xyz’);
- Subquery is always executed first and the result is passed to
the main query.
- If there is possibility of duplicates in subquery the replace =
by in.
Eg.2) Multi-Row subqueries
select sno, sname
from students
where sno in
(select sno from studmarks
where subcode = ‘Oracle’)
- Multiple suqueries
select sno, sname, course from students
where sno in
(select sno from ----------)
and sno in
(select sno from ---------)
- Nesting Subqueries
select sno, ------ from student
where sno in
(select sno from -----------
where subcode in
(select subcode --------));

NICT Computer’s , Near kolhapure circle , beside Maheswari blind school ,

Nehru nager , belgaum-10
 
* Exists and Not Exists
Exists checks whether any row is existing in the subquery, NOT
EXISTS is opposite. Exists does not compare values of columns instead
it checks any row is retrieved from subquery.
* Any and all operators: -
All specifies that al the values given in the list should be taken
into account. Any specifies any of condition should satisfy.
Select sno, sname, totalfee, feepaid
From students where
Feepaid > any (select feepaid from students);

INDEXING AND CLUSTERING

Indexing arranges the database in ascending order. Indexes are
used for two purposes
- To speed update retrieval
- To enforce uniqueness
CREATE [UNIQUE] INDEX index_name
ON table (column1, [column2]-------);
Unique key word is used to enforce uniqueness index can have
up to 16 columns.
Eg.1) create index stud_sname
On student (sname);
Indexes are completely automatic. The index is refered when
where clause of select contains a column on which indexing is done.

- Dropping index
[DROP INDEX indexname;]
Clustering: - Clustering is a method of storing tables, that are often
used together (joined), in one are of the disk to improve performance.

NICT Computer’s , Near kolhapure circle , beside Maheswari blind school ,

Nehru nager , belgaum-10
 
- A cluster is created with a name and a key. The key is the
column which must be present in each table that is placed in the
cluster. The key is also called as cluster key.
- Following steps required to create a cluster and place tables into it
1) Create a cluster
2) Create a index on cluster
3) Place the table into cluster

1) Create Cluster clustername

(column datatype, [-------]-----);
eg. Create cluster cl_subcode
(subcode varchar2(10));
2) Creating cluster index
create index cl_subcode_index
on cluster cl_subcode;
3)Placing table into a cluster
create table tablename
(
--------------
--------------
)
cluster cl_subcode (subcode);

Sequence: -
Sequence is a database object which is used by multiple users to
generate unique numbers.
Create Sequence sequencename
[Increment by integer]
[start with integer]
[maxvalue integer : Nomaxvalue]
[Minvalue integer : Nominvalue]

NICT Computer’s , Near kolhapure circle , beside Maheswari blind school ,

Nehru nager , belgaum-10
 
[Cycle : Nocycle] cache2;

Pseudo Columns: -
Currval -> returns current value of a sequence.
Nextval-> returns next value of a sequence.
Row ID-> returns rowid of a row.
User -> returns name of current user.
Select seq. nextval from dual;

SECURITY
Privilege: - A privilege is a right to access an object such as a table
view etc.

Privilege Meaning
1) Alter Changing the definition of the
object
2) Delete Deleting rows from the object
3) Execute Execute the object
4) Index Creating index on the object
5) Insert Inserting rows into object
6) References References the object
7) Select Selecting rows from object
8) Update Updating data of object

Object Privileges Available

Table 7,5,1,2,4,5,6,8
View Select, insert, update, delete
Sequence Select, Alter

Procedure, Function, Package, Execute

Granting object Privileges: -

[Grant privilege on object]
To user public
The scott will enter.

NICT Computer’s , Near kolhapure circle , beside Maheswari blind school ,

Nehru nager , belgaum-10
 
Eg. Grant select on student to demo;
The user demo can select rows from student table by using
Select * from scott. Student;
* Creating Synonyms
create synonym stud for scott, student;
select * from stud;
* Restricting priviledge to certain columns: -
grant update (feepaid) on student to demo;
* With grant option
allow grantee to pass on the privileges to other users.
Eg. Grant select on student to demo with grant option;
Now demo can grant select privilege to other users
* Revoking object privileges: -
Revoke select on student from demo;

Report Generation using SQL commands

Consider table with subcode, sno, sname, stdate, nod, marks.
- Generate report to display subject wise list & with ang & sum
rem **** report subject wise ****
set numformat 99999
set pagesize 55
set linesize 75
set feedback off
clear breaks
clear computes
clear columns
clear screen
break on subcode skip 2
compute avg sum of marks on subcode
column subcode heading ‘subject / code’
column sno heading ‘student / number’

NICT Computer’s , Near kolhapure circle , beside Maheswari blind school ,

Nehru nager , belgaum-10
 
justify left
column sname heading ‘student / name’ format 20
column stdate heading ‘starting / date’
column nod heading ‘number / of days’ justify left
column marks heading ‘marks / obtained’ justify left
ttitle ‘subject report’

Spool st.1st
Select a.subcode, a.sno, sname, stdate, enddate-stdate, nod, marks
From studmarks a, students b
Where a.sno = b.sno
Order by subcode, sno;
Spool off;

Break Command: -
Used to break the rows selected by SELECT into different groups.

Compute Command: -
Used to compute & print summary information.
[Compute function of column on column;]
Function can be AVG, COUNT, MAX, MIN, STD, SUM, VAR.

NICT Computer’s , Near kolhapure circle , beside Maheswari blind school ,

Nehru nager , belgaum-10