SQL DDL commands

Read this article to understand the SQL DDL commands (Data Definition Language) along with the examples. We have shared few examples and we have shared basic as well as advance level common interview questions also.

In this post, I will provide you a short overview of DDL(Data Definition Language) commands with all its options. In addition, we have covered some questions which we receive from the audience. 

First, let’s talk about the theory of DDL(Data Definition Language) commands and why/when/how we should use it.

Definition

DDL stands for Data Definition Language. As it’s name suggest Data Definition so all commands are used to manage the structure of the database objects such as Database, Schema, Table View etc. The DDL commands are used to create, alter, drop and rename the database object.

Reasons to Use SQL DDL commands

As it’s name suggest DDL, we can use DDL commands for manipulating the database object. Below are the some key areas where we need to use DDL commands.

1. For managing the structure and organisation of a database.
2. For controlling database security and performance.
3. Creating a database and tables for storing the data.
4. Creating indexes for managing the performance.

Here are some commonly used DDL commands in SQL:

Create

Create command is used to create a database object. We can create all types of database objects using create command. Create command always create a new object which are not exists. Once the object is created, it will automatically committed and saved. Some database engine supports the create or replace commands for existing objects. Below are the few database objects

1. Database
2. Table
3. View
4. Index
5. Stored Procedure

Syntax

Creation of object using SQL having Create command but the syntax of creating different object is different. Below are the syntax of each object.

CREATE database database_name;

Above syntax create the database object.

CREATE TABLE table_name(column_name datatype(size),....);

Mentioned syntax creates a table and add columns based on requirements.

CREATE VIEW view_name
AS
SELECT * FROM table_name
[WHERE condition];

Above query creates a view. Understand the view in detail here.

Lets create a sample database and add one table with few columns.

Example

Example 1 :

Create a database with the name learnoffline_db

create database learnoffline_db;

Example 2:

Create a table Customers for storing customer ID, first name ,last name, age and country.

create table Customers (
customer_id int,
first_name varchar(20),
last_name varchar(20),
age int,
country varchar(20)
)

Create table command is used to create a table. We have added 5 columns and each column is added using column name, data type and size of the data. We have added customer_id column with integer type and size will be 4 bytes. Next column is first_name and data type is varchar with size 20 bytes. Lets understood the remaining data types here and comment in the comment section if any more information is required on it.

Above query will create table and we can see the below structure of the table

Alter

As it’s name suggest to alter or update the structure of the table. Alter command is used to add the columns into the table, rename, drop or modify the existing columns in terms of size, datatype or apply any constraint on the column.

ADD column is used to add a new column into the existing table. While adding the column we need to provide column name data type and max size for the data.

MODIFY COLUMN is used to alter or update or modify the existing column for changing the datatype, size or adding any constraints.

DROP COLUMN is used to drop the column from the table.

RENAME COLUMN is used to rename the column name to new name.

Syntax

ALTER TABLE table_name ADD (column_name datatype size);

ALTER TABLE table_name MODIFY COLUMN (column_name datatype size);

ALTER TABLE table_name DROP COLUMN column_name;

ALTER TABLE table_name RENAME COLUMN old_name TO new_name;

Example

Example 3 :

Add a new column gender into the existing table Customers.

alter table customers add gender varchar(10);

Above query will add new column gender of varchar type with size 10.

Example 4 :

Modify a country column for size from 20 bytes to 10 bytes because we don’t have large name countries in the data.

alter table customers modify column country varchar(10);

Above query has updated the size using modify column command.

Example 5 :

Let’s Drop a gender column.

alter table customer drop column gender;

This command has dropped the gender column. Check the new structure of the table.

Example 6 :

Rename the column customer_id to cust_id for temporary purpose.

alter table customer rename customer_id to cust_id;

Above query has updated the column name and below is the structure of table Customers.

I hope, All above examples clear the alter table command.

Drop

This command is used to drop the database object permanently. Drop and delete commands are different. Drop command is a DDL command which drop the database object while Delete is DML command which delete the records from the table.

Syntax

DROP object_type object_name;

Database objects are any object like database, table, view, index or stored procedure.

Example

Example 7:

Delete a table Customers from the database.

drop table Customers;

Example 8 :

Delete a database learnoffline_db.

drop database learnoffline_db;

Drop database command will drop everything which is present into the database like tables, views, indexes, stored procedures etc.

Truncate

Truncate command is used to delete the whole data from the table but keep the structure of the table as it is. Actually, Truncate command drop the table and re-create the whole structure of the table once again. Deleting all the records from the table took time so when you have to delete everything and save the time so you can use the truncate command.

Syntax

TRUNCATE TABLE table_name;

Example

Example 9:

truncate table Customers;

Above command will delete whole data from the table and keep only the structure of the table.

Rename

As it’s name suggest to rename the database object. On some DBMS system the rename will not work so In that case we will use the Alter command with rename option. Check the Alter command with Rename option in this post.

Syntax

RENAME database_object old_name TO new_name;

Example

Example 10 :

rename database learnoffline_db to learn_sql_db;

Above command will rename the database from learnoffline_db to learn_sql_db.

Example 11 :

rename table Customers to customer_details;

Above command will rename the table from Customers to customer_details.

We can use Rename command to rename the column also. Please check the syntax of ALTER command.

I hope, All above examples clear the concept of DDL and the commands which comes under DDL.

Linking

DDL commands are basically used to create and manage the structures of the database objects. Mostly we use Create, Alter, Drop, Truncate and Rename commands to manage the structure of the Database, Table, View, Schema, Index and Stored procedures. You can refer all the interview questions and Quiz. Please refer all the links for understanding the DDL commands in depth.

DDL commands with Latest SQL Interview Questions 2023

Basic DDL commands SQL Interview questions

1. What are some examples of DDL commands in SQL?
2. What is the difference between a CREATE and an ALTER command in SQL?
3. Can you explain what the TRUNCATE command does in SQL?
4. Can you explain the syntax of the ALTER TABLE command in SQL?

Intermediate DDL commands SQL Interview questions

1. How do you troubleshoot issues with DDL commands that are not working as expected?

Advanced DDL commands SQL Interview questions

1. Describe the process of adding a new column to a table that contains millions of rows. What are some potential performance issues you might encounter, and how would you mitigate them using DDL commands?

Questions covered from comment section

In this section we will cover the new questions as well as answers. Please comment the questions into comment section so we will include it here.

Quiz

Solve few MCQ questions on DDL commands [ays_quiz id=’2′]

I hope you have enjoyed this session. Happy offline learning… Lets meet in next session.