Module 1: Introduction to SQL

Topic 1: What is a Database?

Description

A Database is an organized collection of related data that is stored electronically so that it can be
easily accessed, managed, updated, and retrieved whenever required.

In simple terms, a database acts like a digital storage system where information is stored in an
organized manner. Instead of maintaining records on paper, organizations use databases to store
large amounts of information securely.

A database can contain multiple tables, and each table stores a specific type of information. For
example, a college database may have separate tables for students, faculty, departments, courses,
and attendance.

Real-World Examples

 College Management System

 Banking System

 Hospital Management System

 Online Shopping Applications

 Railway Reservation System

Syntax

A database is a concept, so there is no specific SQL syntax for defining a database.

However, to create a database in SQL, the syntax is:

CREATE DATABASE database_name;

Full Query

-- Create a Database
CREATE DATABASE CollegeDB;

-- Select the Database

USE CollegeDB;

Practice Task

Task 1

Create a database named SchoolDB.

Task 2

1
Create another database named HospitalDB.

Task 3

Switch between both databases using the USE command.

Topic 2: What is DBMS?

Description

DBMS (Database Management System) is software that allows users to create, store, retrieve,
update, and manage databases.

A DBMS acts as an interface between the user and the database. Instead of interacting directly with
the database files, users send commands to the DBMS, and it performs the required operations.

A DBMS provides features such as:

 Data Storage

 Data Retrieval

 Data Security

 Backup and Recovery

 Multi-user Access

 Data Integrity

Popular DBMS Software

 MySQL

 Oracle Database

 Microsoft SQL Server

 PostgreSQL

 SQLite

 MariaDB

Syntax

There is no dedicated SQL syntax for DBMS because it is software, not a command.

Example SQL commands executed through a DBMS:

CREATE DATABASE CompanyDB;

USE CompanyDB;

Full Query

2
CREATE DATABASE CompanyDB;

USE CompanyDB;

CREATE TABLE Employee(

EmployeeID INT,
EmployeeName VARCHAR(50),
Department VARCHAR(30)
);

INSERT INTO Employee

VALUES
(101,'Rahul','IT'),
(102,'Priya','HR');

SELECT * FROM Employee;

Practice Task

1. Install MySQL.

2. Create a database named OfficeDB.

3. Create an Employee table.

4. Insert 5 employee records.

5. Display all records.

Topic 3: What is RDBMS?

Description

RDBMS (Relational Database Management System) is a type of DBMS that stores data in the form of
tables.

An RDBMS organizes data into rows and columns and establishes relationships between tables using
Primary Keys and Foreign Keys.

For example, a college database may have separate tables for Students and Departments. These
tables are connected through a common column called a Foreign Key.

Features of RDBMS

 Table-based storage

 Relationships between tables

 Data Integrity

 Reduced Data Redundancy

3
  Multi-user Support

 Security

 SQL Support

Popular RDBMS Software

 MySQL

 Oracle

 PostgreSQL

 SQL Server

 MariaDB

Syntax

There is no specific syntax because RDBMS is a database system.

Example of creating related tables:

CREATE TABLE Department(
DepartmentID INT PRIMARY KEY,
DepartmentName VARCHAR(50)
);

CREATE TABLE Student(

StudentID INT PRIMARY KEY,
StudentName VARCHAR(50),
DepartmentID INT,
FOREIGN KEY (DepartmentID)
REFERENCES Department(DepartmentID)
);

Full Query

CREATE DATABASE CollegeDB;

USE CollegeDB;

CREATE TABLE Department(

DepartmentID INT PRIMARY KEY,
DepartmentName VARCHAR(50)
);

CREATE TABLE Student(

StudentID INT PRIMARY KEY,
StudentName VARCHAR(50),

4
 DepartmentID INT,
FOREIGN KEY (DepartmentID)
REFERENCES Department(DepartmentID)
);

INSERT INTO Department

VALUES
(1,'CSE'),
(2,'ECE');

INSERT INTO Student

VALUES
(101,'Rahul',1),
(102,'Anitha',2);

SELECT * FROM Student;

Practice Task

1. Create a Department table.

2. Create a Student table.

3. Connect both tables using a Foreign Key.

4. Insert 5 records into each table.

5. Display the Student table.

Topic 4: Difference Between DBMS and RDBMS

Description

Although both DBMS and RDBMS are used to manage databases, RDBMS provides additional
features such as relationships, constraints, and better security.

DBMS RDBMS

Stores data Stores data in related tables

Relationships are optional Relationships are mandatory

Lower security Higher security

Supports small applications Supports enterprise applications

May not support Foreign Keys Supports Foreign Keys

Less data integrity Better data integrity

5
Syntax

No syntax available because this is a theoretical topic.

Practice Task

1. Write five differences between DBMS and RDBMS.

2. Name five DBMS software.

3. Name five RDBMS software.

4. Explain why MySQL is considered an RDBMS.

Topic 5: What is SQL?

Description

SQL (Structured Query Language) is the standard language used to communicate with relational
databases.

SQL is used to:

 Create databases

 Create tables

 Insert records

 Retrieve records

 Update records

 Delete records

 Manage users and permissions

 Control transactions

SQL is supported by almost every RDBMS, making it the most widely used language for database
management.

Applications of SQL

 Banking Systems

 Hospital Management

 E-commerce Websites

 College Management Systems

 Employee Management Systems

6
Syntax

SQL_Command;

Examples:

SELECT * FROM Student;

INSERT INTO Student VALUES(...);

UPDATE Student SET ...;

DELETE FROM Student WHERE ...;

Full Query

CREATE DATABASE CollegeDB;

USE CollegeDB;

CREATE TABLE Student(

StudentID INT PRIMARY KEY,
StudentName VARCHAR(50),
Department VARCHAR(30),
Age INT
);

INSERT INTO Student

VALUES
(101,'Rahul','CSE',20),
(102,'Priya','ECE',21),
(103,'Arun','IT',19);

SELECT * FROM Student;

Output

StudentID StudentName Department Age

101 Rahul CSE 20

102 Priya ECE 21

103 Arun IT 19

7
Practice Task

1. Create a database named LibraryDB.

2. Create a table named Books.

3. Add the following columns:

o BookID

o BookName

o Author

o Price

4. Insert 5 records.

5. Display all records using the SELECT statement.

Topic 6: SQL Categories (Types of SQL Commands)

Description

SQL commands are classified into five categories based on the operation they perform on a
database. Understanding these categories is essential because every SQL statement belongs to one
of them.

The five categories are:

1. DDL (Data Definition Language) – Used to define and modify the database structure.

2. DML (Data Manipulation Language) – Used to insert, update, and delete data.

3. DQL (Data Query Language) – Used to retrieve data from a database.

4. DCL (Data Control Language) – Used to control user permissions and access.

5. TCL (Transaction Control Language) – Used to manage database transactions.

SQL Categories Overview

Category Full Form Purpose Common Commands

Defines database CREATE, ALTER, DROP, TRUNCATE,

DDL Data Definition Language
objects RENAME

Data Manipulation
DML Modifies data INSERT, UPDATE, DELETE
Language

DQL Data Query Language Retrieves data SELECT

Controls user
DCL Data Control Language GRANT, REVOKE
permissions

8
Category Full Form Purpose Common Commands

Transaction Control
TCL Manages transactions COMMIT, ROLLBACK, SAVEPOINT
Language

Syntax

DDL

CREATE TABLE table_name (

column_name datatype
);

ALTER TABLE table_name

ADD column_name datatype;

DROP TABLE table_name;

DML

INSERT INTO table_name

VALUES (...);
UPDATE table_name
SET column_name = value
WHERE condition;

DELETE FROM table_name

WHERE condition;

DQL

SELECT * FROM table_name;

DCL

GRANT permission
ON table_name
TO user_name;

REVOKE permission
ON table_name
FROM user_name;

TCL

COMMIT;

9
ROLLBACK;

SAVEPOINT savepoint_name;

Full Query

-- Create Database
CREATE DATABASE CompanyDB;

-- Select Database
USE CompanyDB;

-- DDL
CREATE TABLE Employee(
EmployeeID INT PRIMARY KEY,
EmployeeName VARCHAR(50),
Department VARCHAR(30),
Salary DECIMAL(10,2)
);

-- DML
INSERT INTO Employee
VALUES
(101,'Rahul','IT',50000),
(102,'Priya','HR',45000);

UPDATE Employee
SET Salary = 55000
WHERE EmployeeID = 101;

DELETE FROM Employee

WHERE EmployeeID = 102;

-- DQL
SELECT * FROM Employee;

-- TCL
COMMIT;

Output

After executing the above queries:

EmployeeID EmployeeName Department Salary

101 Rahul IT 55000.00

10
Practice Task

Task 1

Create a database named SchoolDB.

Task 2

Create a table named Student with the following columns:

 StudentID

 StudentName

 Department

 Age

Task 3

Insert five student records.

Task 4

Update the age of one student.

Task 5

Delete one student record.

Task 6

Display all remaining records using the SELECT statement.

Task 7

Commit the transaction.

Key Points

 DDL changes the structure of database objects.

 DML modifies the data stored in tables.

 DQL retrieves data from the database.

 DCL manages user permissions and access control.

 TCL controls transactions to ensure data consistency.

Interview Questions

1. What are the five categories of SQL commands?

2. What is the difference between DDL and DML?

11
 3. Which SQL category does the SELECT command belong to?

4. What is the purpose of COMMIT and ROLLBACK?

5. Which commands are included in DCL?

Topic 7: SQL Data Types

Description

A Data Type defines the type of data that can be stored in a column of a table. Choosing the correct
data type helps improve storage efficiency, data accuracy, and query performance.

For example:

 Student ID should store only numbers.

 Student Name should store text.

 Date of Birth should store dates.

 Salary should store decimal values.

When creating a table, every column must have a data type.

Common SQL Data Types

1. INT

Used to store whole numbers.

Examples:

 10

 100

 5000

StudentID INT

2. VARCHAR(n)

Used to store variable-length text.

The value of n specifies the maximum number of characters.

Examples:

 Rahul

 Computer Science

 Chennai

StudentName VARCHAR(50)

12
3. CHAR(n)

Stores fixed-length text.

If the specified length is not used, SQL automatically fills the remaining space with blank characters.

Example:

Gender CHAR(1)

Possible values:

M
F

4. DATE

Stores date values.

Format:

YYYY-MM-DD

Example:

2026-06-28

DateOfBirth DATE

5. DECIMAL(p,s)

Stores decimal numbers.

 p → Total number of digits

 s → Number of digits after the decimal point

Example:

Salary DECIMAL(10,2)

Possible values

45000.50

150000.75

6. FLOAT

Stores approximate decimal values.

Example

Weight FLOAT

13
Possible values

65.5

72.85

7. BOOLEAN / BOOL

Stores logical values.

Possible values

TRUE

FALSE

Example

IsPlaced BOOLEAN

8. TEXT

Stores large amounts of text.

Example

Address TEXT

Summary Table

Data Type Description Example

INT Stores whole numbers 101

VARCHAR(50) Variable-length text Rahul

CHAR(1) Fixed-length text M

DATE Stores dates 2026-06-28

DECIMAL(10,2) Decimal numbers 55000.75

FLOAT Floating-point numbers 72.50

BOOLEAN True or False TRUE

TEXT Large text Full Address

14
Syntax

CREATE TABLE table_name

(
column1 INT,
column2 VARCHAR(50),
column3 DATE,
column4 DECIMAL(10,2)
);

Full Query

-- Create Database
CREATE DATABASE CollegeDB;

-- Select Database
USE CollegeDB;

-- Create Student Table

CREATE TABLE Student
(
StudentID INT,
StudentName VARCHAR(50),
Gender CHAR(1),
DateOfBirth DATE,
Department VARCHAR(30),
CGPA DECIMAL(3,2),
Attendance FLOAT,
IsPlaced BOOLEAN,
Address TEXT
);

-- Insert Records

INSERT INTO Student

VALUES
(101,
'Rahul',
'M',
'2004-05-18',
'CSE',
8.75,
92.5,
TRUE,
'Chennai');

INSERT INTO Student

VALUES

15
(102,
'Priya',
'F',
'2003-11-20',
'ECE',
9.10,
95.2,
FALSE,
'Coimbatore');

-- Display Records

SELECT * FROM Student;

Output

StudentID StudentName Gender DateOfBirth Department CGPA Attendance IsPlaced Address

101 Rahul M 2004-05-18 CSE 8.75 92.5 TRUE Chennai

102 Priya F 2003-11-20 ECE 9.10 95.2 FALSE Coimbatore

Practice Task

Task 1

Create a database named EmployeeDB.

Task 2

Create an Employee table with the following columns.

Column Name Data Type

EmployeeID INT

EmployeeName VARCHAR(50)

Gender CHAR(1)

JoiningDate DATE

Department VARCHAR(30)

Salary DECIMAL(10,2)

Experience FLOAT

16
Column Name Data Type

IsPermanent BOOLEAN

Address TEXT

Task 3

Insert 5 employee records.

Task 4

Display all employee records.

SELECT * FROM Employee;

Task 5

Identify the data type used for the following fields:

 Mobile Number

 Email

 Salary

 Date of Birth

 Address

 Employee Name

 Department

 Age

Key Points

 Every column in a table must have a data type.

 Choose the correct data type based on the kind of values the column will store.

 VARCHAR is preferred over CHAR when the text length varies.

 DECIMAL is recommended for financial data because it stores exact values.

 DATE should be used for storing dates instead of text.

 Proper data type selection improves storage efficiency and query performance.

17
Interview Questions

1. What is a data type in SQL?

2. What is the difference between CHAR and VARCHAR?

3. When would you use the DECIMAL data type instead of FLOAT?

4. Which data type is best suited for storing dates?

5. Why is selecting the correct data type important in database design?

Topic 8: SQL Naming Rules and Conventions

Description

Naming Rules are guidelines used while creating database objects such as databases, tables,
columns, views, indexes, and constraints.

Following proper naming conventions makes the database easier to read, understand, and maintain,
especially when working on large projects or with multiple developers.

A good naming convention improves code readability and reduces confusion.

SQL Naming Rules

Rule 1: Name Should Start with a Letter

Database names, table names, and column names should begin with an alphabet.

✔ Correct

Student
Employee
Department

❌ Incorrect

123Student
1Employee

Rule 2: Avoid Spaces

Object names should not contain spaces.

✔ Correct

StudentDetails
Student_Details

❌ Incorrect

Student Details
Employee Data

18
Rule 3: Use Meaningful Names

Choose names that clearly describe the purpose of the object.

✔ Correct

Employee
EmployeeSalary
Department
StudentMarks

❌ Incorrect

ABC
XYZ
Table1
Data123

Rule 4: Avoid Special Characters

Avoid using special characters such as:

@
#
$
%
&
*
!
?
+
=

✔ Correct

Employee
Student_Name

❌ Incorrect

Employee#
Student@
Marks$

Rule 5: Avoid SQL Reserved Keywords

Do not use SQL keywords as object names.

Examples of reserved keywords:

19
SELECT
FROM
WHERE
ORDER
GROUP
TABLE
DATABASE
INSERT
UPDATE
DELETE

Instead of

TABLE SELECT

Use

StudentDetails
EmployeeData

Rule 6: Keep Names Short and Meaningful

✔ Good

Employee

Department

StudentMarks

❌ Bad

EmployeeInformationManagementSystemData

Rule 7: Follow a Consistent Naming Style

Examples

StudentID

StudentName

DepartmentID

DepartmentName

Maintain the same naming style throughout the database.

20
Naming Convention Examples

Database Names

CollegeDB

LibraryDB

EmployeeDB

HospitalDB

Table Names

Student

Employee

Department

Course

Book

Column Names

StudentID

StudentName

Age

Salary

DepartmentID

JoiningDate

Syntax

CREATE DATABASE CollegeDB;

USE CollegeDB;

CREATE TABLE Student

(
StudentID INT,

21
 StudentName VARCHAR(50),
Department VARCHAR(30)
);

Full Query

-- Create Database

CREATE DATABASE CollegeDB;

-- Select Database

USE CollegeDB;

-- Create Department Table

CREATE TABLE Department

(
DepartmentID INT PRIMARY KEY,
DepartmentName VARCHAR(50)
);

-- Create Student Table

CREATE TABLE Student

(
StudentID INT PRIMARY KEY,
StudentName VARCHAR(50),
Age INT,
DepartmentID INT
);

-- Display Tables

SHOW TABLES;

Output

+----------------+
| Tables_in_CollegeDB |
+----------------+
| Department |
| Student |
+----------------+

22
Best Practices

 Use singular table names (Student instead of Students).

 Use PascalCase or snake_case consistently.

 Use meaningful column names.

 Avoid abbreviations unless they are widely understood.

 Keep names simple and readable.

 Use ID suffix for primary key columns (e.g., StudentID, EmployeeID).

 Prefix foreign keys with the referenced table name (e.g., DepartmentID in the Student table).

Practice Task

Task 1

Create a database named SchoolDB.

Task 2

Create the following tables using proper naming conventions:

 Student

 Teacher

 Subject

 Classroom

Task 3

Each table should contain meaningful column names.

Example:

Student

 StudentID

 StudentName

 Age

 Gender

Teacher

 TeacherID

 TeacherName

23
  SubjectName

Task 4

Display all tables.

SHOW TABLES;

Task 5

Identify whether the following names are valid or invalid.

Name Valid / Invalid

Student

Employee123

Student Data

#Employee

Department_Name

SELECT

Marks

123College

Key Points

 Always use meaningful names.

 Avoid spaces and special characters.

 Do not use SQL reserved keywords.

 Maintain consistent naming conventions.

 Use descriptive names for tables and columns.

 Follow a uniform naming style throughout the database.

Interview Questions

1. What are SQL naming conventions?

2. Can a table name contain spaces?

24
 3. Why should reserved keywords be avoided as object names?

4. What is the advantage of using meaningful names?

5. What naming convention would you recommend for a large project?

Module 2: Database Operations

This module covers the basic SQL commands used to create, view, select, and delete databases.

Topic 1: CREATE DATABASE

Description

The CREATE DATABASE statement is used to create a new database in the Database Management
System (DBMS).

A database acts as a container that stores tables, views, procedures, functions, and other database
objects.

Before creating tables, a database must be created.

Example

If you are developing a College Management System, the first step is to create a database named
CollegeDB. All related tables such as Student, Faculty, Department, and Course will be stored inside
this database.

Syntax

CREATE DATABASE database_name;

Full Query

-- Create a new database

CREATE DATABASE CollegeDB;

Verify the Database

SHOW DATABASES;

Output
+--------------------+
| Database |
+--------------------+
| CollegeDB |
| information_schema |
| mysql |
| performance_schema |
| sys |
+--------------------+

25
Practice Task

Task 1

Create a database named LibraryDB.

Task 2

Create a database named HospitalDB.

Task 3

Display all available databases.

Task 4

Create a database named EmployeeDB.

Topic 2: SHOW DATABASES

Description

The SHOW DATABASES command displays all databases available in the MySQL server.

It helps users verify whether a database has been created successfully.

Syntax

SHOW DATABASES;

Full Query

CREATE DATABASE SchoolDB;

CREATE DATABASE CompanyDB;

SHOW DATABASES;

Output

+--------------------+
| Database |
+--------------------+
| CollegeDB |
| CompanyDB |
| SchoolDB |
| information_schema |
| mysql |
| performance_schema |
| sys |
+-----------------

26
Practice Task

1. Create three databases:

o BankDB

o ShoppingDB

o RailwayDB

2. Display all databases.

3. Verify that the newly created databases are listed.

Topic 3: USE DATABASE

Description

The USE statement is used to select a database for performing operations.

Before creating tables or inserting records, SQL must know which database should be used.

Only one database can be active at a time.

Syntax

USE database_name;

Full Query

CREATE DATABASE CollegeDB;

USE CollegeDB;

Now any table created will be stored inside CollegeDB.

Example:

CREATE TABLE Student

(
StudentID INT,
StudentName VARCHAR(50)
);

Practice Task

Task 1

Create a database named OfficeDB.

Task 2

Select the database using the USE statement.

Task 3

Create a table named Employee.

27
Topic 4: DROP DATABASE

Description

The DROP DATABASE statement permanently deletes an existing database.

When a database is deleted:

 All tables are removed.

 All records are deleted.

 Views, procedures, and other database objects are also deleted.

Warning: This operation cannot be undone.

Syntax

DROP DATABASE database_name;

Full Query

CREATE DATABASE TestDB;

SHOW DATABASES;

DROP DATABASE TestDB;

SHOW DATABASES;

Output

Before DROP

CollegeDB
EmployeeDB
TestDB

After DROP

CollegeDB
EmployeeDB

Practice Task

Task 1

Create a database named DemoDB.

Task 2

Display all databases.

28
Task 3

Delete DemoDB.

Task 4

Display all databases again to verify that DemoDB has been removed.

Topic 5: Database Operations (Complete Example)

Description

The following example demonstrates the complete workflow of creating, selecting, viewing, and
deleting a database.

Full Query
-- Create Database

CREATE DATABASE CollegeDB;

-- Display Databases

SHOW DATABASES;

-- Select Database

USE CollegeDB;

-- Create Table

CREATE TABLE Student

(
StudentID INT,
StudentName VARCHAR(50)
);

-- Display Tables

SHOW TABLES;

-- Delete Database

DROP DATABASE CollegeDB;

Practice Task

Perform the following steps:

29
 1. Create a database named UniversityDB.

2. Display all databases.

3. Select UniversityDB.

4. Create a table named Student with the following columns:

o StudentID

o StudentName

o Department

5. Display all tables.

6. Delete the UniversityDB database.

7. Display all databases to confirm the deletion.

Module 2 Summary

In this module, you learned the following database operations:

Command Purpose

CREATE DATABASE Creates a new database

SHOW DATABASES Displays all databases

USE Selects a database for use

DROP DATABASE Deletes an existing database

Module 3: Table Operations

In this module, you will learn how to create, view, modify, rename, empty, and delete tables. Tables
are the most important objects in a database because they store the actual data.

Topic 1: CREATE TABLE

Description

The CREATE TABLE statement is used to create a new table inside a database.

A table consists of:

 Rows (Records) – Store individual entries.

 Columns (Fields) – Define the type of information stored.

Before creating a table:

30
 1. A database must already exist.

2. The required database should be selected using the USE statement.

Syntax

CREATE TABLE table_name

(
column_name1 datatype,
column_name2 datatype,
column_name3 datatype
);

Full Query

-- Create Database

CREATE DATABASE CollegeDB;

-- Select Database

USE CollegeDB;

-- Create Student Table

CREATE TABLE Student

(
StudentID INT,
StudentName VARCHAR(50),
Department VARCHAR(30),
Age INT
);

Verify the Table

SHOW TABLES;

Output
+--------------------+
| Tables_in_CollegeDB|
+--------------------+
| Student |
+--------------------+

31
Practice Task

Task 1

Create a database named SchoolDB.

Task 2

Create a table named Student with the following columns:

 StudentID

 StudentName

 Department

 Age

Task 3

Display all tables.

Topic 2: SHOW TABLES

Description

The SHOW TABLES statement displays all tables available in the currently selected database.

It helps verify whether a table has been created successfully.

Syntax

SHOW TABLES;

Full Query

CREATE DATABASE LibraryDB;

USE LibraryDB;

CREATE TABLE Books

(
BookID INT,
BookName VARCHAR(100),
Author VARCHAR(50)
);

CREATE TABLE Members

(
MemberID INT,
MemberName VARCHAR(50)

32
);

SHOW TABLES;

Output

+------------------+
| Tables_in_LibraryDB |
+------------------+
| Books |
| Members |
+------------------+

Practice Task

1. Create three tables:

o Employee

o Department

o Project

2. Display all tables.

Topic 3: DESCRIBE TABLE (DESC)

Description

The DESCRIBE (or DESC) statement displays the structure of a table.

It shows:

 Column Name

 Data Type

 NULL Value

 Key Information

 Default Value

 Extra Information

This command is useful when you want to know the table structure without opening the table.

Syntax

DESCRIBE table_name;

or

33
DESC table_name;

Full Query

USE CollegeDB;

CREATE TABLE Student

(
StudentID INT,
StudentName VARCHAR(50),
Department VARCHAR(30),
Age INT
);

DESC Student;

Output
+-------------+-------------+------+-----+---------+-------+
| Field | Type | Null | Key | Default | Extra |
+-------------+-------------+------+-----+---------+-------+
| StudentID | int | YES | | NULL | |
| StudentName | varchar(50) | YES | | NULL | |
| Department | varchar(30) | YES | | NULL | |
| Age | int | YES | | NULL | |
+-------------+-------------+------+-----+---------+-------+

Practice Task

1. Create a table named Employee.

2. Display its structure using both:

o DESC Employee;

o DESCRIBE Employee;

Topic 4: ALTER TABLE

Description

The ALTER TABLE statement is used to modify the structure of an existing table.

Common operations include:

 Add a new column

 Modify an existing column

 Rename a column

 Drop a column

34
Syntax

Add a Column

ALTER TABLE table_name

ADD column_name datatype;

Modify a Column

ALTER TABLE table_name

MODIFY column_name datatype;

Drop a Column

ALTER TABLE table_name

DROP COLUMN column_name;

Full Query

USE CollegeDB;

CREATE TABLE Student

(
StudentID INT,
StudentName VARCHAR(50)
);

-- Add a Column

ALTER TABLE Student

ADD Age INT;

-- Modify Column

ALTER TABLE Student

MODIFY StudentName VARCHAR(100);

-- Drop Column

ALTER TABLE Student

DROP COLUMN Age;

DESC Student;

Output

+-------------+--------------+
| Field | Type |
+-------------+--------------+
| StudentID | int |

35
| StudentName | varchar(100) |
+-------------+--------------+

Practice Task

1. Create an Employee table.

2. Add a Salary column.

3. Add a Department column.

4. Change EmployeeName from VARCHAR(50) to VARCHAR(100).

5. Remove the Department column.

Topic 5: RENAME TABLE

Description

The RENAME TABLE statement is used to change the name of an existing table.

Only the table name changes; the data remains unchanged.

Syntax

RENAME TABLE old_table_name

TO new_table_name;

Full Query
USE CollegeDB;

CREATE TABLE Student

(
StudentID INT,
StudentName VARCHAR(50)
);

RENAME TABLE Student

TO Students;

SHOW TABLES;

Output

+--------------------+
| Tables_in_CollegeDB|
+--------------------+
| Students |
+--------------------+

36
Practice Task

1. Create a table named Employee.

2. Rename it to Employees.

3. Verify using SHOW TABLES.

Topic 6: TRUNCATE TABLE

Description

The TRUNCATE TABLE statement removes all records from a table but keeps the table structure
intact.

Use TRUNCATE when you want to empty a table quickly without deleting the table itself.

Syntax

TRUNCATE TABLE table_name;

Full Query

USE CollegeDB;

CREATE TABLE Student

(
StudentID INT,
StudentName VARCHAR(50)
);

INSERT INTO Student

VALUES
(101,'Rahul'),
(102,'Priya');

TRUNCATE TABLE Student;

SELECT * FROM Student;

Output

Empty Set

37
Practice Task

1. Create a Product table.

2. Insert 5 records.

3. Display the records.

4. Truncate the table.

5. Verify that the table is empty.

Topic 7: DROP TABLE

Description

The DROP TABLE statement permanently deletes a table from the database.

When a table is dropped:

 The table structure is deleted.

 All records are deleted.

 The table cannot be recovered unless a backup exists.

Syntax

DROP TABLE table_name;

Full Query

USE CollegeDB;

CREATE TABLE Student

(
StudentID INT,
StudentName VARCHAR(50)
);

SHOW TABLES;

DROP TABLE Student;

SHOW TABLES;

38
Output

Before DROP

Student

After DROP

Empty Set

Practice Task

1. Create a table named Customer.

2. Verify that it exists.

3. Drop the table.

4. Verify that it has been removed.

Module 3 Summary

Command Purpose

CREATE TABLE Creates a new table

SHOW TABLES Displays all tables

DESC / DESCRIBE Displays the table structure

ALTER TABLE Modifies the table structure

RENAME TABLE Renames a table

TRUNCATE TABLE Deletes all rows but keeps the table

DROP TABLE Permanently deletes the table

Module 3 Practice Exercise

Create a database named CompanyDB and perform the following:

1. Create an Employee table.

2. Display all tables.

3. Describe the table.

4. Add a Salary column.

5. Rename the table to Employees.

39
6. Insert a few records (you'll learn INSERT in the next module).

7. Truncate the table.

8. Drop the table.

40