DML — Inserting, Updating and Deleting Data

Once a table exists, DML commands are used to put data into it, change it and remove it.

1. Adding data — INSERT INTO: This adds a new tuple (row) into a table. You name the attributes and then list the values in the same order. Text and date values are written inside single quotes; numbers are written without quotes.

INSERT INTO Student (RollNo, Name, Class, Marks)
VALUES (1, 'Aarti Behera', 'XI-A', 78);

INSERT INTO Student (RollNo, Name, Class, Marks)
VALUES (2, 'Rohan Das', 'XI-A', 85);

After these two statements, the Student table holds two records.

2. Changing existing data — UPDATE: This changes values in tuples that already exist. It almost always has a WHERE clause to say which rows to change; without a WHERE clause it would change every row, which is a common and serious mistake.

UPDATE Student
SET Marks = 80
WHERE RollNo = 1;

This changes the marks of only the student whose RollNo is 1.

3. Removing data — DELETE: This removes whole tuples (rows) from a table. Like UPDATE, it should normally have a WHERE clause to say which rows to delete.

DELETE FROM Student
WHERE RollNo = 2;

This removes only the record of the student whose RollNo is 2. …