A Gentle Introduction to SQL using SQLite

SQL or Structured Query language is the language used to communicate with relational databases. What are relational databases? Well, most of the popular database systems you may know, such as MS Access, MySQL or SQLite, are all relational. That is, they all use a relational model, which, it turns out, can be described much like a spreadsheet:

* Data is organized into tables (relations) that represent a collection of similar objects (e.g. contributors).

* The columns of the table represent the attributes that members of the collection share (last name, home address, amount of contribution).

* Each row in the table represents an individual member of the collection (one contributor).

* And the fields in the row represent the attributes of that individual (Smith, 1228 Laurel St., $250).

Much of the power of a relational database lies in the ability to query these relations, both within a table (give me all contributors who donated at least $500 and who live in Wyoming) and among tables (from the contributors, judges and litigants tables, give me all contributors who donated at least $1000 to Judge Crawford and who also had legal cases over which Judge Crawford presided). SQL is the powerful and rather minimalist language we use to ask such questions of our data in a relational database. How minimalist is SQL? The basic vocabulary comes down to a few main verbs:

SELECT

INSERT

UPDATE

DELETE

I imagine you can guess what each of those verbs does, even if you've never written a database query.

To create and change tables in the database, there are a few other verbs to use:

CREATE

DROP

ALTER

Those are the keyword verbs for almost everything you need to do. The language also includes a number of modifiers that help specify the action of the verbs, but the core list comes down to a couple dozen words. These basic keywords are common across pretty much all relational databases. A specific database management system (Access, MySQL or SQLite) may add its own extensions to the common keywords, but the lion's share of the work is done with this handful of words, and they're basically the same across database applications.

By combining these simple keywords, you can create remarkably complex and specific queries. And the basic syntax still reads fairly clearly:

SELECT last_name FROM contributors WHERE state = 'WY';

The SQL query above reads pretty much like the English sentence for the same request: "Select the last name from the contributors table for all contributors whose state is WY." If you're using a graphical interface for Access or another database, that interface is simply constructing queries like these behind the scenes. So, why not

take command of your queries and write them yourself?

So, let's dive in. For this tutorial, we will be using SQLite, a free and open source database manager that's lightweight and portable.

DO WE HAVE TO INSTALL SINCE WE'RE USING FIREFOX SQLITE MANAGER?

First, we have to install SQLite.

For Mac:

If you're using a Mac with OS X 10.5 Leopard or newer, do nothing. It's already installed.

Open up your Terminal (<a href="http://www.osxterminal.com/launch_terminal/">here's how</a>), and type 'which sqlite3'.

You should see the path to SQLite 3, which looks something like this: /usr/local/bin/sqlite3.

Now test the SQLite command prompt. Type 'sqlite3'. You should see something like this:

SQLite version 3.6.22

Enter ".help" for instructions

Enter SQL statements terminated with a ";"

sqlite>

For PC:

First you will need to download two files from the SQLite web site:

1) Go to http://www.sqlite.org/download.html

2) Under the ‘Precompiled Binaries For Windows’ section, download these two files:

sqlite-3_6_23.zip (214.32 KiB)

A command-line program for accessing and modifing SQLite databases.

See the documentation for additional information.

sqlitedll-3_6_23.zip (213.17 KiB)

This is a DLL of the SQLite library without the TCL bindings.

The only external dependency is MSVCRT.DLL.

Download the zip files to C:\Program Files (Technically, you just need to download these files to somewhere your system can find them; any folder defined in the system PATH will do).

3) Unzip the files. (If you do not have WinZip or another Zip archive extractor on your system, then you may need to download one, such as Aladdin Expander: http://download.cnet.com/Aladdin-Expander/3000-2250_4-2931968.html.)

4) Inside the unzipped sqlite-3_6_23, you should find sqlite3.exe. Click on that file, and you should see a black box pop up with something like

SQLite version 3.6.22

Enter ".help" for instructions

Enter SQL statements terminated with a ";"

sqlite>

At this point you have done all the configuration and installation you need to do for SQLite. Taxing, I know.

You are looking at a command-line interface for SQLite. You can type all of your SQL commands here, and eventually you may want to. But we're going to use a different interface to get started. If you want to check out the command-line interface, type ".help" and see what's there. When you're ready, go ahead and exit from that prompt

by typing ".exit". Close the Terminal window for Mac or the command console for Windows.

Install the SQLite Manager Firefox Plugin

Go to https://addons.mozilla.org/en-US/firefox/addon/5817 and click Add to Firefox.

Click Install Now.

Restart Firefox.

In the Tools menu, you should find the SQLite Manager: [screen shot]

Clicking that item should bring up the manager. It's a simple interface for creating and interacting

with your SQLite databases.

Creating the First Database

Mousing over each of the icons at the top of the SQLite Manager tool will show what the icon does.

To create a database, simply click on the icon for "New Database" [screen shot]

Name the database "test" and save it anywhere you like (the desktop will work, or your documents folder).

Creating a Table

Click the "New Table" icon, and you'll get a form allowing you to create a new table.

To create a table, we have to define the attributes or columns it contains. For each column, we define the

datatype of the data it will contain. Define the columns as follows: [screen shot]

Name the table "contributors."

The id field will be a unique identifier for each contributor (and therefore will be the "Primary Key" for the row). It will be an integer that will automatically increment with each row we add, and it cannot be null or empty (because we need it as the unique identifier.

The next three columns will all contain text strings of undetermined lengths (last names, for example, come in all kinds of lengths), so we'll use the VARCHAR or "variable character" datatype.

For the state and zip columns, if we know that the number of characters will always be the same (for example, 2 for state postal abbreviation), we could use the CHAR character type, rather than VARCHAR. The pros and cons of choosing between the two vary among database management systems. SQLite doesn't really distinguish between the two, so we'll just use VARCHAR to keep things simple.

By the way, It may seem strange that the zip column uses a VARCHAR datatype, but remember that some zips start with a 0 (00501 is in NY). So, we want to treat this column as a string of text, rather than as a number (which would be 501).

Click OK, and you will see a dialogue box with the full SQL statement that the manager will execute on your database:

CREATE TABLE "contributors" ("id" INTEGER PRIMARY KEY AUTOINCREMENT NOT NULL , "last_name" VARCHAR, "first_name" VARCHAR, "city" VARCHAR, "state" VARCHAR, "zip" VARCHAR, "amount" INTEGER)

The syntax should be clear, since it just reflects the choices we made in the form. Before you click OK, you can edit the SQL in the box.

SQLite is not at all particular about datatypes, but most other relational databases are, so it's good practice to define the datatypes of a column in a meaningful way. In practice, if we know maximum the length of the string, as in a date or zipcode, we could define it as part of the column definition: VARCHAR(2) or VARCHAR(5).

The syntax is "CREATE TABLE" followed by the

http://www.firstsql.com/tutor.htm

http://www.sqlite.org/lang_keywords.html

SQL Data Statements:

SELECT

INSERT

UPDATE

DELETE

SQL Schema Statements

CREATE TABLE

CREATE VIEW

DROP TABLE

DROP VIEW

GRANT

REVOKE

ALTER

SQL-Transaction

COMMIT

ROLLBACK

Modifiers

WHERE

FROM

DISTINCT

IS NULL (IS NOT NULL)

ORDER BY (ASC, DESC)

GROUP BY

HAVING

AND & OR

JOIN

UNION

IN

INTO

BETWEEN

LIKE

COUNT

MAX

MIN

SUM

LIKE

AVERAGE

JOIN

ALIAS (AS?)

OUTER JOIN

CONCAT

SUBSTRING

TRIM