Data Management with SQL for Social Scientists *alpha*

This is an alpha lesson to teach Data Management with SQL for Social Scientists, We welcome and criticism, or error; and will take your feedback into account to improve both the presentation and the content.

Databases are useful for both storing and using data effectively. Using a relational database serves several purposes.

It keeps your data separate from your analysis. This means there’s no risk of accidentally changing data when you analyze it.
If we get new data we can rerun a query to find all the data that meets certain criteria.
It’s fast, even for large amounts of data.
It improves quality control of data entry (type constraints and use of forms in Access, Filemaker, etc.)
The concepts of relational database querying are core to understanding how to do similar things using programming languages such as R or Python.

This lesson will teach you what relational databases are, how you can load data into them and how you can query databases to extract just the information that you need.

Prerequisites

We expect you to have learn a bit about the SAFI dataset in the spreadsheet and OpenRefine session. It is not necessary, but will greatly improve your ability to understand the power of SQL and when to use it versus another tool.

Schedule

	Setup	Download files required for the lesson
00:00	1. 01 What is a relational database?	What is a relational database and why should I use it? Why do tables have key columns? What is SQL
00:15	2. 02 Using DB Browser for SQLite	What does the DB Browser for SQLite allow me to do?
00:45	3. 03 Accessing Data with Queries	What is SQL? How can I write basic queries in SQL?
01:20	4. 04 Missing Data	How can I deal with missing data?
01:30	5. 05 Creating New Columns	How can I add new columns with derived values in the query results? How can I give a column a new name? How do I use built-in functions to create new values? How can I create binned results?
02:00	6. 06 Aggregations	How can I summarise the data in my tables How can I make sure my column names make sense?
02:20	7. 07 Saving queries for future use - creating views	How can I create a view in DB Browser for SQLite using SQL code? How can I add records of data to a table?
02:50	8. 08 Combining Data with Joins	What is meant by joining tables? Why would I want to join tables? What different types of joins are there? How do Joins help you discover missing data or gaps in the data
03:20	9. 09 EXTRA: Using database tables in other environments	How do I save my query results for use by other programs or applications? What are and how do I use ODBC applications? How can I access an SQLite database table from other programming environments?
03:35	10. 10 EXTRA: The SQLite command line	How can I save my code in a file and run it again?
04:00	Finish

The actual schedule may vary slightly depending on the topics and exercises chosen by the instructor.