Database:Exercise - Adding rows with new data - SQL INSERT

From Juneday education
Jump to: navigation, search

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:

syntax error near unexpected token `$'{\r''

...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 dos2unix (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 illegal byte sequence 'LF', then you can try to run the script like this:

$ LC_ALL=C ./randomcar.sh 1000

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 fixed width font and commands issued in a terminal in your computer shell are prefixed with a $.

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

Example: List the files in the current directory using the command ls:

$ ls

Issue the .tables command inside the SQLite3 interactive shell:

sqlite> .tables

Task 1 - Create the my_cars database

To keep things organised, create a directory for this exercise session:

$ mkdir Ex06
$ cd Ex06

Start SQLite3 and give it the argument my_cars so that it creates a database of that name (once you create a table):

Expand using link to the right to see the syntax.

$ sqlite3 my_cars

Create the table cars:

sqlite> CREATE TABLE cars (make text, color text, LicenseNumber text primary key);

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!):

INSERT INTO tablename(column_name[,column_name]*)
 VALUES(expression[,expression]*);

In the syntax above, tablename is the name of the table (e.g. cars) and column_name is the name of a column (e.g. make) and expression is an expression of the same type as the corresponding column_name (e.g. text surrounded by quotes). Further, brackets are meant to symbolise an optional part and the * means "0 or more occurrences". An example for a table could look like this:

INSERT INTO students(name,grade) VALUES('Lisa Andersson',5);

The table students from the example above has a column name name of type text and a column name grade of type INTEGER. 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.

Expand using link to the right to see the syntax.

sqlite> INSERT INTO cars (make, color, LicenseNumber) VALUES('Volvo', 'Blue', 'XXX 666');

Task 3 - Insert five more cars using only VALUES

If you know the type and order of the column names from the .schema 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:

INSERT INTO tablename VALUES(expression[,expression]*);

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 .schema of the table. The values (expressions) must also match the order of the columns in the .schema.

If the .schema of the example table students from above were listing Name TEXT, Grade INTEGER in that order, and only those columns, the example INSERT statement above could be simplified to

INSERT INTO students VALUES('Lisa Andersson',5);

The DBMS would then be able to infer that 'Lisa Andersson' corresponds to the first column Name, and that 5 corresponds to the second (and last) column Grade. Note that this shorter syntax only works if you provide values for all columns in the correct order according to the .schema 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.

Expand using link to the right to see the syntax.

sqlite> .schema cars

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.

Expand using link to the right to see the syntax and an example of one car being inserted with the shorter syntax.

INSERT INTO cars VALUES('Simca', 'Blue', 'AAA 000');

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.

Expand using link to the right to see what happens.

Error: UNIQUE constraint failed: cars.LicenseNumber

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.

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 .schema)?

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.

Expand using link to the right to see the syntax.

sqlite> INSERT INTO cars(Make,Color) VALUES('Volvo','Pink');

Now list all cars with the color 'Pink’:

Expand using link to the right to see the syntax.

sqlite> SELECT * FROM cars WHERE Color='Pink';

The result from the SELECT should be: Volvo|Pink|

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

Tell the interactive shell that you want it to print NULL for nullvalues:

sqlite> .nullvalue NULL
sqlite> select * from cars where LicenseNumber is null;
Volvo|Pink|NULL

Null is the value for unset columns and non-existing values. To check for NULL you can do as listed above, use the is null 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:

sqlite> select * from cars where LicenseNumber is null;
Volvo|Pink|NULL

OK, we have a Volvo there without LicenseNumber. Let’s delete it. Do you remember the DELETE syntax?

Use WHERE LicenseNumber IS NULL as the WHERE clause for your DELETE statement.

Expand using link to the right to see the syntax.

sqlite> DELETE FROM cars WHERE LicenseNumber IS NULL;

We will later in the course see how we can create a table and disallow NULL 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 randomcar.sh and save it in your (cygwin or bash) home directory:

#!/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

You may also download the script.

Now that you have the file randomcar.sh 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:

$ ls -l randomcar.sh

On my (Rikard's) system (Ubuntu), it gives the following result:

-rw-r--r-- 1 rikard None 591 Dec 14 11:38 randomcar.sh

You don’t need to understand all of that, but let’s look at the first piece of information in that line:

-rw-r--r--

The important part here is the rw- 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: rwx (the x means the right to eXecute the file).

Let’s fix that:

$ chmod u+x randomcar.sh
$ ls -l randomcar.sh
-rwxr--r-- 1 rikard None 591 Dec 14 11:38 randomcar.sh

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:

$ ./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');

Since this script is not in a directory on our PATH, we need to prefix it with the path to it, in this case ./ if we are in the same directory as the script.

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

Let’s save the output from the script in a file called car_inserts.sql:

$ ./randomcar.sh 1000 > car_inserts.sql

Now, we can move that file to our Ex06 directory:

$ mv car_inserts.sql Ex06/

Another way would have been to create the file there:

$ ./randomcar.sh 1000 > Ex06/car_inserts.sql

(provided that the script was in your home directory, and you had cd:d to your home).

Now, since you have a functioning my_cars database in this directory Ex06, you can tell sqlite3 to execute the statements in the file car_inserts.sql for the my_cars database using the redirect trick. Do it!

Expand using link to the right to see the syntax.

$ sqlite3 my_cars < car_inserts.sql

Log into the interactive shell for my_cars and verify the number of rows in the cars table using SELECT COUNT(*).

Expand using link to the right to see the syntax.

$ sqlite3 my_cars
sqlite> SELECT COUNT(*) FROM cars;

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:

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');
  • 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:

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?

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?

Suggested answers/solutions to check-your-progress

SQL INSERT Check Answers

Links

Files

Where to go next

« PreviousBook TOCNext »