CS145 - Introduction to Databases
Getting Started With SQLite on Stanford's Unix Machines

Overview

CS145 programming projects are implemented using the SQLite database system. SQLite also sits behind the relational algebra interpreter used earlier in the quarter. Finally, standalone SQLite is an easy and convenient tool for experimentation with SQL queries.

This document is a brief and simple introduction to using SQLite on Stanford's Unix Machines. Much more detailed information is available in the SQL Syntax and SQLite Command Shell sections of the SQLite Documentation.

Running SQLite

Begin by logging into one of the Stanford Unix machines; we recommend the Corn machines. Run the sqlite binary by executing: 

     /usr/class/cs145/bin/sqlite <databaseName>
This will open a sqlite command-loop executing on the database <databaseName>: 
     sqlite>
Note that a file called <databaseName> has been created in your current directory. It will contain all data within this database.

Creating a Table

The sqlite command-loop executes any valid SQL statement. One simple type of command creates a table (relation). The form is: 

    create table <tableName> ( <list of attributes and their types> );
You may enter text on one line or on several lines; just make sure to terminate the command with a semicolon. An example table-creation command is: 
    create table Test (I int, S varchar);
This command creates a table named Test with two attributes. The first, named I, is an integer, and the second, named S, is a character string.

Aside: Note that we did not specify the maximum length for S; unlike most other database systems where S might be declared with type varchar(10) (a character string of length at most 10), SQLite ignores such specifications and internally stores all values as either an integer, a floating point number, an arbitrary-length text string, an arbitrary-length "blob" of bytes, or null. In fact, SQLite does not require that columns be specified with a type at all. Nonetheless, we recommend that you declare column types as it can affect how SQLite internally stores and orders your values; i.e. it can make the difference between '100.0' being stored as text, an integer, or a floating point number. See SQLite Data Types for more details.

Creating a Table With a Primary Key

To create a table that declares attribute A to be a primary key: 

    create table <tableName> (..., A <type> PRIMARY KEY, B, ...);
To create a table that declares the set of attributes (A,B,C) to be a primary key: 
    create table <tableName> (<attrs and their types>, PRIMARY KEY (A,B,C));

Aside: Declaring an INTEGER column as a PRIMARY KEY gives the column special meaning in SQLite -- it maps the column to a SQLite-internal integer ID column. INTEGER PRIMARY KEY columns are the only SQLite columns that support auto-incrementation. See the SQLite documentation on INTEGER PRIMARY KEY for more details. A column declared as an INT or any other type will not have this special behavior.

Inserting Tuples

Having created a table, we can insert tuples into it. The simplest way to insert is with the INSERT command: 

    insert into <tableName> values( <list of values for attributes, in order> );
For instance, we can insert the tuple (10, 'foobar') into table Test by: 
    insert into Test values(10, 'foobar');
Notice that SQL uses single-quotes for strings, not double-quotes.

Aside: SQLite does not perform any type checking upon insertion. Unlike most database systems, performing: 

    insert into Test values('foobar', 10);
will successfully insert the tuple without any error. Although SQLite columns can be declared with types, each individual value has its own associated type and can be placed in any column (with the exception of INTEGER PRIMARY KEY columns, which can only contain integers). Be careful when ordering arguments for insertion -- SQLite will not warn you if you've done it incorrectly. For more information on how SQLite handles typing, see SQLite Data Types.

Getting Values from a Table

We can see the tuples in a table with the simple query: 

    select * from <tableName>;
For instance, after the above CREATE and INSERT statements, the command: 
    select * from Test;
produces the result 
    10|foobar

SQLite Special Commands

SQLite supports a number of special, non-SQL commands that allow you to perform bulk loading, change the output mode, get information about your database, and more. All special commands begin with a . and do not terminate with a semicolon. For instance, to change the output format for the SELECT statement above, we could specify: 

     sqlite> .mode columns
     sqlite> .headers on
     sqlite> select * from Test;
     I      S
     -----  ------
     10     foobar
More information about SQLite special commands can be found via the special command .help, or in the documentation for the SQLite Command Shell, under Special Commands to SQLite3.

Getting Rid of Your Data

To remove a table from your database, execute: 

    drop table <tableName>;
Alternatively, to delete all tuples in a table while leaving the table intact, execute: 
    delete from <tableName>;

Getting Information About Your Database

To list all the tables in your database, type: 

    .tables
It is also possible to inspect the attributes of a table once you know its name. The special command: 
    .schema <tableName>
shows the CREATE statement that yielded table <tableName>. Note that all triggers defined on <tableName> will also be displayed.

Quitting sqlite

To leave the sqlite command-loop, type either: 

    .quit
or 
    .exit
at the sqlite> prompt. Entering Ctrl-d will also break out of the sqlite command-loop.

Executing SQL From a File

Instead of typing SQL commands in a command-loop, it is often more convenient to create the SQL command(s) in a file and cause the file to be executed. To execute the commands in file Foo.sql, type: 

    .read Foo.sql
at the sqlite> prompt. Foo.sql can also be executed directly from the shell by typing: 
    /usr/class/cs145/bin/sqlite <databaseName> < Foo.sql
Note that Foo.sql can also contain SQLite special commands, as in the example seen in Bulk-Loading into SQLite.