DDl Commands

oracle :
=========
1. sql
2. sql*plus commands
3. pl/sql
4. sql*loader

sql:
- 4gl
- commands [ddl,dml,dcl,tcl]

ddl - [create,alter,rename,drop,truncate]

create - used for creating schema objects

likes: table,view,index,synonym,cluster,sequence
...

syntax:
======

create table
(
colname1 datatype(size),
......................,
......................,
colname_n datatype(size)
);


create table student details
=============================
create table student
(
sno number(4),
sname varchar2(20),
course varchar2(20),
fees number(9,2),
dop date
);


to display table details
=========================
SQL> select * from tab where tname like 'STUDENT';

TNAME TABTYPE CLUSTERID
------------------------------ ------- ----------
STUDENT TABLE


to dispaly the structure of table
=================================
SQL> desc student
Name Null? Type
----------------------------------------- -------- -----------------
SNO NUMBER(4)
SNAME VARCHAR2(20)
COURSE VARCHAR2(20)
FEES NUMBER(9,2)
DOP DATE


alter commands


1. add 2. modify 3. drop 4. rename

1. add
======
synyax
======
alter table
add colname datatype(size);

to add address in student table
==============================
SQL> alter table student add address varchar2(20);

Table altered.

SQL> desc student
Name Null? Type
----------------------------------------- -------- -------------------------
SNO NUMBER(4)
SNAME VARCHAR2(20)
COURSE VARCHAR2(20)
FEES NUMBER(9,2)
DOP DATE
ADDRESS VARCHAR2(20)

2. modify:
=========
syntax:
======
alter table tablename
modify colname datatype(size);

- if data is not inserted in table
we can increase and dicrease size of datatype
and change datatype


to change the sno datatype size from 4 to 2
===========================================
alter table student
modify sno number(2);

to change the datatype of sno
=============================
alter table student
modify sno char(2);

drop
=====
syntax:
=======
alter table tablename
drop column columnname;

to drop address colunm from student table
=======================================
alter table student
drop column address;

rename
======
syntax:
=======
alter table tablename
rename column oldaname to newname;

to change dop as date_of_payment
================================
alter table student
rename column dop to date_of_payment;

rename
=-====
syntax:
======
rename oldname to newname;

ex:
===
rename student to sdetails;

TO CHECK
========
select * from tab where tname like 'SDETAILS';


drop
======
syntax:
======
drop table ;

drop student table
=================
drop table student;