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 StudentsFROM Departments AS d INNER JOIN Students AS s ON s.DepartmentId = d.DepartmentIdGROUP BY d.DepartmentNameHAVING 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,JackPhysics         3              Sara,Sally,Nancy