Oracle_1

Oracle Introduction
Database:
Database is an organized collection of data (information).

It is a collection of different database objects.
DBMS:
Database management system is a software which is used to manage the dsatabase.
User or any application can interact with DBMS to perform an action on DB.
Ex: Xml, Windows Registry, etc.
RDBMS:
Relational Database management system is a database with relational model.
Almost all the RDBMS use SQL for querying.
A relational database refers to a database that stores data in a structured format
using rows and columns.
The values within each table are related to each other. Tables may also be related to other tables.
The relational structure makes it possible to run queries across multiple tables at once
Ex: Oracle, MySQL, Micro soft sql server etc…
SQL (Structured query language)
SQL statements are classified into 5 different commands
Types of SQL commands:
Data Definition language ( DDL ) -- Create, Alter, Truncate, Drop, Rename

Data Manipulation language ( DML ) -- Insert, Update, Delete
Data Retrieval language ( DRL ) -- Select
Data Control language ( DCL ) -- Grant, Revoke
Transaction Control language ( TCL ) -- Commit, Rollback, Save point
Data types of Oracle:
1) char:
It is fixed length character data type which will accept all the letters, numbers and
special characters.
Maximum size is 2000 bytes or characters.
The length how much ever we specify will be allocated/occupied in the memory even if we
insert a value whose length is less than specified length.
Oracle pads the spaces to the character string up to the maximum/specified length.
2) varchar:
It is variable length character data type which accept all the letters, numbers and
special characters.
Maximum size is 2000 bytes.
It is having variable length memory allocation. It means when we pass values to varchar it
allocates only those many blocks of memory in the database.
3) varchar2:
Varchar2 is the updated version of varchar which can store up to 4000 bytes of data.
4) number ( p,s ):
It is only number data type which allows only numbers.
we have precsion and scalar as an optional to specify/restrict decimal places.
5) date:
It is the data type which allows only dates.
we need to pass date in default date format such dd-mon-yy
we can use to_date function if we want insert dates in any other format.
6) timestamp:
It is the data type which allows dates including time stamp ( hours, minutes, seconds and micro
seconds )
7) lob:
Large object data which is used to store large data.
There are two main types of LOBs
i) clob: A character large object containing single-byte or multibyte characters.
Maximum size is 4 GB.
ii) blob: It is for Binary large object. Maximum size is 4 GB.
8) bfile:
Contains a locator to a large binary file stored outside the database. Maximum size is 4 GB.
9) raw:
Raw binary data of length size bytes. Maximum size is 2000 bytes.
10) long raw: Raw binary data of variable. Maximum size is 2 GB.
DDL commands:
1) Create: It is used to create objects.

below is the syntax to create a new table in oracle.
Syntax: create table table_name (column1 dataype (size), column2 datatype (size)
….column(size))
EX: create table emp (empno varchar2(10),ename varchar2(10),salary number(10));
2) ALTER: Alter is used to add a column, modify a column, rename a column and drop a column.
a) ADD: To add a new column to the table.

We can add multiple columns at a time.
Syntax: alter table table_name add column_name datatype (size);

(or)
alter table table_name add (column_name datatype(size)); >> for multiple columns
EX: alter table emp add mgr varchar2(10);
b) MODIFY: To modify the existed column data type or size.

We can modify n number of columns at a time.
Syntax: alter table table_name modify column_name datatype(size);

(or)
alter table table_name modify (column_name datatype(size));
EX: alter table emp modify salary varchar2(10);
C) DROP: To drop the columns of a table.

We can delete n no.of columns at a time.
Syntax: alter table table_name drop column column_name ;

(or)
alter table table_name drop ( column1, column2…. column) >> for multiple columns
Ex: alter table emp drop column empname;
D).RENAME: To rename the column of a table.

We can’t rename more than one column at a time.
Syntax: 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 will be removed .
Syntax: 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 whole data of a table.
When we use truncate only data of a table get deleted, the structure will remain.
Syntax: truncate table table_name;

Ex: truncate table emp;
DML (data manipulation language):
1) 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.
Syntax: insert into table_name values (val1,val2,val3,….);
Ex: insert into emp values ( ‘alex’, 2000, 20 ) >>> we need to provide value for all the columns when we
use this method.
Note: To insert the data for specific columns we use following method.
Syntax: insert into table_name (column_name) values (value);
Ex: insert into emp (empname) values (hanuma);
REFERENCE METHOD: In this method we can insert multiple rows of data at a time. We provide values
through prompt
Syntax: insert into table_name values (&col1,&col2,…);
Ex: insert into emp values (&eno,’&empname’);
Note1: Oracle doesn’t make any permanent changes to any DML operations until we commit it.
Note2: When we are character data type we must specify the value in single quotes.
2) 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;
3) DELETE:
Delete is used to delete the rows of a table.
Ex: delete from emp; >> To delete table content.
delete from emp where ename = ‘alex’ >> to delete particular records.
DRL (data retrieval language) :

SELECT:
It is used to select the desired data from table.
We can mention specific columns or * in select clause, we can also use filters such as where clause,
group by and having clauses.
Ex1: select * from emp;
EX2: select ename, sal from emp;
TCL (transactional control language) :

TCL commands are used to validate the transactions.
COMMIT:
To make changes/save permanently.
We have to use commit command to make the DML operations for permanent save.
Syntax: commit;
ROLL BACK:
To roll back the changes done in previously executed transactions.
It restore the state of data base to the last commit point.
Syntax: 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;
insert into emp (empno,sal) values (02,3000);
save point B;
insert into emp (empno, sal) values (03, 4000);
save point C;
here we can use rollback to A or B or C

(or)
commit to A or B or C
DCL (data control language):
DCL statements are the one of the sql statement types which enforces database security.
It is used to grant defined role 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.
Syntax: grant [privilege]

ON [object]
to{ user | public | role }
When a user is newly created in the data base DBA should grant below privileges to user in order to
connect to the database.
EX1: grant create session to alex;

EX2: grant connect resource to alex;
Above privilege enables Prakash to connect the database.
When user or owner of a table wants to give select permission to another user the following can be
used.
Syntax: grant select on emp to alex;
Privileges are the permission name which is granted to the user likeselect, 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.
Syntax: revoke [privilege]

ON [object]
from { user | public | role }
Ex: revoke select on emp from alex;

Oracle_1

Uploaded by

Copyright:

Available Formats

Oracle_1

Uploaded by

Document Information

Copyright

Available Formats

Share this document

Share or Embed Document

Sharing Options

Did you find this document useful?

Is this content inappropriate?

Copyright:

Available Formats

Oracle_1

Uploaded by

Copyright:

Available Formats

Oracle Introduction

Database is an organized collection of data (information).

SQL (Structured query language)

SQL statements are classified into 5 different commands

Types of SQL commands:

Data Definition language ( DDL ) -- Create, Alter, Truncate, Drop, Rename

1) Create: It is used to create objects.

EX: create table emp (empno varchar2(10),ename varchar2(10),salary number(10));

a) ADD: To add a new column to the table.

Syntax: alter table table_name add column_name datatype (size);

EX: alter table emp add mgr varchar2(10);

b) MODIFY: To modify the existed column data type or size.

Syntax: alter table table_name modify column_name datatype(size);

EX: alter table emp modify salary varchar2(10);

C) DROP: To drop the columns of a table.

Syntax: alter table table_name drop column column_name ;

Ex: alter table emp drop column empname;

D).RENAME: To rename the column of a table.

Ex: alter table emp rename column salary to sal;

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

Syntax: 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 whole data of a table.

Syntax: truncate table table_name;

DML (data manipulation language):

1) INSERT : Insert is used to insert the data into columns of a table.

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

Syntax: insert into table_name (column_name) values (value);

Ex: insert into emp (empname) values (hanuma);

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

DRL (data retrieval language) :

TCL (transactional control language) :

here we can use rollback to A or B or C

These are two types of DCL commands.

Syntax: grant [privilege]

EX1: grant create session to alex;

Above privilege enables Prakash to connect the database.

Syntax: grant select on emp to alex;

Syntax: revoke [privilege]

Ex: revoke select on emp from alex;

You might also like