SQLite / SQL Tutorials

Basic SQL

by Nikolai Shokhirev

Up ABC tutorials

 |  Basic SQL  |  Next: More SQL

See also SQLite with PHP

 

Introduction

This 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

  1. Go to http://www.sqlite.org/download.html 
  2. 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).
  3. 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%';     

 

References

Related Links

Interface-based Delphi wrapper for SQLIte 3, http://www.shokhirev.com/nikolai/programs/code/SQLiteInterface.html 

Up ABC tutorials

 |  Basic SQL  |  Next: More SQL

Home | Resumé |  Shokhirev.com |  Computing |  Links |  Publications

©Nikolai Shokhirev, 2004-2009