Oracle Theory

What is Database?
Introduction to flat file system and need of DBMS
Data abstraction
Data independence
Query language - DDL and DML
Architecture of DBMS
Users of DBMS
Modeling Techniques
RDBMS Concepts
Codd's Rules

In RDBMS all the user requests must be routed through the RDBMS engine only. Direct calls for data cannot be made to the tables themselves. Hence there is a single point of control when it comes to data manipulation, this gives rise to excellent data security.
In a single user environment only one usr can manipulate data in a table through the oracle DBA. No other user can share the data in the table. In multi user environment the request goes to machine where RDBMS engine is loaded. This machine responds to multiple users and hence called as Oracle Server.
Conceptually, oracle is a kernel package that has a number of tools that can be purchased separately and integrated with the kernel as "Addons". These tools allows the user to create database objects, forms, reports, graphs etc. Some of the tools of Oracle are SQL *plus, Oracle Forms, Oracle Report writer, Oracle Graphics etc.
SQL *plus is made up of two distinct parts
Interactive SQL : designed to create access and maintain all data structures like tables, indexes et.
PL/SQL :- To create programs for validation and manipulation of table data.
Oracle Forms : Allows user to create a data entry screen along with suitable menu objects.
Report Writer : Allows user to prepare innovative reports using data from tables.
Oracle Graphics : Allows user to create graphs by using data from tables.
The user or DBA can communicate with oracle using ANSI SQL, the natural language for Oracle. There are two methods through one can construct SQL statements.
1. Use SQL Editor, who is automatically invoked and running while the SQL prompt, is on the screen.
2. Invoke Window's full page Notepad Editor.
Data types in Oracle
Char : It accepts fixed length character strings of maximum length 255 characters.
Varchar2 : values of this data type are variable length character strings of maximum length 2000.
Number : It is used to store numbers (fixed or floating point)
Date : DD-MON-YY format
Long : It is used to store arrays of binary data in ASCII Format.
Raw : To store Images and graphics up to 1 GB
Long Raw : To store Images and graphics up to 2 GB
NOTEPAD EDITOR FOR ORACLE
Select * From Emp;
Save File1 - File1 is the Filename File1.Sql
Get File1 - To Get Back the File which is Stored using save.
/ : To Execute the Get File
Edit File1 : To Edit File1.Sql
To Overwrite the Existing File
Save Filename Replace
Save file1 replace
Query Language
DDL : Create Alter Drop Grant Revoke
DML : Insert Update Delete Select Lock
DCL : Commit Rollback
Data Definition Language
Create
Syntax for create : Create <object type> <object name>
1. Creating Fresh New Table
Create table <table name>
(Column1 column1_datatype(size),
Column2 column2_datatype(size),
Column3 column3_datatype(size));
e.g.
CREATE TABLE TRIAL1
(ROLLNO NUMBER(2),
NAME VARCHAR2(20),
AGE NUMBER(2));
Creating Table from an existing table
Create table <table name>
(column1, column2)
As select columnname, columnname from table name;
e.g.
CREATE TABLE TRIAL1 (CUSTNO,CUSTNAME) AS
SELECT EMPNO,ENAME FROM EMP where 1=2;
Drop (Permanent deletion of table structure along with data)
Drop Table <table name>;
e.g.
drop table trial1;
Alter
Using Alter command one can not do the following things
Changing the name of the table
Changing the name of the column
Dropping a column
Decrease the size of a column - if table data exists
With alter command following two clauses are available
1. Alter Table <Table Name>
Add (newcolumnname datatype(size), newcolumnname datatype(size));
Alter table student
Add(stud_city varchar2(20),
B_date date);
2. Alter Table <Table Name>
Modify (existingcolumnname newdatatype(newsize));
Alter table student
Modify(name char(30));
Grant
Grant <List of Access> Grant insert
On <List of Tables> on chintu
To <List of Users>; to demo;
Revoke
Revoke <List of Access> revoke insert
On <List of Tables> on chintu
From <List of Users>; from demo;
Data Manipulation Language
Insert
Inserting a single complete row
Insert into <Table Name> Values (expression, expression);
INSERT INTO DEPT VALUES(50,'PURCHASE','MUMBAI');
Inserting a single row with specific values
Insert into <Table Name> (columnname, columnname) values (expression, expression);
Insert into dept (dname,deptno) values ('sales',60);
Inserting data into a table from other table
Insert into <Table Name> select columnname, columnname from Table Name;
Update
Update <Table Name> set (columnname = expression, columnname = expression) where columnname = expression;
Where clause is used to update specific row or rows which are following the condition. Update without where clause modifies all records.
Delete
Delete from <Table Name>; To delete all records
Delete from <Table Name> where columnname = expression; To delete specific records following criteria.
Select
Select <required things> from <table name> condition clause/s;
*
And
Or
Not Selected Columns
Where Clause
Column alliesing Elimination of duplicates : Distinct
Sorting : Order By
Range searching : Between, In
Pattern Matching : Like, _, %
SQL*Plus bind and substitution variables.
1. SELECT &columns
FROM emp;
2. SELECT ename,sal
FROM emp
WHERE &where_clause;
3. VARIABLE where_clause VARCHAR2(100)
BEGIN
:where_clause := 'sal > 1000';
END;
/
PRINT :where_clause
Integrity Constraints
These constraints are given a constraint name and the DBA stores the constraints with its name and instructions internally along with the cell (Column) itself.
Column level Constraints : It can be applied to any one column at a time i.e. they are local to a specific column. If the constraint spans across multiple columns, the user will have to use table level constraints
Table level Constraints : If the data constraint attached to a specific cell in a table references the contents or another cell in the table then the user will have to use table level constraints. Table level constraints are stored as a part of the global table definition.
Not Null
Create table umesh (roll_no number(3) not null, name varchar2(10));
Primary Key
Create table umesh (roll_no number(3) Primary key, name varchar2(10));
Unique
Create table umesh (roll_no number(3) not null, name varchar2(10) unique);
Default
Create table umesh (roll_no number(3) not null, city varchar2(20) default 'Pune');
Foreign Key / References
Primary Table (Subject)
Create table sub (sub_no number(3) not null, name varchar2(10));
Foreign Table (Exam Result)
Create table result (exam_no number(2) primary key, sub_no references sub, marks number(3));
Create table result (exam_no number(2) primary key, sub_no number(3), marks number(3), foreign key (sub_no) references sub);
Check
Create table result (exam_no number(2) primary key, sub_no references sub, marks number(3) check (marks <= 100));
Defining integrity constraint using Alter Command
Alter Table umesh add Primary Key (roll_no);
Alter table result add constraint exam_fk foreign key (sub_no) references sub;
Dropping Integrity constraint using Alter Command
Alter table umesh Drop Primary key;
Alter table result Drop constraint exam_fk;

Integrity constraints Demo
create table trial
(rollno number(2) primary key,
name varchar2(30) not null,
address varchar2(50) unique,
city varchar2(20) default 'Pune',
age number(2) check (age>=21));
Check constraint violated
insert into trial values(10,'india','narayan peth','mumbai',20);
Not enough values as city value is missing
insert into trial values(10,'india','narayan peth',22);
Unique constraint voilated - either primary key duplication or unique constraint voilated for address.
insert into trial values(10,'india','narayan peth','mumbai',22);
can not insert null into name
insert into trial (rollno,age,name) values(20,30)
insert into trial (rollno,age,name) values(20,30,'mouse');
insert into trial (age,name) values(40,'pen');
Oracle Functions
Arithmetic / Mathematical Functions
ASCII(char) select ascii('a') from dual;
CEIL( )
FLOOR( )
MOD(13,2) FROM DUAL;
ABS(N)
COS( X) : X IS ANGLE EXPRESSED IN RADIANS
SELECT COS(0) FROM DUAL;
COSH(X) : RETURNS HPERBOLIC COSINE OF X
SELECT COSH(0) FROM DUAL;
SIN(X), SINH(X)
TAN(X), TANH(X)
TRUNC(X,Y) : RETURNS X TRUCTATED TO Y DECIMAL PLACES
SELECT TRUNC(-123.456), TRUNC(-123.456,2) FROM DUAL;
EXP(X) : RETURNS e RAISED TO THE Xth power. E=2.718
Select exp(1) from dual;
LN(X) : RETURNS NATURAL LOGARITHM OF X. X MUST BE GREATER THAN ZERO.
SELECT LN(100) FROM DUAL;
LOG(X,Y) : RETURNS THE LOGARITHM BASE X, OF Y
SELECT LOG(2,32) FROM DUAL;
POWER(M,N)
SELECT POWER(BASE,INDEX) FROM DUAL;
ROUND(M,N)
SELECT ROUND(NUMBER,DECIMAL DEGITS) FROM DUAL;
SELECT ROUND(23.7475,3) FROM DUAL;
SQRT(N)
SELECT SQRT(675) FROM DUAL;
Group Functions
Avg( )
MIN( )
COUNT( )
MAX( )
SUM( )
GLB( ) : RETURNS THE GREATEST LOWER BOUND OF LABEL.
LUB ( ) : RETURNS THE LEAST UPPER BOUND OF LABEL.
Character Functions
Concat( )
Select concat ('hello ' , 'world') from dual;
Chr(n)
LOWER(CHAR)
SELECT LOWER(ENAME) FROM EMP;
Nls_lower( )
Select nls_lower('UMESH PATWARDHAN', 'nls_sort = Xgerman') from dual;
UPPER(CHAR)
SELECT UPPER(ENAME) FROM EMP;
Nls_upper( )
Select nls_upper ('umesh patwardhan', 'nls_sort = Xgerman') from dual;
INITCAP(CHAR)
SELECT INITCAP(ENAME) FROM EMP;
Nls_initcap( ) returns string with the first character of each word capitalised and the remaining characters of each world in lowercase.
Select nls_initcap('umesh patwardhan', 'nls_sort = Xdutch') from dual;
LENGTH(CHAR)
LTRIM(CHAR), RTRIM(CHAR)
LPAD( ), RPAD( )
SELECT LPAD(PADDING STRING,TOTAL STRING LENGTH,'PAD');
SELECT LPAD(ENAME,8,'*') FROM EMP;
SELECT LPAD('UMESH',25,'*') FROM DUAL;
Replace ( )
Replace (string,search_str[replace_str])
Select replace ('This and That', 'Th', 'M') from dual;
Soundex(string)
Returns the phonetic representation string. This is useful for comparing words that are spelled differently but sound alike.
Select ename, soundex(ename) from emp;
e.g. Blake & Black : B420
Substr( )
SELECT SUBSTR(STRING,START WITH, NO OF CHARS);
Select substr('Patwardhan', 3,3) from dual;
Translate( ) : similar to replace( )
Translate (string,from_str,to_str)
Select translate ('Umesh Patwardhan', 'Umesh', 'Yogesh') from dual;
Nlssort( ) : Returns the string of bytes used to sort string. All character values are converted into bytes.
Select nlssort('Scott') from dual;
Conversion Functions
TO_NUMBER(CHAR) TO_DATE(CHAR) TO_CHAR(N)
Other Functions
Decode ( ) : it is similar to a series of nested IF-THEN-ELSE Statements.
Select decode ('umesh', 'me', 1, 'um', 2, 'umesh', 3, 'mesh',4,-1) from dual;
Dump ( ) : Information about the internal expression.
Select ename, dump(ename) from emp;
Greatest ( )
Select greatest (10,30,12) from dual;
Least ( )
Select least (10,30,12) from dual;
Uid : Select uid from dual;
USER : SELECT USER FROM DUAL;
USERENV ( ) : SELECT USERENV('LANGUAGE') FROM DUAL;
SELECT USERENV('TERMINAL') FROM DUAL;
NVL ( )
Select comm, nvl(comm,0) from emp;

DATE FUNCTIONS
SELECT SYSDATE FROM DUAL;
SELECT ADD_MONTHS(SYSDATE,3) FROM DUAL;
SELECT round(MONTHS_BETWEEN(SYSDATE,'2-JAN-2000'),0) FROM DUAL;
SELECT LAST_DAY(SYSDATE) FROM DUAL;
SELECT NEXT_DAY(SYSDATE,'sunday') FROM DUAL;
DATE FORMATING
SELECT HIREDATE, TO_CHAR (HIREDATE, 'century') FROM EMP; - 20
Select sysdate, to_char(sysdate,'yyyy') from dual;
YYYY - YEAR DIGITS - 1981
YEAR - YEAR IN WORDS - TWO THOUSAND ONE
Q - QUARTER OF THE YEAR
MONTH - JUNE
MON - JUN
MM - 06
DAY - FRIDAY
DY - FRI
DDD - 163 (DAY OF YEAR)
DD - 12 (DAY OF THE MONTH)
D - 6 (DAY OF THE WEEK) - SUNDAY=1
DDSP - SPELLING

CONCATINATION ||
SELECT "MY NAME IS " || ENAME FROM EMP;
IS NULL
IS NOT NULL
GROUP BY .... HAVING
SELECT DEPTNO,JOB,SUM(SAL)
FROM EMP
GROUP BY DEPTNO,JOB;
SELECT DEPTNO, COUNT(*)
FROM EMP
GROUP BY DEPTNO HAVING COUNT(*) > 4;

Joins : TO DISPLAY DATA FROM MULTIPLE TABLES
1. EQUI JOIN
SELECT ENAME,JOB,LOC FROM EMP,DEPT
WHERE EMP.DEPTNO=DEPT.DEPTNO
ORDER BY LOC;
SELECT D.*, E.*
FROM EMP E, DEPT D
where JOB='MANAGER' AND E.DEPTNO=D.DEPTNO;
2. CARTETION JOIN (WITHOUT JOIN CONDITION) - MULTIPLE AVAILABLE CONDITIONS
select ename,dname from emp,dept;
3. NON EQUIJOIN
SELECT GRADE,ENAME,JOB,SAL
FROM EMP,SALGRADE
WHERE SAL BETWEEN LOSAL AND HISAL;
4. SELF JOIN
SELECT E.ENAME "EMPLOYEE" ,S.ENAME "MANAGER"
FROM EMP E, EMP S
WHERE E.MGR=S.EMPNO;

Relational ALGEBRA
UNION - COMMON AND UNCOMMON RECORDS FROM BOTH FILES
SELECT SALESMAN_NO, NAME
FROM SALESMAN_MASTER
WHERE CITY='PUNE'
UNION / intersect
SELECT CLIENT_NO, NAME
FROM CLIENT_MASTER
WHERE CITY='PUNE;
INTERSECT - COMMON RECORDS IN BOTH
MINUS :- SUPERSET - SUBSET

Sub queries
Select loc from dept where deptno = (select deptno from emp where ename = 'JAMES');
All Any Exist Clauses for sub query
Exists : Returns Boolean value .T. or .F. for the outer query
Select deptno from emp where deptno exists (select deptno from dept);
SELECT DEPTNO, JOB, AVG(SAL*12) FROM EMP GROUP BY DEPTNO, JOB;
Any : lowest value from inner query
Select * from emp where sal > any
(select sal from emp where deptno=10);
All : Largest Value from inner query
Select * from emp where sal > all
(select sal from emp where deptno=20);
Indexes :
SIMPLE INDEX
CREATE INDEX INDEXFILENAME create index i1
ON TABLENAME(COLUMNNAME); on emp(ename);
COMPOSITE INDEX
CREATE INDEX INDEXFILENAME
ON TABLENAME(COLUMNNAME1, COLUMNNAME2);
TO ENFORCE UNIQUNESS
CREATE UNIQUE INDEX INDEXFILENAME
ON TABLENAME(COLUMNNAME);
DROP INDEX INDEXFILENAME;

VIEWS - VIRTUAL TABLE
CREATE VIEW VIEWNAME
AS (SELECT.......)
Create or replace view v1 as select sal,ename from emp;
CREATE VIEW V1 AS SELECT SAL,JOB FROM EMP;
Creating view with errors : Force option
Create force view v2 as select * from india;
In above example table india does now exist so view will get created with compilation errors. If we create the table india now, the query of an invalid view can be now executed and the view is automatically recompiled and becomes valid i.e. usable view.
One can use functions e.g. sum(), Max() at the time of creation of views.
Update view v1 set sal=100 where job='MANAGER';
SELECT * FROM VIEWNAME;
SELECT * FROM V1 WHERE JOB='MANAGER';
DROP VIEW VIEWNAME;
DROP VIEW V1;
Sequence : Automatic generation of numeric value. Maximum value is up to 38 digits. It can have ascending or descending order.
Create sequence <sequencename>
Increment by <integervalue>
Start with <integervalue>
Maxvalue <integervalue>
Minvalue <integervalue>
Cycle/nocycle;
By default no cycle.
Create sequence trial
Increment by 1
Start with 1
Maxvalue 100
Cycle ;
Once defined, the user must be able to access a unique sequence number. This can be done by using select statement.
To refer next value : select sequence_name.nextval from dual;
To refer current value : select sequence_name.currval from dual;
Usage of sequence
Insert into sales_order (orderno, order_ddt, client_no) Values (order_seq.nextval, sysdate, 'C001');
Altering Sequence
Alter sequence trial
Increment by 2
Maxvalue 300;
Dropping Sequence
Drop sequence <sequence_name>;
Synonym : It is a database object which is used as an alternative name for a table. It essentially renames the table reference, similar to an alias for a selected list item. However, a synonym is a data dictionary object and is created by following statement.
Create synonym backup_students For student;
Grant all on backup_student to demo;
SQL Plus : Report Generation
Ttitle : sets the top title
TTITLE CENTER 'SAMPLE REPORT' SKIP 2
BTITLE : SETS BOTTOM TITLE
BTITLE RIGHT'CONTINUE' SKIP 1
SKIP : SKIPS AS MANY BLANK LINES
SET PAUSE : Makes screen display stop in between pages of display.
SET PAUSE 'MORE ….'
SET PAUSE ON
SELECT ENAME,DNAME FROM EMP,DEPT;
COLUMN : Provides variety of instruction on the heading, format and treatment of the column
COLUMN EMPNO FORMAT A25 JUSTIFY CENTER
COLUMN EMPNO HEADING 'EMPLOYEE NUMBER'
SELECT EMPNO, ENAME FROM EMP;
SQL.PNO : DISPLAYS PAGE NUMBER
BREAK ON : tells where to put spaces between section of a report or where to break for subtotals and totals.
BREAK ON DEPTNO
SELECT DEPTNO, SUM(SAL) FROM EMP GROUP BY DEPTNO;