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