30Sep, 2023
DDL Commands in SQL: A Complete Guide

Understanding Key DDL Commands in SQL

In SQL (Structured Query Language), Data Definition Language (DDL) commands are essential for defining, managing, and manipulating the structure of database objects. DDL commands work with the database schema, including creating, altering, and deleting tables, indexes, views, and databases. Here are the most common DDL commands:

1. CREATE TABLE

The CREATE TABLE command is used to define a new table and its structure within the database. This includes specifying the columns, data types, and constraints. 

CREATE TABLE Employees (
    Employee_ID INT PRIMARY KEY,
    First_Name VARCHAR(50),
    Last_Name VARCHAR(50),
    Email VARCHAR(100),
    Hire_Date DATE
);

This SQL code creates a table called Employees with columns for employee ID, first name, last name, email, and hire date.

2. ALTER TABLE

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

ALTER TABLE Employees
ADD Phone VARCHAR(15);

This SQL command adds a new column called Phone to the Employees table.

3. DROP TABLE

The DROP TABLE command is used to permanently remove a table and all of its data from the database. 

DROP TABLE Employees;

This SQL command deletes the entire Employees table from the database.

4. CREATE INDEX

The CREATE INDEX command is used to create an index on one or more columns of a table, enhancing search and query performance. 

CREATE INDEX idx_lastname
ON Employees (Last_Name);

This SQL command creates an index named idx_lastname on the Last_Name column of the Employees table.

5. DROP INDEX

The DROP INDEX command is used to remove an index that was previously created, improving the performance of certain operations. 

DROP INDEX idx_lastname;

This SQL command deletes the index idx_lastname on the Employees table.

6. CREATE VIEW

The CREATE VIEW command is used to create a virtual table that displays data from one or more underlying tables, based on a predefined query. 

CREATE VIEW EmployeeView AS
SELECT Employee_ID, First_Name, Last_Name
FROM Employees;

This SQL command creates a view named EmployeeView which displays the employee ID, first name, and last name from the Employees table.

7. DROP VIEW

The DROP VIEW command is used to remove an existing view from the database. 

DROP VIEW EmployeeView;

This SQL command deletes the view EmployeeView from the database.

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;

Call to Action: Take Your SQL Skills to the Next Level

Explore Additional SQL Tutorials and Real-World Practice

Now that you’ve mastered the LIKE operator and wildcards, it’s time to expand your knowledge and practice with more advanced SQL topics. The best way to improve your skills is through hands-on experience. Explore the following tutorials and practice on real-world datasets to take your SQL expertise to the next level:

These resources will guide you through complex SQL concepts and offer practical tips for real-world applications. Keep learning and refining your skills, and don’t forget to practice on real datasets to solidify your knowledge.