Introduction to SQL

Fee structure

Students
800 NZD

Everyone else
1,600 NZD

Course outline

This is a gentle, fast-paced introduction to SQL, aiming to build a strong foundation and intuition for SQL programming, with an emphasis on building and testing reliable SQL queries. The course is suitable for beginners.

We use Microsoft’s flavour of SQL (called Transact-SQL) but all of the fundamentals, which are core SQL standards, are transferrable to all others, such as MySQL, PostgreSQL, Oracle SQL, and so on. This course is accompanied by an extensive set of notes and exercises.

Day one
  • Introduction to database technologies
  • The basic structure of a database and the usefulness of the SQL relational database model
  • SQL tables, relationships between tables, and primary/foreign key pairs
  • Connecting to a remote SQL database in the Azure Data Studio development environment
  • Introducing SQL coding with easy examples and the most useful data manipulation queries
  • Fundamentals of SQL: search conditions, subqueries, and joining tables
  • Exercises for practice and revision.
Day two
  • Reading and interpreting Transact-SQL documentation
  • Syntax and intuition for filtering and aggregating on grouped data
  • Skills for iterative development, using the SQL data definition language to create test tables
  • Design our own databases, with which we can safely test complicated queries as we write them.
Day three
  • Working with a large realistic data set split across multiple tables and schemas
  • Using a data dictionary and handling the complications that come with real-world data sets
  • Exploring a mockup of StatsNZ’s Integrated Data Infrastructure (IDI), along with some tips for working with it
  • Making sense of, and simplifying, SQL queries written by other people, and testing the results to make sure they still do what we expect
  • Building effective testing strategies for writing robust queries.
Day four
  • The basics of the R programming language, and its SQL ecosystem
  • Programming in R and making SQL requests from R to a remote SQL server
  • The Tidyverse R meta-package, which includes a range of R functions that work very similarly to SQL functions.

If you want any further advice on whether this course is right for you, please email nzssncourses@auckland.ac.nz and we will put you in touch with the instructor.

Instructor

Danny Fryer
Daniel Fryer is an award-winning Sessional Lecturer and Associate Fellow of the Higher Education Academy of La Trobe University in Melbourne. He is a Data Scientist at the Social Research Centre, technical sub-editor for the ANZ Journal of Statistics, and executive privacy officer for the Statistical Society of Australia, for which he also built the longitudinal member database.

Danny recently finished his PhD at the University of Queensland, which involved applying coalitional game theory in machine learning.