We have mainly 3 languages to access the table.
- DDL
- DML
- DCL
1. Data definition language (DDL):
This is used a set of commands that defines data base objects. (Create, alter, drop and Rename).
Its mainly affects on data base table structure.
(i) Create table Command : The Create Table Command defines each column of the table uniquely. Each column has a minimum of three attributes, a name, data type and size. We already discussed about this one.
Syntax :
Create table <table name> (<column1> <Data Type> [Size],
<column2> <Data Type> [Size],------------------------------<column N> <Data Type> [Size]);
Eg., Create Table Students ( sid number, sname varchar2(50));
(ii) Alter Table :
It is used to change the structure of the Table i.e. adding new column, changing the data type and size. The alter command can have 3 types of sub commands. They are:
1)ADD: By using this command we can add new columns to the existing table.
Syntax:
Alter Table <Table_Name> add (Column1 <data_type>[<size>],
Column2 <data_type>[<size>],- - - - -,Column(n) <data_type>[<size>])
Modify: It is used to change the Data Type and size of the existing columns. If you can change the Data Type and Size you must satisfy the following rules.
By using Modify command in alter we cannot change the column name.
We can not Change the Positions of the existing or new columns
We cannot decrease the length (Size) of an existing column, if that column is having values. But we can increase the size of the existing column even if the data is present
Syntax:
Alter Table <Table_Name> Modify (Column1 <data_type>[<size>],Column2 <data_type>[<size>],- - - - -,Column(n) <data_type>[<size>])
3. Drop This command is introduced in Oracle 8i. It is used to remove the column of a table.
Syntax:
Alter Table <table_name> Drop column <column_name>
Adding single column :
Alter table students Add (total number(4));
Adding Multiple column :
Alter table students Add (average number(6,2),result char(4));
3. Drop Command:
This command is used to drop or delete any table from the database.
Syntax: Drop table <table_name>
Example: Drop table students;
2.Data Manipulating Language (DML):
These are used to update, adding record, and removing records (select, update, insert, delete).
(i) Select Statement :
The select statement is used to display the details of a table
Syntax :
Select [ * | Distinct | <column list>] from <table name>[ Where <condition > ][ Group by <column name(s) > ][ Having <Condition> ][ Order by <Expression > ]
Examples :
1. To display all Tables List
select * from tab;
2. To display a particular table details
select * from emp;
3. to display particular columns in a table
select empno, ename, job from emp;
(ii) Update Command :
This command is used to update or modify all or specified column values with new values.
Syntax:
Update < Table Name > set <column1> = <value1>, <column2>=<value2>, -------<column N>=<value N> where <Condition>
Examples:
(i)To update only one column value
Update students set sno=501 where sno=100;
(ii)To update Multiple column values
Update students set sno=511,sname=’Ramesh’ where sno=500;
(iii)To update all values with same value
Update studnts set sno=502;
(iii) Insert Command :
This command is used to insert the data into the table.
Syntax : Insert into <table name>(columns list) values(sequencename.nextval,……);
eg : Insert into Students Values (501,’kiran’) ;
(iv) Delete Command :
This Command Is Used To Delete All Or Specified Rows In A Table
Syntax : Delete From <table name> where <condition>
Eg: (i) To Delete A Single Row
Delete from students where sno=502;
Eg : (2)To Delete Multiple Rows (More Than One Row)
Delete from students where sno=503 or sno=523;
3.Data Control Language (DCL) or Transaction Control Language (TCL) :
It supports grant, revoke , commit and role back commands.
It performs the actions on transactions which have done.
(i) commit:
statement guarantees all of the transactions, modifications are made permanent part of the data base. By default, all your transactions are temporarily stored in the database.
Syntax : commit;
(ii) Rollback:
This command is used to undo work done in the current transaction. Ie the user can continue with any number of inserts, updates and / or deletion, and still undo the work, issuing the Rollback Command.
Syntax : Rollback To [Savepoint] Savepoint_Name
optional
(iii) Save point:
This command Sets a save point within a transaction or to identify a point in a transaction to which you can later roll back.
Syntax :Savepoint savepoint_name;
(iv) Grant:
Grant command is used to grant the permissions to the data base user.
Grant DBA to user1 with admin option.
