Be able to use SQL to insert data into a relational database using the…

The idea

Inserting data adds a new record (row) to an existing table in a relational database. You write an INSERT INTO statement naming the table, list the columns you are filling in brackets, then give the matching values in brackets after VALUES. The order of the values must match the order of the columns, and text values go in single quotes while numbers do not. For example, to add a pupil to a Pupil table you might write INSERT INTO Pupil (PupilID, Forename, YearGroup) VALUES (1042, 'Aisha', 10). Each value must be the right data type for its column, and a primary key value must be unique. The statement changes stored data, so it is a data-modification command rather than a query that returns rows.

What earns the marks

• Names the table being targeted after INSERT INTO.
• Lists the columns to be populated in brackets, in a defined order.
• Supplies a VALUES clause whose entries match the column order one-to-one.
• Encloses text and date values in single quotes but leaves numeric values unquoted.
• Recognises that the inserted row must satisfy data types and primary-key uniqueness.
• Identifies INSERT INTO as a command that modifies stored data rather than retrieving it.

Common mistakes and how to put them right

• Writing VALUES before the column list.

  Put it right: The correct order is table name, then columns, then VALUES.

• Putting numbers in quotes or leaving text unquoted.

  Put it right: Text needs single quotes, numbers do not.

• Mismatching the number of values and columns.

  Put it right: Each listed column needs exactly one matching value.

• Reusing an existing primary key value.

  Put it right: The key must be unique for each new record.

Exam tips

• Count the columns and count the values; they must be equal before you commit to an answer.
• Check quote marks carefully: only text and date values take single quotes.
• Read the table's column names and data types before choosing an option, so the value types fit.