CSCI 250 - SQL Overview

Data types

SQLite Type Overview
INTEGER
REAL
BLOB
NULL
TEXT
All string literals are surrounded by single quotation marks

Statements

Statements are case insensitive; convention is to use upper case
Variable names are case sensitive
CREATE TABLE TableName (Field1 type, Field2 type, ... FieldN type)
DROP TABLE TableName
INSERT INTO TableName VALUES (value1, value2, ... valueN)
DELETE TableName WHERE...
SELECT Field1, Field2, ... FieldN FROM TableName
SELECT * FROM TableName
SELECT ... WHERE... ORDER BY...
UPDATE TableName SET Field1 = value1, Field2 = value2, ... FieldN = valueN
UPDATE ... SET ... WHERE...

The WHERE clause:

SELECT * FROM NameTable WHERE FirstName = 'John'
SELECT * FROM NameTable WHERE FirstName <> 'John'
SELECT * FROM NameTable WHERE StudentID > 123
SELECT * FROM CourseTable WHERE Course LIKE 'CSCI%'
SELECT * FROM CourseTable WHERE Course LIKE 'MATH%' AND StudentID = 123
SELECT COUNT(*) FROM CourseTable WHERE StudentID = 123
SELECT MIN(StudentID) FROM NameTable
Available functions: MIN, MAX, AVG, COUNT

The ORDER BY clause:

SELECT * FROM NameTable ORDER BY LastName asc
SELECT * FROM NameTable ORDER BY LastName desc
SELECT * FROM NameTable ORDER BY LastName desc, FirstName desc

Join

A join is a query involving multiple tables simultaneously. In SQL, some additional syntax is needed to give the database the necessary cues to perform an efficient query.

Here is an example of a two-step query without a join:

SELECT StudentID FROM NameTable WHERE FirstName = 'John' AND LastName = 'Doe'

In the example tables, this returns 123. Once we know this, we issue a followup query as follows:

SELECT Course FROM CourseTable WHERE StudentID = 123

Now we can see how to issue a single query to obtain this information by using a join:

SELECT CourseTable.Course 
   FROM CourseTable, NameTable 
   WHERE NameTable.FirstName = 'John' 
      AND NameTable.LastName = 'Doe'
      AND NameTable.StudentID = CourseTable.StudentID

Indexing

Without an index, the database will perform an O(n) search through a column when performing queries. Indexing can accelerate search substantially, but indexes can only be created manually.

Indices are typically created along with the table. The syntax is as follows:

CREATE TABLE TableName (Field1 type, Field2 type, ... FieldN type, INDEX indexName (FieldX), ...)

The index name is optional. All data types can be indexed. CHAR indices can be restricted to index on a prefix by putting a length in parentheses. For example, the following will create an index that looks at the first five characters of the last name:

CREATE TABLE NameTable (StudentID INTEGER, LastName TEXT, FirstName TEXT, INDEX ByLast(LastName(5)))