Classes with more than 5 students is the question asked in many interviews and listed on leetcode at 596. 

Let's start with SQL Schema for the table named Courses:


CREATE TABLE Courses (student varchar(255), class varchar(255))


INSERT INTO Courses (student, class) values ('A', 'Math')

INSERT INTO Courses (student, class) values ('B', 'English')

INSERT INTO Courses (student, class) values ('C', 'Math')

INSERT INTO Courses (student, class) values ('D', 'Biology')

INSERT INTO Courses (student, class) values ('E', 'Math')

INSERT INTO Courses (student, class) values ('F', 'Computer')

INSERT INTO Courses (student, class) values ('G', 'Math')

INSERT INTO Courses (student, class) values ('H', 'Math')

INSERT INTO Courses (student, class) values ('I', 'Math')


The table will look like this based on the data of courses:


student class
A Math
B English
C Math
D Biology
E Math
F Computer
G Math
H Math
I Math

Question: Write an SQL query to report all the classes that have at least five students.

The query result will be,


class
Math


You have the table and question to find all the classes that have at least five students.

Now, let's start with the Algorithm:

As we can see that there is a requirement of GROUP BY for each class count of students on the basis of class. 
We can use query,

SELECT class, COUNT(*) FROM courses GROUP BY class;

The table will look like,

class count(*)
Math 6
English 1
Biology 1
Computer 1


As you can see our table is a little sorted now we need to find a class has at least five students and it is very clear Math is a class that has more than 5 students but in query, we need to identify that. So the query will be,

SELECT class FROM courses GROUP BY class HAVING COUNT(*)>5;

This is the final query to get the required output. 

I hope you understood the algorithm, if you have any query or doubt please let us know in comment section.