Click dbms.lab.doc (366 kB)  to Download DBMS Record

                         DDL Commands

TO CREATE TABLE.

SQL> create table depart(dno number(10),dname varchar2(10),primary key(dno));

Table created.

SQL> desc depart;

Name                                      Null?                           Type

—————————————– ——– —————————-

DNO                                       NOT NULL               NUMBER(10)

DNAME                                                                    VARCHAR2(10)

SQL> create table emp(eno number(10),ename varchar2(10),dno number(10),sal number(10),jobid varchar2(10),mgrid varchar2(10),foreign key(dno) references depart(dno));

Table created

SQL> desc emp;

Name                                 Null?        Type

—————————————– ——– —————————-

ENO                                                 NUMBER(10)

ENAME                                           VARCHAR2(10)

DNO                                                 NUMBER(10)

SAL                                                  NUMBER(10)

JOBID                                              VARCHAR2(10)

MGRID                                            VARCHAR2(10)

TO ALTER THE TABLE

ADD

SQL> alter table emp add(primary key(eno),addr varchar2(10));

Table altered.

SQL> desc emp;

Name                                      Null?                           Type

—————————————– ——– —————————-

ENO                                       NOT NULL               NUMBER(10)

ENAME                                                                     VARCHAR2(10)

DNO                                                                          NUMBER(10)

SAL                                                                           NUMBER(10)

JOBID                                                                       VARCHAR2(10)

MGRID                                                                     VARCHAR2(10)

ADDR                                                                         VARCHAR2(10)

SQL> alter table emp add(phno number(5));

Table altered.

SQL> desc emp;

Name                                      Null?                           Type

—————————————– ——– —————————-

ENO                                       NOT NULL               NUMBER(10)

ENAME                                                                     VARCHAR2(10)

DNO                                                                          NUMBER(10)

SAL                                                                           NUMBER(10)

JOBID                                                                       CHAR(20)

MGRID                                                                     VARCHAR2(10)

ADDR                                                                       VARCHAR2(10)

PHNO                                                                        NUMBER(5)

MODIFY

SQL> alter table emp modify(jobid char);

Table altered.

SQL> desc emp;

Name                                 Null?                    Type

—————————————– ——– —————————-

ENO                               NOT NULL           NUMBER(10)

ENAME                                                         VARCHAR2(10)

DNO                                                              NUMBER(10)

SAL                                                               NUMBER(10)

JOBID                                                           CHAR(20)

MGRID                                                         VARCHAR2(10)

ADDR                                                            VARCHAR2(10)

PHNO                                                            VARCHAR2(10)

SQL> alter table emp modify(jobid char(20));

Table altered.

SQL> desc emp;

Name                                  Null?                    Type

—————————————– ——– —————————-

ENO                               NOT NULL           NUMBER(10)

ENAME                                                         VARCHAR2(10)

DNO                                                              NUMBER(10)

SAL                                                               NUMBER(10)

JOBID                                                           CHAR(20)

MGRID                                                         VARCHAR2(10)

ADDR                                                            VARCHAR2(10)

PHNO                                                            VARCHAR2(10)

SQL> alter table emp modify(jobid char(5));

alter table emp modify(jobid char(5))

*

ERROR at line 1:

ORA-01441: cannot decrease column length because some value is too big

DROP

SQL> alter table emp drop(phno);

Table altered.

SQL> desc emp;

Name                                 Null?                    Type

—————————————– ——– —————————-

ENO                                  NOT NULL        NUMBER(10)

ENAME                                                         VARCHAR2(10)

DNO                                                              NUMBER(10)

SAL                                                               NUMBER(10)

JOBID                                                           CHAR(20)

MGRID                                                         VARCHAR2(10)

ADDR                                                             VARCHAR2(10)

SQL> alter table emp drop(addr);

Table altered.

SQL> desc emp;

Name                                 Null?                     Type

—————————————– ——– —————————-

ENO                                NOT NULL           NUMBER(10)

ENAME                                                          VARCHAR2(10)

DNO                                                               NUMBER(10)

SAL                                                                NUMBER(10)

JOBID                                                            CHAR(20)

MGRID                                                        VARCHAR2(10)

TO DROP THE TABLE

SQL> drop table emp;

Table dropped.

SQL> desc emp;

ERROR:

ORA-04043: object emp does not exist