Webinar Alert : Mastering  Manual and Automation Testing! - Reserve Your Free Seat Now

sddsfsf

Data Definition Language (DDL) Commands in SQL

Using Google Standard SQL query syntax, you can create and modify BigQuery resources with data definition language (DDL) statements. Resources like tables, table clones, table snapshots, views, user-defined functions (UDFs), and row-level access policies can all be created, modified, and deleted with the help of DDL commands.

In the following paragraphs, we will talk about the top sql commands, various aspects of the DDL commands statement, and its advantages and disadvantages.

What are DDL Statements

DDL is a standardized language that provides commands for defining the various databases' storage groups (serogroups), structures, and objects. DDL commands statements are used to create, modify, and delete database objects like tables, serogroups, and indexes. Additionally, "DDL" can refer to any language for data description.

Databases, aliases, locations, indexes, tables, and sequences can all be created and deleted with the help of DDL's Structured Query Language (SQL) statements. Statements to alter these objects and impose or remove constraints on tables, such as the ones listed below, are also included.

  • Unique
  • Primary
  • Foreign Key
  • Check

These constraints are used to enforce uniqueness, referential or domain integrity. When a DDL commands in MySQL statement is executed, it takes effect immediately in the database.

DDL commands in MySQL is sometimes known as Data Description Language since its statements can also describe, comment on, and place labels on database objects.

Tasks Allowed by DDL Statements

Data definition language (DDL) statements let you perform these tasks:

  • Create, alter, and drop schema objects
  • Grant and revoke privileges and roles
  • Analyze information on a table, index, or cluster
  • Establish auditing options
  • Add comments to the data dictionary

The CREATE, ALTER, and DROP commands require exclusive access to the specified object. For example, an ALTER TABLE statement fails if another user has an open transaction on the specified table. 

The GRANT, REVOKE, ANALYZE, AUDIT, and COMMENT commands do not require exclusive access to the specified object. For example, you can analyze a table while other users are updating the table.

Types of DDL commands in SQL Server

The DDL statements are:

  • ALTER ... (All statements beginning with ALTER)
  • COMMENT
  • CREATE ... (All statements beginning with CREATE)
  • DROP ... (All statements beginning with DROP)
  • GRANT
  • RENAME
  • REVOKE
  • TRUNCATE

The DDL commands in SQL Server that can be used to define the database schema are part of DDL or Data Definition Language. It is used to create and modify the structure of database objects and only deals with schema descriptions. 

DDL commands is a set of SQL commands that only allow for creating, modifying, and deleting database structures, not data. A general user, who should use an application to access the database, rarely uses these commands. These datasets are an integral part of businesses, and there’s great demand for administrators. Therefore, you can go through the SQL DBA career path to set yourself up for this role.

DDL Commands in MySQL List:

CREATE

This command is used to create the database or its objects (like table, index, function, views, store procedure, and triggers).

Syntax

For Tables 

Create table <table_name>

For Databases

Create database <database_name>

For Function

CREATE FUNCTION [IF NOT EXISTS] [db_name.]function_name([arg_type
[, arg_type...])
RETURNS return_type
Begin
End

For View

CREATE VIEW [IF NOT EXISTS] view_name
[(column_name [COMMENT 'column_comment'][,...])]
[COMMENT 'view_comment']
AS select_statement
DROP

This command is used to delete objects from the database.

Syntax

For Databases

Drop database <database name>

For Tables

Drop table <table name>

For View

DROP VIEW [IF EXISTS] [db_name.]view_name

For Functions

DROP [AGGREGATE] FUNCTION [IF EXISTS] [db_name.]function_name(type[, type...])

ALTER

This is used to alter the structure of the database.

Syntax

For Databases

ALTER DATABASE { database_name | CURRENT }
{
MODIFY NAME = new_database_name
| COLLATE collation_name
| |
}

For Tables

ALTER TABLE 
ADD ;      

For View

ALTER VIEW [database_name.]view_name
[(column_name [COMMENT 'column_comment'][, ...])]
AS select_statement;
TRUNCATE

This is used to remove all records from a table, including all spaces allocated for the records are removed.

Syntax

For Tables

TRUNCATE TABLE <Table_name>

COMMENT

This is used to add comments to the data dictionary.

Single Line Comment

//This is a single-line statement

Multiple Line Comment

/* This is a multiline

    Comment*/

RENAME

This is used to rename an object existing in the database.

Syntax

For Tables

RENAME old_table_name To new_table_name;
?
GRANT

When we create a user in SQL, it is not even allowed to log in and create a session until and unless proper permissions/privileges are granted to the user.

Syntax

GRANT CREATE SESSION TO username;

REVOKE

The REVOKE statement revokes privileges on a specified object from groups or users.

Syntax

REVOKE privilege ON object_type object_name
FROM USER user_name
REVOKE privilege ON object_type object_name
FROM GROUP group_name
privilege ::= ALL | ALTER | CREATE | DROP | INSERT | REFRESH|SELECT |
SELECT(column_name)
object_type::= SERVER | URI | DATABASE | TABLE
COMPUTE

The COMPUTE STATS statement gathers information about the volume and distribution of data in a table and all associated columns and partitions. The information is stored in the meta-store database and used by Impala to help optimize queries.

Syntax

COMPUTE STATS [db_name.]table_name [(column_list)] 【TABLESAMPLE
SYSTEM(percentage) [REPEATABLE(seed)]]
column_list::= column_name [, column_name,...]
COMPUTE INCREMENTAL STATS [db_name.]table_name [PARTITION (partition_spec)]
partition_spec::= partition_col-constant_value
partition_spec: simple_partition_spec | complex_partition_spec
simple_partition_spec: partition_col-constant_value
complex_partition_spec ::= comparison_expression_on_partition_col

Advantages of using DDL Statement 

The following are the benefits of the Data Defining Language Statement (DDL):

  • Uniformity is Data Definition Language's primary benefit.
  • A bunch of guidelines to which all Organized Question Dialects adjust.
  • Database schemas can be easily written and stored in command-line forms with DDL.
  • Over time, this also becomes simple to comprehend and write new commands for.

DDL statements are not allowed in Procedures (PLSQL BLOCK)

Objects in PL/SQL are precompiled. Before the objects are executed, all dependencies are checked. The programs run faster as a result of this.

Database objects, Tables, Views, synonyms, and other objects are included in the dependencies. The data is not necessary for the dependency.

Statements written in DML (Data Manipulation Language) do not alter the dependency so that they can be executed directly in PL/SQL objects. On the other hand, the dependencies can be altered during the program's execution with DDL (Data Definition Language) statements like the CREATE, DROP, and ALTER commands and DCL (Data Control Language) statements like GRANT and REVOKE.

Example: Let's say you have dropped a table during the execution of a program, and later in the same program when you try to insert a record into that table, the program will fail.

DDL statements are not allowed directly in PL/SQL programs.

Difference Between DDL and DML Statements

DDL

DML

It stands for Data Definition Language.

It stands for Data Manipulation Language.

It is used to create database schema and can be used to define some constraints.

It is used to add, retrieve or update the data.

It basically defines the column (Attributes) of the table.

It adds or updates the row of the table. These rows are called tuples.

DDL creates and modifies database objects like tables, indexes, views, and constraints.

DML is used to perform operations on the data within those database objects.

DDL statements are typically executed less frequently than DML statements.

DML statements are frequently executed to manipulate and query data.

Database administrators typically execute DDL statements.

Application developers or end-users typically execute DML statements.

DDL statements are not used to manipulate data directly.

DML statements are used to manipulate data directly.

DDL statements do not change the contents of the database.

DML statements change the contents of the database.

Examples of DDL commands: CREATE TABLE, ALTER TABLE, DROP TABLE, TRUNCATE TABLE, and RENAME TABLE.

Examples of DML commands: SELECT, INSERT, UPDATE, DELETE, and MERGE.

Conclusion

In the last few paragraphs, we learned about different types of DDL commands in SQL server. We have learned about their uses in different SQL Processes. We have also learned about the advantages of DDL statements. Lastly, we also learned about a special case in why DDL statements cannot be used in Stored Procedures. Hope all this encourages the readers to study further about DDL commands statements. Or else you can also enroll in an online SQL server training course and shape your ever-growing SQL career.

SQL Training For Administrators & Developers

  • No cost for a Demo Class
  • Industry Expert as your Trainer
  • Available as per your schedule
  • Customer Support Available
cta13 icon

Trending Courses

Cyber Security icon

Cyber Security

  • Introduction to cybersecurity
  • Cryptography and Secure Communication 
  • Cloud Computing Architectural Framework
  • Security Architectures and Models
Cyber Security icon1

Upcoming Class

-0 day 12 Oct 2024

QA icon

QA

  • Introduction and Software Testing
  • Software Test Life Cycle
  • Automation Testing and API Testing
  • Selenium framework development using Testing
QA icon1

Upcoming Class

14 days 26 Oct 2024

Salesforce icon

Salesforce

  • Salesforce Configuration Introduction
  • Security & Automation Process
  • Sales & Service Cloud
  • Apex Programming, SOQL & SOSL
Salesforce icon1

Upcoming Class

-0 day 12 Oct 2024

Business Analyst icon

Business Analyst

  • BA & Stakeholders Overview
  • BPMN, Requirement Elicitation
  • BA Tools & Design Documents
  • Enterprise Analysis, Agile & Scrum
Business Analyst icon1

Upcoming Class

-0 day 12 Oct 2024

MS SQL Server icon

MS SQL Server

  • Introduction & Database Query
  • Programming, Indexes & System Functions
  • SSIS Package Development Procedures
  • SSRS Report Design
MS SQL Server icon1

Upcoming Class

-0 day 12 Oct 2024

Data Science icon

Data Science

  • Data Science Introduction
  • Hadoop and Spark Overview
  • Python & Intro to R Programming
  • Machine Learning
Data Science icon1

Upcoming Class

-0 day 12 Oct 2024

DevOps icon

DevOps

  • Intro to DevOps
  • GIT and Maven
  • Jenkins & Ansible
  • Docker and Cloud Computing
DevOps icon1

Upcoming Class

6 days 18 Oct 2024

Hadoop icon

Hadoop

  • Architecture, HDFS & MapReduce
  • Unix Shell & Apache Pig Installation
  • HIVE Installation & User-Defined Functions
  • SQOOP & Hbase Installation
Hadoop icon1

Upcoming Class

13 days 25 Oct 2024

Python icon

Python

  • Features of Python
  • Python Editors and IDEs
  • Data types and Variables
  • Python File Operation
Python icon1

Upcoming Class

7 days 19 Oct 2024

Artificial Intelligence icon

Artificial Intelligence

  • Components of AI
  • Categories of Machine Learning
  • Recurrent Neural Networks
  • Recurrent Neural Networks
Artificial Intelligence icon1

Upcoming Class

-0 day 12 Oct 2024

Machine Learning icon

Machine Learning

  • Introduction to Machine Learning & Python
  • Machine Learning: Supervised Learning
  • Machine Learning: Unsupervised Learning
Machine Learning icon1

Upcoming Class

34 days 15 Nov 2024

 Tableau icon

Tableau

  • Introduction to Tableau Desktop
  • Data Transformation Methods
  • Configuring tableau server
  • Integration with R & Hadoop
 Tableau icon1

Upcoming Class

13 days 25 Oct 2024