Manuel Gentile

Back to SQL Course - Back to Command Types

Data Definition Language

It’s used for create and modify the DB structure and its objects.

Commands are CREATE, DROP, ALTER, TRUNCATE and RENAME.

Create

It’s used for creating a database or a table.

CREATE DATABASE <databaseName>;

CREATE TABLE <tableName> (
    col1 datatype,
    ...
    colN datatype
);

Create Example

CREATE DATABASE dbTest;

CREATE TABLE employee (
    ID number(18,0),
    FIRST_NAME varchar(50),
    LAST_NAME varchar(50)
);

Drop

It’s used for dropping a database or a table.

DROP DATABASE <databaseName>;

DROP TABLE <tableName>;

Drop Example

DROP DATABASE dbTest;

DROP TABLE employee;

Alter

It’s used for altering a table content.

-- single column modification, adding a new column
ALTER TABLE <tableName> ADD <columnName> <dataType>;

-- multiple columns modification, adding more columns
ALTER TABLE<tableName> ADD (
    <columnName1> <dataType1>,
    ...
    <columnNameN> <dataTypeN>
);

-- single column modification, changing data type definition
ALTER TABLE <tableName> MODIFY <columnName> <newDataTypeDefinition>;

-- single column modification, adding a new column
ALTER TABLE <tableName> ADD <columnName> <dataType>;

-- multiple columns modification, adding more columns
ALTER TABLE<tableName> ADD (
    <columnName1> <dataType1>,
    ...
    <columnNameN> <dataTypeN>
);

-- single column modification, dropping a column
ALTER TABLE <tableName> DROP <columnName>;

-- multiple columns modification, dropping more columns
ALTER TABLE<tableName> DROP (
    <columnName1>,
    ...
    <columnNameN>
);

Alter Example

ALTER TABLE<tableName> ADD (
    BIRTH_DATE date,
    BIRTH_COUNTRY varchar(50)
);

-- we can always increase the dimension of a column, but can't decrease it
ALTER TABLE employee MODIFY LAST_NAME varchar(100);

ALTER TABLE employee DROP (
BIRTH_DATE,
BIRTH_COUNTRY
);

Truncate

It’s used for removing a table content, while keeping the structure.

Performing a select right after will show no records in the table.

TRUNCATE TABLE <tableName>;

Same result of

DELETE FROM TABLE <tableName>;

but TRUNCATE is faster.

Alter Example

TRUNCATE TABLE employee;

Rename

Used to change the name of an existing table, depending on the RDBMS.

RENAME TABLE <tableName> TO <newTableName>;

ALTER TABLE <tableName> RENAME TO <newTableName>;

Rename Example

ALTER TABLE employee RENAME TO employees;

Back to SQL Course - Back to Command Types

Let’s connect

If you want to learn more about the topic, connect or send me a DM.

Website