Girl Develop It is here to provide affordable and accessible programs to learn software through mentorship and hands-on instruction.
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
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.
|.||the current directory- ex: "touch ./wow.txt"|
|..||the parent of current directory - ex: "cd ../RubyParty"|
|~||the root directory- ex: "cd ~/Pictures"|
|cd [location]||"change directory" to [location]|
|pwd||"present working directory" - where am I?|
|ls -al||"list all" of the contents of current directory, including invisible ones|
|touch [filename.extension]||create a file called filename.extension in the current directory|
|mkdir [directoryname]||create a directory called directoryname in the current directory|
|rm [filename]||"remove" (delete) the file called filename|
|rm -rf [directoryname]||"remove recursively with force" the directory called directoryname|
|clear OR cmd+k||clear the terminal screen|
|help||lists all possible commands|
|man [command]||displays the manual for command|
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!).
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
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
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".
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>
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?
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.)
Spend a few minutes exploring the SQLite Manager
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.
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) email@example.com|5 55|Mark|Taylor||421 Bourke Street|Sidney|NSW|Australia|2010|+61 (02) 9332 firstname.lastname@example.org|4 sqlite>
Alternatively, the SELECT .. WHERE statement can be made on one line:
SELECT * FROM Customer WHERE FirstName = "Mark";
|<>||Not equal. Note: In some versions of SQL, this may be written as !=|
|>=||Greater than or equal|
|<=||Less than or equal|
|BETWEEN||Between an inclusive range|
|LIKE||Search for a pattern|
|IN||To specify multipl possible values for a column|
SELECT * FROM Customer WHERE FirstName <> "Mark";
SELECT * FROM Employee WHERE BirthDate > "1970-01-01";
SELECT * FROM Employee WHERE BirthDate < "1970-01-01";
SELECT * FROM Employee WHERE BirthDate BETWEEN "1965-01-01" AND "1971-01-01";
SELECT * FROM Track WHERE Name LIKE "%Love%";
SELECT * FROM Track WHERE MediaTypeId IN (1,2);