Girl Develop It is here to provide affordable and accessible programs to learn software through mentorship and hands-on instruction.
Some "rules"
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
Open sqlite-shell.
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
sqlite3 Chinook_Sqlite.sqlite
.open C:/Downloads/ChinookDatabase1.4_CompleteVersion/Chinook_Sqlite.sqlite
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) 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";
Operator | Description |
= | Equal |
<> | Not equal. Note: In some versions of SQL, this may be written as != |
> | Greater than |
< | Less than |
>= | 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);