SQL Commands

Ø   Create a table

Syntax: CREATE TABLE TABLENAME

                     (

                     COLUMN_NAME1 COLUMNTYPE(SIZE),

                     COLUMN_NAME2 COLUMNTYPE(SIZE),

                     .                              .

                     .                              .

                     .                              .

                     COLUMN_NAMEn COLUMNTYPE(SIZE)

                     );

Example: Create a table DEPOSIT with columns ACCNO NUMBER(3), NAME VARCHAR2(10), BNAME CHAR(2), BALANCE NUMBER(6).

SQL > CREATE TABLE DEPOSIT

                     (

                     ACCNO NUMBER(3),

                     NAME VARCHAR2(10),

                     BNAME CHAR(2),

                     BALANCE NUMBER(6)

                     );

OUTPUT > Table created.

-------------------------------------------------------------------------------------

Ø   Display the structure of a table

Syntax: DESC TABLENAME;

Example: Display the structure of the DEPOSIT table.

SQL > DESC DEPOSIT;

OUTPUT >

NAME	NULL	TYPE
ACCNO		NUMBER(3)
NAME		VARCHAR2(10)
BNAME		CHAR(2)
BALANCE		NUMBER(6)

-------------------------------------------------------------------------------------

Ø   Add columns to a table

Syntax: ALTER TABLE TABLENAME

          ADD COLUMN_NAME TYPE(SIZE);

Example: Add columns LDATE DATE, ACTYPE CHAR(1) to the DEPOSIT table.

SQL > ALTER TABLE DEPOSIT

         ADD LDATE DATE, ACTYPE CHAR(1);

OUTPUT > Table altered.

-------------------------------------------------------------------------------------

Ø   Alter a table to make a primary key

Syntax: ALTER TABLE TABLENAME

          ADD PRIMARY KEY(COLUMN_NAME);

Example: Alter table DEPOSIT to make ACCNO as primary key.

SQL  > ALTER TABLE DEPOSIT

          ADD PRIMARY KEY(ACCNO);

OUTPUT > Table altered.

-------------------------------------------------------------------------------------

Ø   Drop a column from a table

Syntax: ALTER TABLE TABLENAME

                DROP(COLUMN_NAME);

Example: Drop the column ACTYPE from the table DEPOSIT.

SQL > ALTER TABLE DEPOSIT

               DROP(ACTYPE);

OUTPUT > Table altered.