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:   Lab10-Worksheet.txt

Objectives

• Apply computing tools and techniques to solve problems at multiple levels of abstraction.

• Collaborate with others to gain insight, interpret data, and solve problems using computation.

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.

You need to install Firefox if not already on your computer. If you haven't used Firefox before, navigate to a few web pages so you will have a history of visited sites. After you have visited a few sites, exit out of Firefox. You should view this lab with another web browser,e.g. Chrome.

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

• Linux: ~/.mozilla/firefox/<profile folder>

• macOS: ~/Library/Application Support/Firefox/Profiles/<profile folder>

• Windows: %APPDATA%\Mozilla\Firefox\Profiles\<profile folder>

If you can not find the folder at first, search for firefox within your finder type window. Use

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

Note that finding the Firefox folder may take a bit of investigation. It is highly recommended you do this before class on Thursday. If you are not able to locate, we will help on Thursday. The issue is that the profile folder may be a hidden folder. The link above will help.

Part 1: Your Firefox Profile

1. Navigate to your profile folder (see Part 0), and look for files like cookies.sqlite and places.sqlite. Open each file with DB Browser for 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

5. Download the Lab10.sqlite database and open it in DB Browser. Explore the tables via the "Structure" and "Browse" tabs.

6. 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 tutorials online. The one at w3schools.com is a great place to start.

Submission Instructions

• Submit your completed worksheet via canvas.jmu.edu by Friday at 5pm.