What Are DDL Commands in SQL?

Introduction  

SQL stands for Structured Query Language and is used to store, alter, and retrieve data from a database server. It can handle big datasets and is very adaptable. DDL commands in SQL define database objects. They allow us to create, delete, and modify tables.  

DDL commands are called Data Definition Language commands as they construct a database structure. When we save information in a database, we must construct tables and describe their layout.  

This article will cover the most important DDL commands in SQL. 

Categories of SQL Commands  

DDL, DML, DCL, TCL, and DQL are the five types of SQL commands. Each category is discussed below: 

1) DDL – Data Definition Language 

DDL commands in SQL are used to construct the database structure in the Data Definition Language. It only deals with database schema descriptions and is used to build and alter the structure of database objects. 

It is capable of defining the database schema. It enables the addition, modification, and deletion of logical structures that store data or allow users to access/maintain data. 

• CREATE – to construct a database and its associated items. 
• ALTER – modifies the structure of existing databases. 
• DROP – removes things from databases. 
• TRUNCATE – deletes all records from a table, including the memory allocated for the data. 

2) DQL – Data Query Language 

DQL commands are used to access data stored in database objects. The purpose of the DQL Commands is to return a schema relation based on the query submitted to it.  

DQL is a component of a SQL statement that allows you to get and sort data from a database. 

Data scientists and analysts use this the most. 

• Select command: It is beneficial for data analysis. 

3) DML – Data Manipulation Language 

SQL instructions that manipulate data in a database are categorized as DML (Data Manipulation Language), which encompasses the majority of SQL statements. The SQL statement element governs who has access to the data and the database. 

It allows us to interact with data that is entered into the database, such as 

• SELECT – Gathers data from a table or database. 
• INSERT – Add entries into a table or database. 
• UPDATE – Refreshes existing data in a table/database. 
• DELETE – Clears all database information in a table/database  

4) DCL – Data Control Language  

Data Controlling Language (DCL) is a query language that enables users to obtain and update data from databases. Grant and Revoke are two types of Data Controlling Language commands. 

It enables us to grant and revoke permissions on a specific database or table. 

• GRANT – Allows people to have access to the database/table. 
• REVOKE – Removes users’ access rights granted by the GRANT command. 

5) TCL – Transaction Control Language 

In the database, Transaction Control Language commands govern transactions in the database. This command is used to control modifications to DML statements. TCL allows you to group your arguments into logical transactions. 

It makes it easier to commit and cancel modifications on the server. It also permits us to save points anywhere we see fit. 

• COMMIT– Permanently stores all modifications made during the prior transaction. 
• ROLLBACK – Cancels all changes made in the previous transaction. 
• SAVEPOINTS – The save points command creates a name transaction save-point based on the identifier’s name. 

What is DDL in SQL?  

• DDL in SQL is a language that allows users to specify database components and their relationships. 
• These commands deal with table structure, such as creating, removing, and changing tables. 
• While defining the table structure, DDL commands allow us to specify and update the data types of table columns and the integrity requirements. Integrity constraints are rules that govern how data is stored in a table. 
• All DDL commands are auto-committed, meaning any changes made with them are irrevocably recorded in the database but can be undone. 

For instance, if a school wishes to keep its data in a database, we must establish tables such as students and teachers. Additionally, the structure of tables must be defined; for example, the student’s table will include columns such as their roll number, name, gender, birth date, address, and so on. 

List Of Commands in DDL with Syntax  

CREATE: 

CREATE is a DDL command in SQL used to create tables or databases. Users provide table names, field names, datatypes for each column, and column widths when we create a table. If the table has any integrity requirements or key criteria, such as PRIMARY KEY, UNIQUE, NOT NULL, etc., we must describe them along with the field names and specifications.  

Important Key Points

1. The following are some of the most important datatypes in SQL:

• int/number: Integer numbers are stored. 
• varchar : Stores string 
• date : Stores date

2. A table’s data columns must comply with criteria known as integrity constraints.

3. Examples of SQL integrity constraints are:

• PRIMARY KEY:A primary key is a unique column in a table that is utilized to uniquely identify each table entry. 
• FOREIGN KEY:A foreign key is a column that is used to describe a connection between two tables by citing another table’s primary key. 
• NOT NULL:The integrity constraint not null specifies that this column cannot be null. It must have some data. 

4. Not case-sensitive.

Syntax: 

CREATE TABLE table_name 

( 

column1 datatype ( size of the column ), 

column2 datatype ( size of the column ), 

column3 datatype ( size of the column ), 

column4 datatype ( size of the column ), 

column5 datatype ( size of the column ), 

    ….. 

); 

Let’s use the CREATE DDL command to build a table that stores student data. 

–Making a new table using Create command 

CREATE TABLE School 

( 

    –Specifying the table’s field names and data type. 

   Admission_no. INT PRIMARY KEY, 

    Name VARCHAR(25), 

    Age INT, 

    Gender VARCHAR(6) 

    Address VARCHAR(40) 

); 

Output: 

Admission_no.         Name                       Age                      Gender                  Address 

Using the DDL command CREATE, we have built a new table called School. The table has five columns, which are provided in the command, along with their data types and the size of the column. This table PRIMARY KEY is Admission no, and it is provided when the table is defined using the PRIMARY KEY integrity constraint. In the output section, a new table school with the requested columns is created using the CREATE command. We have not yet entered any data into the table. Therefore, it is empty.  

ALTER: 

ALTER is a DDL command that changes the layout of a database table. Using this SQL command, we can add a new column, remove any column, add or remove integrity requirements, or change the data type of an existing column field in the present table. The ALTER command is used to change the table’s present framework. 

Syntax: 

ALTER TABLE table_name ADD column_name column-definition; 

Example – 1: 

Let’s use the ALTER DDL command to add a new column to the School table. 

ALTER TABLE School ADD Marks Obtained INT; 

Output: 

Admission_no.      Name             Age                 Gender          Address          Marks Obtained 

We used the ALTER command in SQL to modify the existing table School. Using the ALTER DDL command in SQL, we introduced a new column named Marks Obtained with data type INT. The field Marks Obtained has been inserted into the existing table, as demonstrated in the output section. 

Example – 2: 

Let’s look at another example of changing an existing column in the table. 

ALTER TABLE School ALTER COLUMN Admission_no. VARCHAR; The ALTER statement is used in this command to alter the data type of the existing column Admission_no. from int to varchar in the School table. 

TRUNCATE: 

TRUNCATE is an SQL command that deletes all records from a table while preserving its structure. This command is used to delete all of the records and free up the table’s space. The table’s format, including field names and information, remains unaffected. As a result, the table can be utilized later to hold additional data. 

Syntax: 

TRUNCATE TABLE table_name; 

Example 

1. TRUNCATE TABLE School;  

Consider the following scenario: the school has begun a new session of students and wants to keep their information in the same framework. They can utilize the same table by deleting the records from the existing table while maintaining the table’s layout. The query above successfully eliminated all of the records from the school table. 

DROP: 

DROP is a DDL in SQL command used to remove an existing table from the database fully. This command discards the table records as well as the layout of the table. The table and all of its elements are permanently removed from the database and cannot be recovered. 

Syntax: 

DROP TABLE table_name; 

Example: 

Let us remove the SCHOOL table completely from the database. 

DROP TABLE School; 

The DROP command shown above discards the School table from the database. 

Comment: 

SQL comments are similar to comments in other programming languages like C, Java, C, Python, and more. They are mostly used to designate a code area for easy comprehension. Comments can be of three types: 

• Single-line, 
• Multi-line,  
• Inline types.  

Conclusion  

It is critical to grasp the fundamental DDL commands in SQL to use and alter the dataset. The DDL commands assist in creating, updating, altering, and removing data from tables. Now that you understand “What are DDL commands in SQL?”, it’s time for you to start practicing via various datasets available over the web. This is the most crucial step to becoming proficient with SQL. You can save yourself a lot of time and energy by enrolling in PG Certificate Program in Data Science and Machine Learning by UNext.