Division of SQL
There are three major categories:
1. Data definition language (DDL): This is used a set of commands that defines data base objects. (Create, alter, drop and Rename).
(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 (i.e.
column width).
Syntax :
Create table <table name> (<column1> <Data
Type> [Size],
<column2> <Data Type>
[Size],------------------------------<column N> <Data Type>
[Size]);
Eg., Create Table Sample ( sno number(3), sname
varchar2(10));
(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 view, 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
sample set sno=500 where sno=100;
(ii)To update Multiple column values
- Update
sample set sno=111,sname=’ Nandhini’ where sno=500;
(iii)To update all values with same
value
- Update
sample set sno=200;
(iii) Insert Command :
Syntax :
Insert into <table name>(columns list)
values(sequencename.nextval,……);
eg : Insert into Student Values
(100,’Naveen’) ;
(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 sample where sno=109;
Eg : (2)To Delete Multiple
Rows (More Than One Row)
- Delete
from sample where sno=106 or sno=108;
3.Data Control Language (DCL) or Transaction Control Language (TCL) :
It supports grant,
revoke , commit and role back commands.
(i) A 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) This Rollback 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 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;
No comments:
Post a Comment