SQL

ORACLE

·         Oracle is a RDBMS(Rational Data Base Management Systems)

·         Database is a collection of meaning full data

·         RDBMS maintain complex  data easily.

Database:

Collection of meaning full data

SNO	SNAME	MARKS
1	Chandra	446
2	Ravi	525
3	John	555

Management System:

It is a s/w which help to maintain the data

This s/w perform the following activities easily

1.       inserting a new data

2.       updating the existing data

3.       deleting the unnecessary data

4.       Retrieving the required data

·         Oracle is an example of RBMS

Example of RDBMS----------Oracle, Mysql, Sql Server, Sybase, DB2

·         Oracle is most widely used DB in the world

SQL

·         SQL(Structured Query Language)

·         SQL pronounced as  "SEQUEL"

·         This language is generated by IBM in the year of 1972

·         It is a command based language

·         every sql command ends with ;

·         This language is not a case sensitive

Table:

To store data into database we need table. Tables are basic unit to store data.  It's collection of rows and columns.

SQL is divided into 5 languages. They are

1.       DDL(Data Definition Language)

2.       DML(Data Manipulation Language)

3.       DRL/DQL(Data Retrieval/Query Language)

4.       TCL(Transaction Control Language)

5.       DCL(Data Control Language)

DDL:

This language is used to manage the database objects

The commands of DDL are

·         create

·         alter

·         drop

·         truncate

·         rename

DML:

This language is used to manipulate data present in the table

commands of this language

·         insert

·         update

·         delete

·         manage

DRL:

This language is used to retrieve data which is present in the database

This language is consist of only one command that is

·         select

TCL:

This language is controls the transactions

Commands of this language

·         grant

·         rollback

·         save point

DCL:

This language is used to control the data

Commands of this language

·         commit

·         rollback

Create:

this command is used to create the table in the DB

Syntax:

create table <Table Name> (columnName dataType<size>,...., columnName dataType<size>,);

EX: create table emp(eno number,ename varchar2(20),marks number(3));

Insert:

This command is used to insert data into the table

Syntax:

insert into emp values (val1,....,val);

EX: insert into emp values(1,'chandra',446);

Select:

This is used for getting the data from database

Syntax:

select * from <Table Name>;

EX: Select * from emp;

How to select specific columns?

select eno,ename from emp;

How to insert data specific columns?

insert into emp (eno,name) values (2,'ravi');

Note: Null is a value which is unreachable or unidentified

Order by clause:

 This clause is used to arrange the data into the order.

EX: select * from emp order by eno;( it will display rows in ascending order)

·         Null values takes the highest priority

·         Default order is ascending

·         For descending order we will specify the "DESC";

EX: select * from emp order by eno desc;

Distinct clause:

This clause is used to display single value even if we have multiples in the DB

EX: select distinct ename from emp;

Where clause:

Where is used to get the data based on the condition

Write a query to display whose salary is greater than 2000?

select * from emp where sal>2000;

Note:  Data in the database is case sensitive

Write a query to display all the values who are having null in the marks column?

Select * from student where marks is null;

Logical Operators:

Logical operators are 3 types those are

1.       and(Select * from  employee where ename ='chandra' and salary>1000;)

2.       or(select * from employee where ename ='chandra' or  salary>1000;)

3.       not(select * from employee where ename is not null)

Between clause:

It's get the data between values. This query includes the specified values also.

EX: select * from emp where eno between 1 and 3;

In operator:

This operator is used to display rows which matches in the list.

EX: select * from emp where eno in(1,2);

pattern matching operator:

We have two pattern matching operators.

·         %

·         _

ex:% means any number of letters

select * from emp where ename like 'a%';

select * from emp where ename like '%a';

select * from emp where ename like 'a%n';

select * from emp where ename not like 'a%';

select * from emp where ename like '%a%';

EX: _ means one letter

select * from emp where ename like '_a%';

select * from emp where ename like '%a_';

select * from emp where ename like '____';

column alias:

It is used to provide our own column headings. Column alias are temporarily. Scope of this column alias is for that query

select eno as employee_Number from emp;

dual table:

It is a dummy table which is used in select  statement.

select 10*15 from dual;

Update:

This query is used to change the data which is exist in the Db table

update emp set sal=2000 where ename='chandra';

Delete:

this command is remove columns from the table

delete from emp--------------remove all the rows from table

delete from emp where ename = 'chandra';

DESC:

This command is used to see the structure of table

EX: desc emp;

Back Up table:

create table emp1 as select * from emp;

Group By clause:

It is used to divide the rows of table into groups based on the column

EX: select deptno, sum(sal) from emp group by deptno;

Note:

·         All columns in the select list should use group function or should be included in group by clause. If rule is not satisfied we get error.

Having clause:

having clause is filter all the records

EX:

select deptno,sum(sal) from emp where job <> 'PRESEDENT' group by deptno having sum(sal) >9000 order by sum(sal) desc;

Alter:

This command is perform the following tasks

·         Adding a new column

·         drop an existing column

·         modifying a column

Adding new column:

EX: alter table student add(address varchar2(20));

Note:

·         newly added column will have null values

·         at a time we can add multiple columns

Drop an existing column:

EX: alter table student drop(address);

Modifying a column:

EX: alter table student modify(sno number(10));

Note:

·         By using alter we can decrease the column size

·         If the size of value is bigger than the modified size we can't decrease size

·         to decrease the size of number data type column should be empty

·         By using alter we can change the data type. Column should be empty.

·         By using alter we can add the constraint or drop the constraint

·         We can't add the constraint if data is not satisfied the constraint

EX:

 alter table student add(primary key(sno));

alter table student drop primary key;

alter table student add (unique (sno));

alter table student drop unique (sno);

Truncate:

This command is used to remove the columns from the table

EX: truncate table student;

Delete	Truncate
DML command	DDL command
We can use where clause	Where should not work
Roll back data	Can't rollback data

Rename:

This command is used change the table name.

EX: rename <old name> to <new Name>;

Merge:

This command is act like insert and update.

EX:

merge into student1 s1 using student2 s2 on(s1.sno=s2.sno)

when matched then

update set sname=s2.sname,maeks=s2.marks

when not matched then

insert into student1(sno,sname,marks) values(s2.sno,s2.sname,s2.marks);

TCL:

commit:

This command is used for saving data permanently in the DB

rollback:

This command is undo the data which are not permanent

Save Point:

save point is logical marking given to the series of transactions.

Syntax: savepoint <savepoint_Name>;

Functions

Functions are divided into two types

1.       Group functions(Aggregate functions)

2.       Scalar functions(single rows functions)

Grouped functions:

·         sum{ select sum(sal) from emp;}

·         min{select min(sal) from emp;}

·         max{select max(sal) from emp;}

·         avg{select avvg(sal) from emp;}

·         count(*){select count(* )from emp;} used for finding number of rows in the table

·         count(EXP){select count(sal) from emp;} This function will ignore the null values.

Scalar functions:

these functions are divided into 4 types

1.       Number

2.       Character

3.       Date

4.       Conversion

Number function:

EX:

·         abs(-10)

·         power(10,2)

·         sqrt(4)

·         ceil(10.3), ceil(10.2)

·         floor(10.8)

·         greatest(10,20,30)

·         least(10,20,30)

·         trunc(40.0),40.2,40.5,(40.368,2),(4026,-2)

·         round(40.9),40.2,40.5,(4936,-2),(40.368,2)

Character function:

EX:

·         upper('oracle')

·         lower('ORACLE')

·         initcap('oracle server')

·         length('oracle')

·         lpad('oracle',10,'z')--------10 is size of data and 'z' is adding additional values

·         rpad('oracle',10,'z')

·         ltrim('zzzzoracle','z')

·         rtrim('oraclezzz','z')

·         trim  ('z' from 'zzoraclezz') from dual

·         instr('oracle','a')--------O/P:3

·         substr('oracle',2,3)-----O/P:rac

 Date function:

We have 4 date functions

1.       add_months{add_months(sysdate,12)}

2.       months_between{months_between('11-jan-2012','11-jan-2013')}O/P:-12

3.       last_day: return last day of month

4.       next_day: return next of which is specified(next_day(sysdate,'monday'))

Conversion function:

These are divided into 3 types

1.       to_char(sal,'9,9999.99')

2.       to_date(sysdate,'dd-mm-yyyy')

3.       to_number(substr('$300',2,3))

Format	Discription
D	Return week day(1-7)
DD	Return the day(1-31)
DDD	Return day of year(1-365)
MM	Return the month in year(1-12)
Y	Return last one digit of the year
YY	Return last two digits of the year
YYY	Return last 3 digits of the year
YYYY	Return last 4 digits of year
DY	Day name(sat-sun)
DAY	Full name of day
MON	Name of month(jan)
MONTH	Full name of month
YEAR	Display year in words

Miscellaneous Functions

NVL:

This function is used to  replace null values with specified value

select empno,nvl(comm,500) from emp;

NVL2:

This function set the 2nd value if we have null in the column otherwise it return first value

select empno,nvl2(comm,200,500) from emp;

Decode:

 select empno, decode(deptno,10,5000,20,70000,30,4000) from emp;

Data Types

Char:

This data type is used to store alpha numeric values. This data type is of fixed length. Maximum size of 2000 bytes.

varchar:

This data type store alpha numeric values. This data type is variable length. Maximum size 4000bytes

Number(P,S):

It is used to store numbers. P-------Precision, S----------Scale

number(7,2)=12345.12

Date:

Used to store date values. Range of date 01-Jan-4712 to 31-Dec-9999. Default date format

 DD-MON-YY

RAW:

Used to store binary data. Maximum size is 2000 bytes

EX: photos, Digital signatures, logos.....etc.

Long RAW:

Similar to RAW . Maximum size is 2GB

CLOB:(Character Large Object)

Used to store character data. Maximum size 4GB

BLOB:(Binary Large Object)

Is used to store binary data. Maximum size is 4GB

BFILE:

Used to store data files in the external files.

Constraints

Constraints are rules applied on the table.

1.       not null

2.       unique

3.       primary key

4.       Foreign key

5.       check

Not null:

This constraint is not accept null values.

EX:

create table emp(eno number not null);

·         insert into emp values(1);

·         insert into emp values(1);

·         insert into emp values(2);

·         insert into emp values(null);--------------Not work

Unique:

This constraint allows the null values but it will not accept duplicate values

EX:

create table emp(eno number unique);

·         insert into emp values(1);

·         insert into emp values(1);----------Not work

·         insert into emp values(2);

·         insert into emp values(null);---------allow any number of null values

Primary Key:

This constraint will not accept null values as well as duplicate values. It is a combination of not null and unique.

EX:

create table emp(eno number primary key);

·         insert into emp values(1);

·         insert into emp values(1);---------------not work

·         insert into emp values(2);

·         insert into emp values(null);--------------Not work

Note:

1.       One table is having one primary key

2.       primary key is used uniquely identification of the table

Composite primary key:

When primary key is applied on multiple columns is called as composite primary key.

EX: create table emp(fname varchar2(20), lname varchar2(20), primary key(fname,lname))

Note:

Composite primary key allows to add same fname with different lname and reversly. Doesn't allow same values in two times. This will not allow nulls in any of two columns

Foreign key:

it is establish relationship between tables.. This relationship is called as parent and child relationship.. It is also called as master-details relationship. A foreign key column will only accept the values of parent table.

EX:

1.create table student(sno number primary key, name varchar2(20));

2.create table address(sno number references student(sno),

address varchar2(20));

Important points:

1.       Parent table must have primary key/unique key

2.       Foreign key column name is no need to match with primary/unique key column name but data type should match.

3.       Foreign key column will accept null values

4.       Foreign key also called as referential integrity

5.       We can't delete rows from the parent table until child record is present

on-delete-cascade:

If we set this property once we delete parent record, child record should be deleted automatically

EX: 1.create table student(sno number primary key, name varchar2(20));

2.create table address(sno number references student(sno) on delete cascade,

address varchar2(20));

on delete set null:

If we delete parent record child record will set to null value

1.create table student(sno number primary key, name varchar2(20));

2.create table address(sno number references student(sno) on delete set null,

address varchar2(20));

Check:

This constraint define the values of a column it can hold.

EX:

create table student (sno number, name varchar2(20), marks number check(marks<=100));

--> We can also create our own constraint names

EX:

create table student (sno number, name varchar2(20), marks number check(mar

ks<=100) constraint my_const unique);

--> We can see the list of table names and constraint names by using data dictionary table user_constraints

EX:

select table_name,constraint_name from user_constraints;

Note:

Constraints can be create at two levels

·         column level{create table student (sno number unique);}

·         table level{create table student(sno number, unique(sno));}

not null constraint can be create only at column level

Joins

Joins are used to retrieve data from more than one table

1.       Equi join

2.       non equi join

3.       self join

4.       outer join

EQUI join:

When tables are join based on common column is called as equi join.

EX:

select emp.empno,emp.deptno from emp emp, dept dept where emp.deptno=dept.deptno;

Non equi join:

when tables are joins with out any common column is called as non equi join

EX:

select e.empno,.s.grade,e.empname from emp e, salgrade s where where e.sal between s.lowsal and s.highsal;

Self join:

When we get data based on single table is called as self join

EX:

select e.ename, m.ename manager_Name from emp e, emp m where e.mgr = m.empno;

Outer join:

Right outer join:

select e.ename,d.dname,d.loc from emp e, dept d where e.deptno (+)=  d.deptno;

left outer join:

select e.ename,d.dname,d.loc from emp e, dept d where e.deptno =  d.deptno(+);

Full outer join:

No sql format.

ASCII format:

select e.ename,d.dname,d.loc from emp e right outer join dept d on (e.deptno =  d.deptno);

select e.ename,d.dname,d.loc from emp e left outer join dept d on (e.deptno =  d.deptno);

select e.ename,d.dname,d.loc from emp e full outer join dept d on (e.deptno =  d.deptno);

Cartesian product:

When tables are joined without any common column then we call it as Cartesian product

select * from emp,dept;

cross join:

select * from emp cross join dept; --------------same as Cartesian product.

Natural join:

select * from emp natural join dept;----------Same as equi join

using clause:

select * from emp join dept using(deptno);---------Same as equi join

on clause:

select * from emp e join dept d on (e.deptno = d.deptno);------------Same as equi join

Inner join:

select *from emp e inner join dept d on (e.deptno=d.deptno);--------Same as equi join.

Set Operators

1.       union

2.       union all

3.       minus

4.       intersect

Student1	Student2
101	101
103	105

union:

select sno from student1 union select sno from student2;

O/P: 101,103,105

Union all:

select sno from student1 union all select sno from student2;

O/P: 101,103,101,105

Intersect:

select sno from student1 intersect select sno from student2;

O/P: 101

Minus:

select sno from student1 minus select sno from student2;

O/P: 103

Note:

1.       Number of columns in the two queries should be same

2.       Column data type should match

Sub Queries

When we create a query inside another query then that inner query is called as sub query and outer query is called as parent query. Always sub query will be executed first and then parent query will be executed based on the sub query results.

Nested Sub Queries:

writing sub query inside another sub query is called as nested sub query.

We can write nested sub queries up to 32 levels.

Types of sub queries:

1.       single row sub query

2.       multi row sub query

3.       multi column sub query

4.       scalar sub query

5.       co related sub query

Single row sub query:

If sub query returned single row then we call it as single row sub query.

EX:

select * from emp where sal> (select sal from emp where ename='ALLEN');

Multi row sub query:

If sub query return more than one row we can call it as multi row sub query. We have multi row operators from the sub queries(IN,ANY,ALL)

ALL operator:

This will return all the values,  greater than the least value which is returned by the sub query

EX:

select * from emp where sal > all(select sal from emp where deptno=20);

Any operator:

This will return all the values, greater than the greatest value in the sub query)

EX:

select * from emp where sal > any(select sal from emp where deptno=20);

In operator:

Return only selected values in the sub query

EX:

select * from emp where sal > in(select sal from emp where deptno=20);

Multi column sub query:

EX:

select * from emp where (sal,job) in (select sal,job from emp where deptno=20);

Co related sub query:

If sub query is executed based on the parent query then we can call it as co related sub query.

EX:

select * from emp e where sal>(select sal from emp d where d.deptno = e.deptno);

Scalar sub query:

sub query is used in the select clause then that queries are called as scalar sub queries.

EX:

select deptno,(select sum(sal) from emp where deptno = e.deptno) sum_sal from dept;

Pseudo Columns

1.       Rownum

2.       rowid

3.       level

4.       currval

5.       nextval

rownum:

It is a pseudo column. Rownum values start with 'one' and increment by 'one'. These values are generated when query is executed.

EX:

select * from emp where rownum<6;

rowid:

It is a hexa decimal value. RowId indicate memory location where the row stored in the database. rowid values are permanent. Rowid is generated when row is created.

select rowid from emp;

level:

It is used to display level of an employee.

EX:

 select level,empno from emp connect by prior empno=mgr start with mgr is null;

inline view:

sub query written in from clause then is called as inline view.

EX:

select * from (select empno,ename from emp order by sal desc) where rownum<=5;

view

A view is logical representation of data from one or more tables.

Syntax:

create view <view_name> as (select * from emp);

Note:

1.       view doesn't contain any space

2.       When we write a select statement from view, we will get data from base table

3.       View doesn't contain any data at all.

4.       When we write a select statement on view, views assocated select statement will be executed

5.       There are two types of views

·         simple view

·         complex view

Simple view:

When a view is created based on one table, it is called as simple vew.

Complex view:

When a view is created based on more than one table then that view is called as complex view.

·         DESC <View Name> is for seeing the structure of view

·         To see the list of views

select view_name from user_views;

·         To drop the views

drop <view Name>

·         A view can be created based on another view

·         To get the select statement used for creating view is

select view_name, text from user_views;

·         create or replace command is used to change the definition of view

Read only views:

we can restrict DML operations on view by creating read only views.

EX:

create view v1 as select * from emp with read only;

With Check option:

DML operations were allowed if condition is satisfied.

EX:

create view v1 as ( select * from emp where sal>2000 with check option);

Materialized views:

·         Only DBA can create the materialised views.

·         In order to create the materialized views the base table should have primary key value.

·         Materialized vies are read-only

·         If any modifications are done in the parent table view can't updated

EX:

create materialised view v1 as select * from emp;

·         materialized vies previously called as snapshot

·         For refreshing the materialized vie

exec.dbms_snapshot.refresh(<Materialized viewName>)

materialized view contains image of data from base table in client system's memory location. Hence materialized views run faster than normal views.

Indexes

Index is a oracle object which improves the performance of select statement.

Syntax:

create index <indexName> on tableName(columnName);

EX:
create index index1 on emp(sal);

Indexes are two types

1.       simple index

2.       complex table

Simple index:

When index is created on single column we can call as simple index.

EX:

create index index1 on emp(sal);

Complex index:

When index is created on multiple columns it is called as composite index.

create index index1 on emp(sal,job);

·         To see the list of indexes in the database we use the data dictionary table user_indexes

select index_name from user_indexes;

How index works?

When index is created a separate structure will be created with two columns

RowId	Sal
	500
	1500
	2000

The index column will be generated in the ascending order.

Indexes are classified into two ways

·         unique index

·         non unique index

unique index:

This index is created automatically. When we create a table with primary key or unique constraint. Unique index name is same as constraint name.

Function based index:

 When we create index based on function then this is called as function based index.

EX:

create index index1 on emp(lower(ename));

sequences

Sequence is an oracle object which is generate numbers.

create sequence seq 1 start with 1 increment by 1 maxvalue 5000, minvalue 50 cycle;

select seq1.currval from dual;

select seq1.nextval from dual;

select sequence_name from user_sequence;

drop sequence seq1;

Synonyms

Synonym is additional names gives to an object

EX:

create synonym for emp;

select synonym_name, table_name from user_synonyms

Normalizations

The process of reducing redundancy and improving accuracy of a database  is called "normalization".

We can archive normalization by using "Normal Forms"

1NF:

A database is in 1NF, it should fallow the following rules

1.       Each cell can have only one column

2.       Every table should have primary key

Customer	Day	Amount
ABC	Monday	1800,1900
DEF	Tuesday	400,300
GHI	Wednesday	200,100

 The above table is not in the 1NF as the amount column is having 2 values

The above table is transformed into bellow table

Customer	Day	Amount
ABC	Monday	1800
DEF	Tuesday	400,300
GHI	Wednesday	200,100
ABC	Monday	1900
DEF	Tuesday	300
GHI	Wednesday	100

This table is in 1NF

2NF:

A database is in 2NF, if it satisfied in the following rules

1.       Data base is in 1NF

2.       There should not be partial dependencies

Partial Dependencies:

A non key attribute depends on part of primary key

Part ID	Supplier id	Sname	price	Address
65	2	SP	59	Bangalore
73	2	SP	60	Bangalore
65	1	AI	54	Hyderabad

Part Id and Supplier Id is primary key columns

In the above table  there are partial dependencies because a non key column(sname) is dependences on primary key(supplier Id) and supplier is a part of primary key. So we can eliminate partial dependencies by splitting tables

Part Id	Supplier id	Price
65	2	59
73	2	60
65	1	54

Part Id, supplier id is primary key

Supplier id	sname	Address
2	SP	Bangalore
1	AP	Hyderabad

Supplier id is primary key

3NF:

A database is in 3NF it should fallow the following rules

1.       Data should be in 2NF

2.       No transitive dependencies.

Transitive dependencies:

A non key attribute depends on another non key attribute

Part No	Manufacturer name	Manufacturer address
1000	T	Park avenue
1001	M	Los angels
1002	T	Part avenue

In the above table there are transitive dependencies as a non key column (address) dependences on another non key  column(name) . We can archive it by splitting tables.

Part no	Manufacturer name
1000	T
1001	M
1002	T

 part no is primary key

Manufacturer name	Manufacturer address
T	Park avenue
M	Los angels

name is PK