SQL Data Manipulation Language (DML) (CBSE Class 12 Computer Science - 083)

Once a table has been created, the next step is to store, retrieve, modify, and remove data from it. SQL provides the Data Manipulation Language (DML) commands for performing these operations. These commands work on the records (rows) stored in database tables.

Learning Objectives

• Understand Data Manipulation Language (DML).
• Insert records into a table.
• Retrieve records using SELECT.
• Modify existing records using UPDATE.
• Delete records using DELETE.

---

What is Data Manipulation Language (DML)?

Data Manipulation Language (DML) consists of SQL commands used to insert, retrieve, update, and delete records stored in database tables.

---

Sample Table Used in This Chapter

Student Table

StudentID	Name	Class	Marks
101	Riya	XII-A	95
102	Aman	XII-B	91
103	Priya	XII-A	88

---

INSERT Command

The INSERT command is used to add new records into a table.

Syntax (Insert All Values)

INSERT INTO table_name
VALUES
(value1, value2, value3, ...);

Example

INSERT INTO Student
VALUES
(104,'Rahul','XII-B',90);

---

INSERT with Column Names

Column names may also be specified while inserting data.

Syntax

INSERT INTO table_name
(column1, column2, column3)
VALUES
(value1, value2, value3);

Example

INSERT INTO Student
(StudentID, Name, Marks)
VALUES
(105,'Neha',94);

Columns not mentioned receive their default value or NULL (if allowed).

---

SELECT Command

The SELECT command retrieves data from one or more tables. It is one of the most frequently used SQL commands.

---

Select All Columns

Syntax

SELECT * FROM table_name;

Example

SELECT * FROM Student;

Output

StudentID	Name	Class	Marks
101	Riya	XII-A	95
102	Aman	XII-B	91
103	Priya	XII-A	88

---

Select Specific Columns

Syntax

SELECT column1, column2
FROM table_name;

Example

SELECT Name, Marks
FROM Student;

Output

Name	Marks
Riya	95
Aman	91
Priya	88

---

UPDATE Command

The UPDATE command modifies existing records in a table.

Syntax

UPDATE table_name
SET column_name = value
WHERE condition;

Example

UPDATE Student
SET Marks = 98
WHERE StudentID = 101;

Updated Record

StudentID	Name	Marks
101	Riya	98

---

Updating Multiple Columns

Syntax

UPDATE table_name
SET column1 = value1,
    column2 = value2
WHERE condition;

Example

UPDATE Student
SET Class='XII-C',
    Marks=96
WHERE StudentID=102;

---

Updating All Records

If the WHERE clause is omitted, all records in the table are updated.

Example

UPDATE Student
SET Marks=100;

---

DELETE Command

The DELETE command removes records from a table.

Syntax

DELETE FROM table_name
WHERE condition;

Example

DELETE FROM Student
WHERE StudentID=103;

---

Deleting All Records

If the WHERE clause is omitted, every record is deleted from the table, but the table structure remains unchanged.

Example

DELETE FROM Student;

---

Complete Example

INSERT INTO Student
VALUES
(104,'Rahul','XII-A',92);

SELECT * FROM Student;

UPDATE Student
SET Marks=96
WHERE StudentID=104;

DELETE FROM Student
WHERE StudentID=104;

---

Execution Flow of DML Commands

INSERT
   │
   ▼
SELECT
   │
   ▼
UPDATE
   │
   ▼
DELETE

---

Difference Between INSERT, UPDATE and DELETE

Command	Purpose
INSERT	Adds new records.
SELECT	Retrieves records.
UPDATE	Modifies existing records.
DELETE	Removes records.

---

Common Errors

• Forgetting quotation marks around text values.
• Inserting values in the wrong column order.
• Using UPDATE without a WHERE clause.
• Using DELETE without a WHERE clause.
• Trying to insert duplicate values into a PRIMARY KEY column.

---

Exam Tips

---

Frequently Asked Questions (FAQs)

1. Which SQL command is used to add records to a table?

The INSERT command is used to add new records.

2. Which SQL command retrieves data from a table?

The SELECT command retrieves records from one or more tables.

3. Which SQL command modifies existing records?

The UPDATE command modifies existing records.

4. Which SQL command removes records from a table?

The DELETE command removes records from a table.

5. What happens if the WHERE clause is omitted in an UPDATE or DELETE command?

All records in the table are updated or deleted, respectively.

---

Summary

• DML commands are used to manipulate data stored in database tables.
• INSERT adds new records.
• SELECT retrieves records from a table.
• UPDATE modifies existing records.
• DELETE removes records from a table.
• The WHERE clause should be used with UPDATE and DELETE to avoid affecting all records.
• DML commands operate on data, while DDL commands operate on the database structure.