Introduction of database

Database:

Database is a collection of data ( information).

Ex :

which we use in daily life. Attendance register, telephone directory etc..

DBMS: (DATABASE MANAGEMENT SYSTEM )

DBMS is a collection of programs written to manage a database. It acts as a interface b/w

user and database

RDBMS: (RELATIONAL DATABASE SYSTEM)

RDBMS is a DBMS that is based on the relational model as introduced by E.F.Codd.

Oracle is a RDBMS ,it one of the mostly trusted and widely used RDBMS currently.

Versions= > 8i

9i i => internet version

10g g => grid version

11g c => cloud version

12c

Oracle database contains logical and physical structure. Logical structure as table spaces ,
schema objects . Physical structure as data files, redo log files and control files.

In oracle all the information is stored in forms of tables. A table consists of rows and columns.

Oracle datatype:
In oracle it is mandatory to specify the datatype for columns.

Char :

it allows alpha numeric values .

It can store upto 2000 bytes of characters.

Char is a fixed data length, when we specify some length under char function it occupies the
memory irrespective entered data .
VARCHAR:

it allows alpha numeric values.

It can store upto 2000 bytes of chatacters.

If we declare varchar then it will occupy space for null values.

When we use varchar the memory allocation is dynamic , wastage of memory is not there.

VARCHAR2:

It allows alpha numeric values.

It can store upto 4000 bytes of characters.

This wil not occupies spacefor null values. memory allocation is dynamic. Memory wasted is not
there.

NUMBER:

It allows only numeric values. We can declare upto 38 digits .

In this we can specify precision and scale .

Ex : 236.15

DATE:

The date datatype stores point – in – time values .

It is used to stores dates in oracle data format

Syn :

columnname date

Note:

In oracle by default date format is DD-MON-YY

TIMESTAMP:

LONG:

It is used to store information enter

Maximum size is 2GB

Only once we can use in entire table , repetition of long is not possible in one table.

RAW:

It is used store images.

Maximum size is 200 bytes.

LONG RAW:

We can store information and images.

Max size is 2 GB
we can use only once In entire table. Repetition is not possible in one table.

LOB:( LARGE BJECTS)

CLOB (Character lob):

To store huge information.

Max size is 4 GB.

BLOB(Binary lob):

To store images but in the form of binary , Max size is 4 GB.

Bfile:

To store the files. Max size is 4 GB.

SQL STATEMENTS
Sql statements are categorized into 5 different languages.

DDL ( Data definition language):

1.CREATE:

It is used to create a table.

Syn:
create table table_name( column1 datatype(size), column2 datatype(size)…………….);
EX:
create table emp (empnovarchar2(10),empname varchar2(10),salary number(10),……);

2.ALTER:

Alter is used to add a column, modify a column, rename a column, drop (or) delete a
column.
I.ADD:

To add a new column into the table.

Syn:

alter table table_name add column_namedatatype(size);

EX:

table emp add enamevarchar2(10);

We can add n no.of columns at a time.

II.MODIFY:

To modify the existed column of table

We can modify the data type or size.

We can modify n no.of columns at a time.

Syn:

alter table table_name modify column_namedatatype(size);

EX:

alter table emp modify salary varchar2(10);

III.DROP:

To delete or drop the columns of a table.

We can delete n no.of columns at a time.

Syn:

alter table table_name drop column column_name ;

Ex:

alter table emp drop column empname;

IV.RENAME:

To rename the column of a table.

We can’t rename more than one column at a time.

Syn:

alter table table_name rename column old column name to new column name;
Ex:

alter table emp rename column salary to sal;

3. DROP:

It is used to drop the database objects from oracle data base.

When we use drop command both structure and data of the table get delete.

Syn:

drop table table_name;

Ex:

drop table emp;

4. RENAME:

It is used to rename the table name.

Syn:

rename old name to new name;

Ex:

rename emp to employee;

5. Truncate :

It is used to delete the content or data of a table.

When we use truncate only data of a table get delete, the structure should remain.

Syn:

truncate table table_name;

Ex:

truncate table emp;

2.DML (data manipulation language): Insert, update, delete

I. INSERT :

Insert is used to insert the data into columns of a table.

There are several ways to insert the data.

DIRECT METHOD:

In this method we provide values directly by addressing the table.

Syn:

insert into table_name values (val1,val2,val3,….);

Ex:

insert into empvalues(01,’Prakash’,3000);

Note:

To insert the data for specific columns we use following method.

Syn:

insert into table_name (column_name) values (value);

Ex:

insert into emp (empname) values (hanuma);

REFERENCE METHOD:

In this method we can enter or insert multiple rows of data at a time. We provide values
through prompt.

Syn:

insert into table_name values (&col1,&col2,…);

Ex:

insert into emp values (&eno,’&empname’);

SQL>eno: 01

SQL>empname: prakash

Note:

Oracle doesn’t permanent any DML operations until we commit it.

Note:

When we are specifying character data type we must specify the value in single quots.

II. UPDATE:

Update is used to update the existed data of the table based on where condition.
Ex:

update emp set sal=2000 where empid = 123;

Note:

We should use ‘is’ to compare null in where clause.

Ex:

update emp set sal = 2000 where empid is null;

III. DELETE:

Delete is used to delete the content of a table and delete records of the table.

Ex:

delete from emp; => To delete table content.

Delete from emp where ename = hanuma; => to delete particular record.

3. DRL (data retrieval language) : select

SELECT:

It is used to select the desired data from table.

We can mention specific columns or hold data in select clause and we can filter data by using where,
group by, having etc clauses

Ex:

select * from emp;

TCL (transactional control language) : commit,roll back, savepoint

TCL commands are used to validate the transactions.

COMMIT:

To make changes permanent.

We have to use commit command to make the DML operations permanent save.

Syn : commit;

ROLL BACK:

To roll back the changes done in transaction.

It restore the state of data base to the last commit point.

Syn: roll back;

SAVE POINT:

Specify the point of transaction to which later we can roll back.

Ex:

insert into emp (empno,sal) values (01,2000);

 save point A;

Ex:

insert into emp (empno,sal) values (02,3000);

 save point B;

Ex:

insert into emp (empno, sal) values (03, 4000);

Based on above example if I give roll back to A then the rows which we inserted for ’02,03’ will be
roll back.

At this point we can commit the row instead for 01 by using commit.

If I give roll back to b

The rows which we inserted for 03 will be roll backed at this point we can commit the rows
inserted for 01 and 02 by using commit.

DCL (data control language): Grant, revoke

DCL statements are the one of the sql statement types which enforce database security.

It is used to grant defined roll and access privileges to the users.

These are two types of DCL commands.

GRANT :

A DBA or user can grant access permission to other users on database using grant option.

Syn: grant [privilege]

ON [object]

to{ user | public | role }

Ex:

When a user is newly created in the data base DBA should grant below privileges to user in order
to connect to the database.

Syn : grant create session to Prakash;

 Grantconnectresource to Prakash;

Above privilege enables Prakash to connect the database.

Ex:

When user or owner of a table wants to give select permission to another user the following
can be used.

Syn:

grant select on emp to Prakash;

Privileges are the permission name, which is granted to the user, it may select, insert, update,
delete, execute.

REVOKE:

The revoke command is used to take back the existing privilege from a user.

It only DBA or a user with admin option can revoke system privilege.

Syn: : revoke [privilege]

ON [object]

from { user | public | role }

Ex: revoke select on emp from martin;

DUAL TABLE:

Dual is dummy table in the oracle database.

It is one column, one row table which contains the value of X.

It contains the datatype varchar2 (1)

We can use dual table in select clause for performing any operations such as

Select sysdate from dual;

OPERATORS IN SQL
1 ARITHMETIC OPERATORS: ( +,-,*,/ )

We can use arithmetic operators for numeric evaluation purpose.

Ex:

select sal/2 from emp;

2 LOGICAL OPERATORS: AND, OR, NOT:

Logical operators are used to filter the data based on conditions such as ‘and, or, not’.

AND :

Select * from emp where deptno = 10 and sal> 1000;

OR :

Select * from emp where deptno = 10 or sal> 1000;

NOT :

Select * from emp where not deptno = 10; (or)

Select * from emp where deptno != 10;

3) RELATIONAL (OR) COMPARISION OPERATORS:

= à equal to

< àless than

> àgreater than

<= à less than equal to

>= à greater than equal to

!= à not equal to

<>à not equal to

Ex:

select * from emp where salary <> 2000;

4) SPECIAL OPERATORS: ( IS, IN, LIKE ( %, - ), NOT LIKE, ANY/SOME, ALL, BETWEEN, NOT BETWEEN

IS :

Is operator is similar to ‘=’ operator.’

It is used to handle the null values

Ex:

select * from emp where sal is null;

Note :

Null values must be use with operator ‘is’.

IN & NOT IN :

It is used compare in n number of values .

It is used to compare character, number and dates also

Ex :

select * from emp where sal in ( 1000, 2000, 10,000 );

Select * from emp where manager in (‘ram’, ‘martin’);

Select * from emp where sal not in (4000,5000);

BETWEEN & NOT BETWEEN:

It is provide range of values

Ex :

select * from emp where sal between 1000 and 3000;

Select * from emp where sal not between 4000 and 6000;

LIKE & NOT LIKE:

It is used to search for pattern searching or character comparision

% and – are the symbol s used with like operator.

% à for the wild search.

- àfor the single character or number

Ex:

select * from emp where ename like ‘ra%’;

Select * from emp where ename like ‘-------‘;

Select * from emp where ename like ‘%p%’;

ANY/SOME:

It compares a value with every value in a list, it must be processed by = ,!=,>,<,>=,<=.

Ex:

select * from emp where sal>= all (1400,3000);

5 SET OPERATORS:

Set operators are used to combine select statement

The pre requisite condition to set operator is datatype of the corresponding columns should be
same.

The result of each select statement can be treated of set and sql operators can be applied on those
sets.

Set operators can be called as virtual joins

The set operators as follows

1)union

2)unionall

3)intersect

4)minus

FUNCTIONs IN ORACLE

Functions are used to meet the business requirement. There are two types of functions

1) single row functions

2) multi row functions (or) Agreegate functions

1) Single Row Functions :

It works or executes from each of value. The following are the single row functions.

a) Number Functions

b) String Functions

c) Date Functions

d) Conversion Functions

e) General Functions.

a)NUMBER FUNCTIONS:

i)Power:

power(m,n)

itreturns the m raised to the n’th power .It multiply the m specified in n number of times.

EX: select power(3,3)from dual;

o/p ……>27

ii)sqrt :

it returns the squre root of specified value.

EX: select sqrt(25) from dual;

iii)mod(M,N):-

it returns remainder of m/n. It returns m if n is zero.It returns error if n is not specified.

EX:- select mod(10,3)mod(10,0)from dual;

o/p……..>1,10

iv)ABS (M):

it returns the absolute value of m

EX:- select Abs(-25) from dual:

o/p→ 25 …..>it always displays the positive value.

v)trunc:-

trunc function returns number truncated to certain number of decimal places.

The function returns n1 truncated to n2 decimal places.

If n2 is omitted, then n1 truncated to ‘0’ places. n2 can be negative to truncate (make zero) n2 digits
left of the decimal points.

Ex: trunc (125.815)

Output: 125

Trunc (125.815,0)

O/P : 125

Trunc (125.815,1)

O/P : 125.8

Trunc (125.815,2)

O/P : 125.81

Trunc (-125.815,2)

O/P: -125.81
Trunc (125.815,-1)

O/P : 120

Trunc (125.815,-2)

O/P : 100

vi) Round :

The round function returns a number rounded to a certain number of decimal places.

The function rounds to the nearest even value.

If parameter is omitted the function will round number to ‘0’ decimal places.

Ex:

select 15.999,round (15.9999),round (15.9999,2),round (15.5555,2),round (15.4899,2) from dual;

O/P : 15.999,16,15.56,15.49

vii) Sign :

It returns the sign of specified number.

If number is less than zero output is ‘-1’

If number is zero then output is ‘0’

If number is more than zero output is ‘1’

Ex:

select sign (-0.1),sign (0.1),sign (0) from dual;

O/P : -1,1,0

viii) Ceil :

It returns the smallest integer greater than or equal to m

The adjustment is done to the highest nearest decimal place.

Ex: select ceil (14.27) from dual;

O/P : 15

iX) Floor:

It returns the largest integer less than or equals to m, The adjustment should done to the lowest
nearest value.

Ex: select floor (14.27) from dual;

O/P : 14

Select floor (14.99) from dual;

O/P : 14

X) ASCII :

This function returns the number code for the specified number.

Ex: select ASCII (‘t’) from dual;

O/P: 116

Select ASCII (‘T’) from dual;

O/P: 84

Xi) CHR :

It returns the character based on number code

Ex: select chr (116) from dual;

O/P: ‘t’

Select chr (84) from dual;

O/P: ‘T’

Xii) Greatest : (exp1,exp2,…..,expn)

Greatest returns maximum value among given expressions.

Ex: select greatest (3,5,8,9) from dual;

O/P: 9

Xiii) Least: (exp1,exp2,…..,expn)

Least returns minimum value among given expressions.

Ex: select least (3,2,1,9) from dual;

O/P: 1

b. String functions:

i. Length:

Length function gives the length of character.

Ex: select length (‘Prakash’) from dual;

O/P: 7

ii. Reverse:

It is used to reverse the given string

Ex: select reverse (‘prakash’) from dual;

O/P:

iii. Upper:

It converts the given string to upper letters.

Iv: Lower:

It converts the given string to lower letters.

V. Initcap:

It converts the given string initial letter to upper letter and remaining characters to lower letter.

Vi. Concat:

It is used to join the two strings.

We can only join two strings.

Ex: select concat (‘praveen’, ‘kumar’) from dual;

O/P: Praveen Kumar

We have to use ‘||’ pipe symbol to join multiple strings.

Ex: select ‘EBS’ || ‘solutions’ || ‘for’ || ‘technical’ from dual;

O/P: EBS solutions for technical

Vii. Substr :

Substr function returns specified characters from character value starting from position m to n
characters.

Syn: substr( expression,m,n)

It returns the substring of string from ‘m’ ending to ‘n’

If m is zero it is treated as ‘1’

If m is positive oracle counts the beginning of string to find the first character.

If m is negative oracle counts back words from the end of the string

If n is omitted the oracle counts returns all characters from Mth to end of the string
If n is < 1 or ‘0’ null is returned

Ex: select substr( technologies ) from dual;

O/P: error

Select substr (‘technologies ‘ ,2,4) from dual;

O/P: echn

Select substr (‘technolofies’,2) from dual;

O/P: echnologies

Select substr (‘technologies’,-2) from dual;

O/P: es

Select substr (‘technologies’-4,2) from dual;

O/P: gi

Select substr (‘technologies’-4,-2) from dual;

O/P: null

Select substr (‘technologies’4,0) from dual;

O/P: null

Viii. Instr:

It returns the numeric position of the a named character.

Instr function searches the numeric position of string that is mention

It searches for expression beginning with mth character for nth occurrence and returns the numeric
position of substring.

m can be positive or negative.

If it is negative searches in backword direction from end of the expression.

N always be positive.

Default value of m and n are ‘1’.

If search is unsuccessful (if given pattern not found) output is zero.

Examples:

Select instr (‘orporatororders’ ‘or’) from dual;

O/P: 2
Select instr (‘orporatororders’ ‘or’,4) from dual;

O/P: 5

Select instr (‘orporatororders’ ‘or’,7) from dual;

O/P: 10

Select instr (‘orporatororders’ ‘or’,-1) from dual;

O/P: 17 [It started searching from back word direction and found or at 17 th position]

Select instr (‘orporatororders’ ‘or’,1,2) from dual;

O/P: 5 [It started searching from 1st character for the 2nd occurrence of or string find at 5th position]

iX. Trim:

It trims the leading or trailing or both characters from string

If we specify it trim only the leading characters

If we specify trailing it trim only the trailing characters

If we don’t specify leading /trailing/both, the trim function will remove characters from both front
and end of string

Syntax:

trim (leading | heading |both, ’character’ from ‘string’);

Trim (‘0’ from 002200);

O/P: 22

Trim (leading ‘0’ from 002200);

O/P: 2200

Trim (trailing ‘0’ from 002200);

O/P: 0022

Trim (both ‘0’ from 002200);

O/P: 22

X. Ltrim:

It removes the specified characters from left side of the specified string

Ex:

ltrim (‘xyz bellandurxyz’,’xyz’)

O/P: Bellandur xyz

Ltrim (‘0001230’,’0’)

O/P: 1230

Ltrim (‘6372tech1234’,’0123456789’)

O/P: tech1234

In the above example every number combination from 0-9 has been listed in the string parameter.

By doing this it doesnot only for order that appears the string, all leading number will be removed.

Xi. Rtrim:

It removes the specified characters from right of specified string.

Ex: ltrim (‘xyz bellandurxyz’,’xyz’)

O/P: xyz bellandur

Ltrim (‘0012300’,’0’)

O/P: 00123

Ltrim (‘6372tech1234’,’0123456789’)

O/P: 6372tech

Xii. Lpad:

The Lpad function pads (adds) the specified characters to the left side of the string.

Ex: Lpad (string, padded _length, ‘padding characters)

String à the string to pad characters

Padded_lengthà the numbers of characters to return

Padà this is the characters that will be characters padded to the left side of the string.

If we don’t specify any characters to be pad by default it will pad the spaces.

Ex: Lpad (‘ tech’,7);

O/P: ‘ tech’

Lpad (‘tech’,8,’0’);

O/P: 0000tech

Lpad (‘tech on the net’,15,’2’);

O/P:

Xiii. Rpad:

The Rpad function pads (adds) the specified characters to right of the specified string

Syn: Rpad (string, padded_length, padding characters);

Rules as per the Lpad functions

Ex: Rpad (‘tech’, 7);

O/P: tech

Rpad (‘tech’, 8, ‘0’);

O/P: ‘tech0000’

Rpad (‘tech on the net’, 16, ‘z’)

O/P: ‘tech on the netz’

Xiv. Translate:

It is used to translate character by character in a string

The function replaces a sequence of characters a string with another set of characters.

However it replaces character by character.

Syn: Translate ( string-name, string to replace, replacement string)

Ex: translate (‘1tech23’, ‘123’, ‘456’)

O/P: ‘4tech456

Translate (‘222tech’, ‘2ec’, ‘3it)

O/P: 333tith

XV. Replace:

This function replaces a sequence of characters in a string with another set of characters

Syntax: replace (string-name, ‘string to replace’, ‘replacement string’)

Note: Here replacement string is optional

If we don’t specify replacement string , the replace function simply remove all occurrences of string
and give us the result output

Ex: replace (‘222tech’, ‘222’, ‘333’)

O/P: 333tech2
Replace (‘123tech324’,’123’,’456’)

O/P: 456tech324

Replace (‘123123tech’, ‘123’)

O/P: Tech

C) Date functions:

i. Sysdate:

It displays the system date and time

We can use date functions by adding and subtracting values from a date function.

Ex: select sysdate from dual;

O/P: 28-oct-19

Select sysdate-1 from dual;

O/P: 27_oct-19

ii. Current date:

The current date function returns the current date in the time zone of current sql session as set
by the alter session command

Syn: select current_sate from dual;

O/P: 28-oct-19

Note: we have to alter the session and time zone to use current_date

iii. Add_months:

it is used to add or subtract months from given date.

Syn: select add_months (sysdate,3) from dual;

O/P: 28-jan-20

Select add_months (sysdate,-3) from dual;

O/P:
iV. Months-between:

It is used to display the number of the months between two dates.

Ex: select months-between ( sysdate, ’15-sep-19’ ) from dual;

O/P: 1
Select months-between (sysdate, hiredate) from dual;

O/P: 410.958

V. Next_day:

To display next_day based on the specified day

Ex: select next_day (sysdate, ‘monday’) from dual;

O/P: 29-oct-19

Select next_day (sysdate, ‘thrusday’) from dual;

O/P: 31-oct-19

Vi. Last_day:

To display the last day of the given month

Ex: select last_day (sysdate ) from dual;

O/P: 31-oct-19

D) Conversion functions:

i. To_char:

this function converts a number or date to string

Syx: to_char( value, desired format )

To_char with numbers:

To_char (1210.73, ‘9999.9’)

O/P: 1210.7

To_char (-1210.73, ‘9999.9’)

O/P: -1210.7

To_char (1210.73, ‘9999.99’)

O/P: 1210.73

To_char (1210.73, ‘9999.00’)

O/P: 1210.73

To_char (21, ‘000099’)

O/P: 000021
To_char with dates:

It converts the system format to user format

To-char (sysdate, ‘yyyy/mm/dd’)

O/P: 2010/03/10

To_char (sysdate, ‘month dd,yyyy’)

O/P: July 09,2003

To_char (sysdate, ‘fmmonth,yyyy’)

O/P: july9,2003

To_char (sysdate, ‘mon ddth, yyyy’)

O/P: Jul 9th,2003

To_char (sysdate, ‘fmmonddth,yy)

O/P: jul 9th, 03

Note:

In the above examples fm means format_mask parameter. It means the zeros and blanks are
suppressed

Parameter/Format Explanation

yyyy 4 digit year

yyy or yy or y last 3 or 2 or 1 digits

mm month (01-12)

mon abbreviated month (jan-dec)

month name of the month (January-December)

day name of day

dd day of month(1-31)

ddd day of year (1-31)

hh hour of day (1-12)

hh24 hour of day (0-23)

mi minutes (0-59)

ss (0-59)
year to write the complete year name

ii. to_date:

it is used to convert user format system format

Ex: to_date (‘2003/07/09’, ‘yyyy/mm/dd’)

O/P: sysdate format à 03-july-09

To_date (‘070903’, ‘mmddyy’)

O/P: sysdate format à 09-jul-03

To_date (‘20020315’, ‘yyyymmdd’) from dual;

O/P: sysdate format à 15-mar-02

To_date (‘2015/05/15 8:30:24, yyyy/mm/ddhh:mi:ss’)

O/P: 15-may-15

iii. To_number:

it is used to convert the string to number

Ex: select to_number (120.80) from dual;

O/P: 120.80

General functions:

User:

Select user from dual;

O/P: system

Uid:

Select uid from dual;

O/P: 5

NVL:

It is used to replace the null values of a column with given value

Syn: nvl (col_name, replacement value)

Ex: select nvl (sal,10000) from emp;

The above command replaces the null values of sal column with values of 10000
NVL2:

It is extended functionality of nvl function

It allows you to substitute a value when a null is encountered as well as not null values is
encountered

Syn: nvl2 (col_name, value_if_not_null, value_if_null);

Select nvl (sal, 10000, 20000) from emp;

The above command replaces null values sal column with 20000 and non-null value with 10000

Null if:

The null-if function compares expr1 and expr2

If expr1 and expr2 are equal it returns null, It expr1 and expr2 not equal it returns expr1

Syn: nullif (expr1, expr2)

Ex: select nullif (10,10) from dual;

O/P: null

Select nullif (20,10) from dual;

O/P: 20

Coalesce:

The coalesce function returns the first non-null expression in the list. If all expressions evaluate to
null then the coalesce function will return null.

Syn: coalesce (expr1,expr2,…..exprn);

Ex: coalesce (empname1, empname2, empname3) from emp;

In the above example if empname is not null it will display empname1

If empname1 is null, if empname2 is not-null it will display empname2

If empname1 and empname2 is null, if empname3 is not null it will display empname3;

If all nulls it will display null only.

Case:

Case statement used to implement logic as if-then-else statement

There are two types of case expressions

1. Simple case expression

2. Searched case expression
1.Simple case expression:

Syn: case expression when comparison_expr then

When comparison_expr then return_expr

When comparison_expr then return_expr

Else return_expr end

In the simple case expression oracle database searches the first when_then pair which is equal to
comparison_expr and returns return_expr

If none of the when_then pairs with comparison_expr it returns else return_expr

The maximum number of argument in a case expression is 255. All expression including optional else
expression and initial case expression. Each when and then pairs counts as two arguments

Note: If we don’t specify the else condition it compared expression

Example:

Select cust-last-name, case credit-limit

When 1000 then low

When 5000 then high

Else medium END

From emp;

O/P: alex high

Martin medium

Denis low

2.Searched case expression:

Case when condition then statement

When condition then statement

.
 Else statement

End

Here it searches of condition that is true and returns the expression

Case when empno in (1234,1235) and sal> 5000

Then “first grade”

When empno in (1236,1237) and sal< 5000

Then “second grade”

Else “third grade”

End;

Differences:

The simple case performance a simple equality check of expression against each of the when options

The searched case evaluates the conditions independently under each of the when options, more
complex conditions can be implemented with search case

A searched case can combine multiple tests using

Decode:

It has the functionality of if-then statement using decode function we can substitute value with
another value

Decode ( col_name, value, result, value, result, value,….. default value)

Here default value used to replace the other values with default value, If we don’t specify the default
value it will replaces the rest of values as null

Ex:

select decode (S-area, ‘bellandur’, ‘karnataka’, ‘rct’, ‘a.p’, ‘chennai’, ‘t.n’ , ‘rest of india’);

The above function replaces s-area column where Bellandur with Karnataka, Rct with A.P , Chennai
with T.N and other values with rest of India

Aggregate functions:

Aggregate functions returns a single row output based on group of rows

These functions can appear in select list and having clauses only

The functions operate on set of rows to give one result per each group

Aggregate functions can’t be performed on dual table

i.Avg:

It returns the average value of the column, It ignores the null values

Ex:

select Avg (sal) from emp;

ii.Sum:

It returns the sum value (total value) of a column

Ex:

select sum (sal) from emp;

iii.Max:

It returns the maximum value of the column

Ex:

select Max(sal) from emp;

iV.Min:

It returns the minimum value of the column

Ex:

select min(sal) from emp;

v. Std dev (standard deviation):

It returns the standard deviation of the column

Ex:

select Stddev(sal) from emp;

vi. Variance:

It returns the variance of the column

Ex:

select variance(sal) from emp;

vii. Count:

It returns the number of rows in column

If * is used it will return all rows including null values

If we specify the column name it counts the distinct values and eliminates null values
Ex: select count(*), count(sal) from emp;

viii. Distinct:

It returns the distinct rows of a columns, it eliminates the duplicate rows

Ex:

select distinct(sal) from emp;

ix. Greatest:

It returns the greatest of the value in the list of expression

It works on particular rows

Syn: greatest (col1,col2…..coln)

Ex:

if we have 7 students in a table and 6 different subjects, for every student if you want to find out
in which subject student has gotten the highest marks on this case we can use greatest function

S-name S1 S2 S3 S4 S5 S6

Prakash 84 86 88 75 68 82

Alex 75 87 90 95 90 80

Martin 96 97 95 94 93 92

Denis 90 89 95 92 91 97

Den mort 85 88 90 97 98 80

Select s-name, greatest (s1, s2, s3, s4, s5, s6) from new;

Prakash 88

Alex 95

Martin 97

Denis 97

Den mort 98

x. Least:

It returns the least value in the list of expressions

Ex:

select S-name, least (s1, s2, s3, s4, s5, s6) from new;
Prakash 68

Alex 75

Martin 92

Denis 89

Den mort 80

Difference between decode and case function:

Decode and case statements both give values based on condition, like it-then-else

àIn case function we can use logical all operators including =,<>,>,< and all other operators

But in decode function we cannot use any operators we can use equity check condition

àCase function can be used with subqueries in searchable from

In decode function we can’t use any within queries

àCase can work as a PL/SQL construct, but decode is used only in SQL statement

Can be used as parameter of a function/procedural

àCase expects datatype consistency and decode does not expect datatype consistency

Inserting data of other table into other table:

Insert into last-table (col-names) select (col-names) from source-table;

Ex: insert into customer (traded, value) select (ordered, value) from manager;

While inserting from one table to another table the data type should be same on both columns of
tables

CLAUSES IN ORACLE

Clauses are classified in to four types

1. Where clause:

It is used to limit the records

By providing conditions we can limit the records

Where clause doesn’t allow group by function in where condition

Ex:

select * from emp where sal> 1000;

2. Groupby clause:

The group by clause is used in the select statement to collect data from multiple records
to group the results that have matching values from one or more columns

Note: Group by clause must be used when we have aggregate function in select clause

Aggregate functions are like Min, Max, Count, Sum, Avgetc

Syn:

Select col1, col2,….coln, aggregate function (col1) from table-name where condition group by
col1, col2,….coln;

Ex:

select deptno, sum(sal) from emp where sal> 500 group by deptno;

O/P: deptno sum(sal)

Note:

1 Here where condition is optional to filter the records, the where clause should be preceded
by group by clause

2 all the columns used in select statement along with aggregate functions must be included in the
group by clause

3.Having clause:

It is used in the select statement to filter the data returned by the group by clause

We cannot use having clause without group by clause

Syn:

select col1, col2,…coln from table-name where condition group by col1, col2,……coln having
condition

Ex:

select deptno, max(sal) from emp where sal> 2000 group by deptno having max(sal) >3000;

Select deptno, max(sal) from emp where sal> 2000 group by deptno having deptno = 10;

Note:

Similar to group by function all the columns that we are specifying in condition with having
must be existed in select clause.
4.Order by clause:

The order by clause us used to sort the records from select statement in ascending or
descending order.

By default is sort in ascending order

Syn:

select col1, col2,…..coln from table-name where condition order by col1,col2,…..colnasc/desc;

Note:

If we omit ascending or descending from the syntax then default data will be sorted in
ascending order

Ex:

select ename, sal from emp where sal> 2000 order by empno, sal;

Here when we are specifying the column names in order by clause, instead of columns we can give
1,2,3,4,… numbers to sort the data

Ex:

select empname, deptno, sal from emp order by 2;

Here order by 2 specifies that 2nd column mentioned in the select list i.edeptno

Ex:

select empno, sal, mgr from emp order by saldesc;

ANALYTICAL FUNCTIONS IN ORACLE

The analytical functions in oracle helps us to analyze the data with various functions.

A) Rollup:

The rollupfunction is used to calculate multiple levels of subtotals across the specified group

Of dimensions.

It also calculate the grand total .It is the sample extension to group by clause.

EX:

select job,count(*)from emp by rollup(job);

In the above example the rollup function calculates the grand total of all count of jobs.
EX:

select deptno,job,count(*)from emp group by rollup(job,deptno);

In the above example the rollup function counted the total of all multiple sublevels of managers and
grand total.

B) Cube:-

The cube function is used to calculate subtotals for all possible combinations of group
dimensions.

EX:-

select deptno,job,count(*)from emp group by cube(job,deptno);

C) Rank:-

The rank function returns the rank of a value in group of values.

Note:

The rank function gives the non-consecutive ranking if the tested values are same.

The rank function can be used be in two ways

1) Aggregate Function.

2) Analytical Function.

1) Rank as Aggregate function :

The rank function returns the rank of a row within group of rows.

EX:

select rnk(1000)within group(orderbysal)from emp;

2) Rank as analytical function:-

Rank as analytical function returns the rank of the row by dividing other groups.

Syntax:

rank ()over([query-partition clause]orderby clause)

EX:-

select empno,ename,deptno,sal,rank()over(partition by deptnoorderbysaldesc)”sal_rr”from

emp;

in the above example ranking has been salaries of deptno by grouping.

If we see the ranking column the rank function has given the non consecutive rankings such as for
deptno 20 it has given as 1 1 3 and it skipped the second rank.

èwe can mention the where clause in the end to filter the desired records.

EX:

selectempno,ename,deptno,sal,rank()over (partition by deptnoorderbysaldesc)”sal_ra” from

emp where deptno=20;

èto get the consecutive rankingswe have to use the dense_rank function .

èwe can use the syntax without partition also.

Ex:-

select ename,sal,rank()over(order by saldesc)”sal_rank” from emp;

1) find the highest salry holder in earn department.

Select * from (select empno,sal,deptno,rank()over(partition by

deptnoorderbysaldesc)”sal_rank”fromemp) where “sal_rnk”=1;

D) Row_number:-

It works as same asrownums but it allows partition by nd order by clauses.

The syntax is similar to rank function .

Note:

It won’t depends on orderby clause to generate sequence of numbers.

èif record is same it simplify generate values in sequence.

EX:-

select sal,deptno,dense_rank()over(partition by deptnoorderbysaldesc)from emp;

CONSTRAINTS:
Constraints are the rules enforced on data columns on table.These are used to limit the type
of data that entering into the table.this ensures the accuracy and reliability of the data in the
database.

Constriants could be column level or table level ,column level constraints are applied only to
the column,where as table level constraints are to multiplecolumns at a time.

The following are the commonly used constraints in oracle

1) Notnull

2) Unique

3) Check

4) Default

5) Primarykey

6) Foreignkey

1) Notnull:-

By defining notnull constraint we can avoid null values to the column.

Notnull constraint allows duplicate values to the columns.

Syntax:-

constraint constraint_namenotnull; (or) notnull

2) unique:-

Unique constraint avoid duplicate values from columns of a table.it allows null values.

It allows n number of null values because two null values are not same.

Syntax:-

constraint constraint_name unique (or) unique

We can declare unique constraint in table level as well as column level.

column level

EX:-

create table emp(ename varchar2(10),deptno number(15)constraint con_un1 unique,sal

number(5)con_un2 unique);

tablelevel

create table emp(ename varchar2(10),deptno number(5)sal number(5),unique(deptno,number));

3) check:-

Check constraint is used to specify range of constraints or rules to the context of a table.

If the condition evaluates to false the record validates the constraint and is not entered into the
table.
Syntax:-

constraint constraint_name check(column conditions)

Or

Check(column condition)

EX:-

create table emp(ename varchar2(10),deptno number(5),sal number(6) check(sal

in(1200,2200)));

EX:-

create table emp(ename varchar2(10),deptno number(5),sal number(6) check(sal

Between 1200 and 2200));

Note:-

The check constraint allows null values it means though we keep condition as sal>2000,if we
enter null value it accepts the null value as null is unique value. We need to declare not null and
check both constraints to satisfy the condition as follows.

create table emp(ename varchar2(10),sal number(6) notnull check(sal>2000));

if we create check constraint in table level the constraint will be checked each time the row has been
alters by any type of change.

EX:-

create table emp(sno number not null,star_date date not null,end_date date not
null,joined_date

date,check(joined_date between start_date andend_date));

if we update the star_date or end_date which satisfy the condition the check constraint validation
users.

select * from emp;

start_dateend_datejoined_date

10-jan-1610-mar-16 09-feb-16

11-jan-1612-mar-16 10-mar-16

We try to update any of the columns which does not satisfy the condition constraint violation
triggers.
EX:-

update table emp set end_date=’10-mar-16’ where joined_date=’11-mar-16’;

error: ora-02290 check constraint(system sys-co4098)violated .

4) Default:-

The default constraint provides a default value to the column when the insert into statement
doesn’t provide a specific values.

This rule applicable only when we use the direct method while inserting the values.

Syn: default value

Ex:

crate table emp (stu_idnumber(9), sal number(9) default 90);

Insert into emp (stu_id) values (1234);

As we set value of 90 to sal column it will take 90 to the sal column as we set the default value
for this column.

To add the default constraint we can use only modify option to the alter statement.

Ex:

alter table emp modify stu_idnumber(9) default 10;

5. Primary Key:

The primary key constraint is a column of unique and not null constraints.

It ensures that null values and duplicate values shouldn’t be entered into the column

“A table can have only one primary key either in table level or column level

Using table level we can declare multiple columns in primary key”

Syn:

constraint constraint_name primary key; (or)

Primary key

Ex: Creating primary key at column level:

Create table emp (enamevarchar2(10), sal number(10), empid varchar2(10) constraint con_pk
primary key, deptno number(10);

By using above example we have created a table and declared primary key to the empid
column, we cannot declare more than one primary key.
Creating primary key at table level:

Create table emp (enamevarchar2(10), sal number(10), empid varchar2(10), deptno

number(10), constraint con_pk1 primary key (ename, empid);

By using above syntax we have created a table and declared primary key to the columns as we have
used table level constraint.

With the help of primary key we can relate another table using foreign key of another table.

Note:

If we create or define primary key or unique constraint automatically unique index will get
create on the columns.

6. Foreign key:

Foreign key is used to present actions that would destroy links between two tables.

Foreign key points to the primary key in another table

The purpose of foreign key is to ensure referential integrity of the data.

The table which we are referring to is called as parent table, the table which contains foreign key is
called as child table.

Child table another records always be derived from parent table, it means that the record which we
are insert or which is existed in child table the same record or data must be one of the value in the
table points to

Foreign key constrain :

Relational integrity constraint :

Foreign key constraint also presents k=invalid data from being inserted into the foreign key
column,because the data must be existed in parent table.

Note:

The foreign key can also reference a column that has the unique constraint.

Syn: Defining at table level:

Constraint constraint_name foreign key (col_name) references table name (col_name)

(or)

Foreign key (col_names) references table_name (column_name);

Defining at table level:

References table_name (col_name)

To the column level:

Create table student (enamevarchar2(10), deptno number(10) references emp (deptno));

To the table level:

Create table student (enamevarchar2(10), deptno number(10) foreign key (deptno) references emp
(deptno));

As we discussed carrier by defining foreign key we set the relation between two tables.

So the data dependency is there between two tables the parent table data shouldn’t be deleted if
the data exist in child table.

To avoid this we have two options in foreign key:

A. On delete cascade
B. On delete set null

A. On delete cascade:

While creating foreign key if we define on delete set null option, if we delete data from parent
table child table data will also be deleted.

B.On delete set null:

While creating foreign if we define on delete set null option, if we delete data from parent
table chid table records will be update as null.

Ex:

create table student (enamevarchar2(10) not null, stuno number(10), sal number(10) not
null, constraint con_fk foreign key (deptno) references emp(deptno) on delete cascade);

(or)

create table student (ename varchar2(10) not null, stuno number(10), sal number(10) not null,
constraint con_fk foreign key (deptno) references emp(deptno on delete set null:

Joins

Joins are used to fetch the data from one or more tables or views

The prerequisite condition of the join is the columns which we are comparing must have the similar
data type.

In select list the column names should be identified uniquely like table name. column name or alias
name . column name

When we are creating a join condition for three or more tables, oracle first joins two of the tables
based on the join conditions by comparing their columns and then joins the result to another table
based on join condition containing columns of the joined table and the new table
Oracle has following types of joins:

1. Self join

2. Inner join à a. Equi join

b. Non-equijoin

3. Outter join à a. Left outer join.

b. Right outer join.

C. Full outer join.

4. Courtision join

1. Self join:

Self-join is the join which joins the table itself

It means a self-join joins one row of a table with another row in the same table.

With help of aliases we spilt one table into two different tables while creating the query and
performing join condition.

Syn:

Select col1, col2, col3 from table A, table B where A.col1= B.col2;

Ex:

To find out who is the number for earn employee in emp table we can use sel- join.

Select e1.ename “employee”, e2.ename “manager” from emp e1, emp e2 where e1.mgr =
e2.empno;

2. Inner join:

The inner join joins or more tables and returns table rows that follow the join condition

There are two types of inner joins

a.Equi join:

Equi-join uses equals to (=) operator in the join condition to match rows from different tables.

It is used to interact the command data between two or more tables.

Syn:

select col1, col2 from table1, table2 where col1 = col3;

Ex:

select ename, dname, job, deptno from emp, dept where empdeptno = dept.deptno and job=
‘clerk’;

b.Nonequi-join:

nonequi join uses an unequal operation that is <>, >, <, !=, between, etc in join condition to
match the data.

Ex:

select ename,sal,comm from emp,dept where sal>comm;

Select e.empno, e.ename, e.sal, s.grade from emp e, salgrade s where e.sal between s.lowsal
and s.hisal;

3.Outer join:

With the help of outer join we can extract matched and non-matched data from specified
tables.

The outer join returns all rows of table with only those rows from another table that follow the join
condition

It also returns a null value inplace of the record which don’t follow the join condition from another
table.

Using (+) operator we can perform outer join in oracle.

There are three types of outer joins:

a.Left outer join:

In this join we will get the matched data right side mentioned table and the whole data from left
side mentioned table and non-matched data from right side tables will be replaced with null values.

Note:

In this join the (+) operator is placed in right side of the equals to operator or any condition

Ex:

select a.empno, a.ename, a.sal, b.deptno, b.dname, b.loc from empa outer join dept b on
(a.deptno = b.deptno);

2)Right outer join :

In right outer join (+)operator is in left side of the condition operator.

In this join we will get the matched data left side mentioned table and complete data from right side
table.
Non-matched data replaced with null values.

EX:-

select a.empno,a.ename,a.sal,b.deptno,b.dname,b.loc from emp right after join dept b on

(a.deptno=b.deptno);

3)full outer join:-

With the help of full outer join we can get complete data from two tables, non-matched data will
be replaced with null values.

But oracle doesn’t support placing(+)symbol on both sides of condition.

By using ANSI we can perform full outer join.

EX:-

select s.*,d.* from student s full outer join stu d on(s.s_id=d.s_id);

In full outer join if table A is having 6 records and table B having 5 records,if 3 records are common
in two tables ,the final row count is 6+5=11-3=8 records.

4)courtision join:-

It is a join that contains query with that a join condition. Oracle compares each row of one
table with each row of the other table.

If table A is having 3 records and table B is having 5 records,final output will be 5*3=15 records.

EX:-

select e1.*,e2.* from emp e1,dept e2;

5)complex join:-

VIEWS
Views is a logical repressentation of a table based on one or more tables or views.

View practically does not contain any data by itself wherever we perform any dml operations it will
hit the base table and provide the result.

The tables that are from view derives the data is called base tables.Base tables can be origin tables
or views.we can use the view in almost same way as we use tables like insert,update,delete and
select.
ADVANTAGES

 Views restrict access to the database ,since view contain no data of its own,users can access
a view for information without accessing the basetable where the data is actullay stored.
 To provide security on table.
 views enable users to simplifying queries and retrieve results from underlying complicated
queries .
 views provide data flexibility to multiple group of users by creating multiple views of the
same data.
 views are 3 types.

1) simple view.

2) complex view.

3) materialized view.

1) Simple view :

It is view which creates based on one table without contain any group functions such as
max, min, count(*) , etc..,

We can perform DML operations on simple view

2.Complex view:

It is a view which derives data from multiple tables or containing any group functions

We cannot perform any DML operations in complex view

If you want to perform any DML operations on complex view by using “instead of triggers”

3.Materialized view:

These are used in data ware house and data merge the main objective of materialized views
are to increase the speed of select query

Whenever we create a Materialized view we will get a message like snapshot is created.

These are allocates space in database,where as normal views don’t allocate space in DB

We can use materialized views when we have aggregation on single table and when we join two
tables

When we have to refresh the materialized view always whenever we update the data in base
table,bcoz materialized views are directly contains data and gives results directly without going to
base tables

The following are the options to refresh materialized view

Syn : DBMS_MVIEW.REFRESH(VIEWNAME)èTo refresh single Mview

DBMS_MVIEW.REFRESH_ALL_MVIEWSèTo refresh all Mviews

How to create a view:

Create or replace [force\noforce]viewview_name as select query from table_name with check

option\with readonly;

Create :- It specifies to create a view

Replace:- It specifies that view needs to be replace even if it is already existed

Force:- It specifies that view needs to be created even if base table doesnot exists

Note:-

When we create a view with force option when base table exists view get creates and
whenever we create the base table the view will be useful

Noforce:- It specifies that views should not be created when base doesn’t exists.

With check option:-

It specifies that rows that would be retrieved from query and where clause can only be
inserted,updated and deleted .

With read only:-

It specifies that rows must only be readable .we can’t perform any DML operations

Indexes
=> Indexes is a schema object where a pointer locates the physical address of the data

=> It is contains entry for each value in the index columns of table

=> the main use of index is to retrieve the data in faster way, in other way we can say that it is used
to speed up the select query performance

=> index provides faster and direct access to rows

=> we generally use indexes when the columns are frequently required.

=> having too many indexes on table will decrease the performance.
 The disadvantages of index:
 Indexes are takes addition disk space
 It will slow down insert, delete,update query .because whenever we update table it must be
updated in index.

The following are the types of indexes in oracle

1. B_tree index
2. Bitmap index
3. unique index
4. non-unique index
5. function based index
6. clustered index
7. non clustered index

Syntax:

Create index index_name on table_name(columns);

If we specifying more than one column in the column _list it is called as composite index.

EX: create index in_emp on emp [empid];

create index in_emp on emp [empid,ename];

Note:

 If we don’t specifying any index type while creating the default index that get creates is b
tree index.
 After creating an index we should collct statistics of index using below query.

Alter index index_name rebuild online;

OR
Alter index index_name rebuild compute statistics;

The table called all_in_columns contains ondex names with associated columns

How to drop index:

Drop index index_name

Note:

1. Indexes are internally creates keys to the range of rows.

2. A key is a set of columns or expressions on which we build on index.

Unique and non-unique index:

1. Indexes can be unique or non-unique

2. Unique indexes guarantee that note two rows of table have duplicate values in the key
column or columns.

Note:

a unique index is automatically get create when we define unique or primary constraints
To the columns.

Syntax:

Create unique index index_name on table_name (columns);

Clustered and non-clustered index:

 A cluster index created on cluster table.

 A cluster index must be created before any rows can be inserted into any cluster table.
 A cluster is group of tables that shares the same data block.
 Normally a data block contains data for only one table.
 In a cluster will contain or store data for many tables sharing the same block.
 If we frequently join the data from two tables we must need clustering.

Ex:

If we consider emp and dept tables if user usually fetch the data using dept num,if we cluster
these two tables the common data will be reside of the same data block.

Clustered index Non clustered index

A table can have only one clustered index A table can have N number of non clustered
It determines the data order in which the indexes
rows will be stored on disk. It don’t effect the order in which rows will be
It is faster because same order will be saved stored
as per disk. It is not so faster because order is not same
Clustered index don’t save a pointer to the as per the disk
actual row. A non clustered index saves both a value and
pointer to the actual row
 Sub queries

1. A query with in the query is called as sub query.

2. We can create sub queries with our SQL statements,these sub queries can be placed in
where clause,from clause,select clauses.
3. A sub query in a where clause is called as “nested sub query”.
4. A sub query in a from clause is called as “in line views”.
5. A sub query is select statement is called as “select sub quey”.

Oracle allows maximum of 255 sub query levels in a where clause that is for nested sub
query.

There is no limit for inline views and select sub query.

“Mainly sub queries are useful when we need to select rows from the table with a condition
on the data in the table itself”.

There are three main types of sub queries:

1. Single row sub queries

2. Multiple row sub queries
3. Correlated sub queries

Single row sub queries:

A single row sub query returns only one row,it can be used with the =,<,<=,>,>=,<>,!=
symbols.

Ex:

select * from emp where sal =(selec t max(sal) from emp);

Multiple row sub queries:

 A multiple row subquery returns one or more rows.

 As it returns multiple values we must use the comparisons operators such as in, not in, any,
all.
 If we use multiple row sub query with the comparision operators,the database will return an
error if more than one row is returned.

Ex:

select ename,dept_id from emp where dept_id in (select dept_id from dept where
location_id=100);
Correlated sub queries:

A correlated sub queries is a evaluated for each row processed by the main query,it
on excutes the inner query based on the value fletched by the outer query,continues till all
the values returned by the main query that are matched.

Here outer query excutes first.

The inner query is driven by the outer query.

EX:

select emp num,fname, sal,dept_id from emp where sal=(select avg(sal)from emp where
dept_id=e.dept_id);

Important queries
Q) How to display duplicate records? From table how to delete duplicate records from table?

Ans: By using row id we can get this

To display:

Select*from table_name where row id not in (select max(rowid)from table_name group by

col1,col2,col3,col4);

To delete:

Delete from emp where rowid not in (select max (rowid)from table_name groupby
col1,col2,col3,col4…….);

Or

Select * from table_name group by col1,col2,col3,col4 having count (*)>1;

Q ) How to find out second highest salary and first highest salary and nth highest salary

Ans:

To find out first highest salary

Select max (sal from table_name);

To find out first highest salary whole row;

Select * from table_name where sal_in (select max(sal)from table_name);

To find out second highest salary:

 Select max(sal from table_name where sal not_in(select max(sal from table_name);

To find out second highest salary whole row

Selec ct * from table_name where sal_in (select max (sal) from table_name where sal not_in
(select max(sal from table_name));

To find out nth highest salary:

Select *from (select table_name.*,danger rank()over (order by sal, desc) rn from high) where
‘rn=&n;

We can substitute any value with n to find out nth highest salary:

Select * from emp where s=(select from emp m )where e.sal<=m.sal count (distinct m.sal)

Q) How to find out top five salaries

Ans:

select * from(select Table_ name.*,dense _rank ()over (order by sal desc)rn from
table_name)where rn <=5;

Q ) How to select even rows or odd rows or alternate records from table

Ans:

To even rows

Select * from (select rownum rn,emp.* from emp)where mod (rn,2)=0;

To odd rows

Select * from (select rownum rn,emp.* from emp)where mod (rn,2)<>=0;

Q) Display records of employee whose salary is >= avg salary

Select* from emp where sal >=(select avg(sal)from emp);

Q )Find out highest salary row in each dept

Select * from (select emp.*,dense _rank ()over (partition by dept no order by salary desc()rn
from emp)where rn=1:

Q )How to find out first record in a table?

Select * from emp where row_id in (select min(rowid)from emp);

Q )How to find out last record in table

Select * from emp where row_id in (select max(rowid)from emp);

Q ) How to find out last before record in table

Select * from emp where row_id in (select min(rowid)from emp where row id not in (select
max(rowid)from emp));

Q ) Find out how many O letters or there in a given string

Select length(‘good morning’)-length(replace(‘good morning’)O)) from dual;

Table space
1. A table space is a logical storage of the data in database. It logically organize the in database.
2. A table space belongs to only one database.
3. A table space contains atleast one database.
4. A table space can store tables and can also store many other database objects such as
indexes, views,sequences, etc.,.
5. Table spaces are the bridge between certain physical and logical content
6. A table space contain one or more files called data files.
7. Data files are physical structure where the data is collection and stored.
8. The size of the table space is the size of the data files.
9. The size of the database is collective size of the table spaces.

Database errors
1. Table space error
2. Deadlock error
3. Number of connecting limit exceeded
4. Maximum open cursor exceeded

The above are the database errors which we face on frequent basis

1.Table space error:

This error indicates that the space which is allocated for table space is exceeded.

EX:

ORA:01688=>unable to extend table table_name partition_name in table space.

So in this case we have to in increase the table space size by adding data file or by resize.
 The above error basically indicates when we need to add space to a table space.

We check truly error with DBA learn to increase the table space.for a temporary resolution
we can delete the unwanted data or any backups which are unnecessary and we can re run called
process.

2.Deadlock error:

It an error which result through multi thread process.

Whenever particular or processes services try to hit same database object at a time the
database get having and throws deadlock error.

Here the process are hitting the same object in same time using same thread so that is
called of multithread error.

We need to restart the failure services or processes to resolve the issue, when we restart
the process ,processes hits the database at different timings.

3.Number of connecting limit exceeded:

In database the DBA teams set the limit to connecting that hit the database object .so when
total number of connections limit exceeded it throws the an error

So we need to innercase the connections limit or we need to start the process when database
object is free from connections.

4.Maximum open cursors exceeded:

In oracle program thetre must be certain cursors which gets declare. All the cursors which are
opened should get close within the period of time.if these don’t get close automatically it will throw
an error of maximum open cursor exceeded.

If we are facing this issue frequently we have to increase the limit for this.

How to determine whether database is up and running:

If any of database is running in background PMON,SMON,CRPT are the background or

deamon processes which must be running.if above processes are running we can confirms that
database is up and running. In unix by using below command we can confirm

Ps-ef|grep-I”PMON”

In windows we have to check under services.mse and need to search for these processes.

Normalization
 Normalization is the processes of organizing the data in database.
  This includes creating tables and establishing relationships between those tables according
to rules design to protect the data and make the database more flexible.

By eliminating the below factors we can achieve this.

1. Redundancy

2. Inconsistent dependency

And make sure that data is logically stored.

Normalization tool is divided into following normalization forms.

1. First normal forms

2. Second normal forms
3. Third normal forms
4. BCNF(boyce codd normal forms)

First Normal forms:

As per the first normal form, no two rows of data must contain repeating group of information.

It means is set of column have unique value so that multiple rows cant be used to fetch the
same row.

Each table shoulb be organized into rows, and each should have a primary key that distinguish it
has unique.

Ex:

STUDENT TABLE WITHOUT FIRST N.F

NAME AGE SUBJECT

Martin 15 bilology,maths

Alex 14 maths

Cary 17 maths

In first NF any row should not contain a columns with more than one value is saved, like
separated with comma rather than that we must separated from data into multiple columns.

Ex:

STUDENT TABLE WITH N.F

NAME AGE SUBJECT

Martin 15 bilology
Martin 15 maths

Alex 14 maths

Cary 17 maths

By using first nf data redundancy increases there will be many columns with same data in
multiple rows, but each row as whole will be unique.

Second normal form:

As per the second normal form there must not be any partial dependency of any column on
primary key. it means that for a table that has concatenated primary key (composite primary
key),each column in the table that is not part of the primary key must depend upon the entire
concatenated key for its existence.

It any columns depends only one part of the concatenation key, then the table files second
normal form.

In example of first normal form there are two rows for martin to include multiple subjects
that martin opted for. And also in first nf we set composite primary key on name, subject columns,
age of the student any depends on name column,which is incorrect as for second normal form.

To achieve second normal form we have to split out the subject into different table and link
them up using name column as foreign key.

Now tables following second nf will be

Student

Name age

Martin 15

Alex 14

Cary 17

Subject:

Name subject

Martin biology

Martin maths

Alex maths

Cary maths
 As per above examples in the student table the primary will be on name columns and other
column that is age depend on it.

In subject table the primary key will be on name columns. Now both the above examples
qualifies for second nf

And we can relate any of the other columns with the help of foreign key relation.

Third normal forms:

Third normal forms applies that every non prime attribute of table(non primary key
columns)must be dependant in primary key or we can say that there should not be the case that a
non primary key is determined by another non primary attribute.

This transitive functional dependency should be removed from the table and also must be
second normal form.

If we consider the following table

Student detail table:

Student_id Student DOB street City state ZIP

name

In this table student id is primary key but street, city and state depends upon zip.

The dependancy between zip and other field called as transitive dependancies.

To achieve third nf we need to more the street,city and state to new table with zip is primary
key.

New student detail table:

Std_id Std_name DOB Zip

Address table:

Zip Street city State4

The advantage of removing transitive dependency is amount of data duplication is reduced
and data integrity achieved.

Boyce and codd normal form(BCNF):

It is a higher version of the third normal forms. this forms deals with certain table of logic that
is not handle by third nf.