Introduction to Databases

Class 2 - Intro to SQL

Welcome!

Intro to SQL

• SQL - Structured Query Language
• Special purpose language for managing data in a RDBMS
• First introduced by Oracle in 1979
• SQL is an ANSI and ISO standard. While not perfectly portable, syntax across systems is mostly the same.
• SQL is great to know, because it is everywhere.

Why SQLite?

• Flexible relational database that works with a huge range of software and hardware
• Embeddable database with small footprint, often used for development
• Supported by languages such as
• • C
  • Java
  • Perl
  • Ruby
  • Python
  • Objective C
  • ...and more!

SQLite - Distinctive Features

• Zero-Configuration
• Serverless - no separate server process is required. SQLite reads and writes direclty to disk.
• Single, compact database file
• Stable cross-platform database file

SQLite - What's it good for?

• Desktop applications
• Mobile apps
• Low traffic websites
• Ad hoc command line data analysis
• Stand-in for enterprise RDBMS during testing or demos

What's it not good for?

• Client/Server applications
• High volume websites
• Very large datasets
• High concurrency (simultaneous readers)

Installing SQLite3

Windows

• Install Git (Windows only)
• We will be using Git Bash instead of the Command Line
• Install SQLite3
• Download “sqlite-shell” and “sqlite-dll” archive files
• Unpack sqlite-dll files and move them to C:\WINDOWS\system32 folder
• Launch sqlite-shell

Installing SQLite3

Mac

Open Terminal and see if you have Homebrew installed

$ brew -v
Homebrew 0.9.5

If you do not see "Homebrew" and a version number, copy and paste this line into Terminal:

ruby -e "$(curl -fsSL https://raw.github.com/Homebrew/homebrew/go/install)"

Install SQLite3 using Homebrew

brew install sqlite3

Intro to Terminal

Mac & Linux users: Open Terminal

Windows users: Open Git-Bash

We will not be using Windows "cmd" program, as it uses a different syntax than *NIX systems.

Terminal Cheatsheet

Let's Develop It!

Working in the terminal, create a directory called gdi_db in the location of your choosing (Desktop? Documents? Somewhere you'll be able to find it again!).

Let's Develop It! (continued)

Testing our SQLite3 Installation (Mac)

Run "sqlite3" in Terminal

$ sqlite3
SQLite version 3.7.8 2011-09-19 14:49:19
Enter ".help" for instructions
Enter SQL statements terminated with a ";"
sqlite>

If successful, you will see the version info and the sqlite prompt

Testing our SQLite3 Installation (Windows)

Open sqlite-shell.

SQLite3 Help

Enter .help for instructions

We will go through many of these. For now, note that you can use .exit or .quit to exit the Sqlite prompt

The Chinook Database

• Download the Chinook Database files
• Open source sample database
• Available for many popular RDBMS, including SQL Server, Oracle, MySQL, PostgreSQL, and SQLite
• Data model for a digital media store

The Chinook Database

Installing the Chinook Database

• Move the zipped file you downloaded to the folder you just created
• Unzip ChinookDatabase1.4_CompleteVersion.zip
• Execute the following command to open the Chinook_Sqlite.sqlite database

Mac:

sqlite3 Chinook_Sqlite.sqlite

Windows:

.open C:/Downloads/ChinookDatabase1.4_CompleteVersion/Chinook_Sqlite.sqlite

Database Schemas

• A structure described in a formal language
• Acts as a blueprint for how the database is constructed
• In a relational database, the schema defines:
  • tables
  • fields
  • relationships
  • views
  • indexes
  • functions
  • and other elements

Tables

In a relational database, a table is an organized set of data using columns and rows.

You may sometimes hear a table referred to as a "relation".

View Tables

Let's check out the tables included in the Chinook database:

sqlite> .tables
Album          Employee       InvoiceLine    PlaylistTrack
Artist         Genre          MediaType      Track
Customer       Invoice        Playlist
sqlite>

Table Schemas

We can also check out the schema for the tables.

sqlite> .schema Artist
CREATE TABLE [Artist]
(
    [ArtistId] INTEGER  NOT NULL,
    [Name] NVARCHAR(120),
    CONSTRAINT [PK_Artist] PRIMARY KEY  ([ArtistId])
);
CREATE UNIQUE INDEX [IPK_Artist] ON [Artist]([ArtistId]);

What are the columns in the Artist table?

GUI Tools

Many GUI tools exist for SQLite.

Let's download SQLite Manager, an add-on for Firefox

(First install Firefox, if you do not have it already.)

Chinook Database in SQLite Manager

• Open SQLite Manager in Firefox Tools
• Database -> Connect Database
• Navigate to Chinook_Sqlite.sqlite in your directory
• Click "Open"

Let's Develop It!

Spend a few minutes exploring the SQLite Manager

• Pair up with your neighbor and discuss what you find.
• View a few tables.
• What do you find in the Structure tab? What are some different data types you can find?
• How about the Browse and Search tab?
• We can ignore Execute SQL and DB Settings for now. Just focus on what we see in the data.

The SELECT Statement

• We use the SELECT command to show data from a database
• The output from a SELECT statement is always a grid of rows and columns.
• The most simple SELECT statement retrieves all values from a single table

sqlite> SELECT * FROM Album;
1|For Those About To Rock We Salute You|1
2|Balls to the Wall|2
3|Restless and Wild|2
4|Let There Be Rock|1
5|Big Ones|3

This will return all of the results from the table, which could be expensive.

SELECT .. WHERE

You can have only a subset of rows returned by using the WHERE clause

sqlite> SELECT *
   ...> FROM Customer
   ...> WHERE FirstName = "Mark";
14|Mark|Philips|Telus|8210 111 ST NW|Edmonton|AB|Canada|T6G 2C7|+1 (780) 434-4554|+1 (780) 434-5565|mphilips12@shaw.ca|5
55|Mark|Taylor||421 Bourke Street|Sidney|NSW|Australia|2010|+61 (02) 9332 3633||mark.taylor@yahoo.au|4
sqlite>

Alternatively, the SELECT .. WHERE statement can be made on one line:

SELECT * FROM Customer WHERE FirstName = "Mark";

WHERE Clause Operators

Not Equal

SELECT * FROM Customer WHERE FirstName <> "Mark";

Greater Than

SELECT * FROM Employee WHERE BirthDate > "1970-01-01";

Less Than

SELECT * FROM Employee WHERE BirthDate < "1970-01-01";

BETWEEN

SELECT * FROM Employee WHERE BirthDate BETWEEN "1965-01-01" AND "1971-01-01";

LIKE

SELECT * FROM Track WHERE Name LIKE "%Love%";

IN

SELECT * FROM Track WHERE MediaTypeId IN (1,2);

Let's Develop It!

• Spend the rest of class writing SELECT statements with WHERE clauses
• Use SQLite Manager to get ideas of how the data is structured, and what you might query
• Feel free to work with a neighbor!

Resources

• When to Use SQLite - A list of good uses for SQLite, as well as situations where another RDMBS would be better.
• More about SQLite WHERE clauses

Questions?