Lab Notes Sql – Ddl | Create, Alter, Drop | Hbo | 2026/27

DATABASE MANAGEMENT SYSTEMS LAB MANUAL


SQL – DDL
Topic: CREATE, ALTER, and DROP Statements
1. Objective:
The objectives of this lab are to:
 Understand Data Definition Language (DDL) in SQL.
 Differentiate DDL commands from commands used to manipulate table data.
 Create databases and tables using MySQL.
 Use ALTER TABLE to change the structure of an existing table.
 Add, modify, and rename columns using appropriate SQL syntax.
 Understand the purpose and consequences of DROP DATABASE and DROP TABLE.
 Verify database structure changes using SQL commands and MySQL Workbench.
 Develop safe database-management practices before performing destructive operations.


2. HARDWARE AND SOFTWARE REQUIRED:
Hardware
 PC or laptop
 Keyboard and mouse
Software
 MySQL Server
 MySQL Workbench
 Windows, Linux, or macOS
Other Requirements
 Access to the course LMS
 Lab folder for SQL scripts and screenshots
 Basic understanding of databases, tables, and SQL syntax



3. Introduction to Data Definition Language (DDL)
A database stores organized information in structures such as tables. Before storing information, we must
define the structure in which that information will be kept.
For example, a university database may contain tables for students, teachers, courses, and departments.
Each table needs a name and a defined set of columns with suitable data types.
Data Definition Language (DDL) is a category of SQL used to define and modify database structures.
The three main commands covered in this lab are:
Command Purpose
CREATE Creates a database or database object.
ALTER Changes the structure of an existing database object.
DROP Removes a database object.
DDL primarily deals with database structures rather than inserting, updating, or retrieving individual
records.
3.1 Database Structure Example
1|Page

,DATABASE MANAGEMENT SYSTEMS LAB MANUAL
Consider a university database:




In this example, UniversityDB is the database, while Students and Courses are tables. The columns
define the information each table can store.


4. The CREATE Statement
The CREATE statement is used to create database objects, such as databases and tables.
4.1 Creating a Database
Syntax:
CREATE DATABASE database_name;
Example:
CREATE DATABASE UniversityDB;
This statement creates a database named UniversityDB.
To display the available databases, use:
SHOW DATABASES;
To avoid an error if the database already exists, MySQL also supports:
CREATE DATABASE IF NOT EXISTS UniversityDB;
4.2 Selecting a Database
Before creating a table inside a database, select the appropriate database:
USE UniversityDB;
The USE statement selects the database for subsequent operations in the current session. It is not itself a DDL command.
4.3 Creating a Table
A table consists of columns, and each column has a name and a data type.
Syntax:
CREATE TABLE table_name (
column1 datatype,
column2 datatype,
column3 datatype
);
Example:
CREATE TABLE Students (
Student_ID INT,
Student_Name VARCHAR(50),
Department VARCHAR(50),
Semester INT
);
Explanation:
 Student_ID INT defines an integer column.
 Student_Name VARCHAR(50) stores text of up to 50 characters.
 Department VARCHAR(50) stores department information.
 Semester INT stores an integer value.
To verify that the table exists:
SHOW TABLES;
To view the table structure:
DESCRIBE Students;

2|Page

, DATABASE MANAGEMENT SYSTEMS LAB MANUAL

5. The ALTER Statement:
The ALTER statement changes the structure of an existing database object. It can be used to add, modify,
rename, or remove columns.
5.1 Adding a Column
Suppose the Students table does not contain an email address column.
ALTER TABLE Students
ADD Email VARCHAR(100);
This adds a column named Email.
5.2 Adding Another Column
ALTER TABLE Students
ADD Phone VARCHAR(20);
This adds a column named Phone.
5.3 Modifying a Column's Data Type
To change the size of the Student_Name column:
ALTER TABLE Students
MODIFY Student_Name VARCHAR(100);
The column can now store text of up to 100 characters.
5.4 Renaming a Column
MySQL supports renaming a column using RENAME COLUMN:
ALTER TABLE Students
RENAME COLUMN Department TO Department_Name;
This changes the column name from Department to Department_Name. The command does not create a
new table.
5.5 Removing a Column
ALTER TABLE Students
DROP COLUMN Phone;
This removes the Phone column and its stored values from the table. It is a structural change, so verify the
column is no longer needed before running the command.
5.6 Verifying Changes
After each alteration, execute:
DESCRIBE Students;
Compare the output with the original table structure to identify the changes.

6. The DROP Statement
The DROP statement permanently removes a specified database object. Students must use it carefully
because it can remove structures and their associated data.
6.1 Dropping a Table
Syntax: DROP TABLE table_name;
Example:
DROP TABLE Students;
This removes the Students table, including its structure and stored records.
6.2 Dropping a Database
Syntax: DROP DATABASE database_name;
Example:
DROP DATABASE UniversityDB;
This removes the database and the objects contained in it.
3|Page