Data Definition Language (DDL) in SQL:
DDL or Data Definition Language actually consists of the SQL commands that
can be used to defining, altering, and deleting database structures such
as tables, indexes, and schemas. It simply deals with descriptions of the
database schema and is used to create and modify the structure of database
objects in the database
Create:
To create a new table in the database, use the SQL CREATE
TABLE statement. A table’s structure, including column names,
data types, and constraints like NOT NULL, PRIMARY KEY, and
CHECK, are defined when it is created in SQL.
The CREATE TABLE command is a crucial tool for database
administration because of these limitations, which aid in
ensuring data integrity. To create a table in SQL, use
this CREATE TABLE syntax:
Syntax:
CREATE table table_name
(
Column1 datatype (size),
column2 datatype (size),
.
.
columnN datatype(size)
);
Key Terms
table_name: The name you assign to the new table.
column1, column2, … : The names of the columns in
the table.
datatype(size): Defines the data type and size of each
column.
Example of SQL CREATE TABLE
Let us look at examples of CREATE TABLE command in SQL and see how to create
table in SQL.
CREATE TABLE in SQL and Insert Data
In this example, we will create a new table and insert data into it. Let us create a table to
store data of Customers, so the table name is Customer, Columns are Name, Country, age,
phone, and so on.
Query:
CREATE TABLE Customer(
CustomerID INT PRIMARY KEY,
CustomerName VARCHAR(50),
LastName VARCHAR(50),
Country VARCHAR(50),
Age INT CHECK (Age >= 0 AND Age <= 99),
Phone int(10)
);
Out put:
Drop:
In SQL, the DROP command is used to permanently remove an object
from a database, such as a table, database, index, or view. When we
DROP a table, both the data and the structure of the object are
permanently removed from the database leaving no trace of the
object.
Syntax:
DROP object object_name ;
Key Terms
object: The type of object you want to drop (e.g., TABLE,
DATABASE).
object_name: The name of the object to be deleted.
DROP Command Examples:
Let’s look at some examples of the DROP statement in SQL.
1. DROP Table
To delete an entire table, including its data and structure:
Syntax:
DROP TABLE table_name;
ALTER:
The ALTER TABLE statement in SQL is used to modify an existing table
structure in a database without losing any data. It allows you to add, remove, or
modify columns, change data types, or apply constraints to improve data integrity
and ensure that the table meets evolving business requirements. It allows for
structural changes like adding new columns, modifying existing ones, deleting
columns, and renaming columns within a table. To alter/modify the table use the
ALTER TABLE syntax:
Syntax:
ALTER TABLE table_name
[ADD | DROP | MODIFY] column_name datatype;
Key Terms
table_name refers to the name of the table you want to modify.
ADD is used to add a new column.
DROP is used to remove an existing column.
MODIFY is used to change the datatype or definition of an existing
column.
1. ADD – To add a new column to the table
The ADD clause is used to add a new column to an existing table. You must
specify the name of the new column and its data type.
Query:
ALTER TABLE table_name
ADD column_name datatype;
TRUNCATE:
The TRUNCATE command is a Data Definition Language (DDL) action that
removes all rows from a table but preserves the structure of the table for future
use. Although TRUNCATE is similar to the DELETE command (without the
WHERE clause), it is much faster because it bypasses certain integrity
constraints and locks. It was officially introduced in the SQL:2008 standard.
Syntax:
TRUNCATE TABLE table_name;
Key Terms
table_name: Name of the table to be truncated.
DATABASE name: student_data
Key Differences Between DROP and TRUNCATE
The key differences between DROP and TRUNCATE statements are explained
in the following table:
Feature DROP TRUNCATE
Completely removes both Removes all rows but
Effect on Table
the data and the table preserves the table
Structure
structure structure
Deletes both data and table Deletes only the data, not
Data Deletion
definition the table structure
Non-recoverable; once
Can be rolled back if used
dropped, the table cannot
Recovery in a transaction (if
be restored (unless you
supported by the DBMS)
have backups)
Does not activate triggers Does not activate DELETE
Triggers
associated with the table triggers
Feature DROP TRUNCATE
Slower due to data and Faster, especially for large
Performance
structural removal datasets
Used when you want to Used to quickly remove all
Usage completely remove a table rows from a table but keep
or database the structure for future use
Rename:
The Rename operation in Relational Algebra is used to rename a relation
(table) or its attributes (columns) to make expressions more readable and
manageable. It helps when dealing with multiple relations or avoiding confusion
in queries.
Renaming a table.
Changing a column name.
Adding or deleting columns.
Modifying the data type of a column.
Syntax:
1. Renaming a Table
ALTER TABLE table_name
RENAME TO new_table_name;
2. Renaming a Column
ALTER TABLE table_name
RENAME COLUMN old_column_name TO new_column_name;
3. Adding a New Column
ALTER TABLE table_name
ADD column_name datatype;
4. Modifying a Column Data Type
ALTER TABLE table_name
MODIFY COLUMN column_name new_datatype;
Example 1: Rename a Column
Change the name of column name to FIRST_NAME in table Student. To
change the column name of the existing table we have to use Column
keyword before writing the existing column name to change
Syntax
ALTER TABLE Student RENAME COLUMN Column_NAME TO
FIRST_NAME;
Query:
ALTER TABLE Student RENAME Column name TO FIRST_NAME;
Output
Output