INSERT, UPDATE and DELETE in SQL: Changing the Data of a Table (Class 12 Computer Science)

Understand INSERT, UPDATE and DELETE Commands

INSERT INTO EMPLOYEE VALUES ('E07', 'Neha Jain', 'D4', 47000, 'Pune', '2024-01-08');
INSERT INTO EMPLOYEE (EmpID, Name, DeptID) VALUES ('E08', 'Tara Nair', 'D2');

UPDATE EMPLOYEE SET Salary = Salary + 5000 WHERE DeptID = 'D1';
UPDATE EMPLOYEE SET Salary = 40000, City = 'Pune' WHERE EmpID = 'E05';

DELETE FROM EMPLOYEE WHERE City = 'Mumbai';
DELETE FROM EMPLOYEE;

INSERT, UPDATE and DELETE Commands: key facts

INSERT

• •INSERT INTO table VALUES (all values in order)
• •INSERT INTO table (cols) VALUES (values)
• •Text and dates in single quotes
• •Missing columns become NULL

A duplicate primary key is rejected.

UPDATE

• •UPDATE table SET col = value [, col = value] WHERE condition
• •Without WHERE all rows change
• •NULL plus a number stays NULL

Add WHERE to limit the change.

DELETE

• •DELETE FROM table WHERE condition
• •Without WHERE all rows are deleted
• •The table structure remains

DROP TABLE removes the table.

Effect on the table

INSERT	cardinality plus 1 per row
DELETE	cardinality minus the rows removed
UPDATE	no change to degree or cardinality

Degree changes only through ALTER TABLE.

How to answer INSERT, UPDATE and DELETE Commands questions

1. 1Write the current table or list the affected columns before starting.
2. 2For INSERT, add the row and note the new count; check the primary key is not repeated.
3. 3For UPDATE, find the rows that satisfy WHERE (NULL never satisfies a comparison) and compute the new values.
4. 4For DELETE, remove all rows that satisfy WHERE; without WHERE remove all rows.
5. 5Re-count the rows or recompute the sum only at the end, from the final table.
6. 6Write the answer in the form the query asks for (count, sum, list of rows).

INSERT, UPDATE and DELETE Commands in real life

The idea, met in an everyday situation. Answer the question in your own words, then compare with the model answer. The one-line hook is what to remember.

Tricks to remember INSERT, UPDATE and DELETE Commands

Short memory aids. Say each one aloud once, then use it on a question the same day.

• Insert adds, Update changes, Delete removes

  The three verbs in order of the CRUD cycle.

  Use it when: Choosing the command.

• WHERE is the seat belt

  Without WHERE, UPDATE and DELETE hit every row.

  Use it when: Missing condition errors.

• Quotes for text and dates

  Numbers bare, text and dates in single quotes.

  Use it when: INSERT syntax.

• NULL plus anything is NULL

  Arithmetic on NULL stays NULL.

  Use it when: UPDATE with NULL salary.

INSERT, UPDATE and DELETE Commands: solved examples

Ordered from board level to HOTS. Try each one before you open the solution.

INSERT INTO EMPLOYEE VALUES ('E07', 'Neha Jain', 'D4', 47000, 'Pune', '2024-01-08');
UPDATE EMPLOYEE SET Salary = Salary + 2000 WHERE Salary < 45000;
DELETE FROM EMPLOYEE WHERE DOJ < '2018-01-01';

Where students lose marks in INSERT, UPDATE and DELETE Commands

The slips examiners see most often, and what to write instead.

• ✗Mistake: UPDATE or DELETE without WHERE

  Every row is changed or removed.

  Wrong working

  DELETE FROM EMPLOYEE; (to remove one employee)

  Right working

  DELETE FROM EMPLOYEE WHERE EmpID = 'E06';

  ✓Do this: Ask: which rows? and write the condition.

  Usually costs: 1 mark

• ✗Mistake: Text without quotes

  Text and dates must be in single quotes.

  Wrong working

  INSERT INTO DEPT VALUES (D5, Admin, Pune);

  Right working

  INSERT INTO DEPT VALUES ('D5', 'Admin', 'Pune');

  ✓Do this: Quote every text and date value.

  Usually costs: 1 mark

• ✗Mistake: Expecting NULL to be updated by arithmetic

  NULL + 2000 is NULL; the condition Salary < 45000 does not match NULL either.

  Wrong working

  E05 salary becomes 2000

  Right working

  E05 salary stays NULL

  ✓Do this: Treat NULL as unknown in every calculation.

  Usually costs: 1 mark

Practice questions on INSERT, UPDATE and DELETE Commands

Write each answer on paper first. Open the hint only if you are stuck, and the answer only when you have finished.

1. Q1 · 1 mark · MCQBoard level

   Which statement removes all the rows of a table but keeps the table?
   (a) DROP TABLE T; (b) DELETE FROM T; (c) ALTER TABLE T DROP; (d) REMOVE T;

   Hint

   Rows, not structure.

   Answer

   (b) DELETE FROM T;

2. Q2 · 1 mark · Very short answerBoard level

   What is the effect of UPDATE EMPLOYEE SET City = 'Pune'; (no WHERE clause)?

   Hint

   Which rows are changed?

   Answer

   Every row of the table gets City = Pune.

3. Q3 · 2 marks · Short answerHarder board level

   Write a statement to set the salary of employee E05 to 36000 and DeptID to D4.

   Hint

   Two columns in one SET.

   Answer

   UPDATE EMPLOYEE SET Salary = 36000, DeptID = 'D4' WHERE EmpID = 'E05';

4. Q4 · 1 mark · Assertion-reasonHarder board level

   Assertion (A): INSERT INTO EMPLOYEE VALUES ('E08', 'Tara') fails for the six-column EMPLOYEE table.
   Reason (R): When no column list is given, a value must be supplied for every column.
   (a) Both A and R are true and R is the correct explanation of A. (b) Both A and R are true but R is not the correct explanation of A. (c) A is true but R is false. (d) A is false but R is true.

   Hint

   Count the values given.

   Answer

   (a) Both are true and R explains A: two values for six columns is rejected.

5. Q5 · 3 marks · Short answerHOTS / competency

   The original EMPLOYEE table receives, in order: UPDATE EMPLOYEE SET Salary = 50000 WHERE Salary IS NULL; DELETE FROM EMPLOYEE WHERE Salary = 45000; INSERT of E07 (Neha Jain, D4, 47000, Pune, 2024-01-08). Find COUNT(*), SUM(Salary), MAX(Salary) and MIN(Salary) of the final table.

   Hint

   Work out the surviving rows first.

   Answer

   After the UPDATE E05 has 50000. The DELETE removes E04 and E06. Rows: E01 52000, E02 38000, E03 61000, E05 50000, E07 47000. COUNT(*) = 5; SUM = 248000; MAX = 61000; MIN = 38000.

Test your recall

Answer in your head or on paper first, then open the card. Pulling an answer out of memory makes it stick far better than re-reading.

1. 1.Which command adds a new row?Show answer

   INSERT INTO.

2. 2.Which command changes existing values?Show answer

   UPDATE ... SET.

3. 3.Which command removes rows?Show answer

   DELETE FROM.

4. 4.What happens to omitted columns in an INSERT with a column list?Show answer

   They become NULL (or their default).

5. 5.What does DELETE FROM T; do?Show answer

   Deletes all rows, keeps the table.

6. 6.How must a date be written?Show answer

   In quotes as YYYY-MM-DD.

INSERT, UPDATE and DELETE Commands: frequently asked questions