SQL> Create Table Student(Rollno Number(3),Name Varchar(15),Address Varchar(20),Phno Number(10));

Ex No:1
Date: / DDL(Data Definition Language) Commands

AIM:

To write a query with a function of DDL commands such as create , alter , drop.

PROGRAM:

CREATE TABLE:

This sql command is used to creating a table .

SQL> create table student(rollno number(3),name varchar(15),address varchar(20),phno number(10));

Table created.

SQL> desc student

Name Null? Type

------

ROLLNO NUMBER(3)

NAME VARCHAR2(15)

ADDRESS VARCHAR2(20)

PHNO NUMBER(10)

ALTER TABLE:

This sql command is used to alter or do any changes in a table .

SQL> alter table student add(age number(2));

Table altered.

SQL> desc student

Name Null? Type

------

ROLLNO NUMBER(3)

NAME VARCHAR2(15)

ADDRESS VARCHAR2(20)

PHNO NUMBER(10)

AGE NUMBER(2)

PRIMARY KEY:

This sql command is used to find a unique value.

SQL> alter table student add(primary key(rollno));

Table altered.

SQL> desc student

Name Null? Type

------

ROLLNO NOT NULL NUMBER(3)

NAME VARCHAR2(15)

ADDRESS VARCHAR2(20)

PHNO NUMBER(10)

AGE NUMBER(2)

MODIFY THE DATATYPE:

This sql command is used to modify a table .

SQL> alter table student modify(rollno number(5));

Table altered.

SQL> desc student

Name Null? Type

------

ROLLNO NOT NULL NUMBER(5)

NAME VARCHAR2(15)

ADDRESS VARCHAR2(20)

PHNO NUMBER(10)

AGE NUMBER(2)

RENAME COLUMN:

This sql command is used to rename a column in a table .

SQL> alter table student rename column phno to phoneno;

Table altered.

SQL> desc student

Name Null? Type

------

ROLLNO NOT NULL NUMBER(5)

NAME VARCHAR2(15)

ADDRESS VARCHAR2(20)

PHONENO NUMBER(10)

AGE NUMBER(2)

DELETE COLUMN:

This sql command is used to delete a column in a table .

SQL> ALTER TABLE STUDENT DROP(AGE);

Table altered.

SQL> desc student

Name Null? Type

------

ROLLNO NOT NULL NUMBER(5)

NAME VARCHAR2(15)

ADDRESS VARCHAR2(20)

PHONENO NUMBER(10)

RENAME THE TABLE NAME:

This sql command is used to delete a table name .

SQL> rename student to student1;

Table renamed.

SQL> desc student1

Name Null? Type

------

ROLLNO NOT NULL NUMBER(5)

NAME VARCHAR2(15)

ADDRESS VARCHAR2(20)

PHONENO NUMBER(10)

SQL> desc student

ERROR:

ORA-04043: object student does not exist

DELETE THE TABLE:

This sql command is used to delete a table .

SQL> drop student1;

Table droped.

SQL> desc student1

ERROR:

ORA-04043: object student1 does not exist

RESULT

Thus the DDL commands are created,altered and deleted.

Database Management System Lab