Top 7 Commands in Data Definition Language (DDL)

Introduction

 

Database management system (DBMS) is an integral part of computing. DBMS uses different languages to perform different operations and queries. SQL is one such language. 

 

The Structured Query Language (SQL), as we all know, is a database language that allows us to execute specific operations on existing databases as well as build new databases. 

 

To do the tasks, SQL employs commands such as Create, Drop, and Insert. These SQL commands are primarily divided into four groups:

 

1. DDL – Data Definition Language

2. DQL – Data Query Language

3. DML – Data Manipulation Language

4. DCL – Data Control Language

 

In this blog, we are solely going to talk about DDL or Data definition language. First of all, let us try to know what DDL is.

 

 

Data Definition Language (DDL)

 

A data definition language (DDL) is a programming language that is used to define and alter the structure of database items. Views, schemas, tables, and indexes are examples of database objects.

 

In certain cases, this word is referred to as data description language since it defines the columns and records in a database table.

 

DDL is now incorporated into any formal language for expressing data in the database industry. It is, nevertheless, considered a subset of SQL (Structured Query Language), read more about SQL: Easy or Tough)

 

SQL frequently combines imperative verbs with ordinary English, such as phrases, to make database changes. As a result, DDL does not appear in a SQL database as a distinct language, but it does describe modifications to the database schema.

 

It deals with descriptions of the database schema to build and alter the structure of objects in a database. Unlike data manipulation language (DML) commands, which are used to modify data, DDL commands are used to change the database structure, such as adding new tables or objects, as well as all of their properties (data type, table name, etc.).

 

CREATE, ALTER, DROP, and TRUNCATE are the most often used DDL in SQL queries.

 

Popular DDL Commands

 

DDL commands are widely used to perform different kinds of operations in a database. Some of the most popular DDL commands are listed below.

 

1. CREATE

 

This command creates a new table using a specific syntax. The syntax for the CREATE statement is as follows:

 

CREATE TABLE [NAME OF TABLE] ([column definitions]) [parameters for table];

 

For example:

 

CREATE TABLE Employee (Employee Id INTEGER PRIMARY KEY, First name CHAR (50) NULL, Last name CHAR (75) NOT NULL);

 

Every command preceding the semicolon is processed by the statement's required semicolon. The data type is specified in this case by the string CHAR. DATE, NUMBER, and INTEGER are examples of other data types.

 

 

2. ALTER

 

Altering a database table is done with the modify command. This command may be used to add new columns to a database table, delete existing columns, and even modify the data type of the columns concerned.

 

The syntax of an Alter command is as follows:

 

Alter object type object name object parameters;

 

For example:

 

ALTER TABLE Employee ADD PRIMARY KEY (employee_pk);

 

To create a constraint and enforce a unique value, we introduced a unique primary key to the table in this example. On the Employee table, the constraint "employee pk" is a primary key.

 

(Read also: SQL for data scientist: Part-1)

 

 

3. DROP

 

Drop command is used to remove items from a database, such as tables, indexes, and views. Because a DROP command cannot be reversed, once an item is deleted, it cannot be recovered.

 

The syntax for the Drop statement is: DROP object type object name;

 

For example:

 

DROP TABLE Employee;

 

Here, in this example employee tables are being deleted.

 

 

4. TRUNCATE

 

The TRUNCATE command, like DROP, is used to rapidly delete all records from a table. Unlike DROP, which totally destroys a table, TRUNCATE keeps the table's whole structure so it may be reused afterward.

 

The syntax for the Truncate command goes like this:

TRUNCATE TABLE table_name;

 

For example:

 

TRUNCATE TABLE Employee;

 

Here all the extents of the Employee table are marked for deallocation in this example, so they're deemed vacant for reuse.

 

 

5. COMMENT

 

For an existing object, it adds a remark or overwrites an existing remark.

 

All objects can have comments added to them (users, roles, warehouses, databases, tables, etc.). Individual table columns can also have comments written to them.

 

The syntax for Comment command is:

 

COMMENT [IF EXISTS] ON <object_type> <object_name> IS '<string_literal>';

 

COMMENT [IF EXISTS] ON COLUMN <table_name>.<column_name> IS '<string_literal>';

 

When creating or modifying an object, a comment can be provided in addition to this command:

 

• In the CREATE OBJECT> or ALTER OBJECT> commands, specify the COMMENT argument.

 

• The ALTER object> command can now utilize the Remark argument to modify an existing comment at any time.

When adding comments to table columns, a somewhat different syntax is used:

 

• A remark may be added to a column by using the COMMENT keyword after the column definition (not property).

• This command can then be used to modify the comment.

 

 

6. SHOW

 

It returns a list of all existing objects for the given object type. The objects' metadata is included in the output, which includes:

 

• Properties that are common (name, creation timestamp, owning role, comment, etc.)

• Specific characteristics for each object

 

The syntax for the Show command is:

 

SHOW <object_type_plural> [ LIKE '<pattern>' ] [ IN <scope_object_type> [ <scope_object_name> ] ]

 

Notes on General Usage:

 

• SHOW commands can be executed without the need for a running warehouse.

• Only objects for which the current user's current role has been granted the required access privileges are returned by SHOW commands. Consider the following scenario:

 

• The databases for which the user's role has the OWNERSHIP or USAGE privilege are listed in the SHOW DATABASES output.

• The schemas for which the user's role has the OWNERSHIP or USAGE privilege are included in the SHOW SCHEMAS output.

• The tables for which the user's role has any privileges are included in the SHOW TABLES output. The USAGE privilege on the parent database and schema must also be granted to the role.

• The MANAGE GRANTS access permission allows its user to see all of the account's objects. The MANAGE GRANTS permission is only available to account administrators (users with the ACCOUNTADMIN role) and security administrators (users with the SECURITYADMIN role) by default.

 

The following clauses can be used to control the output of most SHOW commands:

 

• The optional LIKE clause can be used to filter the list of items returned by name.

• The database object types provide an optional IN clause that may be used to limit the command's scope to a single schema or database, or the whole account.

 

(Must read: SQL for data scientist: part-2)

 

7. DESCRIBE

 

It describes the details for any specified object in the Database. 

 

The syntax for DESCRIBE command is:

 

DESC[RIBE] <object_type> <object_name>

Notes on Application

 

• You may use the RESULT SCAN function to post-process the output of this command, which treats it as a table that can be queried.

 

(Related blog: SQL vs NoSQL)

 

Conclusion

 

These were some of the main commands used in DDL. These commands are used to modify any database in SQL. Data Definition Language is an integral part of DBMS and is used to alter and create the schema of a database with the commands mentioned above. 

 

(Must check: Top Sites to Learn SQL)

 

These commands are used to perform queries and operations in a database and are categorically organized. All these commands have their own specific syntax and are used in SQL codes. Some other commands might be seen in the future as the language will progress, till then these commands are what form the base of DDL.