Getting Started with DDL in SQL: A Brief Overview

It is no secret that SQL is a declarative language generally oriented toward results. Of course, it’s also accessible, efficient, and easy to learn, which certainly gives it a competitive edge over many of its programming language peers.

Thanks to these benefits, SQL makes creating and interacting with databases a very straightforward process, eased further by the main components of the SQL syntax: DDL, DML, DCL, and TCL. In this article, we’ll focus on DDL and the core commands it uses to specify the structure of a database.

What Is DDL?

DDL, or data definition language, is used for setting up new databases or adjusting already existing ones. It is called a language, but you can think of it more as a syntax of its own. Оr, alternatively, a set of statements that allow the user to define or modify data structures and objects such as data tables.

How to Use DDL Commands?

As already mentioned, DDL constitutes the part of the SQL syntax that deals with a database’s elements by applying commands (otherwise known as statements) such as:

• CREATE
• ALTER
• DROP
• RENAME
• TRUNCATE

These commands can be used in conjunction with other SQL statements and languages such as DML.

CREATE Statement

It’s only logical to start at the very beginning – with the CREATE statement. As the name suggests, it is used for producing entire databases and objects:

CREATE object_type object_name;

For example, if we want to create a table, we must abide by the following line:

CREATE TABLE object_name (column_name data_type);

Observe how simple the syntax is – you will have to write it at the beginning, then directly point out the type and name of the object.

While we’re on the topic, if you’d like to learn more about data objects and tables, read our article on basic database terminology.

Now, we also have to assign and name a column, as well as the type of data contained in each one – after specifying the table name within parentheses, of course. For example:

CREATE TABLE sales (purchase_number INT);

With this line of code, you can create a table named “sales” which contains 1 column, “purchase number”, storing only integer values.

Word of advice: Don’t worry if the table name coincides with the name we’ve assigned to the database – this is one of the most common SQL practices. On one hand, the syntax will make it clearer when you refer to the database and to the table, respectively. On the other hand, there is a certain logic to the naming convention as it makes sense for a database to bear the same name as its main table.

ALTER Statement

The CREATE command helps us better understand how ALTER works – the operation we carry out when reworking existing objects. For example, we can use this DDL statement to modify a table by adding, removing, or renaming columns in the table.

To add a column, called “date of purchase”, you should begin with the same structure, however, instead of CREATE, you’ll use ALTER before designating the object’s type and name. After that, you write down the precise modification:

ALTER TABLE sales

ADD COLUMN date_of_purchase DATE;

This means a new column, containing the date of the purchase, will be added to our table. Furthermore, its values will be of the DATE type.

DROP Statement

What if you want to delete a database object? In that case, use the DROP statement:

DROP object_type object_name;

For instance, say you want to delete the entirety of the table “customers” with a single line of code:

DROP TABLE customers;

As you can see, it’s self-explanatory – the DDL command is accompanied by the object type and its name.

RENAME Statement

Another valuable tool when using DDL is RENAME which allows you to retitle an object like a database table:

RENAME object_type object name TO new_object_name;

To illustrate, if we hadn’t dropped the “customers” table, we could have changed the name of the table to “customer data” as follows:

RENAME TABLE customers TO customer_data;

As you can see, this SQL syntax follows the same principle as the previous ones and is extremely intuitive.

TRUNCATE Statement

Last, but not least, we can remove data without applying DROP if we want to continue using the table as an object in the database. In such a situation, TRUNCATE is the perfect DDL command for the job:

TRUNCATE object_type object_name;

In the context of our example, if we don’t want to entirely delete our “customers” table, just the values in it, we can apply the code:

TRUNCATE TABLE customers;

After executing this clause, all records from “customers” will be deleted. Although it’s empty, the table will continue to exist.

DDL: Next Steps

DDL commands prove just manageable the SQL syntax is – just about anyone can quickly familiarize themselves with it. And now that you know how to create, structure and load a database with information, you can to proceed to the next level. Are you ready to learn how to obtain various statistics or other tables that will help you solve your business analytical problems?