Count affiliations per university
Now that your data is ready for analysis, let's run some example SQL queries on the database. You'll now use already known concepts such as grouping by columns and joining tables.
In this exercise, you will find out which university has the most affiliations (through its professors). For that, you need both affiliations and professors tables, as the latter also holds the university_id.
As a quick repetition, remember that joins have the following structure:
SELECT table_a.column1, table_a.column2, table_b.column1, ...
FROM table_a
JOIN table_b
ON table_a.column = table_b.column
This results in a combination of table_a and table_b, but only with rows where table_a.column is equal to table_b.column.
This exercise is part of the course
Introduction to Relational Databases in SQL
Exercise instructions
- Count the number of total affiliations by university.
- Sort the result by that count, in descending order.
Hands-on interactive exercise
Have a go at this exercise by completing this sample code.
-- Count the total number of affiliations per university
SELECT ___(*), professors.university_id
FROM ___
JOIN ___
ON affiliations.professor_id = professors.id
-- Group by the university ids of professors
GROUP BY professors.___
___ ___ count DESC;