Question

Write a query to display list of department names and number of students in that department, if student exist Give an alias to the count as student count. Sort the record based on
department name.​

Answer 1

Assuming the table name to be Students, the column holding the department names as Department and the column holding the names of the students as Names, the query would be:

Select distinct department, count(name) as 'Student count' from Students group by department order by department;

• Where is a clause used to specify conditions that the records must satisfy before being displayed.
• Select is used to retrieve/display information from the table.
• Distinct is a clause used to eliminate data redundancy.
• Count is an aggregate function used to count the number of records in the specified column.
• Group by is a clause used to group the records in a particular manner.
• Order by is a clause used to sort the data in order, depending on their datatype.

An easy approach to the usage of the group by clause would be to look for the word(s) 'each'/'-wise'/'number of records in a particular section/etc'. It must also be kept in mind that group by can only be used with aggregate functions, in this case, it being count.