hands-on tutorial

Hands-on for 'Advanced SQL' tutorial

The questions this addresses are:
- 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?

The objectives are:
- 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: data-science, SQL, jupyter-notebook

Target audience: Students

Resource type: hands-on tutorial

Authors: Avans Hogeschool, Helena Rasche, The Carpentries

Contributors: Avans Hogeschool, Helena Rasche, The Carpentries

Scientific topics: Software engineering


Activity log