To get started, type the following command to check if you already have SQLite installed.
$ sqlite3
And you should see:
SQLite version 3.7.15.2 2014-08-15 11:53:05
Enter ".help" for instructions
Enter SQL statements terminated with a ";"
sqlite>
If you do not see above result, then it means you do not have SQLite installed on your machine. Follow the appropriate instructions below.
Go to SQLite Download page and download the precompiled binaries for your machine. You will need to download sqlite-shell-win32-*.zip
and sqlite-dll-win32-*.zip
zipped files.
Create a folder C:\sqlite
and unzip the files in this folder which will give you sqlite3.def
, sqlite3.dll
and sqlite3.exe
files.
Add C:\sqlite
to your PATH environment variable and finally go to the command prompt and issue sqlite3
command.
First, try to install via Homebrew:
brew install sqlite3
If not, download the package from above. After downloading the files, follow these steps:
$tar -xvzf sqlite-autoconf-3071502.tar.gz
$cd sqlite-autoconf-3071502
$./configure --prefix=/usr/local
$make
$make install
Download the musichistory.db file, and then copy it to the folder that you created for this exercise. That file is the database. It contains all of the tables and data.
- Open up the database file in the DB Browser for SQLite application to see it.
- Go ahead and click around a little bit to familarize yourself with the database.
- When you're ready to start the exercise, click the tab labeled "Execute SQL", type in your query and run it.
For each of the following exercises, provide the appropriate query. Yes, even the ones that are expressed in the form of questions. Everything from class and the Sqlite documentation for SQL keywords and functions is fair game.
-
Query all of the entries in the
Genre
table -
Using the
INSERT
statement, add one of your favorite artists to theArtist
table. -
Using the
INSERT
statement, add one, or more, albums by your artist to theAlbum
table. -
Using the
INSERT
statement, add some songs that are on that album to theSong
table. -
Write a
SELECT
query that provides the song titles, album title, and artist name for all of the data you just entered in. Use theLEFT JOIN
keyword sequence to connect the tables, and theWHERE
keyword to filter the results to the album and artist you added.Reminder: Direction of join matters. Try the following statements and see the difference in results.
SELECT a.Title, s.Title FROM Album a LEFT JOIN Song s ON s.AlbumId = a.AlbumId; SELECT a.Title, s.Title FROM Song s LEFT JOIN Album a ON s.AlbumId = a.AlbumId;
-
Write a
SELECT
statement to display how many songs exist for each album. You'll need to use theCOUNT()
function and theGROUP BY
keyword sequence. -
Write a
SELECT
statement to display how many songs exist for each artist. You'll need to use theCOUNT()
function and theGROUP BY
keyword sequence. -
Write a
SELECT
statement to display how many songs exist for each genre. You'll need to use theCOUNT()
function and theGROUP BY
keyword sequence. -
Using
MAX()
function, write a select statement to find the album with the longest duration. The result should display the album title and the duration. -
Using
MAX()
function, write a select statement to find the song with the longest duration. The result should display the song title and the duration. -
Modify the previous query to also display the title of the album.