SQL queries contain complex joins, subqueries, and some aggregates

In the following section we will see an example of an advanced query:

Where we get the,

• Department's names with all the students for each department
• Students name separated with comma and
• Showing the department having at least three students in it

SELECT 
  d.DepartmentName,
  COUNT(s.StudentId) StudentsCount,
  GROUP_CONCAT(StudentName) AS Students
FROM Departments AS d 
INNER JOIN Students AS s ON s.DepartmentId = d.DepartmentId
GROUP BY d.DepartmentName
HAVING COUNT(s.StudentId) >= 3;

We added a JOIN clause to get the DepartmentName from the Departments table. After that we added a GROUP BY clause with two aggregate functions:

• "COUNT" to count the students for each department group.
• GROUP_CONCAT to concatenate students for each group with comma separated in one string.
• After the GROUP BY, we used the HAVING clause to filter the departments and select only those departments that have at least 3 students.

The result will be as following:

sqlite> SELECT
   ...>   d.DepartmentName,
   ...>   COUNT(s.StudentId) StudentsCount,
   ...>   GROUP_CONCAT(StudentName) AS Students
   ...> FROM Departments AS d
   ...> INNER JOIN Students AS s ON s.DepartmentId = d.DepartmentId
   ...> GROUP BY d.DepartmentName
   ...> HAVING COUNT(s.StudentId) >= 3;
DepartmentName  StudentsCount  Students
--------------  -------------  -----------------
IT              3              Michael,John,Jack
Physics         3              Sara,Sally,Nancy