Introduction to Database Management (CS-504 (C)) Notes

How unit 4 is examined

This unit covers the DML commands that read and change table rows (SELECT, INSERT, UPDATE, DELETE, MERGE); the only asked question is the syntax of Select, Update and Delete.

Data Manipulation Language

<span style="display:inline-block;padding:.16em .6em;border:1.5px solid currentColor;border-radius:999px;font-size:.68em;font-weight:700;letter-spacing:.06em;text-transform:uppercase;opacity:.75">Low weight</span>

Definition. <mark>Data Manipulation Language (DML) is the set of SQL commands used to retrieve and change the data stored inside tables, without changing the table structure.</mark>

Key points.

SELECT reads rows, INSERT adds rows, UPDATE changes rows, DELETE removes rows and MERGE combines insert and update.
The WHERE clause filters rows; without it UPDATE and DELETE act on every row.
DML changes can be undone with ROLLBACK until COMMIT is issued.

SELECT col1, col2 FROM table WHERE condition ORDER BY col [ASC|DESC];
UPDATE table SET col1 = value1, col2 = value2 WHERE condition;
DELETE FROM table WHERE condition;

Example. SELECT * FROM student WHERE marks > 50 ORDER BY marks DESC; UPDATE student SET marks = 70 WHERE roll = 2; DELETE FROM student WHERE roll = 3;

Asked: [7 marks] (Dec 2020) Explain the following commands with syntax: i) Select ii) Update iii) Delete

Insert Statement

Definition. INSERT adds a new row to a table.

Key points.

Syntax: INSERT INTO table (c1, c2) VALUES (v1, v2);
Values must match the listed columns in order and data type.
If the column list is omitted, values must be given for all columns in table order.
Unlisted columns get their default value or NULL.

Multiple Inserts

Definition. Multiple insert adds several rows using one statement.

Key points.

Syntax: INSERT INTO table (c1, c2) VALUES (v1, v2), (v3, v4);
Rows can also be copied from another table with INSERT INTO t1 SELECT * FROM t2;
It is faster than many single inserts, and one bad row can fail the whole statement.

Delete Statement

Definition. DELETE removes rows from a table.

Key points.

Syntax: DELETE FROM table; removes all rows but keeps the table structure.
DELETE is a DML command, so it can be rolled back and fires triggers.
DROP removes the table itself, and TRUNCATE empties it quickly without rollback in most systems.

Delete with conditions

Definition. DELETE with a WHERE clause removes only the rows that satisfy the condition.

Key points.

Syntax: DELETE FROM table WHERE condition;
Example: DELETE FROM student WHERE marks < 40; removes only failing students.
Conditions can use AND, OR, IN, BETWEEN, LIKE and subqueries.
Forgetting WHERE deletes every row.

Update statement

Definition. UPDATE modifies the existing values in the columns of a table.

Key points.

Syntax: UPDATE table SET col = value; changes that column in every row.
Several columns can be set at once, separated by commas.
The new value can be an expression, such as SET marks = marks + 5.
UPDATE can be rolled back before COMMIT.

Update with Conditions

Definition. UPDATE with WHERE changes only the rows that satisfy the condition.

Key points.

Syntax: UPDATE table SET col = value WHERE condition;
Example: UPDATE student SET marks = marks + 5 WHERE roll = 3; changes one row.
A subquery can be used in the condition or in the new value.
Without WHERE every row is changed.

Merge Statement

Definition. MERGE (upsert) updates a row if it already exists and inserts it if it does not, in one statement.

Key points.

It compares a target table with a source using the ON condition.
WHEN MATCHED runs UPDATE, and WHEN NOT MATCHED runs INSERT.
It is supported in Oracle and SQL Server; MySQL uses INSERT ... ON DUPLICATE KEY UPDATE.

MERGE INTO target t USING source s ON (t.id = s.id)
WHEN MATCHED THEN UPDATE SET t.marks = s.marks
WHEN NOT MATCHED THEN INSERT (id, marks) VALUES (s.id, s.marks);

Last-minute revision

DML = SELECT, INSERT, UPDATE, DELETE, MERGE; it changes data, not structure.
SELECT syntax: SELECT cols FROM t WHERE cond ORDER BY col;
UPDATE syntax: UPDATE t SET c = v WHERE cond;
DELETE syntax: DELETE FROM t WHERE cond;
INSERT syntax: INSERT INTO t (cols) VALUES (vals);
Multi-row insert lists several value tuples separated by commas.
No WHERE means all rows are updated or deleted.
DML can be rolled back before COMMIT.
MERGE = update if matched, insert if not matched.

Memory hooks

SIUD-M: Select, Insert, Update, Delete, Merge.
"No WHERE, all gone": the missing WHERE clause is the classic disaster.
Upsert = Update + Insert.

Coverage checklist

Data Manipulation Language: Dec 2020 Select, Update, Delete syntax question.
Insert Statement: no past questions.
Multiple Inserts: no past questions.
Delete Statement: no past questions.
Delete with conditions: no past questions.
Update statement: no past questions.
Update with Conditions: no past questions.
Merge Statement: no past questions.

Account

Introduction to Database Management Systems (CS-504 (C)) - Unit 4 Short Notes

How unit 4 is examined

Data Manipulation Language

Insert Statement

Multiple Inserts

Delete Statement

Delete with conditions

Update statement

Update with Conditions

Merge Statement

Last-minute revision

Memory hooks

Coverage checklist