Thursday, October 20


Reading Your Browser's History with SQLite
How the SQLite database is used in billions of real-world applications today is of little relevance to us in this class. But the web browser is a easy-to-understand scenario of how a database gets created and filled.



Sorry for the informal writeup.

I didn't write a different GROUP BY tutorial for this year. These are still relevant in terms of the same code:

Requirements: Two SQL analyses-visualizations due

Make a spreadsheet named sql-to-spreadsheets. It will have to sub-sheets.

Send me an email with:

  • The question, in normal English, that you were trying to answer.
  • The SQL query you wrote.

First sheet

Pick a dataset from here. You can look around on your own, but I don't want you to get stuck on hiccups in the importing process.

Read about the data (even if it means Googling around)

Write a query that has these at least 3 of these components:

  • Selects specific columns (don't use *)
  • Filters the data using a WHERE clause.
  • Sorts the result by some field or condition
  • Limits the results to something useful.

Second sheet

Same thing as the first sheet, but pick a different dataset. This time, include a GROUP BY function.

Example query

An example: Using the Pensinsula city salary data, group the data by city name and year, produce a list of average salary, by city and year, in sorted in ascending order of city name and year.