Data Definition Language: DDL SQL commands with examples

0
97

▪ Data Definition Language (DDL) is a standard for commands that define the different structures of tables, functions or views in a database
They are of type:

CREATE :

Use to create objects like CREATE TABLE, CREATE FUNCTION, CREATE SYNONYM, CREATE VIEW. Etc.

Create New Table

create table table_name_1 (id_col number(5) primary key, name_col varchar(20));

Create Table from existing table

Below code creates new table from old table of all with id>2000, and make it

new_id = (id-2000)

create table table_name_2 as select (id_col-2000), name_col as table_name_2 (from table_name_1 where id_col>2000

ALTER :

Use to Alter Objects like ALTER TABLE, ALTER USER, ALTER TABLESPACE, ALTER DATABASE. Etc.

To add new column

alter table table_name_1 add (new_col varchar(20) , other_col int(20));

To drop existing column

alter table table_name_1 drop column (new_col, other_col) <cascade constraints>;

Add constraints on existing columns

Modify Constraint: ALTER TABLE table_name_1 MODIFY(id_col NOT NULL);

Primary Key: alter table table_name_1 add constraint idpk primary key (id_col);

Foreign Key: ALTER TABLE table_name_2 ADD CONSTRAINT id_col_fk FOREIGN KEY (id_col) REFERENCES table_name_1(id_col) <ON DELETE CASCADE/NOT NULL>;

▪ CHECK Constraints:
Use the check constraint to validate values entered into a column alter table table_name constraint constraint_name check (condition(s));
▪ UNIQUE KEY alter table table_name add constraint constraint_name unique (column_name);
▪ DEFAULT: ALTER TABLE table_name ALTER COLUMN column_name DEFAULT value; ALTER TABLE table_name ALTER COLUMN column_name DROP DEFAULT;

DROP :

Use to Drop Objects like DROP TABLE, DROP USER, DROP TABLESPACE, DROP FUNCTION. Etc.

Dropping Constraint

alter table table_name drop constraint constraint_name;

Dropping Table

drop table table_name <cascade constraints>;

RENAME :

Use to Rename table names

rename table_name_2 to new_table_name

TRUNCATE :

Use to truncate (delete all rows) a table

truncate table table_name_2