SQL INSERT, UPDATE and DELETE

Overview

Adding records with INSERT INTO, changing them with UPDATE and SET, removing them with DELETE FROM, worked commands on small tables, and the danger of a missing WHERE clause.

What you need to know

Answer each one before you reveal it.

Questions from this subtopic

Each one with the model answer underneath.

1. 1

   What does an INSERT INTO command do in SQL?

   Model answer

   The INSERT INTO command adds a new record to a table. The VALUES part lists the data for the new record.

2. 2

   A table Pupil has fields PupilID, Name and Form. What does INSERT INTO Pupil (PupilID, Name, Form) VALUES (27, 'Amir Khan', '10B') do?

   Model answer

   The command adds a new record for pupil 27, Amir Khan, in form 10B. The table gains one extra row.

3. 3

   Why must the values in an INSERT INTO command be in the same order as the listed fields?

   Model answer

   Each value is matched to a field by its position. A value in the wrong position would be stored in the wrong field.

4. 4

   Why is it good practice to list the field names in an INSERT INTO command?

   Model answer

   Listed field names make it clear which value goes into which field. The command still works if the table's fields are later rearranged.

5. 5

   What happens if an INSERT INTO command gives a new record the same primary key value as an existing record?

   Model answer

   The database rejects the new record. A primary key value must be unique within its table.

6. 6

   What does an UPDATE command do in SQL?

   Model answer

   The UPDATE command changes the data in records that are already in a table. The command does not add or remove any records.

Other ways to work this

The same content, four other ways.

What is inside Levo

Six ways to work the same material.