View source for Database:Exercise - Adding rows with new data - SQL INSERT

=Work in progress=
Remove this section when the page is production-ready.
=Exercise for the Adding rows with new data - SQL INSERT=
==Trouble-shooting==
If you get this error from running a script:
<source lang="Bash">
syntax error near unexpected token `$'{\r''
</source>
...then you probably have modified the script in a Windows editor so that it got CRLF line endings (the Windows style line endings).

You can convert the file (any text file, actually) from windows format to Unix format using <code>dos2unix</code> (install it in Cygwin if you are running Cygwin). If you are using a text editor, you can always change the line endings in the file:

Atom: Look for the letters CRLF at the bottom of the editor, click on it and change it to LF only.

Notepad++:  Edit -> EOL Conversion -> Unix Format

If you are running Mac OS and the above doesn't help and you get something like <code>illegal byte sequence 'LF'</code>, then you can try to run the script like this:
<source lang="Bash">
$ LC_ALL=C ./randomcar.sh 1000
</source>
What you did was to change the locale temporarily for the command line to use C (which is a general locale).

==Notes on typography==
Commands are written in a <code>fixed width</code> font and commands issued in a terminal in your computer shell are prefixed with a <code>$</code>.

Commands issued inside the SQLite3 interactive shell are prefixed with <code>sqlite></code>

Example: List the files in the current directory using the command <code>ls</code>:
<source lang="Bash">
$ ls
</source>
Issue the <code>.tables</code> command inside the SQLite3 interactive shell:
<source lang="SQL">
sqlite> .tables
</source>
==Task 1 - Create the my_cars database==
To keep things organised, create a directory for this exercise session:
<source lang="Bash">
$ mkdir Ex06
$ cd Ex06
</source>
Start SQLite3 and give it the argument my_cars so that it creates a database of that name (once you create a table):
<div class="mw-collapsible mw-collapsed">
Expand using link to the right to see the syntax.
<div class="mw-collapsible-content">
<source lang="SQL">
$ sqlite3 my_cars
</source>
</div></div>
Create the table cars:
<source lang="SQL">
sqlite> CREATE TABLE cars (make text, color text, LicenseNumber text primary key);
</source>

==Task 2 - Insert five cars==
While you are in the my_cars database in the interactive shell, insert five cars. You can choose between the makes Honda, Saab, Dodge, Suzuki, Chevrolet, Volvo, Simca, Mazda and Ford; the colors White, Yellow, Green, Red, Black, Blue, Brown and Silver. The LicenseNumber should be on the format “XXX 000” (three upper case letter, a space and three digits).

The syntax for the INSERT statement is (this is a formal syntax showing you the form of the statement!):
<source lang="SQL">
INSERT INTO tablename(column_name[,column_name]*)
 VALUES(expression[,expression]*);
</source>
In the syntax above, <code>tablename</code> is the name of the table (e.g. <code>cars</code>) and <code>column_name</code> is the name of a column (e.g. <code>make</code>) and expression is an expression of the same type as the corresponding <code>column_name</code> (e.g. text surrounded by quotes). Further, brackets are meant to symbolise an optional part and the <code>*</code> means "0 or more occurrences". An example for a table could look like this:

<source lang="SQL">
INSERT INTO students(name,grade) VALUES('Lisa Andersson',5);
</source>

The table <code>students</code> from the example above has a column name <code>name</code> of type <code>text</code> and a column name <code>grade</code> of type <code>INTEGER</code>. You shouldn't use this table here, it was just an example to help you understand the formal syntax given above!

Now you know how to insert the five cars with values you choose yourself. Do it if you haven't already.
<div class="mw-collapsible mw-collapsed">
Expand using link to the right to see the syntax.
<div class="mw-collapsible-content">
<source lang="SQL">
sqlite> INSERT INTO cars (make, color, LicenseNumber) VALUES('Volvo', 'Blue', 'XXX 666');
</source>
</div></div>

==Task 3 - Insert five more cars using only VALUES==
If you know the type and order of the column names from the <code>.schema</code> of a table, and you want to insert values for every column in the table, you may optionally leave out the list of column names. The syntax then becomes:
<source lang="SQL">
INSERT INTO tablename VALUES(expression[,expression]*);
</source>
In this case, the list of expressions should be separated by comma as usual, but it must match the corresponding type and order of the <code>.schema</code> of the table. The values (expressions) must also match the order of the columns in the <code>.schema</code>.

If the <code>.schema</code> of the example table <code>students</code> from above were listing <code>Name TEXT, Grade INTEGER</code> in that order, and only those columns, the example <code>INSERT</code> statement above could be simplified to
<source lang="SQL">
INSERT INTO students VALUES('Lisa Andersson',5);
</source>
The DBMS would then be able to infer that <code>'Lisa Andersson'</code> corresponds to the first column <code>Name</code>, and that <code>5</code> corresponds to the second (and last) column <code>Grade</code>. Note that this shorter syntax only works if you provide values for all columns in the correct order according to the <code>.schema</code> for the table.

Back to the Task 3 and the cars table! While inside the interactive shell for the table my_cars, check the schema of the table cars.
<div class="mw-collapsible mw-collapsed">
Expand using link to the right to see the syntax.
<div class="mw-collapsible-content">
<source lang="SQL">
sqlite> .schema cars
</source>
</div></div>
Note the order of the columns and their type. (For the cars table, all columns have the same type, text, so the order is the important part here!)

Now insert five new cars using this shorter syntax.
<div class="mw-collapsible mw-collapsed">
Expand using link to the right to see the syntax and an example of one car being inserted with the shorter syntax.
<div class="mw-collapsible-content">
<source lang="SQL">
INSERT INTO cars VALUES('Simca', 'Blue', 'AAA 000');
</source>
</div></div>

==Task 4 - Try to insert the exact same car twice==
Try to insert the exact same car twice. What happens?

Hint: LicensNumber is the "primary key", and as such it is guaranteed to be unique for all rows.
<div class="mw-collapsible mw-collapsed">
Expand using link to the right to see what happens.
<div class="mw-collapsible-content">
<code>Error: UNIQUE constraint failed: cars.LicenseNumber</code>

From this we learn that SQLite3 helps us not to insert two rows with the same value for a column which is "primary key". The column which is primary key, automatically gets a so called constraint ensuring uniqueness.
</div></div>
==5. Leave out a column when inserting a car==
What happens if we list only two columns and the corresponding values when inserting a car (since the cars table has three columns as you can see from the <code>.schema</code>)?

There’s no point in guessing. Let’s do it and see what happens!

Insert a car listing only Make and Color (i.e. leave out the LicenseNumber column) using the longer syntax. Use the Color ‘Pink’ so that we easily can investigate the row later.
<div class="mw-collapsible mw-collapsed">
Expand using link to the right to see the syntax.
<div class="mw-collapsible-content">
<source lang="SQL">
sqlite> INSERT INTO cars(Make,Color) VALUES('Volvo','Pink');
</source>
</div></div>
Now list all cars with the color 'Pink’:
<div class="mw-collapsible mw-collapsed">
Expand using link to the right to see the syntax.
<div class="mw-collapsible-content">
<source lang="SQL">
sqlite> SELECT * FROM cars WHERE Color='Pink';
</source>
</div></div>
The result from the SELECT should be: <code>Volvo|Pink|</code>

That looked odd, didn’t it? What’s the invisible thing after the last <code>|</code> ?

Tell the interactive shell that you want it to print NULL for nullvalues:
<source lang="SQL">
sqlite> .nullvalue NULL
sqlite> select * from cars where LicenseNumber is null;
Volvo|Pink|NULL
</source>
Null is the value for unset columns and non-existing values. To check for NULL you can do as listed above, use the <code>is null</code> predicate.
==Task 6 - Delete the row (or rows) with NULL values==
Let’s keep our database clean and remove the rows with NULL values in the LicenseNumber column. First let’s figure out if we have any:
<source lang="SQL">
sqlite> select * from cars where LicenseNumber is null;
Volvo|Pink|NULL
</source>
OK, we have a Volvo there without LicenseNumber. Let’s delete it. Do you remember the DELETE syntax?

Use <source lang="SQL" inline>WHERE LicenseNumber IS NULL</source> as the <source lang="SQL" inline>WHERE</source> clause for your <source lang="SQL" inline>DELETE</source> statement.
<div class="mw-collapsible mw-collapsed">
Expand using link to the right to see the syntax.
<div class="mw-collapsible-content">
<source lang="SQL">
sqlite> DELETE FROM cars WHERE LicenseNumber IS NULL;
</source>
</div></div>
We will later in the course see how we can create a table and disallow <code>NULL</code> values for a column.

==Task 7 - Insert a thousand cars==
Hey, waitaminute... A thousand cars? Let’s say that for testing purposes, we need a table with at least a thousand cars in it. We have no plans of spending our week typing down a thousand random cars that we make up ourselves. That would seem boring and stupid. Let’s ask the computer to do it for us!

Enter (or paste) the following into your favorite editor and create a script called <code>randomcar.sh</code> and save it in your (cygwin or bash) home directory:
<source lang="Bash">
#!/bin/bash

regnum(){
    alpha=$(cat /dev/urandom |tr -dc 'A-Z'| fold -w 3|head -1)
    num=$(cat /dev/urandom |tr -dc '0-9'| fold -w 3|head -1)
    echo -n "$alpha $num"
}
brand(){
    brands=( Volvo Mazda Honda Suzuki Ford Chevrolet Saab Simca Dodge )
    index=$((RANDOM%9))
    echo -n ${brands[$index]}
}
color(){
    colors=( Blue Red Green Yellow Black White Brown Silver )
    index=$((RANDOM%8))
    echo -n ${colors[$index]}
}
export LC_ALL=C # for all the mac-os out there...
NUMBER=$1
for i in $(seq 1 $NUMBER)
do
    r=$(regnum)
    m=$(brand)
    c=$(color)
    echo "INSERT INTO \"cars\" VALUES('"$m"','"$c"','"$r"');"
done
</source>
You may also [https://raw.githubusercontent.com/progund/databases-introduction/master/SQL-INSERT/exercises/randomcar.sh download] the script.

Now that you have the file <code>randomcar.sh</code> in your home directory, we must change the mode of the file so that we have the permissions to run it as a script. Issue the following command:
<source lang="Bash">
$ ls -l randomcar.sh
</source>
On my (Rikard's) system (Ubuntu), it gives the following result:
<source lang="Bash">
-rw-r--r-- 1 rikard None 591 Dec 14 11:38 randomcar.sh
</source>
You don’t need to understand all of that, but let’s look at the first piece of information in that line:
<source lang="Bash">
-rw-r--r--
</source>
The important part here is the <code>rw-</code> in the beginning. It shows the users (owners) permissions on this file and you can read it out as ReadWrite. So you have the right as the user to read from and write to this file. We would also like to have the right to eXecute the file as a command. So we’d really like that part to read: <code>rwx</code> (the x means the right to eXecute the file).

Let’s fix that:
<source lang="Bash">
$ chmod u+x randomcar.sh
$ ls -l randomcar.sh
-rwxr--r-- 1 rikard None 591 Dec 14 11:38 randomcar.sh
</source>
That looks better. The script works like this: You tell it how many cars you want to insert, and it creates as many insert statements with randomly generated cars.

Example: 
<source lang="Bash">
$ ./randomcar.sh 10
INSERT INTO "cars" VALUES('Honda','Black','FQA 386');
INSERT INTO "cars" VALUES('Simca','Red','EDM 723');
INSERT INTO "cars" VALUES('Mazda','Red','XFX 164');
INSERT INTO "cars" VALUES('Saab','White','IAT 971');
INSERT INTO "cars" VALUES('Mazda','Blue','WSM 578');
INSERT INTO "cars" VALUES('Simca','Green','ELT 371');
INSERT INTO "cars" VALUES('Simca','Yellow','MTK 717');
INSERT INTO "cars" VALUES('Dodge','Green','EOA 514');
INSERT INTO "cars" VALUES('Honda','Green','GVW 640');
INSERT INTO "cars" VALUES('Dodge','Green','GDF 935');
</source>
Since this script is not in a directory on our PATH, we need to prefix it with the path to it, in this case <code>./</code> if we are in the same directory as the script.

Ok, now we know how to generate insert statements. How to we get <code>sqlite3</code> to execute them?

Let’s save the output from the script in a file called car_inserts.sql:
<source lang="Bash">
$ ./randomcar.sh 1000 > car_inserts.sql
</source>
Now, we can move that file to our Ex06 directory:
<source lang="Bash">
$ mv car_inserts.sql Ex06/
</source>
Another way would have been to create the file there:
<source lang="Bash">
$ ./randomcar.sh 1000 > Ex06/car_inserts.sql
</source>
(provided that the script was in your home directory, and you had cd:d to your home).

Now, since you have a functioning <code>my_cars</code> database in this directory <code>Ex06</code>, you can tell <code>sqlite3</code> to execute the statements in the file car_inserts.sql for the <code>my_cars</code> database using the redirect trick. Do it!
<div class="mw-collapsible mw-collapsed">
Expand using link to the right to see the syntax.
<div class="mw-collapsible-content">
<source lang="Bash">
$ sqlite3 my_cars < car_inserts.sql
</source>
</div></div>
Log into the interactive shell for my_cars and verify the number of rows in the cars table using <code>SELECT COUNT(*)</code>.
<div class="mw-collapsible mw-collapsed">
Expand using link to the right to see the syntax.
<div class="mw-collapsible-content">
<source lang="Bash">
$ sqlite3 my_cars
sqlite> SELECT COUNT(*) FROM cars;
</source>
</div></div>
Were all the 1000 new cars inserted? They should have been!

=Check your progress=
Here are some self-test questions for you. You will find a link to suggested solutions/answers further down. The idea is for you to test yourself by writing down your answers ''before you check the answer''. If you don't get it right, contact your supervisors or your teacher. Don't stop at looking at the suggested answers. This is a piece of advice also for all of our exercises. If you fear that you don't understand what you have done simply by checking the answers, then ''don't look at the answers'' before you have tried yourself. If you don't understand the answers, don't stop at that. Contact your teacher and supervisors, go to supervision sessions, ask your class mates and read the further reading links.

Anyway, here goes the questions for the self-test.
==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:
<source lang="SQL" highlight="3">
sqlite> CREATE TABLE customer(customer_id INTEGER PRIMARY KEY, name TEXT, email TEXT);

sqlite> INSERT INTO customer(name, email) VALUES('Rikard', 'rikard@gu.se');
</source>
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: <source lang="SQL" inline="yes">INSERT INTO customer(name, email) VALUES('rikard@gu.se', 'Rikard');</source>
* 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''

A slightly shorter syntax for accomplishing an INSERT is:
<source lang="SQL">
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
</source>
* 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?

Finally, here's a third syntax version. What do you think it does? Try it out and use SELECT to figure out the result:
<source lang="SQL">
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');
</source>
* 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?

==Suggested answers/solutions to check-your-progress==
[[SQL INSERT Check Answers]]

=Links=
=Files=
* [https://raw.githubusercontent.com/progund/databases-introduction/master/SQL-INSERT/exercises/randomcar.sh randomcar.sh (github)]
<div class="noprint">
==Where to go next==
{{Nav links|prev=Database:Adding rows with new data - SQL INSERT|TOC=Introduction_to_Databases#Chapters|next=Database:Combining rows of data from related tables - SQL JOIN}}
</div>