The ALTER TABLE Statement

View All Scripts Login to Run Script

Script Name The ALTER TABLE Statement
Description Example on using the ALTER TABLE statement to add, modify, delete, and rename a column
Area SQL General / DDL
Contributor Pangalan dito
Created Monday June 06, 2022

Statement 1

Create the PRODUCTS table.

CREATE TABLE products ( 
    prod_id NUMBER(4,0) 
)

Table created.

Statement 2

DESCRIBE products

TABLE PRODUCTS
Column Null? Type
PROD_ID  - NUMBER(4,0)

Column	Null?	Type
PROD_ID	-	NUMBER(4,0)

Statement 4

DESCRIBE products

TABLE PRODUCTS
Column Null? Type
PROD_ID NOT NULL NUMBER(4,0)

Column	Null?	Type
PROD_ID	NOT NULL	NUMBER(4,0)

Statement 6

DESCRIBE products

TABLE PRODUCTS
Column Null? Type
PROD_ID NOT NULL NUMBER(6,0)

Column	Null?	Type
PROD_ID	NOT NULL	NUMBER(6,0)

Statement 7

Use the ADD clause to add a new column to a table.

The ADD Clause

ALTER TABLE products 
ADD product_description VARCHAR2(50) NOT NULL

Table altered.

Statement 8

DESCRIBE products

TABLE PRODUCTS
Column Null? Type
PROD_ID NOT NULL NUMBER(6,0)
PRODUCT_DESCRIPTION NOT NULL VARCHAR2(50)

2 rows selected.

Column	Null?	Type
PROD_ID	NOT NULL	NUMBER(6,0)
PRODUCT_DESCRIPTION	NOT NULL	VARCHAR2(50)

Statement 10

DESCRIBE products

TABLE PRODUCTS
Column Null? Type
PROD_ID NOT NULL NUMBER(6,0)

Column	Null?	Type
PROD_ID	NOT NULL	NUMBER(6,0)

Statement 12

DESCRIBE products

TABLE PRODUCTS
Column Null? Type
PRODUCT_NUM NOT NULL NUMBER(6,0)

Column	Null?	Type
PRODUCT_NUM	NOT NULL	NUMBER(6,0)

Additional Information