Download Oracle Notes PDF PDF

TitleOracle Notes PDF
File Size639.6 KB
Total Pages32
Document Text Contents
Page 1

M o b . N o : 0 9 4 6 6 8 5 6 1 0 1 , E m a i l : - r l v . h c l @ g m a i l . c o m Page 1

Oracle 8i By:- R.L.Verma

Definations In Oracle
ATTRIBUTE(COLUMN):- IT REPRESENTS A PARTICULAR
CHARACTERSTIC OF A PARTICULAR ENTITY. E.Q. ROLLNO , NAME ,
AGE OF A STUDENT.

ENTITY (ROW) :- IT REPRESENTS A RECORD/AN INSTANCE OF A
PARTICULAR ENTITY.

CELL:- IT REPRESENTS VALUE/DATA ITEM OF AN ATTRIBUTE OF A
RECORD.

ENTITY SET (TABLE):- IT IS A COLLECTION OF MULTIPLE
ENTITIES/RECORDS/INSTANCE/ROWS . IT IS A 2-D STRUCTURE WHICH
IS COMPOSED OF ROWS AND COLUMNS WHERE EACH COLUMN
REPRESENTS A PARTICULAR CHARACTERSTIC AND EACH ROW
REPRESENTS A RECORD OF A PARTICULAR ENTITY.

DATABASE:- IT IS A COLLECTION OF TABLES RELEATED TO
PARTICUALAR SUBJECT.

DATABASE MANAGEMENT SYSTEM(DBMS):- IT IS A
SOFTWARE(SYSTEM) WHICH HELPS THE USER TO MANAGE THE
DATABASE IN AN EFFICIENT MANNER. IT ALLOWS THE USER TO
SELECT , INSERT , UPDATE AND DELETE RECORDS IN VARIOUS
TABLES. IT PROVIDES VARIOUS TYPES OF COMMANDS TO PERFORM
VARIOUS TYPES OF OPERATIONS. IT ALSO ALLOWS USER TO CREATE
VIEWS , PROCEDURES AND SO MANY OTHER THINGS SO THAT IT
BECOMES QUITE EASY FOR THE USER TO HANDLE THE DATABASE. IT
IS BASICALLY OF THREE TYPES :-
RELATIONAL (RDMBS)
HIERARCHIAL
NETWORK
RELATIONAL DATABASE MANAGEMENT SYSTEM(RDBMS):-
IT IS A TYPE OF DBMS IN THE CONECEPT OF RELATIONS IS USED . A
RELATION CAN BE IN BETWEEN ATTRIBUTES OF A SINGLE ENTITY OR
A RELATION CAN BE IN BETWEEN TWO OR MORE THAN ENTITIES
BOUND BY A COMMON COLUMN. ORACLE IS ALSO A RDBMS. OTHER
EAXMPLES OF ORACLE ARE :- MS-ACCESS , SQL SERVER ETC.

Page 2

M o b . N o : 0 9 4 6 6 8 5 6 1 0 1 , E m a i l : - r l v . h c l @ g m a i l . c o m Page 2

Oracle 8i By:- R.L.Verma


SQL(STRUCTURED QUERY LANGUAGE) :- IT IS A QUERY
LANGUAGE WHICH PROVIDES VARIOUS TYPES OF QUERIES TO
PERFORM VARIOUS TASKS. THREE TYPES OF SQL QUERIES ARE AS
FOLLOWS :-

DDL (DATA DEFINATION LANGUAGE) :- THIS TYPE OF QUERY
ALLOWS US TO CREATE AND ALTER VARIOUS OBJECTS LIKE TABLES
AND VIEWS. E.Q, CREATE TABLE , CREATE VIEW , ALTER TABLE ,
ALTER VIEW ETC.

DML (DATA MANIPULATION LANGUAGE) :- THIS TYPE OF QUERY
ALLOWS US TO SELECT , INSERT , UPDATE AND DELETE DATA FROM
VARIOUS TABLES.

DCL (DATA CONTROL LANGUAGE) :- THIS TYPE OF QUERY
ALLOWS TO ASSIGN AND REVOKE PERMISSIONS FROM USER FOR
VARIOUS OPERATIONS ON VARIOUS OBJECTS LIKE TABLE AND
VIEWS. E.Q, GRANT , REVOKE .

Page 16

M o b . N o : 0 9 4 6 6 8 5 6 1 0 1 , E m a i l : - r l v . h c l @ g m a i l . c o m Page 16

Oracle 8i By:- R.L.Verma


3.STRING FUNCTIONS:-

A.LOWER:- THIS FUNCTION CONVERTS ALL THE CHARACTERS OF A
STRING TO LOWER CASE.
EXAMPLE:-
SELECT NAME, LOWER(name) FROM student

B.UPPER:- THIS FUNCTION CONVERTS ALL THE CHARACTERS OF A
STRING TO UPPER CASE.
EXAMPLE:-
SELECT NAME, UPPER(name) FROM student

C.INITCAP:- THIS FUNCTION CONVERTS FIRST CHARACTER OF
STRING TO UPPERCASE AND THE REMAINING CHARACTERS OF A
STRING TO LOWERCASE.
EXAMPLE:-
SELECT NAME, INITCAP(name) FROM student

D.LENGTH:- THIS FUNCTION RETURNS TOTAL NO OF CHARACTERS
IN A STRING
EXAMPLE:-
SELECT NAME, LENGTH(name) FROM student

E.LTRIM:- THIS REMOVES A PARTICULAR CHARACTER FROM THE
LEFT PART OF A STRING. IF THE CHARACTER TO BE REMOVED IS NOT
SPECIFIED , THEN BY DEFAULT , IT REMOVES SPACES.
EXAMPLE:-
i.SELECT NAME , LTRIM(NAME , 'a') FROM student
ii.SELECT LTRIM('aaaaabcdeaa' , 'a') FROM dual
iii.SELECT LTRIM(' ABC ') FROM DUAL

F.RTRIM:- THIS REMOVES A PARTICULAR CHARACTER FROM THE
RIGHT PART OF A STRING. IF THE CHARACTER TO BE REMOVED IS
NOT SPECIFIED , THEN BY DEFAULT , IT REMOVES SPACES.
EXAMPLE:-
i.SELECT name , RTRIM(name , 'a') FROM student
ii.SELECT RTRIM('aaaaabcdeaa' , 'a') FROM dual
iii.SELECT RTRIM(' abc ') FROM DUAL

Page 17

M o b . N o : 0 9 4 6 6 8 5 6 1 0 1 , E m a i l : - r l v . h c l @ g m a i l . c o m Page 17

Oracle 8i By:- R.L.Verma


G. LPAD:- THIS FUNCTION PADS THE STRING IN THE LEFT PART WITH
A CHARACTER SO THAT LENGTH OF STRING BECOMES EQUAL TO
SPECIFIED NO OF ALPHABETS. BY DEFAULT, STRING IS PADDED WITH
SPACES.
EXAMPLE:-
i.SELECT LPAD('abcdef',10,'*') FROM DUAL
ii.SELECT LPAD('abcdef',10) FROM DUAL
iii. SELECT LPAD(name,10,'-') FROM student

H. RPAD:- THIS FUNCTION PADS THE STRING IN RIGHT PART WITH A
CHARACTER SO THAT LENGTH OF STRING BECOMES EQUAL TO
SPECIFIED NO OF ALPHABETS. BY DEFAULT, STRING IS PADDED WITH
SPACES.
EXAMPLE:-
i.SELECT RPAD('abcdef',10,'*') FROM DUAL
ii.SELECT RPAD('abcdef',10) FROM DUAL
iii. SELECT RPAD(name,10,'-') FROM student


I.SUBSTR:- THIS FUNCTION RETURNS A PART OF
STRING(SUBSTRING). WE ALSO MENTION STARTING POSITION OF
SUBSTRING AND LENGTH OF SUBSTRING.
EXAMPLE:-
SELECT name,SUBSTR(name,2,3) FROM student

Page 31

M o b . N o : 0 9 4 6 6 8 5 6 1 0 1 , E m a i l : - r l v . h c l @ g m a i l . c o m Page 31

Oracle 8i By:- R.L.Verma

SEQUENCES:-

SEQUENCE IS AN OBJECT THAT CAN GENERATE NUMERIC VALUES.
SEQUENCES ARE USED WHILE INSERTING DATA IN A COLUMN OF
TABLE. IT AUTOMATICALLY INCREMENTS A VALUES IN A COLUMN.
IT ALSO PROVIDES THE OPTION TO INSERT ONLY UNIQUE VALUES.
BY USING SEQUENCES , WE CAN ALSO MENTION MINIMUM VALUE
AND MAXIMUM VALUE ENTERED INSIDE A COLUMN.

SYNTAX:-
CREATE SEQUENCE seq_name
[ INCREMENT BY value
START WITH value
MINVALUE value / NOMINVALUE
MAXVALUE value / NOMAXVALUE
CYCLE/NOCYCLE
CACHE value /NOCACHE]

DESCRIPTION:-

INCREMENT BY:- IT SPECIFIES THE INTERVAL BETWEEN TWO
SEQUENCE NUMBERS.

MINVALUE:- IT SPECIFIES MINIMUM SEQUENCE NUMBER. IF WE
DON’T WANT TO SPECIFY ANY MINIMUM VALUE THEN ,
‘NOMINVALUE’ CLAUSE IS USED.

MAXVALUE:- IT SPECIFIES MAXIMUM SEQUENCE NUMBER. IF WE
DON’T WANT TO SPECIFY ANY MAXIMUM VALUE THEN ,
‘NOMAXVALUE’ CLAUSE IS USED.

CYCLE / NOCYCLE:- IT SPECIFIES WHETHER SEQUENCE NUMBERS
ARE REPEATED AFTER MAXIMUM VALUE IS ENTERED.

CACHE:- IT SPECIFIES HOW MANY SEQUENCE NUMBERS ARE PRE-
ALLOCATED IN MEMORY FOR FASTER ACCESS. IT WE DO NOT WANT
TO PRE-ALLOCATE ANY SEQUENCE NUMBER , THEN ‘NOCACHE’
CLAUSE IS USED.

Page 32

M o b . N o : 0 9 4 6 6 8 5 6 1 0 1 , E m a i l : - r l v . h c l @ g m a i l . c o m Page 32

Oracle 8i By:- R.L.Verma



EXAMPLE(TO CREATE A SEQUENCE):-
CREATE SEQUENCE seq_rn INCREMENT BY 1 START WITH 8 MINVALUE
1 MAXVALUE 10 CYCLE CACHE 2

EXAMPLE(TO INSERT A VALUE IN A TABLE USING SEQUENCE):-
INSERT INTO student VALUES(seq_rn.NEXTVAL , 'a')

EXAMPLE(TO ALTER A SEQUENCE):-
ALTER SEQUENCE seq_rn INCREMENT BY 2 MINVALUE 1 MAXVALUE
20

EXAMPLE(TO DROP A SEQUENCE):-
DROP SEQUENCE seq_rn

Similer Documents