CS 101: Introduction to Computer Science
James Madison University, Fall 2019 Semester

Lab11: Exploring your SQLite data

Background

SQLite is the most widely deployed database engine in the world. It's embedded in Chrome and Firefox, iOS and Android, and many other applications that people use every day. You may not have realized how many SQLite databases you have on your desktop, laptop, or phone, but they are everywhere! In today's lab, you will learn how to access these files directly and unlock their true potential. You may even learn a thing or two about yourself in the process.

Download:   Lab11-Worksheet.txt

Objectives

Part 0: Connecting to SQLite

This lab assumes you have already worked through the SQL examples in this week's video lecture. If you have not done so already, please install DB Browser for SQLite on your computer. (It should already be installed in the CS 101 Virtual Machine.)

In order to complete today's lab, you will need to open SQLite databases found in your Firefox profile folder. The location of your profile folder depends on your operating system:

The name of your <profile folder> will look something like xxxxxxxx.default, where xxxxxxxx are random letters and numbers. See the support article "where firefox stores user data" for more details.

Part 1: Your Firefox Profile

  1. Run the DB Browser app, and click the "Open Database" button on the toolbar. Navigate to your profile folder (see Part 0), and look for files like cookies.sqlite and places.sqlite. Use the "Database Structure" and "Browse Data" tabs to view the contents of each table stored in these files. Explore the other databases using this same process. Answer #1 and #2 in your worksheet.

  2. Sometimes you will need to query two or more tables that have columns with the same name (e.g., id). You need to use the table name in that case so that SQL can tell which column you want. For example, in places.sqlite:

    SELECT *
FROM moz_historyvisits, moz_places
WHERE moz_historyvisits.place_id = moz_places.id

    Because the table.column syntax makes the code harder to read, SQL allows you to rename tables within the query. Often developers use a single letter name for this purpose, like this:

    SELECT h.visit_type, h.visit_date, p.url
FROM moz_historyvisits h, moz_places p
WHERE h.place_id = p.id

    Note how p.url in the SELECT clause makes it easy to tell what table that column comes from. Answer #3 in your worksheet.

  3. In addition to SELECT, FROM, and WHERE, there are many other clauses in SQL. For example, you can sort the query results:

    SELECT *
FROM moz_places
ORDER BY visit_count DESC, url

    In this query, the output is first sorted by visit_count in descending order and then sorted by url in ascending order (for records that have the same visit_count). Answer #4 in your worksheet.

  4. One of the most interesting features of SQL is grouping and aggregation, which allows you to do more than just filter data. For example, this query shows which servers you visit most: 

    SELECT rev_host, sum(visit_count)
FROM moz_places
GROUP BY rev_host
ORDER BY sum(visit_count) DESC

    GROUP BY essentially means "for each." This query finds all rows with the same rev_host, and then calls the sum function to add up all their visit_count values. The query then outputs a single row for each rev_host with its total number of visits. Answer #5 in your worksheet.

Part 2: Other Data Sets

  1. Download the Lab11.sqlite database and open it in SQLite Manager. Explore the tables via the "Structure" and "Browse" tabs.

  2. Write SQL statements to answer the remaining questions in your worksheet. For each one, include both your SQL code and the result.

To learn more about SQL, check out some of the many free tutorials online. The one at w3schools.com is a great place to start.

Submission Instructions