SQL workshop for the Masters of Social Science Program
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.
- 2017-11-02 - 10-1pm
Syllabus
- Selecting, sorting and removing duplicates
- Filtering and creating subsets from a database
- Calculating new values using SQL
- Dealing with missing Data
- Creating sums, averages, and summary values on your Data
- Combining data over multiple tables
- Creating and modifying a database
- Using SQL in a programming language ®
Audience: closed to MASS students.
Setup
Software
SQL is a specialized programming language used with databases. We use a simple database manager called SQLite in our lessons.
We will use SQLite Manager plugin for the Firefox web browser. If you don’t have Firefox installed, you need to install it first and then you will be able to add the plugin.
Windows
- If you don’t already have Firefox, download Firefox from the Firefox download page
- Install Firefox following the installer instructions
- Install the SQLite Manager add on: Within Firefox, go to https://addons.mozilla.org/en-us/firefox/addon/sqlite-manager/.
- Click on the green ‘Add to Firefox’
- When a window pops up, click on ‘Install’
- When the next box pops up, click on ‘Restart Now’ to restart Firefox
- Add SQLite Manager to the menu: Menu (the three horizontal lines near the top right corner of Firefox) -> Customize, then drag the SQLite Manager icon to one of the empty menu squares on the right, Exit Customize
- Open SQLite Manager: Menu -> SQLite Manager
Mac
- If you don’t already have Firefox, download Firefox from the Firefox download page
- Install Firefox following the installer instructions
- Install the SQLite Manager add on: Within Firefox, go to https://addons.mozilla.org/en-us/firefox/addon/sqlite-manager/.
- Click on the green ‘Add to Firefox’
- When a window pops up, click on ‘Install’
- When the next box pops up, click on ‘Restart Now’ to restart Firefox.
- After restart the SQLite Manager will be added ot the Tools menu
- Open SQLite Manager: Tools -> SQLite Manager
Database
Download the data for the class and save somewhere you can find it: https://github.com/swcarpentry/sql-novice-survey/raw/gh-pages/files/survey.db