30Sep, 2023

DDL Commands in SQL: A Complete Guide

In SQL (Structured Query Language), a DDL (Data Definition Language) command is used to define, manage, and manipulate the structure of database objects such as tables, indexes, and views. DDL commands do not manipulate the data within the database but rather the schema or structure of the database itself. Here are some common DDL commands in SQL:

Table of Contents

CREATE TABLE:

This command is used to create a new table in the database. It specifies the table name, columns, data types, constraints, and more Syntex :

CREATE TABLE TableName (
    Column1 DataType,
    Column2 DataType,
    ...
);

EXAMPLE:

CREATE TABLE Employees (
    EmployeeID INT PRIMARY KEY,
    FirstName VARCHAR(50),
    LastName VARCHAR(50),
    DepartmentID INT
);

ALTER TABLE

The ALTER TABLE command is used to modify an existing table, such as adding, modifying, or deleting columns. Sentex

ALTER TABLE TableName
ADD NewColumn DataType;

ALTER TABLE TableName
MODIFY ColumnName NewDataType;

ALTER TABLE TableName
DROP COLUMN ColumnName;

EXAMPLE

ALTER TABLE Employees
ADD Email VARCHAR(100);

ALTER TABLE Employees
MODIFY DepartmentID INT NOT NULL;

ALTER TABLE Employees
DROP COLUMN Email;

DROP TABLE:

This command is used to delete an existing table and all the data stored within it. Syntex

DROP TABLE TableName;

Example

DROP TABLE Employees;

CREATE INDEX:

An index is used to improve the speed of data retrieval operations on a table. This command creates an index on one or more columns of a table.

CREATE INDEX IndexName
ON TableName (Column1, Column2, ...);

EXAMPLE

CREATE INDEX IX_DepartmentID
ON Employees (DepartmentID);

DROP INDEX:

This command is used to delete an existing view. syntex <

DROP VIEW ViewName;

Example

DROP INDEX IX_DepartmentID;

CREATE VIEW

In SQL, a view is a virtual table that is based on the result of a SELECT query. Views do not store data themselves; instead, they provide a way to present data from one or more tables in a structured and simplified manner. Views are often used to:

Simplify Complex Queries: Views can encapsulate complex SQL logic and join operations, making it easier for users to query and retrieve data without needing to understand the underlying data model intricacies.
Enhance Data Security: Views can restrict access to specific columns or rows of a table. This can help enforce security policies by limiting what data users can see or modify.
Provide Data Abstraction: Views allow you to present a simplified and user-friendly interface to the database, hiding details about the actual schema and making it easier to work with the data.
Centralize Business Logic: Views can encapsulate business rules and calculations, ensuring that they are applied consistently whenever the view is queried.

A view is a virtual table based on the result of a SELECT query. This command creates a view.

Syntex

CREATE VIEW ViewName AS
SELECT Column1, Column2, ...
FROM TableName
WHERE Condition;

Example

CREATE VIEW EmployeeNames AS
SELECT FirstName, LastName
FROM Employees
WHERE DepartmentID = 1;

DROP VIEW:

This command is used to delete an existing view. Syntex

DROP VIEW ViewName;

Example

DROP VIEW EmployeeNames;

CREATE DATABASE

In some database management systems, you can use this command to create a new database.

CREATE DATABASE DatabaseName;

DROP DATABASE

his command is used to delete an existing database and all its associated objects.

DROP DATABASE DatabaseName;