Teach: Advanced SQL — Retrieving Data through Aggregation and Joins

Advanced SQL — Extracting Data with Aggregation and Joins

Advanced SQL operations are needed to retrieve necessary information from a database whose tables have been separated through normalization. In this lesson, you will learn sorting (ORDER BY), aggregation (GROUP BY and aggregate functions), and joins (JOIN), which connect multiple tables. Once you can use these operations, you will be ready to begin practical data analysis.

Reviewing the Basic Form and Sorting

The basic form of SQL was SELECT column_name FROM table_name WHERE condition;. To sort the results, add ORDER BY. ORDER BY score DESC sorts scores from highest to lowest (descending order), while ASC (the default when omitted) sorts them from lowest to highest (ascending order).

Aggregate Functions — Counting, Summing, and Averaging

SQL provides aggregate functions that combine multiple rows to calculate a single value.

・COUNT(*): counts rows　・SUM(column): total　・AVG(column): average　・MAX(column)／MIN(column): maximum and minimum

GROUP BY — Aggregating Each Group

Using GROUP BY, you can group together rows that have the same value in a specified column and apply aggregate functions to each group. Calculations such as 「total sales for each product」 and 「average score for each class」 all use this form.

JOIN — Reconnecting Separated Tables

Tables separated through normalization are reconnected and used through joins (JOIN). The most basic type, an inner join (INNER JOIN), compares 2 tables using shared columns (a foreign key and a primary key) and connects matching rows side by side.

SELECT student_table.name, loan_table.loan_date
FROM loan_table
INNER JOIN student_table ON loan_table.student_id = student_table.student_id;

This SQL means 「for each row in the loan table, connect the row in the student table with the matching student ID, and retrieve the name and loan date」. Even if the loan table contains only student IDs, joining the tables produces a list that includes names. Separate with normalization, then connect with JOIN — this is the basic way to use a relational database.

Practice Problems

1. If a score table has 3 rows containing 70 points, 80 points, and 90 points, state the result of SELECT AVG(score) FROM score_table;.
2. State the result of executing SELECT product_name, COUNT(*) FROM sales_table GROUP BY product_name; on the sales table in the main text.
3. To retrieve a list of names and class names from the 「class table (class ID, class name)」 and the 「student table (student ID, name, class ID)」, state which column should be compared with which column when joining the tables.