Data Manipulation Language (DML) in SQL: INSERT, DELETE, UPDATE

 Data Manipulation Language (DML) in SQL: INSERT, DELETE, UPDATE

DML commands are used to manipulate data within existing database tables. They allow you to add, remove, and modify data in a structured manner. Here's an overview of the key DML commands:

1. INSERT

The INSERT statement is used to add new rows of data into a table.

Syntax:

sql
INSERT INTO table_name (column1, column2, ...)
VALUES (value1, value2, ...);

Example:

sql
INSERT INTO students (student_id, first_name, last_name, birthdate, grade_level)
VALUES (1, 'John', 'Doe', '2006-05-15', 10);

This command inserts a new student into the students table with the specified values for student_id, first_name, last_name, birthdate, and grade_level.

Inserting Multiple Rows:

sql
INSERT INTO students (student_id, first_name, last_name, birthdate, grade_level)
VALUES
(2, 'Jane', 'Smith', '2007-08-22', 9),
(3, 'Emily', 'Johnson', '2006-12-01', 10);

This command inserts two new rows into the students table at once.

2. DELETE

The DELETE statement is used to remove one or more rows from a table based on a condition.

Syntax:

sql
DELETE FROM table_name
WHERE condition;

Example:

sql
DELETE FROM students
WHERE student_id = 1;

This command deletes the row in the students table where student_id is 1.

Deleting All Rows: If you want to delete all rows from a table but keep the structure intact, you can use:

sql
DELETE FROM students;

This command removes all data from the students table.

Note: Be careful when using DELETE without a WHERE clause, as it will remove all rows from the table.

3. UPDATE

The UPDATE statement is used to modify existing data in one or more rows in a table.

Syntax:

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

Example:

sql
UPDATE students
SET grade_level = 11
WHERE student_id = 2;

This command updates the grade_level of the student with student_id 2 to 11.

Updating Multiple Columns:

sql
UPDATE students
SET first_name = 'Jane', last_name = 'Doe'
WHERE student_id = 2;

This command updates both the first_name and last_name of the student with student_id 2.

Updating Multiple Rows:

sql
UPDATE students
SET grade_level = 12
WHERE grade_level = 11;

This command updates all students currently in grade 11 to grade 12.

Summary

• INSERT adds new data to a table.
• DELETE removes data from a table.
• UPDATE modifies existing data in a table.