Introductory SQL

purlPURL: https://gxy.io/GTN:P00010
Comment: What is a Learning Pathway?
A graphic depicting a winding path from a start symbol to a trophy, with tutorials along the way
We recommend you follow the tutorials in the order presented on this page. They have been selected to fit together and build up your knowledge step by step. If a lesson has both slides and a tutorial, we recommend you start with the slides, then proceed with the tutorial.

This is an introductory course of SQL, as it was taught in Avans Hogeschool in the Netherlands.

This learning path covers all the topics usually taught during our 4 week SQL course.

Week 1: SQL Basics

The lesson is to be followed in class, and the game given as homework.

Time estimation: 5 hours

Learning Objectives
  • Explain the difference between a table, a record, and a field.
  • Explain the difference between a database and a database manager.
  • Write a query to select all values for specific fields from a single table.
  • Write queries that display results in a particular order.
  • Write queries that eliminate duplicate values from data.
  • Write queries that select records that satisfy user-specified conditions.
  • Explain the order in which the clauses in a query are executed.
  • Write queries that calculate new values for each selected record.
  • Explain how databases represent missing information.
  • Explain the three-valued logic databases use when manipulating missing information.
  • Write queries that handle missing information correctly.
  • Explore SQL City and discover who committed the murder
  • Reinforce your experiences with SQL such as querying, filtering, and joining data.
Lesson Slides Hands-on Recordings
Introduction to SQL
SQL Educational Game - Murder Mystery

Week 2: Advanced SQL

Today we introduce complex operations like Joins.

Time estimation: 3 hours

Learning Objectives
  • Define aggregation and give examples of its use.
  • Write queries that compute aggregated values.
  • Trace the execution of a query that performs aggregation.
  • Explain how missing data is handled during aggregation.
  • Explain the operation of a query that joins two tables.
  • Explain how to restrict the output of a query containing a join to only include meaningful combinations of values.
  • Write queries that join tables on equal keys.
  • Explain what primary and foreign keys are, and why they are useful.
  • Explain what an atomic value is.
  • Distinguish between atomic and non-atomic values.
  • Explain why every value in a database should be atomic.
  • Explain what a primary key is and why every record should have one.
  • Identify primary keys in database tables.
  • Explain why database entries should not contain redundant information.
  • Identify redundant information in databases.
  • Write statements that create tables.
  • Write statements to insert, modify, and delete records.
  • Write short programs that execute SQL queries.
  • Trace the execution of a program that contains an SQL query.
  • Explain why most database applications are written in a general-purpose language rather than in SQL.
Lesson Slides Hands-on Recordings
Advanced SQL

Week 3: SQL in other languages

Based on which language you’re teaching alongside SQL, you can choose one of the following lessons which have the same content for different programming languages.

Time estimation: 1 hour 30 minutes

Learning Objectives
  • Write short programs that execute SQL queries.
  • Trace the execution of a program that contains an SQL query.
  • Explain why most database applications are written in a general-purpose language rather than in SQL.
  • Write short programs that execute SQL queries.
  • Trace the execution of a program that contains an SQL query.
  • Explain why most database applications are written in a general-purpose language rather than in SQL.
Lesson Slides Hands-on Recordings
SQL with R
SQL with Python

Week 4: Schema Design

In the Avans course an external tool was used when discussing Schema design.


Editorial Board

This material is reviewed by our Editorial Board:

orcid logoHelena Rasche avatar Helena Rasche Bazante Sanders avatar Bazante Sanders

Funding

These individuals or organisations provided funding support for the development of this resource