SQL
DDL Commands
Saroj S Shivagunde
Overview
• Data Definition Language (DDL)
• CREATE Keyword
• Create new database, table or view
• DROP Keyword
• Delete a database, table or view
• ALTER Keyword
• Add/delete columns or constraints, change the data type of columns
• TRUNCATE Keyword
• Truncate a table
Data Definition Language (DDL)
• Structured Query Language (SQL) is used to build and access the database
• A database contains tables that store the information of different instances of
entities and the relationships between them
• DDL has commands that facilitate the creation and deletion of databases, tables
and views
• It can also facilitate the alteration of the schema or datatype of columns of the
tables
• It also facilitates the deletion of data without deleting the schema
CREATE Keyword
• Creating a database
• Syntax- CREATE DATABASE < Database_Name >;
• Example- Query to create a database named ‘University’
CREATE DATABASE University;
• Creating a table
• Syntax- CREATE TABLE < Table_Name > ( < Column_Name_1 >< Datatype_1 >,
< Column_Name_2 >< Datatype_2 >, …
< Column_Name_n >< Datatype_n >);
• Example- Query to create a table named ‘Student’ that has 3 attributes- Roll number, Name
and Phone number
CREATE TABLE Student (Roll_No INT, Name VARCHAR(50), Phone_No INT);
CREATE Keyword
• Creating a view
• A view shows a subset of a table and it is virtual in nature
• To create a view we use SELECT statement that extracts the desired subset from the
underlying table in the dataset
• Syntax- CREATE VIEW < View_Name > AS < Subquery >;
• Example-
A query to create a view named ‘Student_View’ for a student with roll number 1. Being a
student, he can only see the details of himself.
CREATE VIEW Student_View AS SELECT ∗ FROM Students WHERE Roll_No = 1;
DROP Keyword
• DROP keyword is used to delete a database, table or a view
• It deletes the said object along with its schema or metadata
• Delete a database
• DROP DATABASE University;
• Delete a table
• DROP TABLE Student;
• Delete a view
• DROP VIEW Student_View;
ALTER Keyword
• ALTER keyword is used to add, modify or delete a column or a constraint from a
table
• ALTER with ADD
• If we wish to add a column named ‘Email_ID’ to the ‘Students’ table after its creation
ALTER TABLE Students ADD Email_ID VARCHAR(50);
• If we wish to add a primary key constraint on the ‘Roll_No’ column of table ‘Students’
ALTER TABLE Students ADD PRIMARY KEY(Roll_No);
• ALTER with MODIFY
• If we wish to modify the datatype of ‘Email’ column of table ‘Students’
ALTER TABLE Students MODIFY COLUMN Email VARCHAR(100);
ALTER Keyword
• ALTER with DROP
• If we wish to delete a column named ‘Email_ID’ from the ‘Students’ table after its creation
ALTER TABLE Students DROP COLUMN Email_ID;
• If we wish to delete a primary key constraint on the ‘Roll_No’ column of table ‘Students’
ALTER TABLE Students DROP PRIMARY KEY;
• ALTER keyword can also be used on the views
• However, it works in the same way as creating a new view because the views are virtual in
nature
• Hence, instead of using ALTER command for view, one may even choose to DROP the view
and create a new view again
TRUNCATE Keyword
• TRUNCATE keyword deletes all the data from a table without deleting its schema
• So, the attribute values are deleted from a table, but the columns and constraints
remain intact
• TRUNCATE on a table
• TRUNCATE TABLE Students;
• TRUNCATE command is different than DROP command
• DROP deletes the whole table- the data and the schema
• TRUNCATE deletes only the data and keeps the schema intact
End
