Database:Exercise - Getting started with SQLite
- 1 Notes
- 2 Exercise for the Getting started with SQLite chapter
- 2.1 Notes on typography
- 2.2 Task 1 - Install SQLite3
- 2.3 Task 2 -Create a database called my_books
- 2.4 Task 3 - Create a table in your newly created database
- 2.5 Task 4 - Log out from the database and examine stuff
- 2.6 Task 5 - Enter your database in various ways
- 2.7 Task 6 - Investigate the my_books database
- 2.8 Task 7 - Create an SQL text file with books
- 2.9 Task 8 - Look at the books table contents
- 2.10 Task 9 - Feel free to examine various commands
- 2.11 Task 10 - Create a backup SQL text file using .dump
- 2.12 Summary of what you should know after this session
- 3 Check your progress
- 4 Links
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
- Some of the exercises use such names, other use names with no file-ending at all, e.g.
- 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.
- If you make a mistake and do e.g.
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
Example: List the files in the current directory using the command
.tables command inside the SQLite3 interactive shell:
Task 1 - Install SQLite3
If you are running Ubuntu this is as simple as issuing the following command:
$ sudo apt-get install sqlite3
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 cygwin.com 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
$ 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
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); sqlite>
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:
(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:
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
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:
Find out what databases are loaded:
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:
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:
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:
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; BEGIN TRANSACTION; 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'); COMMIT;
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 sqlite>
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
.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):
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
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:
Log out and examine the contents of the new file, which was created by the commands above,
$ 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
- 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
- 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
- How to investigate what tables exist in the currently selected database using
- How to investigate how a table was created using
- 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
- How to list the SQLite3 specific commands using
- How to redirect output from the interactive session
- 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
SELECTend 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!
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?)
The output should come in the following order:
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
Do the same again, but include the name of the columns in the output and use a tabular format.
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.
Explain the following SQLite3 specific commands: