DDL Commands in MySQL | CREATE DATABASE, USE, CREATE TABLE, ALTER TABLE and DROP TABLE

Before storing data in MySQL, we first create a database and then create tables inside it. These operations are performed using Data Definition Language (DDL) commands. DDL commands define the structure of a database and its tables. They allow users to create, modify, and delete database objects.

In this chapter, you will learn the most commonly used DDL commands prescribed in the CBSE Class 11 Informatics Practices syllabus, including CREATE DATABASE, USE, CREATE TABLE, ALTER TABLE, and DROP TABLE.

---

Learning Outcomes

After studying this chapter, you will be able to:

• Understand the purpose of DDL commands.
• Create databases and tables.
• Select a database using the USE command.
• Modify table structures using ALTER TABLE.
• Delete tables and databases when required.

---

What are DDL Commands?

---

Common DDL Commands

Command	Purpose
CREATE DATABASE	Creates a new database.
USE	Selects the database for use.
CREATE TABLE	Creates a new table.
ALTER TABLE	Modifies the table structure.
DROP TABLE	Deletes a table.

---

1. CREATE DATABASE

Syntax

CREATE DATABASE database_name;

Example

CREATE DATABASE SchoolDB;

The above command creates a database named SchoolDB.

---

2. USE Command

Syntax

USE database_name;

Example

USE SchoolDB;

After executing this command, MySQL uses the SchoolDB database.

---

3. CREATE TABLE

Syntax

CREATE TABLE table_name
(
column1 datatype,
column2 datatype,
...
);

Example

CREATE TABLE Student
(
Roll_No INT,
Name VARCHAR(50),
Class CHAR(2),
Marks FLOAT
);

The Student table contains four columns: Roll Number, Name, Class, and Marks.

---

Understanding the CREATE TABLE Statement

Column Name	Data Type	Purpose
Roll_No	INT	Stores roll numbers.
Name	VARCHAR(50)	Stores student names.
Class	CHAR(2)	Stores class such as XI or XII.
Marks	FLOAT	Stores examination marks.

---

Real-Life Example

A school wants to maintain student records in a database.

Step 1: Create the database.

CREATE DATABASE SchoolDB;

Step 2: Select the database.

USE SchoolDB;

Step 3: Create the Student table.

CREATE TABLE Student
(
Roll_No INT,
Name VARCHAR(50),
Class CHAR(2),
Marks FLOAT
);

The database is now ready to store student information.

---

Important Notes

• Create the database before creating tables.
• Use the USE command to select the required database.
• Every column must have an appropriate data type.
• Column names within a table should be unique.
• Every SQL statement ends with a semicolon (;).

---

---

4. ALTER TABLE Command

The ALTER TABLE command is used to modify the structure of an existing table. It allows us to add new columns, modify existing columns, or remove unwanted columns without creating a new table.

---

Adding a New Column (ADD COLUMN)

Syntax

ALTER TABLE table_name
ADD COLUMN column_name data_type;

Example

ALTER TABLE Student
ADD COLUMN Section CHAR(1);

The above command adds a new column named Section to the Student table.

Before	After
Roll_No, Name, Class, Marks	Roll_No, Name, Class, Marks, Section

---

Modifying an Existing Column (MODIFY COLUMN)

Sometimes we need to change the data type or size of an existing column.

Syntax

ALTER TABLE table_name
MODIFY COLUMN column_name new_data_type;

Example

ALTER TABLE Student
MODIFY COLUMN Name VARCHAR(100);

The size of the Name column changes from 50 characters to 100 characters.

---

Dropping a Column (DROP COLUMN)

If a column is no longer required, it can be removed from the table.

Syntax

ALTER TABLE table_name
DROP COLUMN column_name;

Example

ALTER TABLE Student
DROP COLUMN Section;

The Section column is permanently removed from the Student table.

---

5. DROP TABLE Command

Syntax

DROP TABLE table_name;

Example

DROP TABLE Student;

The Student table and all its data are permanently deleted.

---

Summary of ALTER TABLE Operations

Operation	Purpose	Example
ADD COLUMN	Adds a new column.	ADD COLUMN Section CHAR(1)
MODIFY COLUMN	Changes the data type or size of a column.	MODIFY COLUMN Name VARCHAR(100)
DROP COLUMN	Removes a column.	DROP COLUMN Section

---

Comparison of DDL Commands

Command	Purpose	Example
CREATE DATABASE	Creates a new database.	CREATE DATABASE SchoolDB;
USE	Selects a database.	USE SchoolDB;
CREATE TABLE	Creates a table.	CREATE TABLE Student (...);
ALTER TABLE	Changes the table structure.	ALTER TABLE Student ADD COLUMN Age INT;
DROP TABLE	Deletes a table.	DROP TABLE Student;

---

Solved Example 1

Create a database named LibraryDB and select it.

Solution:

CREATE DATABASE LibraryDB;

USE LibraryDB;

---

Solved Example 2

Create a table named Book with Book_ID, Book_Name, and Price.

Solution:

CREATE TABLE Book
(
Book_ID INT,
Book_Name VARCHAR(100),
Price FLOAT
);

---

Solved Example 3

Add a column named Author to the Book table.

Solution:

ALTER TABLE Book
ADD COLUMN Author VARCHAR(50);

---

Solved Example 4

Increase the size of Book_Name to 150 characters.

Solution:

ALTER TABLE Book
MODIFY COLUMN Book_Name VARCHAR(150);

---

Solved Example 5

Remove the Author column from the Book table.

Solution:

ALTER TABLE Book
DROP COLUMN Author;

---

Common Errors

Mistake	Correct Practice
Creating a table without selecting a database.	Use the USE command first.
Using duplicate column names.	Each column name must be unique.
Forgetting the semicolon (;).	Terminate every SQL statement with a semicolon.
Using an incorrect data type.	Select the appropriate data type for each column.
Dropping a table accidentally.	Remember that DROP TABLE permanently deletes the table and all its records.

---

Interview Corner

---

---

Real-Life Applications of DDL Commands

Organization	DDL Command Used	Purpose
School	CREATE DATABASE	Create a database to store student records.
College	CREATE TABLE	Create tables for students, teachers, and courses.
Hospital	ALTER TABLE	Add a new column for blood group or insurance details.
Bank	MODIFY COLUMN	Increase the size of the customer name field.
Library	DROP COLUMN	Remove an unnecessary column from the Book table.
Company	DROP TABLE	Delete an old table that is no longer required.

---

Solved Example 6

Create a database named LibraryDB and create a table named Book.

Solution:

CREATE DATABASE LibraryDB;

USE LibraryDB;

CREATE TABLE Book
(
Book_ID INT,
Book_Name VARCHAR(100),
Price FLOAT
);

---

Solved Example 7

Add a column named Publisher to the Book table.

Solution:

ALTER TABLE Book
ADD COLUMN Publisher VARCHAR(50);

---

Solved Example 8

Change the size of the Publisher column to 100 characters.

Solution:

ALTER TABLE Book
MODIFY COLUMN Publisher VARCHAR(100);

---

Solved Example 9

Delete the Publisher column from the Book table.

Solution:

ALTER TABLE Book
DROP COLUMN Publisher;

---

Solved Example 10

Delete the Book table.

Solution:

DROP TABLE Book;

---

Competency-Based Questions

1. A school wants to create a new database named SchoolDB and a table named Student. Write the SQL commands required to complete the task.
2. A library has decided to add a new column named Author to its Book table. Which SQL command should be used? Write the syntax.
3. A company wants to increase the size of the Employee_Name column from 50 to 100 characters. Which DDL command will be used? Explain.
4. A hospital no longer requires the Middle_Name column in its Patient table. Which SQL command should be used? Why?
5. Differentiate between CREATE TABLE, ALTER TABLE, and DROP TABLE with suitable examples.

---

Multiple Choice Questions

1. DDL stands for:
   • (a) Data Development Language
   • (b) Data Definition Language
   • (c) Data Description Logic
   • (d) Database Definition Language
2. Which command creates a new database?
   • (a) CREATE TABLE
   • (b) CREATE DATABASE
   • (c) ALTER TABLE
   • (d) USE
3. Which command selects a database for use?
   • (a) CREATE
   • (b) USE
   • (c) ALTER
   • (d) DROP
4. Which command creates a new table?
   • (a) MODIFY TABLE
   • (b) CREATE TABLE
   • (c) UPDATE TABLE
   • (d) SELECT TABLE
5. Which command is used to add a new column to an existing table?
   • (a) UPDATE COLUMN
   • (b) ALTER TABLE ADD COLUMN
   • (c) CREATE COLUMN
   • (d) INSERT COLUMN
6. Which command changes the data type or size of an existing column?
   • (a) UPDATE
   • (b) ALTER TABLE MODIFY COLUMN
   • (c) ALTER TABLE ADD COLUMN
   • (d) CREATE TABLE
7. Which command removes a column from a table?
   • (a) DELETE COLUMN
   • (b) REMOVE COLUMN
   • (c) ALTER TABLE DROP COLUMN
   • (d) DROP DATABASE
8. Which command permanently deletes an entire table?
   • (a) DELETE
   • (b) REMOVE TABLE
   • (c) DROP TABLE
   • (d) CLEAR TABLE
9. Which SQL command is used to modify the structure of a table?
   • (a) UPDATE
   • (b) INSERT
   • (c) ALTER TABLE
   • (d) SELECT
10. Which of the following is not a DDL command?
    • (a) CREATE TABLE
    • (b) ALTER TABLE
    • (c) DROP TABLE
    • (d) INSERT

---

Quick Revision

Command	Purpose
CREATE DATABASE	Creates a new database.
USE	Selects a database.
CREATE TABLE	Creates a new table.
ALTER TABLE ADD COLUMN	Adds a new column.
ALTER TABLE MODIFY COLUMN	Changes the data type or size of a column.
ALTER TABLE DROP COLUMN	Removes a column.
DROP TABLE	Permanently deletes a table.

---

Important Points to Remember

• DDL commands define and modify the structure of databases and tables.
• Always create a database before creating tables.
• Use the USE command to select the required database.
• CREATE TABLE requires column names along with their data types.
• ALTER TABLE is used to add, modify, or remove columns.
• DROP TABLE permanently deletes the table and all the records stored in it.
• DDL commands mainly work on the structure of a database rather than the data itself.
• Every SQL statement should end with a semicolon (;).

---

CBSE Exam Tips

• Memorize the syntax of all DDL commands prescribed in the syllabus.
• Practice writing SQL commands without syntax errors.
• Understand the difference between CREATE, ALTER, and DROP.
• Remember that DROP TABLE permanently deletes both the table structure and its data.
• Competency-based questions often require writing complete SQL statements for real-life scenarios.

---

Summary

Data Definition Language (DDL) is used to create and manage the structure of databases and tables. The CREATE DATABASE command creates a database, USE selects it, and CREATE TABLE creates tables with appropriate columns and data types. The ALTER TABLE command modifies the table structure by adding, changing, or deleting columns, while DROP TABLE permanently removes a table and all its data. Understanding these commands is essential before learning how to insert, update, delete, and retrieve records using SQL.