Getting More Familiar with DB Browser for SQLite

I have just completed my fifth week of fifteen at the Flatiron School in NYC. There was a moment when my buddy told me how valuable knowing SQL is to companies and I went “Well we spent about half a day on it, is that enough time?” Clearly no as I began setting up two tables, one which used a foreign key and immediately I started getting errors.

Let’s look at an Artist / Album model. First I created the Artist table.

CREATE TABLE "artist" (
 "artistid" INTEGER,
 "artistName" TEXT,
 PRIMARY KEY("artistid")
);

Then I wanted each album to belong to an artist and I created my Album table.

CREATE TABLE "album" (
 "albumid" INTEGER,
 "albumName" TEXT,
 "albumArtist" INTEGER,
 "albumYear" INTEGER,
 "albumNumberOfTracks" INTEGER,
 FOREIGN KEY("albumArtist") REFERENCES "artist"("artistid")
);

It was important to assign the foreign key (albumArtist) to the artist table and the artistid column.

Next I inserted two artists and about six or seven albums per artist into my database with the last column being the number of songs on the album.

Image 1.01

Starting with the two basic lines of SQL code to show me my tables I’ve included the code below.

SELECT * FROM album 
SELECT * FROM artist

I will include a few snippets below of basic SQL code on each column.

For all the Beatles albums my SQL code is

SELECT * FROM album
WHERE albumArtist = 2

Let’s say I had included all of the Beatles albums, but my favorite ones only go from 1965 to 1969 my SQL code is

SELECT * FROM album
WHERE albumYear BETWEEN 1965 AND 1969

Who doesn’t love an album with less than 9 tracks? Plenty of Kraftwerk albums to pick from.

SELECT * FROM album 
WHERE albumNumberOfTracks < 9

Maybe I want a column of album names.

SELECT DISTINCT albumName FROM album;

Maybe I’m looking for the best electronic album of all time, Kraftwerk’s Trans Europa-Express.

SELECT * FROM album 
WHERE albumArtist = 1 AND albumYear = 1977

If I wanted albums before 1970 or any album with exactly 12 tracks.

SELECT * FROM album 
WHERE albumYear < 1970 OR albumNumberOfTracks = 12

Using Not I could find albums that are not from 1970 or before, which gives me my favorite part of Kraftwerk’s catalogue here.

SELECT * FROM album
WHERE NOT albumYear < 1970

Looking at all of my of my favorite Kraftwerk and Beatles albums if I arrange them by track numbers every Kraftwerk album has less songs than the Beatles album Sgt. Peppers, with the least number of songs, 13.

SELECT * FROM album
ORDER BY albumNumberOfTracks

If I wanted the minimum or year or maximum number of tracks I could use MIN() OR MAX() to show me that.

SELECT MIN(albumYear)
FROM album
SELECT MAX(albumNumberOfTracks)
FROM album

I was always curious about the average number of songs on my favorite Kraftwerk and Beatles albums. Turns out to be 8 for Kraftwerk and 17.33333333 for The Beatles.

SELECT AVG(albumNumberOfTracks)
FROM album
WHERE albumArtist = 2
SELECT AVG(albumNumberOfTracks)
FROM album
WHERE albumArtist = 1

I will close with the power of an Inner Join to take my image 1.01 above and include the Artist Name instead of Album’s Artist ID number.

SELECT artist.artistName, album.albumName, album.albumYear, album.albumNumberOfTracks
FROM album
INNER JOIN artist
ON album.albumArtist = artist.artistid;

Gives a much nicer looking table, 1.02 shown below. I’m glad I have gone through more work on SQL thanks to w3schools.com for their stellar resources.

Image 1.02