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.
DDL, DML, DCL, TCL, and DQL are the five types of SQL commands. Each category is discussed below:
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.
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.
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
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.
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.
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.
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:
2. A table’s data columns must comply with criteria known as integrity constraints.
3. Examples of SQL integrity constraints are:
4. Not case-sensitive.
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,
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 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.
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;
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 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.
TRUNCATE TABLE table_name;
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 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.
DROP TABLE table_name;
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.
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:
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.