Databases and SQL
In the late 1920s and early 1930s, William Dyer, Frank Pabodie, and Valentina Roerich led expeditions to the Pole of Inaccessibility in the South Pacific, and then onward to Antarctica. Two years ago, their expeditions were found in a storage locker at Miskatonic University. We have scanned and OCR’d the data they contain, and we now want to store that information in a way that will make search and analysis easy.
Three common options for storage are text files, spreadsheets, and databases. Text files are easiest to create, and work well with version control, but then we would have to build search and analysis tools ourselves. Spreadsheets are good for doing simple analyses, but they don’t handle large or complex data sets well. Databases, however, include powerful tools for search and analysis, and can handle large, complex data sets. These lessons will show how to use a database to explore the expeditions’ data.
Prerequisites
- UNIX shell plus SQLite3 or Firefox SQLite plugin.
- survey.db
Getting ready
You need to download some files to follow this lesson:
- Make a new folder in your Desktop called
sql-novice-survey. - Download sql-novice-survey-data.zip and move the file to this folder.
- If it’s not unzipped yet, double-click on it to unzip it. You should end up with a new folder called
data. - You can access this folder from the Unix shell with:
$ cd && cd Desktop/sql-novice-survey/dataTopics
- Selecting Data
- Sorting and Removing Duplicates
- Filtering
- Calculating New Values
- Missing Data
- Aggregation
- Combining Data
- Data Hygiene
- Creating and Modifying Data
- Programming with Databases - Python
- Programming with Databases - R