Get startedGet started for free

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

View Course

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;
Edit and Run Code