SQLite SQL Tutorials

Basic SQL

Nikolai Shokhirev

- Basic SQL
- More SQL
- SQL joins
- Triggers

Introduction

This tutorial is an updated version of my 2004 tutorial. The tutorial is applicable both to Windows and Linux. Linux user do not need such a detail explanations about running command-line programs and can easy adjust the tutorial.

Some information about cmd.exe: http://www.ss64.com/nt/cmd.html ,

I used the contents of sample tables from an excellent series of tutorials at http://www.linuxdevcenter.com/pub/ct/19 .

SQLite installation

Go to http://www.sqlite.org/download.html
Download the newest version of a command-line program for accessing and modifying SQLite databases. (for example, sqlite-3_2_7.zip. For this tutorial the version does not matter).
Create a directory (e.g. c:/sqlite3">c:\sqlite3) and unzip sqlite*.exe in that directory.

That is it!

Use of SQLite

Start the command-line interpreter (Cmd.exe):

From here on we will display this as follows:

C:\WINNT\system32>

Change a directory by typing the following command:

C:\WINNT\system32>cd c:\sqlite3

and press "Enter".

Creation / Opening Database

A database can be opened by the following command:

C:\sqlite3>sqlite3 mycds.sl3

A file extension is arbitrary. I use "sl2" and "sl3" for SQLite version 2 and 3 respectively. The program creates a new database if a file does not exists.

C:\sqlite3>sqlite3 mycds.sl3
SQLite version 3.2.7
Enter ".help" for instructions
sqlite>

SQLite commands

Note that the prompt changed to "sqlit>". It indicates that the program is ready to execute SQL commands. A command ends with a semicolon. Just pressing "Enter" allows entering SQL commands that span multiple lines (see below). There are also dot-commands. They start with a dot symbol and control the database program itself. See .help for details.

Closing Database

Type ".exit" or ".quit" to exit the program:

sqlite>.exit

Table Creation

Let us now create a table with two fields.

sqlite>create table Artists (
   --->ArtistID INTEGER PRIMARY KEY,
   --->ArtistName TEXT);

Note a continuation prompt at extra lines. Below is an example of more complex table.

sqlite>create table CDs (
   --->CDID INTEGER PRIMARY KEY,
   --->ArtistID INTEGER NOT NULL,
   --->Title TEXT NOT NULL,
   --->Date TEXT);

SQL commands are case-insensitive.

Table Population

Note that PRIMARY KEY is AUTOINCREMENT by default.

sqlite>insert into Artists (ArtistID,ArtistName) values (NULL,'Peter Gabriel');
sqlite>insert into Artists (ArtistID,ArtistName) values (NULL,'Bruce Hornsby');
sqlite>insert into Artists (ArtistID,ArtistName) values (NULL,'Lyle Lovett');
sqlite>insert into Artists (ArtistID,ArtistName) values (NULL,'Beach Boys');

sqlite>insert into CDs (CDID,ArtistID,Title,Date) values (NULL,1,'So','1984');
sqlite>insert into CDs (CDID,ArtistID,Title,Date) values (NULL,1,'Us','1992');
sqlite>insert into CDs (CDID,ArtistID,Title,Date) values (NULL,2,'The Way It Is','1986');
sqlite>insert into CDs (CDID,ArtistID,Title,Date) values (NULL,2,'Scenes from the Southside','1990');
sqlite>insert into CDs (CDID,ArtistID,Title,Date) values (NULL,1,'Security','1990');
sqlite>insert into CDs (CDID,ArtistID,Title,Date) values (NULL,3,'Joshua Judges Ruth','1992');
sqlite>insert into CDs (CDID,ArtistID,Title,Date) values (NULL,4,'Pet Sounds','1966');

SQLite is forgiving, but better to use single quotes for TEXT data (Date).

Tip 1: A line can be copied and pasted by right-clicking on CMD. Selected text from CMD can be copied by "Shift Right-click" (or " Right-click" in some versions).

Tip 2: Alternatively, you can create a text file (named e.g. ins_artists.sql) with the following contents:

-- insert 4 records into Artists (a comment line starts with "--")
insert into Artists (ArtistID,ArtistName) values (NULL,'Peter Gabriel');
insert into Artists (ArtistID,ArtistName) values (NULL,'Bruce Hornsby');
insert into Artists (ArtistID,ArtistName) values (NULL,'Lyle Lovett');
insert into Artists (ArtistID,ArtistName) values (NULL,'Beach Boys');

Save it and execute this dot-command (without the semicolon at the end):

sqlite>.read ins_artists.sql

This can be done with any set of SQL statements.

Select Statement

Let us check the results of our work. Execute the following statements.

sqlite>select * from Artists;

and

sqlite>select * from CDs;

If you want to display field's headers, execute the the following "dot" command.

sqlite>.headers ON

Results of the Queries are:

ArtisID|ArtistName
1      |Peter Gabriel
2      |Bruce Hornsby
3      |Lyle Lovett
4      |Beach Boys

and

CDID|ArtisID|Title                    |Date
1   |1      |So                       |1984
2   |1      |Us                       |1992
3   |2      |The Way It Is            |1986
4   |2      |Scenes from the Southside|1990
5   |1      |Security                 |1990
6   |3      |Joshua Judges Ruth       |1992
7   |4      |Pet Sounds               |1966

Try some other statements:

sqlite>SELECT Title AS AlbumName FROM CDs;

sqlite>SELECT Title FROM CDs WHERE Date>=1990 ORDER BY Title;

sqlite>SELECT Date FROM CDs;

sqlite>SELECT DISTINCT Date FROM CDs;

sqlite>SELECT Title FROM CDs GROUP BY ArtistID;

Selecting from two tables

The following SQL statement

sqlite>SELECT t1.ArtistName,CDs.Title FROM Artists t1, CDs WHERE t1.ArtistID=CDs.ArtistID

gives

ArtistName   |Title
Peter Gabriel|So
Peter Gabriel|Us
Peter Gabriel|Security
Bruce Hornsby|The Way It Is
Bruce Hornsby|Scenes from the Southside
Lyle Lovett  |Joshua Judge Ruth
Beach Boys   |Pet Sounds

In the above statement we used t1 as an alias for Artists.

Update statement

First insert an incorrect record:

sqlite>insert into Artists (ArtistID,ArtistName) values (NULL,'Supernatural');

Then correct:

sqlite>UPDATE Artists SET ArtistName ='Santana' WHERE ArtistID=5;

Now you can insert a CD:

sqlite>insert into CDs (CDID,ArtistID,Title,Date) values (NULL,5,'Supernatural','1999');

Delete statement

First try

sqlite>select * FROM CDs WHERE Title LIKE 'Super%';

OK? Now we are sure what we are going to delete:

sqlite>DELETE FROM CDs WHERE Title LIKE 'Super%';

As a precaution you can try first

sqlite>Select * From CDs WHERE Title LIKE 'Super%';

Backing up and restoring the database

To back up the database, run from the command line:

sqlite>sqlite3 mycds.sl3 .dump > mycds.sql

This creates the file mycds.sql with the database creation script.

To restore the database, run this command:

sqlite>sqlite3 new_mycds.sl3 < mycds.sql

This also can be used for database conversion from version 2 to version 3 if the 'dump' is performed with sqlite 2 and restoring with sqlite 3.

References

SQLite home page, http://www.sqlite.org/
A command-line program to administer SQLite databases, http://www.sqlite.org/sqlite.html
SQL As Understood By SQLite, http://www.sqlite.org/lang.html
Frequently Asked Questions, http://www.sqlite.org/faq.html
SQLite Tutorial by Mike Chirico mchirico@users.sourceforge.net, http://souptonuts.sourceforge.net/readme_sqlite_tutorial.html
SQLite: The Hammer For Software Problems by JayGodse - a lot of useful information about SQLite.
Syntax Diagrams For SQLite.
Datatypes In SQLite Version 3.
SQL As Understood By SQLite.
SQLite - Data Types.
Best practice: Optimizing SQLite database performance.
How to get Top 5 records in SqLite?.

- Basic SQL
- More SQL
- SQL joins
- Triggers