Introductory SQL
purlPURL: https://gxy.io/GTN:P00010Comment: What is a Learning Pathway?
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:
Helena Rasche
Bazante Sanders
Funding
These individuals or organisations provided funding support for the development of this resource
