Database:Exercise - Getting started with SQLite

From Juneday education
Jump to: navigation, search


Some notes on database names:

  • The database is stored in a binary (non-text) file
  • This file can be named anything - the name doesn't change the contents of the file
  • It might be wise to name the database files with a file-ending of .db, e.g. books.db .
  • Some of the exercises use such names, other use names with no file-ending at all, e.g. books
  • It is especially useful to use file-endings if you have two files relating to the same data
    • books.sql - contains ASCII text with SQL statements for books, tables etc
    • books.db - contains the database in a format that only sqlite can understand (not readable for humans)
  • You may choose any name for your database files, but be consistent!
    • If you make a mistake and do e.g. $ sqlite3 books (when the database file is actually stored in books.db, then sqlite3 won't warn you, but instead create an empty database and use that.

Exercise for the Getting started with SQLite chapter

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 - Install SQLite3


If you are running Ubuntu this is as simple as issuing the following command: $ sudo apt-get install sqlite3

Mac OS

If you are running Mac OS, please refer to the instructions here:

You need to install it as root (using sudo).

If you can't install it following the instructions above, use a search engine to find out how to install sqlite3 on Mac OS and follow the instructions you find. If that doesn't work, ask a class mate or colleague or the teachers.


Read this first install additional software in cygwin.

To install the SQLite package, click on the "Install Cygwin now" link on the web page or run your installer again if you already installed Cygwin (the file is usually called setup(X86).exe or something similar). Then, run setup and answer all of the questions. You'll find the package "sqlite" listed in the "All" category. You can also search for sqlite in the search box of the installer. Restart your Cygwin terminal and you should be set.

If you have problems finding the database category, make sure the installer isn't showing only "Pending" packages.

Confirming it works

Type $ sqlite3 -version and you should see something similar to 3.11.0 2016-02-15 17:29:24 3d862f207e3adc00f78066799ac5a8c282430a5f as the output. If you see Command not found, you either didn't succeed to install it or it is not in your PATH.

Task 2 -Create a database called my_books

Syntax difference ahead! Click on the image to see MariaDB version.

Hint: Always do your exercises in a fresh directory. First create a directory where you will have the database, e.g.:

$ mkdir databases
$ cd databases

Or, even better:

$ mkdir -p databases/getting-started
$ cd databases/getting-started

To create the database, issue the following command:

$ sqlite3 my_books

This invokes sqlite3 with the argument my_books. Since there is no such database file in the current directory, sqlite3 will create one for you and open the interactive shell with this database selected. Note that the database will not be saved until you create at least one table in it, which we'll do next!

Task 3 - Create a table in your newly created database

Inside the interactive sqlite3 shell, issue the following command:

sqlite> CREATE TABLE books(author TEXT, title TEXT, isbn TEXT PRIMARY KEY, publisher TEXT);

Hint: the command (more correctly: SQL statement) should be entered on one line. You can, however, break it up on several lines. If you hit [Enter] in an incomplete SQL statement, you will get a secondary prompt awaiting the rest of the statement up until the semicolon:

sqlite> CREATE TABLE books
   ...> (author TEXT, title TEXT, isbn TEXT PRIMARY KEY, publisher TEXT);

The secondary prompt of sqlite3 looks like this: ...> and you should think about it as meaning: "Yes, please continue!". Until you end your statement with a semicolon, hitting the enter key will produce a secondary prompt inside the SQLite3 interactive shell.

Task 4 - Log out from the database and examine stuff

In order to log out from the database (and SQLite3), simply press CTRL-D. Another way is to issue the following:

sqlite> .exit

(Note the leading dot - commands inside the SQLite3 interactive shell, which start with a dot are SQLite3-specific commands and not part of the SQL language)

Now, list the files in the current directory:

$ ls

Note the newly created file my_books which was created by SQLite3. Examine the file:

$ file my_books

What filetype is the file command reporting?

Expand using link to the right to see the answer.

You should see something similar to SQLite 3.x database as the result of querying file about the my_books file.

Task 5 - Enter your database in various ways

In this exercise you will learn how to enter the interactive shell and load a named database in various ways.

First, start SQLite3 without any arguments:

$ sqlite3

Find out what databases are loaded:

sqlite> .databases

Only the "main" database is loaded. It contains no tables. Load the my_books database:

sqlite> .open my_books

The main database is now listed as pointing to the file my_books if you issue .databases again. Do it!

Exit the database again (see Q4 for how you exit, if you already have forgotten).

Now, start SQLite3 with the argument of my_books:

$ sqlite3 my_books

Look what database is loaded again:

sqlite> .databases

As you see, again the main database is linked to the my_books database file. Now you know two ways of activating a specific database for the interactive shell. This is something we will assume that you know from now on.

Task 6 - Investigate the my_books database

Log in to your my_books database (if you are not still logged in). Let’s find out what tables are in the database. If you do not know what a table is yet, it is the thing you created with the CREATE TABLE statement in Q2. A table is a container in a database for the data to be stored. Data is stored in a table in rows with columns. Re-visit chapters Introduction and SQL SELECT for a recap of how data is stored inside a database.

To see what tables exist in the current database, issue the following command:

sqlite> .tables

Only books should be reported back. It’s the only table you have created!

Now, let’s examine the books table. In order to insert data, we need to know how the books table is organised (if we have forgotten the create table statement from Q3):

sqlite> .schema books

This command will echo back the create table statement, revealing how the books table was created (what the names and types are for the columns in the table). We will assume that you know the .schema command from now on.

Remember, commands inside the SQLite3 interactive shell, which start with a dot are SQLite3-specific commands and not part of the SQL language. So far, you have learned a few of them: .open, .load, .tables, .schema and .exit.

MariaDB difference warning!

The syntax in this question differs a lot from the syntax used in MariaDB. The syntax in this question works for SQLite3. If you want to see how you can investigate tables etc in MariaDB, please see This page

Task 7 - Create an SQL text file with books

Hint: If you are unfamiliar with redirecting streams in bash, you can read about it in the Bash redirection chapter of our introduction to bash book.

Exit the database again and in the shell, open your favorite text editor, and create a file called my_books.sql in the current directory, and let the text content be exactly this before you save the file:

PRAGMA foreign_keys=OFF;
CREATE TABLE IF NOT EXISTS books(author TEXT, title TEXT, isbn TEXT PRIMARY KEY, publisher TEXT);
INSERT INTO "books" VALUES('John Smith','Life','0-0-0-0-0-1','Bonnier');
INSERT INTO "books" VALUES('James Woody','Love','0-0-0-0-0-2','Bonnier');
INSERT INTO "books" VALUES('Joan Carmen','Guns','0-0-0-0-0-3','Bonnier');
INSERT INTO "books" VALUES('Johnanna Boyd','Code','0-0-0-0-0-4','Bonnier');
INSERT INTO "books" VALUES('Eva Peron','Cars','0-0-0-0-0-5','Books R us');

This text file contains a lot of insert statements (OK, not that many) that will populate the books table. Of course, you could issue them one at the time in the interactive shell, but that gets old pretty fast if there are many insert statements. Instead of typing them manually, let’s tell SQLite3 to run the SQL statements from the file, and do so on the my_books database:

$ sqlite3 my_books < my_books.sql

This command invokes SQLite3 with the argument my_books and uses the my_books.sql text file as standard input to read from. It will read and execute the statements in the my_books.sql file and execute them on the my_books database, where the books table exists.

Note the part of the CREATE TABLE statement that says IF NOT EXISTS. This actually makes the CREATE TABLE statement conditional. If the table already exists, it does nothing. If not, it creates it.

Task 8 - Look at the books table contents

Now, we have inserted some data in the books table of the my_books database. But did it work? Was it really that easy to insert a whole list of rows of books to the table? Let’s log in to the my_books database and select all data, to find out if it worked:

$ sqlite3 my_books
sqlite> SELECT * FROM books;

You should now see all the rows of books that were inserted via the my_books.sql file. This is what you should see, including the SQL statement:

sqlite> SELECT * FROM books;
John Smith|Life|0-0-0-0-0-1|Bonnier
James Woody|Love|0-0-0-0-0-2|Bonnier
Joan Carmen|Guns|0-0-0-0-0-3|Bonnier
Johanna Boyd|Code|0-0-0-0-0-4|Bonnier
Eva Peron|Cars|0-0-0-0-0-5|Books R us

Task 9 - Feel free to examine various commands

We will introduce a lot more SQL statements in the chapters and video lectures to come. For now, you can practise the SQL SELECT statement. Feel free to re-visit SELECT exercise chapter and try out the same exercises with the books table that you have created today.

You may have noticed that SQL statements end with a semicolon. But the commands that start with a dot do not. The commands .tables .databases and .schema are not SQL statements but rather commands specific to SQLite3. To list the commands available, issue yet another such command (when you are inside the interactive SQLite3 shell):

sqlite> .help

We will talk about some of them in lectures to come. But note that we will assume that you now are familiar with the ones used in this chapter. If you feel uncertain, do the exercises again and convince yourself that you know them by heart.

Task 10 - Create a backup SQL text file using .dump

Enter the shell with the my_books database selected again, if you are not still inside it. Tell the interactive shell to use a file for all output it generates (it will turn off echoing to the screen and put all output in the file instead):

sqlite> .output my_books_backup.sql

Next, tell the interactive shell to dump the whole database to that file:

sqlite> .dump

Log out and examine the contents of the new file, which was created by the commands above, my_books_backup.sql:

$ cat my_books_backup.sql

Note: if you want to redirect output to a file, you do as above. If you later, in the same session, want to send output to the screen again, you do the following:

sqlite> .output stdout

That will stop the writing to the file, and turn on echoing the output to the screen again.

If you want to redirect output only once, for one single command, to a file, you may use the SQLite3 command .once <filename> for example:

sqlite> .once my_books_backup.sql
sqlite> .dump

It will redirect the output once, to the specified file. In this case, only the first following command will be redirected to the file, in other words, only the .dump command. Then you’re back to seeing the results of commands in the interactive shell again!

Summary of what you should know after this session

Use this checklist to see if you are ready to move on to the next chapter:

  • How to create a database, giving the name as an argument to sqlite3
  • How to create a simple table inside a database
  • How to log out of the interactive sqlite3 shell
    • Using Ctrl-D
    • Using .exit
  • How to enter the interactive shell
    • Without argument to sqlite3 - will not select any database as the main database
    • Choosing a named database as the main database using .open
    • With an argument of the name of the database - will select it as the main database right away
  • How to investigate what database is selected as the main database using .databases
  • How to investigate what tables exist in the currently selected database using .tables
  • How to investigate how a table was created using .schema
  • How to create a text file with SQL commands to optionally create a table and insert a lot of rows into it
  • How to run sqlite3 from the command line and have it read all the SQL statements from such a file, using redirection of standard in
  • How to select all rows and columns from a table using SELECT
  • How to list the SQLite3 specific commands using .help
  • How to redirect output from the interactive session
    • Using .output
    • Using .once
  • How to create a backup (as a text file with create table and all necessary insert statements) using .dump (in combination with redirecting the output to a file)
  • That SQL statements like SELECT end with a semicolon
  • That SQLite3-specific commands start with a dot

Check your progress

Note: the coding style of this example is inconsistent with other examples. We are reviewing this book in order to make names more consistent. For instance, LicenseNumber is a horrible choice, where license_number would have been much better. Note, however, that SQLite is case insensitive, when it comes to names. Note also, that there is little consensus on coding style for SQL, but some standards have been proposed by various sources.

This is the same check your progress questions as in the SELECT chapter. In that chapter, perhaps you didn't know all the questions yet!

Check 01

Download the SQL file cars.sql. Create the database cars.db from that file.

  • What is the name of the only table in the database?
  • What is the schema of that table?
  • What is the count of rows? (how many rows exist in the table?)

Check 02

SELECT the 10 cars with the greatest license number (you have to order by license number and also use LIMIT).

The output should come in the following order:

licenseNumber make color

Expected output:

ZZX 117|Suzuki|Blue
ZYT 514|Ford|Blue
ZYO 227|Mazda|Silver
ZXL 136|Simca|Yellow
ZWX 235|Simca|Yellow
ZWQ 666|Suzuki|Red
ZWF 975|Chevrolet|Brown
ZWE 201|Mazda|Blue
ZWA 270|Volvo|Black
ZVS 665|Suzuki|Brown

Check 03

Do the same again, but include the name of the columns in the output and use a tabular format.

Expected output:

LicenseNumber  make        color     
-------------  ----------  ----------
ZZX 117        Suzuki      Blue      
ZYT 514        Ford        Blue      
ZYO 227        Mazda       Silver    
ZXL 136        Simca       Yellow    
ZWX 235        Simca       Yellow    
ZWQ 666        Suzuki      Red       
ZWF 975        Chevrolet   Brown     
ZWE 201        Mazda       Blue      
ZWA 270        Volvo       Black     
ZVS 665        Suzuki      Brown 

Check 04 Challenge

Really hard: SELECT the same 10 cars as the above, but the output of these exact 10 cars should be ordered by LicenseNumber ascending. Expected output:

LicenseNumber  make        color     
-------------  ----------  ----------
ZVS 665        Suzuki      Brown     
ZWA 270        Volvo       Black     
ZWE 201        Mazda       Blue      
ZWF 975        Chevrolet   Brown     
ZWQ 666        Suzuki      Red       
ZWX 235        Simca       Yellow    
ZXL 136        Simca       Yellow    
ZYO 227        Mazda       Silver    
ZYT 514        Ford        Blue      
ZZX 117        Suzuki      Blue

HINT: Use a SELECT from a sub-select of the ten cars, and order the result of the sub-select on LicenseNumber.

Check 05

Explain the following SQLite3 specific commands:

  • .once
  • .dump
  • .mode
  • .headers
  • .schema
  • .tables

Check your answers

SQL SELECT Check Answers


Where to go next

« PreviousBook TOCNext »