SQL Tutorial

SQL Tutorial SQL Introduction SQL Syntax SQL OPERATORS SQL COMMANDS SQL Queries

SQL Table

SQL TABLE SQL CREATE TABLE SQL ALTER TABLE SQL DELETE SQL TRUNCATE TABLE SQL UPDATE TABLE

SQL SELECT

SQL SELECT Statement SQL SELECT IN Operator SQL BETWEEN Operator SQL SELECT BETWEEN Operator SQL SELECT AND Operator SQL SELECT DISTINCT SQL SELECT SUM SQL SELECT MAX SQL SELECT MIN SQL SELECT AVG

SQL Clause

SQL GROUP BY CLAUSE SQL ORDER BY Clause SQL HAVING Clause

SQL INSERT

SQL INSERT INTO Statement

SQL JOIN

SQL JOIN SQL Inner Join SQL Left Join SQL Right Join

SQL OPERATOR

SQL Comparison SQL LOGICAL Operator SQL Arithmetic

Difference

SQL vs NOSQL WHERE vs HAVING DELETE vs DROP GROUP BY vs ORDER BY DROP vs TRUNCATE SQL IN vs SQL EXISTS

MISC

SQL SubQuery SQL CASE Commit and Rollback in SQL Pattern Matching in SQL DDL Commands in SQL DML Commands in SQL Types of SQL Commands SQL COUNT SQL Primary Key SQL FOREIGN KEY SET Operators in SQL Check Constraint in SQL SQL EXCEPT SQL VIEW SQL WHERE Statement SQL CRUD Operation Where Condition in SQL TCL Commands in SQL Types of SQL JOINS SQL Nth Highest Salary SQL NOT OPERATOR SQL UNION ALL SQL INTERSECT SQL Data Definition Language SQL Data Manipulation Language SQL Data Control Language SQL CONSTRAINTS SQL KEYS Codd’s Rules in SQL What is SQL Injection? Trigger In SQL SQL WHERE Multiple Conditions Truncate function in SQL

How To

How to use the BETWEEN operator in SQL How To Use INNER JOIN In SQL How to use LIKE in SQL How to use HAVING Clause in SQL How to use GROUP BY Clause in SQL How To Remove Duplicates In SQL How To Delete A Row In SQL How to add column in table in SQL ? How to drop a column in SQL? How to create a database in SQL? How to use COUNT in SQL? How to Create Temporary Table in SQL? How to Add Foreign Key in SQL? How to Add Comments in SQL? How To Use Group By Clause In SQL How To Use Having Clause In SQL How To Delete Column In Table How To Compare Date In SQL

DDL Commands in SQL

DDL stands for Data Definition Language.

Data Definition Language is a bunch of SQL commands used to create, modify and delete Database schema but not the records or data. Data Definition Language includes Structured Query Language Commands used to define the Database structure. The Data Definition Language commands in Structured Query Language define the schema and modify the Database's schema and its objects as well.

List of Data Definition Language commands in Structured Query Language are:

  1. CREATE Command.
  2. ALTER Command.
  3. TRUNCATE Command.
  4. DROP Command.

Let’s understand each Data Definition Language commands.

1. CREATE Command

In SQL, CREATE command in Data Definition Language is used to create Database and Database objects like to create Table, Index, Views, Triggers, Stored Procedure, and other Database Objects.

Syntax of CREATE Command in Structured Query Language:

CREATE OBJECT OBJECT_NAME; 

Here Object can be Table, Index, View, Trigger, or any other Database Object.

Examples of CREATE Command

Example 1: This example helps us how to create a database using the CREATE Command of the Data Definition Language in SQL:

Syntax to Create a DB (Database):

CREATE DATABASE DATABASE_NAME; 

Now, we will create one Database, and the Database name is School in the SQL Database. To Create a School Database, we will write the following Data Definition Language Command:

CREATE DATABASE SCHOOL;

We defiined the above query to create a database, but how did we know it created a database. To crosscheck whether the Database is created, we will use the SHOW keyword, which will display the entire Database present in the system.

The output of the above query is as follows:

SHOW Databases;
Database
Company
demo
e
information_schema
Mysql
performance_schema
Phpmyadmin
ranking
School
Student

Output 

DDL Commands in SQL

Now, we will create a table inside this School Database. For this, we will use the following command:

USE SCHOOL;

Now, whatever Object we create, modify or delete will be inside this school Database.

Example 2:  This example helps us how to create a table using the CREATE Command of the Data Definition Language in SQL:

Syntax to create Table:

CREATE TABLE TABLE_NAME (
COLUMN NAME1 DATA_TYPE (COLUMN SIZE),
COLUMN NAME2 DATA_TYPE (COLUMN SIZE),
COLUMN NAME N DATA_TYPE (COLUMN SIZE)
);

Now, we will create one Table, and the Table name is Stud in the SQL Database. To Create Stud Table with some columns, we will write the following Data Definition Language Command:

CREATE TABLE STUD (
STUD_ID INT,
STUD_NAME VARCHAR (30),
STUD_AGE INT,
STUD_CITY VARCHAR (20),
STUD_MARKS INT
);

To crosscheck whether Table is created or not, use DESC table - name

DESC STUD;

2. ALTER Command

Another Data Definition Language command is Alter Command in the Structured Query Language. Alter command is used to add the column, delete the column, or modify a column in an existing table. Alter command is also used to add or drop constraints in an existing table.

Examples of Alter Command:

Example 1: This example helps us with how to add a column to the existing Table:

Syntax to add column in the existing Table:

ALTER TABLE TABE_NAME ADD COLUMN_NAME DATA_TYPE (COLUMN SIZE);

Now we will add the last name column to the existing stud table. To add a column in an existing table, we will write the following Data Definition Language Command:

ALTER TABLE STUD ADD STUD_LAST_NAME VARCHAR(20);

Use the DESC STUD command to check whether the query is executed properly or not

FieldTypeNullKeyDefaultExtra
STUD_IDint(11)YES NULL 
STUD_NAMEvarchar(30)YES NULL 
STUD_AGEint(11)YES NULL 
STUD_CITYvarchar(20)YES NULL 
STUD_MARKSint(11)YES NULL 
STUD_LAST_NAMEvarchar(20)YES NULL 

Output

DDL Commands in SQL

Example 2: These examples help how to drop the existing field from the Table.

Syntax to drop a column from the Table:

ALTER TABLE TABLE_NAME DROP COLUMN_NAME;

Now we will drop the last name column from the existing stud table. To drop a column from the existing Table, we will write the following Data Definition Language Command:

ALTER TABLE STUD DROP STUD_LAST_NAME;

Use the DESC STUD command to check whether the query is executed properly or not

FieldTypeNullKeyDefaultExtra
STUD_IDint(11)YES NULL 
STUD_NAMEvarchar(30)YES NULL 
STUD_AGEint(11)YES NULL 
STUD_CITYvarchar(20)YES NULL 
STUD_MARKSint(11)YES NULL 
DDL Commands in SQL

3. TRUNCATE Command

The TRUNCATE command is used to delete or remove all the records from the table. These commands only remove data, but the table's structure still exists. And these commands can be rollback after executing the command.

Syntax of TRUNCATE Command:

TRUNCATE TABLE TABLE_NAME;

Example: We will remove all the records from the Stud table, and there is some dummy or temporary data inserted into the stud table.

Table Name: Stud.

STUD_IDSTUD_NAMESTUD_AGESTUD_CITYSTUD_MARKS
1Manan16Dhule85
2Monika15Dhule80
3Chirag16Chalisgaon95

We will remove all the records of the Stud table using the below query:

TRUNCATE TABLE STUD;  

This query will delete the records; the structure remains to exist.

After executing the above query, execute a SELECT query to check whether data is deleted or not.

SELECT * FROM STUD;

The output shows the empty table.

4. DROP Commands: 

DROP Commands are Data Definition Language Commands used to drop the Database and the Database objects from the Structured Query Language. Using the DROP command, we can easily drop view, Index, and table from the Database.

Example of DROP Commands in Structured Query Language:

Example 1: These examples help how to drop existing Index from the Structured Query Language Database:

Syntax to drop an Index:

DROP INDEX NAME_OF_INDEX;

We want to drop the Stud_Index from the Structured Query Language Database. For this, we will execute the following query:

DROP INDEX STUD_INDEX;

Example 2: These examples help how to drop existing tables from the Structured Query Language Database:

Syntax to drop a table:

DROP TABLE TABLE_NAME

We will drop the Stud table and delete all the table structure records in this example.

DROP TABLE STUD;

Example 3: These examples help how to drop existing Database from the Structured Query Language Database:

Syntax to drop a Database:

DROP TABLE DATABASE_NAME; 

In this example, we will drop School Database.

DROP DATABASE SCHOOL;



ADVERTISEMENT
ADVERTISEMENT