e-learning

Advanced SQL

View material

Abstract

About This Material

This is a Hands-on Tutorial from the GTN which is usable either for individual self-study, or as a teaching material in a classroom.

Questions this will address

  • How can I calculate sums, averages, and other summary values?
  • How can I combine data from multiple tables?
  • How should I format data in a database, and why?
  • How can I create, modify, and delete tables and data?
  • How can I access databases from programs written in Python?

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.

Licence: Creative Commons Attribution 4.0 International

Keywords: Foundations of Data Science, SQL, jupyter-notebook

Target audience: Students

Resource type: e-learning

Version: 8

Status: Active

Prerequisites:

Introduction to SQL

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.

Date modified: 2024-09-26

Date published: 2021-10-11

Authors: Avans Hogeschool, Helena Rasche, The Carpentries

Contributors: Avans Hogeschool, Helena Rasche, The Carpentries

Scientific topics: Software engineering

Activity log