SQL INSERT Check Answers

From Juneday education
Jump to: navigation, search

Answers to INSERT - check your progress questions

Check 1

There are several versions of the syntax for the INSERT statement and they all work slightly differently. Your task is to think about how and when they work, write down your answer and only then compare with the suggested answer/solution. We suggest that you answer all questions in the "check your progress" before looking at any answers.

The first syntax is the most classic one:

sqlite> CREATE TABLE customer(customer_id INTEGER PRIMARY KEY, name TEXT, email TEXT);

sqlite> INSERT INTO customer(name, email) VALUES('Rikard', 'rikard@gu.se');

In this syntax, you list the column names and then provide the values.

  • Check 1 a) What happens if you provide the values as follows instead: INSERT INTO customer(name, email) VALUES('rikard@gu.se', 'Rikard');

Answer: The email string will be inserted into the name column and the name string will be inserted into the email column. You need to provide the values in the same order as you list the columns.

  • Check 1 b) What happens to the customer_id field if you use the above syntax? Hint: We talked about it when we introduced primary keys - INTEGER primary keys are special in SQLite3

Answer: Since customer_id is both INTEGER and PRIMARY KEY, in SQLite3, the value for the customer_id will be calculated by the database engine. It will be one larger than the last number, in order to guarantee a unique number - primary keys must be unique. This is rather convenient, since the id is more of a technicality than something which exists "in the real world".

A slightly shorter syntax for accomplishing an INSERT is:

sqlite> INSERT INTO customer VALUES(2, 'Henrik', 'henrik@gu.se');
sqlite> SELECT * FROM customer;
customer_id|name|email
1|Rikard|rikard@gu.se
2|Henrik|henrik@gu.se
  • Check 1 c) The column names weren't listed in the shorter syntax version. What must we think about and do in order for this shorter version to work?

Answer: In order to skip the column names in the INSERT statement, you need to provide values for every column in the table, and do so in the same column order as was used when the table was created.

Finally, here's a third syntax version. What do you think it does? Try it out and use SELECT to figure out the result:

sqlite> INSERT INTO customer(name, email)
         VALUES('Alan', 'alan@gu.se'),
               ('Lennart', 'lennart@gu.se'),
               ('Juho', 'juho@gu.se'),
               ('Aida', 'aida@gu.se'),
               ('Dina', 'dina@gu.se');
  • Check 1 d) Explain in your own words how the above syntax works. Can you combine it with the shorter syntax without the column names?

Answer: It inserts five rows, and each row will have its customer_id calulated for you by the SQLite3 engine. It is possible to skip the column names, but then, as said above, you need to provide all column values and in the same order as the table was created with.