Learn SQL Data Definition Language by Examples

 

 What is SQL?

SQL stands for Structured Query Language, it allows you to access update, retrieve, and manage the relational database.

ANSI SQL

ANSI stands for American National Standards Institute adapted the database language SQL. SQL is based on ANSI standards to support most widely used databases.

What is the latest version of SQL?

ISO in 2016 released a new version of SQL ISO/IEC9075. 

 

Data Definition Language (DDL)

 It is a language used for creating and and managing database objects such as tables, views, and users

DDL consists of SQL commands like create, alter, and drop here are some examples:

 

Create Table Syntax

CREATE TABLE table_name (
column1 DATA TYPE,
column2 DATA TYPE(SIZE),
column3 DATA TYPE(SIZE),
); 

Example:

  CREATE TABLE customers (
customer_id int,
first_name varchar(20),
last_name varchar(20),
);

To show the structure of the newly created table we use Describe command.

DESCRIBE Table_Name;

Describe Customers; 

Drop Table Syntax

DROP TABLE Table_name;

Example

DROP TABLE customers;

Alter 

Alter command is used to add, modify, delete column from exiting table.

ALTER TABLE Table_name

ADD New_Column Data_Type(Size);

Example:

ALTER TABLE Customers

ADD country varchar(20);

Alter to modify column name from exiting table in our example here customers table.

Syntax:

ALTER TABLE Table_name

MODIFY Old_name to New_name;

Example: Rename country column to customer_country.

ALTER TABLE Customers

MODIFY country to customer_country;

 

Alter to modify column data type from exiting table in our example here we would like to modify

customer_country data type from varchar to char.

Syntax:

ALTER TABLE Table_name

MODIFY Column_name New_Data_Type;

Example: Rename country column to customer_country.

ALTER TABLE Customers

MODIFY customer_country char;

 

Alter command to modify column size from exiting table.

Syntax:

ALTER TABLE Table_name

MODIFY Column_name Data_Type (New Size);

 

Example: Modify customer_country column from (20) to (30).

ALTER TABLE Customers

MODIFY customer_country char(30);

 

 

Note: 

• we can update the data  type and size at the same time with a single command.
• If the table contains data we should be careful when decreasing the size.