Introductory SQL

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.

Licence: Creative Commons Attribution 4.0 International

Keywords: python, real-course, data-science

Authors: Helena Rasche, Bazante Sanders

Status: Active

1

Week 1: SQL Basics

2 materials

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.
2

Week 2: Advanced SQL

1 material

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.

3

Week 3: SQL in other languages

2 materials

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.
4

Week 4: Schema Design

0 materials

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

Activity log