Introduction to SQL

Dates

Wellington: Wednesday 28 – Thursday 29 November 2018

Auckland: Wednesday 27 – Thurdsay 28 February 2019

Instructor

Daniel Fryer

Prerequisites

None; and no previous computing experience is necessary.

Course outline

Day 1
We start with a short introduction to database technologies, covering the basic structure of a database and the usefulness of the SQL relational database model. We then dive into SQL coding, beginning with easy examples and the most useful data manipulation queries: Joining tables, using unique identifiers, selecting, projecting, aggregating, ordering data and doing top-k searches. By the end of day 1 we will have plenty of practice in using basic SQL queries to efficiently extract useful datasets and to export them to R, SAS, Stata, or other statistical software.

Day 2
This is a chance to apply our day-1 knowledge to the Microsoft SQL Server and Visual Studio coding environment commonly used to access the IDI. We start with a brief introduction to the IDI and the important peculiarities of this system, from both the research and database usage perspectives. We then learn some intermediate SQL: Nested queries, unioning, grouping, creating views, built-in functions and anything we have time for (including creating our own basic T-SQL stored procedures). The goal will be to practice using these queries to greatly speed up and simplify the process of extracting useful data from a database.

Course text

The instructor's notes will contain a wealth of tutorials, examples and take-home notes for future reference.