In this post, we shall gain an understanding of the commands in DDL using CREATE, ALTER, and DROP.

CREATE

CREATE Statement The numerous forms of the SQL CREATE statement is responsible for constructing vital database structures and objects – tables, views, schemas, domains and so on. The act of creating tables could be easy, but you need to take into consideration numerous factors. Some of the factors to take into consideration through when creating tables:

• Type of data the table will contain

• Table and column names

• Primary key (the column that makes each row of data unique to avoid

  duplicate records in a table)

• Column length

• Columns containing null values

CREATE DATABASE DATABAS_NAME
CREATE TABLE TABLE_NAME (
col1 DATA_TYPE,
col2 DATA_TYPE,
col3 DATA_TYPE
);

CREATE DATABASE CustomerDetails;
CREATE TABLE customer(
    id INT PRIMARY KEY NOT NULL,
    firstName VARCHAR(100) NOT NULL,
    lastName VARCHAR(100) NOT NULL,
    gender VARCHAR(6) NOT NULL,
    city VARCHAR(100) NOT NULL,
    country VARCHAR(100) NOT NULL
);

INSERT INTO customer(id, firstName, LastName, gender, city, country)
VALUES (10001    ,'Cornelius'    ,'Kujawa'    ,'Male'    ,'Tokyo'    ,'Japan'),
(10002    ,'Patrica'    ,'Courville',    'Female',    'New York Metro',    'USA'),
(10003    ,'Sanford'    ,'Xiong',    'Male',    'Sao Paulo'    ,'Brazil'),
(10004    ,'Allen',    'Burrus',    'Male',    'Seoul',    'South Korea'),
(10005    ,'Kathrine'    ,'Fritzler',    'Female',    'Mexico City'    ,'Mexico'),
(10006    ,'Colin',    'Minter',    'Male',    'Osaka',    'Japan'),
(10007    ,'Velda',    'Kimberling',    'Female',    'Manila',    'Philippines'),
(10008    ,'Vernon',    'Addy',    'Male',    'Mumbai',    'India'),
(10009    ,'Blythe',    'Fleischer',    'Female'    ,'Delhi',    'India'),
(10010    ,'Tad'    ,'Hammack',    'Male',    'Jakarta',    'Indonesia');

To get the data used for this tutorial: click here

ALTER

This command statement is in charge of changing the database structure after it has been created. Its syntax is

--CHANGE A DATA TYPE
ALTER TABLE customer 
ALTER COLUMN Gender VARCHAR(15) NOT NULL;

--check if the data type change was effective
SELECT 
TABLE_CATALOG,
TABLE_SCHEMA,
TABLE_NAME, 
COLUMN_NAME, 
DATA_TYPE 
FROM INFORMATION_SCHEMA.COLUMNS
WHERE TABLE_NAME = 'customer'

--ADD A COLUMN TO 'customer'
ALTER TABLE customer 
ADD timeStampColumn TIMESTAMP NOT NULL;

-- add a new column sn with the data type int and increase by 1
ALTER TABLE customer 
ADD SN INT IDENTITY(1,1) NOT NULL

-- DELETE A COLUMN
ALTER TABLE customer 
DROP COLUMN timeStampColumn;


-- RENAME A COLUMN

EXEC sp_rename 'customer.Country' , 'CountryName', 'COLUMN';
SELECT * FROM customer;

DROP

This command is the reverse of the CREATE statement, which destroys the database structure. Its syntax is:

--DROP 
DROP DATABASE CustomerOrderDetailsDatabase
DROP TABLE customer