_logo.png){kind=logo}
.jpg)
oracle
INTRODUCTION TO ORACLE
ORACLE is a powerful rdbms product that provides efficient and effective solutions for major database.An oracle data base system can easily take advantage of distributed processing by using its client/server architecture.ORACLE IS A COMBINATION OF SQL AND PL/SQL.
FIRST KNOWN SQL:
WHAT IS SQL:
SQL stands for structured query language.SQL is used to communicate with a database.According to ANSI(american national standards institute),it is the standard language for relational database management syatems.SQL statements are used to perform tasks such as update data on a database.or retrieve data from database.some common relational database management systems that use SQL are:oracle,sybase,microsoft sql server,access,ingres,etc.
it is non procedural language which is used to communicate with any type of database.this language was developed by german scientist mr E.F CODD AT 1968 in 1970 ANSI approved this language and 1972 sql was released in the market.
FEATURES OF SQL:
- it is a non-procedural language
- it can also pronounced as SEQUEL
- it is not a case sensitive language.
- it can be called as natural language interface because almost all sql commands similar to normal english language
- it is the common language to interact with any type of database such as oracle,db2 etc.
it was divided into following types.
- data defination language(DDL)
- data manipulation language(DML)
- data retrieval language(DRL)
- transaction control language(TCL)
- data control language(DCL)
PROPERTIES OF RDBMS:
1.data should store in the form of a table.a table can be different as collection of rows&columnsA horizontal lines as Rows or Records/touples
A vertical lines are known as fields or attributes/columns
the intersection of rows&columns is known as cell
a cell is a place where we can store or a data.the other name of table can be known as entity/object.
table/entity/object:
fields/attributes/columns
2.table should not contain duplicate column 3.while do inserts the records the table user no need to follow any specific order.
4.data base should not contain duplicate objects
5.while do defines columns in the table user follow no need to order
E-R DIAGRAMS:
this concept was developed by a u.s scientist MR PETER CHAN ,this is the diagramatical representation of manual database when we design E-R diagrams we need to use following symblos.
→ Connection
ex:draw E-R diagram which represent the relation between sales representive and customer
SUB LANGUAGE COMMANDS:
1.DATA DEFINATION LANGUAGE COMMANDS:
WHAT IS DATA DEFINITION LANGUAGE?this is first sub language in sql which is used to define database objects such as table,view,stored procedure,function and trigger etc.a data definition language has a pre-defined syntax for describing data
this language contains 5 commands those are
- CREATE
- ALTER
- RENAME
- TRUNCATE
- DROP
this command is used to create databse objects such as table,view,synonym,trigger,stored procedure etc.,
syntax:
CREATE TABLE table-name(column 1 data-type-for-column1,
column 2 data-type-for-column2,
............);
ex:
CREATE TABLE user(name char(50),villagename char(50),mobilenum number(10));
RULES FOR CREATING A TABLE:
- table name should start with an alphabet and it contains minimum 1 character and maximum 30 characters and it should not allow any space or any special chartcters except _,#,$ and 0 to 9 numbers
- a table can have minimum 1 column and maximum 1000 columns
- a table can have minimum 0 records and maximum n number of records upto harddisk capacity
- oracle keywords should not be used as column names or table names
- column name should start with an alphabet and it contains minimum 1 character and maximum 30 characters and it should not allow any space or any special chartcters except _,#,$ and 0 to 9 numbers
this command is used to modify the structure of the table.
using this command we can perform 4 different operations on the table
- alter-modify
- alter-add
- alter-rename
- alter-drop
this command is used to increse or decrese the size of the column or we can change the data type of the column.
RENAME:
This command is used to change the table name.
syntax:
rename old-table-name to new-table-name;
ex:
rename user to cust;
TRUNCATE:
This command is used to delete the records from the existing table permanently.
syntax:
truncate table
;
ex:
truncate table user;
DROP:
This command is used to drop the table from the data base.
syntax:
drop table
;
ex:
drop table user;
using drop command once we drop the table those tables are not dropped permantely from the database.
thosetables are gone to recycle bin.if you want to get back the table from the recycle bin then we have to use followin command.
FLASHBACK COMMAND:
This command was introduced from oracle log onwords and which is used to retrieve back the table from the recycle bin
syntax:
FLASHBACK table
to before drop;
ex:
flashback table user to before drop;
permantely delete from the database :
following this two commands
drop table
purge;
purge table
;
