SQLite Practice Notes

• An orgmode based note publishing pipeline example created while looking at SQLite.
• Uses the chinook.db database from SQLite Tutorial.

Setup

sqlite3 command line options

Usage: sqlite3 [OPTIONS] FILENAME [SQL]

FILENAME is the name of an SQLite database. A new database is created
if the file does not previously exist.

OPTIONS include:
   -ascii               set output mode to 'ascii'
   -bail                stop after hitting an error
   -batch               force batch I/O
   -column              set output mode to 'column'
   -cmd COMMAND         run "COMMAND" before reading stdin
   -csv                 set output mode to 'csv'
   -echo                print commands before execution
   -init FILENAME       read/process named file
   -[no]header          turn headers on or off
   -help                show this message
   -html                set output mode to HTML
   -interactive         force interactive I/O
   -line                set output mode to 'line'
   -list                set output mode to 'list'
   -lookaside SIZE N    use N entries of SZ bytes for lookaside memory
   -mmap N              default mmap size set to N
   -newline SEP         set output row separator. Default: '\n'
   -nullvalue TEXT      set text string for NULL values. Default ''
   -pagecache SIZE N    use N slots of SZ bytes each for page cache memory
   -scratch SIZE N      use N slots of SZ bytes each for scratch memory
   -separator SEP       set output column separator. Default: '|'
   -stats               print memory stats before each finalize
   -version             show SQLite version
   -vfs NAME            use NAME as the default VFS

Show tables

sqlite3 -cmd '.tables' chinook.db

Show schema

sqlite3 -cmd '.schema albums' chinook.db

Practice

Tables

Schemas

.schema albums

CREATE TABLE IF NOT EXISTS "albums"
(
    [AlbumId] INTEGER PRIMARY KEY AUTOINCREMENT NOT NULL,
    [Title] NVARCHAR(160)  NOT NULL,
    [ArtistId] INTEGER  NOT NULL,
    FOREIGN KEY ([ArtistId]) REFERENCES "artists" ([ArtistId])
		ON DELETE NO ACTION ON UPDATE NO ACTION
);
CREATE INDEX [IFK_AlbumArtistId] ON "albums" ([ArtistId]);

.schema artists

CREATE TABLE IF NOT EXISTS "artists"
(
    [ArtistId] INTEGER PRIMARY KEY AUTOINCREMENT NOT NULL,
    [Name] NVARCHAR(120)
);

.schema tracks

CREATE TABLE IF NOT EXISTS "tracks"
(
    [TrackId] INTEGER PRIMARY KEY AUTOINCREMENT NOT NULL,
    [Name] NVARCHAR(200)  NOT NULL,
    [AlbumId] INTEGER,
    [MediaTypeId] INTEGER  NOT NULL,
    [GenreId] INTEGER,
    [Composer] NVARCHAR(220),
    [Milliseconds] INTEGER  NOT NULL,
    [Bytes] INTEGER,
    [UnitPrice] NUMERIC(10,2)  NOT NULL,
    FOREIGN KEY ([AlbumId]) REFERENCES "albums" ([AlbumId])
		ON DELETE NO ACTION ON UPDATE NO ACTION,
    FOREIGN KEY ([GenreId]) REFERENCES "genres" ([GenreId])
		ON DELETE NO ACTION ON UPDATE NO ACTION,
    FOREIGN KEY ([MediaTypeId]) REFERENCES "media_types" ([MediaTypeId])
		ON DELETE NO ACTION ON UPDATE NO ACTION
);
CREATE INDEX [IFK_TrackAlbumId] ON "tracks" ([AlbumId]);
CREATE INDEX [IFK_TrackGenreId] ON "tracks" ([GenreId]);
CREATE INDEX [IFK_TrackMediaTypeId] ON "tracks" ([MediaTypeId]);

Select

Syntax:

SELECT DISTINCT column_list
FROM table_list
JOIN a_table ON join_condition
WHERE row_filter
ORDER BY a_column
LIMIT a_count OFFSET offset
GROUP BY a_column
HAVING group_filter;

Select * from albums

SELECT *
FROM albums
LIMIT 10 OFFSET 10;

AlbumId	Title	ArtistId
11	Out Of Exile	8
12	BackBeat Soundtrack	9
13	The Best Of Billy Cobham	10
14	Alcohol Fueled Brewtality Live! [Disc 1]	11
15	Alcohol Fueled Brewtality Live! [Disc 2]	11
16	Black Sabbath	12
17	Black Sabbath Vol. 4 (Remaster)	12
18	Body Count	13
19	Chemical Wedding	14
20	The Best Of Buddy Guy - The Millenium Collection	15

Select albums with most tracks

SELECT count(tr.trackid) Tracks, title Album_Title, ar.name Artist_Name
FROM artists ar
JOIN albums al ON al.artistid = ar.artistid
JOIN tracks tr ON al.albumid  = tr.albumid
GROUP BY al.title
HAVING Tracks > 13
ORDER BY Tracks DESC, ar.name ASC
LIMIT 10;

Tracks	Album_Title	Artist_Name
57	Greatest Hits	Lenny Kravitz
34	Minha Historia	Chico Buarque
30	Unplugged	Eric Clapton
26	Lost, Season 3	Lost
25	Lost, Season 1	Lost
25	The Office, Season 3	The Office
24	Battlestar Galactica (Classic), Season 1	Battlestar Galactica (Classic)
24	My Way: The Best Of Frank Sinatra [Disc 1]	Frank Sinatra
24	Lost, Season 2	Lost
23	Afrociberdelia	Chico Science & Nação Zumbi