Practical - 4 Implement SQL Queries for ALTER, UPDATE, DELETE, DROP, and SELECT Commands

 Implement SQL Queries for ALTER, UPDATE, DELETE, DROP, and SELECT Commands

---

Aim

To study and implement SQL commands ALTER, UPDATE, DELETE, DROP, and SELECT for modifying database structures, manipulating records, deleting data, removing database objects, and retrieving information from relational database tables.

---

Objectives

After completing this experiment, students will be able to:

1. Understand the purpose of SQL DDL, DML, and DQL commands.
2. Modify the structure of an existing table using the ALTER command.
3. Update existing records using the UPDATE command.
4. Delete specific or multiple records using the DELETE command.
5. Permanently remove database objects using the DROP command.
6. Retrieve and display records using different forms of the SELECT command.
7. Apply filtering, sorting, and projection techniques while retrieving data.
8. Understand the importance of the WHERE clause in SQL commands.

---

Theory

Introduction to SQL Commands

Structured Query Language (SQL) is the standard language used to communicate with Relational Database Management Systems (RDBMS) such as MySQL, Oracle, SQL Server, PostgreSQL, and SQLite. SQL enables users to create, modify, retrieve, and manage data efficiently.

SQL commands are classified into several categories:

Category	Description	Examples
DDL	Defines or modifies database objects	CREATE, ALTER, DROP
DML	Manipulates table data	INSERT, UPDATE, DELETE
DQL	Retrieves data	SELECT
DCL	Controls user permissions	GRANT, REVOKE
TCL	Manages transactions	COMMIT, ROLLBACK

This practical focuses on five essential SQL commands used in day-to-day database management.

---

1. ALTER Command

The ALTER command is a Data Definition Language (DDL) command used to change the structure of an existing table. It allows users to add, modify, rename, or remove columns without affecting existing records.

Syntax

ALTER TABLE table_name
ADD column_name datatype;

ALTER TABLE table_name
MODIFY column_name datatype;

ALTER TABLE table_name
DROP COLUMN column_name;

Real-Life Example

A college initially stores only student names and departments. Later, the administration decides to maintain students' email addresses. Instead of creating a new table, the existing table is modified using the ALTER command.

---

2. UPDATE Command

The UPDATE command belongs to Data Manipulation Language (DML). It modifies existing records stored in a table. It is commonly used when information changes after data entry.

Syntax

UPDATE table_name
SET column_name = value
WHERE condition;

Real-Life Example

After a university examination revaluation, some students receive additional marks. Their existing records are updated rather than inserting new records.

---

3. DELETE Command

The DELETE command removes one or more records from a table while preserving the table structure.

Syntax

DELETE FROM table_name
WHERE condition;

Real-Life Example

If a student cancels admission, the student's record is removed from the student database using the DELETE command.

---

4. DROP Command

The DROP command permanently removes database objects such as tables or databases.

Syntax

DROP TABLE table_name;

Real-Life Example

Temporary tables created during project development are deleted using the DROP command once they are no longer required.

---

5. SELECT Command

The SELECT command belongs to Data Query Language (DQL) and is used to retrieve information stored in database tables.

Syntax

SELECT * FROM table_name;

SELECT column_name
FROM table_name
WHERE condition;

Real-Life Example

A college administrator retrieves all Computer Engineering students who scored above 80% marks using a SELECT query.

---

Algorithm

1. Start MySQL Server.
2. Open MySQL Command Line Client or MySQL Workbench.
3. Create the required database.
4. Create the Student table.
5. Insert sample records.
6. Execute the ALTER command to modify the table structure.
7. Execute UPDATE queries to modify existing records.
8. Execute DELETE queries to remove records.
9. Execute DROP command on a temporary table.
10. Execute SELECT queries to retrieve required information.
11. Observe and verify the output.
12. Stop the MySQL Server.

---

SQL Program

Step 1: Create Student Table

CREATE TABLE Student(
StudentID INT PRIMARY KEY,
Name VARCHAR(50),
Department VARCHAR(30),
Semester INT,
Marks INT
);

---

Step 2: Insert Records

INSERT INTO Student
VALUES
(101,'Rahul','Computer',5,85),
(102,'Neha','IT',4,78),
(103,'Amit','Mechanical',6,91),
(104,'Priya','Computer',3,67),
(105,'Kiran','Civil',5,80);

---

Step 3: ALTER Command

Add New Column

ALTER TABLE Student
ADD Email VARCHAR(50);

Modify Existing Column

ALTER TABLE Student
MODIFY Department VARCHAR(50);

Drop Column

ALTER TABLE Student
DROP COLUMN Email;

---

Step 4: UPDATE Command

UPDATE Student
SET Marks=90
WHERE StudentID=101;

UPDATE Student
SET Department='Information Technology'
WHERE StudentID=102;

UPDATE Student
SET Marks=Marks+5
WHERE Department='Computer';

---

Step 5: DELETE Command

Delete one record

DELETE FROM Student
WHERE StudentID=104;

Delete Civil Department students

DELETE FROM Student
WHERE Department='Civil';

---

Step 6: DROP Command

DROP TABLE TemporaryStudent;

---

Step 7: SELECT Commands

Display all records

SELECT * FROM Student;

Display Name and Marks

SELECT Name, Marks
FROM Student;

Display students scoring above 80

SELECT *
FROM Student
WHERE Marks>80;

Display Computer Department students

SELECT *
FROM Student
WHERE Department='Computer';

Sort records

SELECT *
FROM Student
ORDER BY Marks DESC;

Display distinct departments

SELECT DISTINCT Department
FROM Student;

---

Expected Output

Student Table

StudentID	Name	Department	Semester	Marks
101	Rahul	Computer	5	85
102	Neha	IT	4	78
103	Amit	Mechanical	6	91
104	Priya	Computer	3	67
105	Kiran	Civil	5	80

After UPDATE

StudentID	Name	Marks
101	Rahul	90

SELECT Marks > 80

StudentID	Name	Marks
101	Rahul	90
103	Amit	91

Distinct Departments

• Computer
• IT
• Mechanical
• Civil

---

Result

The SQL commands ALTER, UPDATE, DELETE, DROP, and SELECT were executed successfully. The experiment demonstrated how to modify table structures, update existing records, delete unnecessary data, remove database objects, and retrieve information efficiently from a relational database.

---

Precautions

1. Always create a backup before executing UPDATE, DELETE, or DROP commands.
2. Use the WHERE clause carefully to avoid modifying or deleting unintended records.
3. Verify table and column names before executing SQL statements.
4. Ensure that the correct database is selected before running queries.
5. Execute DROP commands only when the table is no longer required.
6. Review query results before committing changes in production environments.
7. Maintain proper data integrity while updating records.

---

Applications

• Student Information Management System
• Employee Payroll System
• Library Management System
• Hospital Management System
• Banking Applications
• Inventory and Stock Management
• E-Commerce Systems

---

Viva Questions

1. What is SQL?
2. What is the purpose of the ALTER command?
3. Differentiate between ALTER and UPDATE.
4. Why is the WHERE clause important in UPDATE and DELETE?
5. What happens if DELETE is executed without a WHERE clause?
6. What is the difference between DELETE and DROP?
7. Differentiate between DELETE and TRUNCATE.
8. What is the purpose of the SELECT command?
9. Explain the use of the DISTINCT keyword.
10. What is the purpose of the ORDER BY clause?
11. What is DDL?
12. What is DML?
13. What is DQL?
14. Can a dropped table be recovered without a backup?
15. Which SQL command is used to retrieve specific columns from a table?