Database:Assignment 1 - The basics

From Juneday education
Jump to: navigation, search

Assignment 1 - The basics

This chapter makes most sense if you are using this book as the course literature in a database course. If you are using it for self-studies, you obviously must ignore the passages mentioning handing stuff in to the "teachers".

Goal of the assignment

The goal of this assignment is to verify that you are confident with the concepts from the first 6 chapters. Another goal is also to verify that you can follow precise and detailed instructions. Therefore, file names of the files you create and hand in to the teacher must be exactly as specified in this assignment.

Concepts tested in this assigmnet are:

SQL:

  • SELECT
  • UPDATE
  • DELETE
  • INSERT

SQLite3 and Bash(Cygwin):

  • Introduction to databases
  • Introduction to SQLite
  • Executing SQL statements from files

Files to be handed in to the teacher

You shall create a zip file with the exact name answer-handin01.zip . This is how you create it in bash:

  • Inside your working directory, create a directory named exactly answer-handin01 for your files to hand in
  • The directory must contain the following files (created throughout the assignment):
    • name.txt (shall contain your complete name - you create this file manually)
    • silver-fords.txt
    • select-commands.txt
    • pink-chevies.txt
    • update-commands.txt
    • bad-cars.txt
    • delete-commands.txt

Make sure you have the zip command installed in your bash environment. In order to zip the directory with all files (of course, this is something you do when you are done with all steps in the assignment!) in bash, you do this from the directory containing answer-handin01:

$ zip -r answer-handin01.zip answer-handin01
  adding: answer-handin01/ (stored 0%)
  adding: answer-handin01/bad-cars.txt (stored 0%)
  adding: answer-handin01/delete-commands.txt (stored 0%)
  adding: answer-handin01/name.txt (stored 0%)
  adding: answer-handin01/pink-chevies.txt (stored 0%)
  adding: answer-handin01/select-commands.txt (stored 0%)
  adding: answer-handin01/silver-fords.txt (stored 0%)
  adding: answer-handin01/update-commands.txt (stored 0%)

Note that you must stand in the same directory where you created answer-handin01 in order for the above to work.

The Database

Download the file cars.sql. It contains the SQL statements for setting up the database for this assignment. The database will contain one single table (created and populated from the file you downloaded) with a lot of rows of cars.

The cars table is defined like this:

CREATE TABLE cars (Make text,Color text,
                   LicenseNumber text primary key);

Getting started with the assignment

Create a directory named for instance handin01. This is where you will work and where you will create the answer-handin01 directory for the files you will create. Change directory to handin01. Download the cars.sql file and put it in the directory.

Expand using link to the right to see how to create the directory and enter it.

$ mkdir handin01
$ cd handin01
$ mkdir answer-handin01
$ wget https://raw.githubusercontent.com/progund/databases-introduction/master/assignment-01/cars.sql
--2018-01-30 07:44:36--  https://raw.githubusercontent.com/progund/databases-introduction/master/assignment-01/cars.sql
Resolving raw.githubusercontent.com (raw.githubusercontent.com)... 151.101.84.133
Connecting to raw.githubusercontent.com (raw.githubusercontent.com)|151.101.84.133|:443... connected.
HTTP request sent, awaiting response... 200 OK
Length: 54300 (53K) [text/plain]
Saving to: ‘cars.sql’

cars.sql            100%[===================>]  53,03K  --.-KB/s    in 0,03s   

2018-01-30 07:44:36 (1,51 MB/s) - ‘cars.sql’ saved [54300/54300]
$ tree
.
├── answer-handin01
└── cars.sql

1 directory, 1 file

Now, create a database called many_cars from the file cars.sql. You may use redirection or a pipe in order to accomplish this.

Next, enter the interactive shell for sqlite3 for the database many_cars .

Task 1 - SELECT

From the interactive shell with the database many_cars as the active database, you shall create a text file called exactly silver-fords.txt containing all the cars from the table cars whose color is silver and whose make is Ford. The result should be ordered by the license number. The resulting file should have the following structure:

ASO 410|Ford|Silver
AZJ 204|Ford|Silver

etc...

Expand using link to the right to see the hints for this task.

The sqlite3 directive .once lets you redirect the output of the next SQL statement to a file. The syntax for once is:

.once silver-fords.txt

The next SQL statement you issue will go to the file silver-fords.txt .

Think about the order of the columns for the output. We demand that the order of the data comes from columns in the folowing order:

LicenseNumber, Make, Color.

Try without the .once directive to see that the order of the output columns is correct, and that the result is ordered on LicenseNumber before you use the .once directive to produce the file.

The file should be included in the zip file you shall hand in.

Create a text file with the exact name select-commands.txt where you should paste the sqlite3 directives and SQL statements you used to produce the file silver-fords.txt .

We will use this file to verify that you created the silver-fords.txt file from those commands.

From this task you should produce two files - select-commands.txt and silver-fords.txt to be included in the zip file for the assignment. We recommend that you put these files in the directory you started off by creating, answer-handin01 already.

Task 2 - UPDATE

In this task, you shall, still from the interactive shell, change the color of all red Chevrolet cars whose license number ends with "5". The new color for those cars shall be "Pink". After the change of color for those cars, you should create a text file called exactly pink-chevies.txt containing all the pink chevies and it should have the same structure as the file in Task 1. Also, you must create a text file called update-commands.txt containing all the SQL statements (and sqlite directives) in the same order as you issued them. These two files should be included in the zip file you shall hand in, so put the files in the directory for the zip file when you are sure you got them right.

Expand using link to the right to see the hints for this task.

  • Read up on the LIKE operator: (w3schools - SQL LIKE).
  • Remember that three criteria must be met for the update statement which sets the color to Pink:
    • The car must be a Chevrolet
    • The car must be red
    • The car must have a license number ending with a "5"
    • The above means you must have a WHERE clause using AND to combine the criteria

It is recommended that you test your WHERE clause using it in a SELECT before you use it in an UPDATE. When the SELECT only returns rows of cars which fulfill the criteria, it is probably correct.

Task 3 - INSERT

The Swedish Transport Agency (Transportstyrelsen) has banned certain combinations of letters for Swedish license plates. Download the file banned.txt which contains all banned letter combinations, one per line.

Here's a more recent list of banned letter combinations: [1]

It would be useful to have these combinations in a table in the many_cars database. In the interactive shell for this database, and create the table banned with only one column combo with type TEXT.

Expand using link to the right to see the hints for this part of this task.

sqlite> CREATE TABLE banned(combo TEXT primary key);

Log out of the interactive sqlite3 shell. It's time to create a text file with SQL INSERT statements using the banned.txt file you downloaded. But before we do that, let's learn how to iterate over all the lines of text in the file. In the bash shell, issue the following command:

$ for bad in $(cat banned.txt); do echo "$bad"; done

The expression $(cat banned.txt) will be expanded to the same result as if we simply ran cat banned.txt, so what you will see as the result of the for-loop is every line being printed out. The for-loop in bash has the following syntax:

$ for VARIABLE in VALUES; do COMMAND; done

In the command line you tried, the variable was called bad and the VALUES was the lines from the text file (which each is a letter combination to be inserted to the table soon), and the COMMAND was echo "$bad" (print the value of the variable bad).

So let's build an INSERT statement! We want every iteration in the loop to echo the following:

INSERT INTO banned(combo) VALUES('THE_COMBINATION_FROM_A_LINE_IN_THE_FILE');

The THE_COMBINATION_FROM_A_LINE_IN_THE_FILE should correspond to the current line of the file in the loop, which is the value of the varialbe bad.

Let's start small, and see if we can print the SQL part of the statement (and leave out the value from the bad variable for now):

$ for bad in $(cat banned.txt);do echo "INSERT INTO banned(combo) VALUES('');";done

Try it. It looks correct! So let's put the value of the variable bad variable between the single quotes:

$ for bad in $(cat banned.txt);do echo "INSERT INTO banned(combo) VALUES('$bad');";done

If you get confused by all the quotes and single quotes, don't worry! The string we are echoing is enclosed in double quotes. It is broken up to three parts:

  1. "INSERT INTO banned(combo) VALUES('
  2. $bad /the current value from one line of the file)
  3. ');"

Anyway, let's run the command again but save the result in a file rather than echoing it to the terminal. Remember that you can repeat the last command by using the arrow-up key, to scroll through the bash command history. Do you remember how to redirect output from commands in bash to a file? If you do remember, run the command again but redirect the output to a file called bads.sql.

Expand using link to the right to see the hints for this part of this task.

$ for bad in $(cat banned.txt);do echo "INSERT INTO banned(combo) VALUES('$bad');";done > bads.sql

Use ls to verify that bads.sql was created. Print it to the terminal, using cat. If it looks like a bunch of correct INSERT statements, it's time to ask sqlite3 to execute the statements for the database many_cars.

Expand using link to the right to see the hints for this part of this task.

$ sqlite3 many_cars < bads.sql

Start the interactive sqlite3 again and give many_cars as the argument. Verify that the banned table has been populated with all the values from banned.txt.

One way to verify that the table and file at least contain the same amount of lines is to check the number of lines in the file in bash:

$ wc -l banned.txt 
89 banned.txt

And then, in the SQLite shell, check the number of rows in the banned table.

sqlite> SELECT COUNT(*) FROM banned;
89

Task 4 - DELETE

Mission: Using the banned table, remove all rows from cars whose LicenseNumber starts with any of the letter combinations found in the banned table.

This task is purely academic. Let's pretend that this is a database of cars for testing purposes only. We want to adhere to the Transport agency's rules for license numbers for our test cars, to make sure we don't upset anyone with offensive license numbers.

Wouldn't it be nice if we could somehow use the contents of the banned table, in order to locate rows in the cars table whose license numbers contained any of the banned letter combinations?

In fact, we can. But it is rather complicated. For this reason, we'll guide you through this task. The assignment isn't only a test of your knowledge, it is also a great learning opportunity and we hope that you will learn from it and get inspired to explore SQL further.

Let's start with learning about the function substr(X,Y,Z).

Since we are only interested in the first three characters of the LicenseNumber (the letter part, and not the space and the numbers), we'll need a way to extract only the first three characters. This is because we want to find out which license numbers contain a banned combination.

Here, the substr functions comes in handy. Let's look at how it works. The arguments to the function are (STRING,START_POS,NUM_CHARS) where STRING is the string to extract from, START_POS is the starting position (strings start at position 1) and NUM_CHARS is the number of characters to extract:

sqlite> SELECT substr("abcdefghij",1,4);
abcd
sqlite> SELECT substr("abcdefghij",4,3);
def
sqlite> SELECT substr("abcdefghij",7,4);
ghij

That wasn't so hard, was it?

Let's start by extracting the first three characters of the license numbers. It's annoying to see all the one thousand license numbers of the cars table, so we'll use LIMIT 5 to limit ourselves to the five first license numbers.

sqlite> SELECT substr(LicenseNumber,1,3) FROM cars LIMIT 5;

Good! We have a strategy for extracting only the letter part of the license numbers!

What if there was a way to use this in a comparison againt the values of the banned table. Of course, there is a way. There are actually many ways to do this, but we are not familiar with those techniques yet, so we'll practice a little first.

The IN operator (aka IN condition)

In SQL, you can investigate if an element is a member of a list, using the condition IN This is an example of using IN:

sqlite> SELECT DISTINCT Color FROM cars WHERE Color IN ('Dark Blue', 'Magenta', 'Cyan', 'Purple', 'Lavender', 'Gold', 'Green', 'Red');

Only "Green" and "Red" are returned, since they are the only colors from the list which also exist as colors in the cars table.

The statement checks the column Color against a list of named colors. The syntax is in pseudo code:

WHERE ColumnName IN (Value[,Value]*);

That means that the condition is a boolean statement claiming that the value for some column is a member of a list of possible values. A clever thing is that we can create the list using an SQL statement!

sqlite> SELECT LicenseNumber FROM cars WHERE substr(LicenseNumber,1,3) IN (SELECT combo FROM banned);

The above will list all LicenseNumber values from cars whose first three characters exist in the list we get from SELECTing all combo values from banned.

Create a textfile named exactly bad-cars.txt with the result from the above statement. You should create the file from inside the interactive sqlite shell.

Your second task now is to use the syntax above in order to delete all the cars whose license number contains the banned combinations. Save the SQL statements in a file named exactly delete-commands.txt .

Both files should be included in the zip file to be submitted. So we recommend that you put the files in the directory to be zipped.

Bonus - check for banned license numbers in bash

The idea here, is to show you that you can check for banned license numbers also using bash. Log out of the interactive shell. Now, lets use the file banned.txt and the command grep on the cars.sql file, to see if the same cars turn up as when we used SQL.

How do we do that? We'll iterate over all combinations from the banned.txt and look for them using grep on the cars.sql file.

First, let's practice grep by looking for "OND" in cars.sql:

$ grep OND cars.sql

You should see the INSERT statement for the car with license number "OND 743".

Now, let's use the good old for-statement again, in order to grep for each of the banned combinations:

$ for bad in $(cat banned.txt);do grep $bad cars.sql;done

Check list

Before submitting the zip file, verify that your directory answer-handin01 contains the following files with the exact same names:

  • name.txt (shall contain your complete name - you create this file manually)
  • silver-fords.txt
  • select-commands.txt
  • pink-chevies.txt
  • update-commands.txt
  • bad-cars.txt
  • delete-commands.txt

Zip the directory and the resulting zip file should be named exactly answer-handin01.zip .

The reason we are so particular about the exact names of the files, is that we use a script to correct the submitted files. The script will only work if you follow our naming conventions. But a more important reason for requiring exact names, is to prepare you for very specific requirements. It is a skill which often is required when working with computers. If you have difficulties spelling names etc, you can use copy and paste for the file names. And double check with a friend before submitting.

The file name.txt will be used by our script in order to extract your name when correcting, and create a directory with your name. So it is very important that the file is called exactly name.txt and that it contains your name and nothing else.

What have we learned?

  • SQL SELECT
    • order of columns in the result set
    • WHERE
    • WHERE clause with predicates connected by AND
    • Using a subselect in combination with the IN operator
  • SQL UPDATE
  • SQL DELETE
  • Creating a database from an SQL text file
  • Adding a table and data to an existing database using an SQL text file
  • SUBSTR function for creating a substring in SQLite3
  • Creating a file with output from SQLite3 using .once
  • Creating INSERT statements from a text file with data, using a bash for loop
  • Some funny rules about what combinations of letters are not allowed by the Transport Agency