EX NO:1
DATE: 20-08-09 DDL COMMANDS
AIM:
To write a query with a functions of DDL commands such as create, alter, drop.
CREATE A TABLE CUSTOMER14 WITH SOME ATTRIBUTES
Customer id
Name
Address
Phone no
Date of birth
Age
//create an table with create table command
SQL> create table customer014(customerid number(25),name char(25),address char(25),pno number(25),dob date,age number(10));
Table created.
//To view the table use desc command
SQL> desc customer014
Name Null? Type
------
CUSTOMERID NUMBER(25)
NAME CHAR(25)
ADDRESS CHAR(25)
PNO NUMBER(25)
DOB DATE
AGE NUMBER(10)
//use alter command and add the primary key to customer id
SQL>alter table customer014add(primary key(customerid));
Table altered.
SQL> desc customer014
Name Null? Type
------
CUSTOMERID NOT NULL NUMBER(25)
NAME CHAR(25)
ADDRESS CHAR(25)
PNO NUMBER(25)
DOB DATE
AGE NUMBER(10)
//use the alter command and modify the customer table
SQL> alter table customer014 modify(name varchar2(20),address varchar2(10));
Table altered.
SQL> desc customer014
Name Null? Type
------
CUSTOMERID NOT NULL NUMBER(25)
NAME VARCHAR2(20)
ADDRESS VARCHAR2(10)
PNO NUMBER(25)
DOB DATE
AGE NUMBER(10)
//use the alter command and rename the column of the customer table
SQL> alter table customer014 rename column customerid to cid;
Table altered.
SQL> desc customer014
Name Null? Type
------
CID NOT NULL NUMBER(25)
NAME VARCHAR2(20)
ADDRESS VARCHAR2(10)
PNO NUMBER(25)
DOB DATE
AGE NUMBER(10)
//use the alter command and drop the column in the customer table
SQL> alter table customer014 drop(address,age,pno);
Table altered.
SQL> desc customer014
Name Null? Type
------
CID NOT NULL NUMBER(25)
NAME VARCHAR2(20)
DOB
//use the alter command and add the column in the customer table
SQL> alter table customer014 add(street varchar2(20),city varchar2(20),pincode number(10));
Table altered.
SQL> desc customer014
Name Null? Type
------
CID NOT NULLNUMBER(25)
NAME VARCHAR2(20)
DOB DATE
STREET VARCHAR2(20)
CITY VARCHAR2(20)
PINCODE NUMBER(10)
//create an another table
SQL> create table phno014(cid number(10),phoneno number(10),location varchar2(15));
Table created.
SQL> desc phno014
Name Null? Type
------
CID NUMBER(10)
PHONENO NUMBER(10)
LOCATION VARCHAR2(15)
//use the alter command and add the primary key
SQL> alter table phno014 add(primary key(cid,phoneno));
Table altered.
SQL> desc phno014
Name Null? Type
------
CID NOT NULL NUMBER(10)
PHONENO NOT NULL NUMBER(10)
LOCATION VARCHAR2(15)
//rename the table name
SQL> rename phno014 to phoneno014;
Table renamed.
SQL> desc phoneno014
Name Null? type
------
CID NOTNULL NUMBER(10)
PHONENO NOTNULL NUMBER(10)
LOCATION VARCHAR2(15)
//drop the table
SQL> drop table phoneno014;
Table dropped.
SQL> desc phoneno014;
ERROR:
ORA-04043: object phoneno014 does not exist
RESULT:
Thus the query using DDL commands was executed successfully.