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:
DDL – Data Definition Language
DQL – Data Query Language
DML – Data Manipulation Language
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.
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];
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.
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;
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)
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;
DROP TABLE Employee;
Here, in this example employee tables are being deleted.
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;
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.
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:
When adding comments to table columns, a somewhat different syntax is used:
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)
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)
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.