Write an SQL query to report all the classes that
have at least five students. Return the result table in any order.
table name: customers
Input:
Solution Explanation:
- Apply group by clause on class column. It will divide the rows on different groups for each unique class.
select class from courses group by class
- Then count number of student for each class.
select count(student),class from courses group by class
- Apply where clause to get only those rows which count(student) value is greater than or equal to 5.
select count(student),class from courses group by class having count(student)>=5
Final Solution:
select class from courses group by class having count(student)>=5
Output:
SQL Script:
CREATE TABLE [dbo].[courses](
[student][varchar](50) NULL,
[class][varchar](50) NULL
) ON [PRIMARY]
GO
INSERT [dbo].[courses] ([student], [class]) VALUES (N'A', N'Math')
GO
INSERT [dbo].[courses] ([student], [class]) VALUES (N'B', N'English')
GO
INSERT [dbo].[courses] ([student], [class]) VALUES (N'C', N'Math')
GO
INSERT [dbo].[courses] ([student], [class]) VALUES (N'D', N'Biology')
GO
INSERT [dbo].[courses] ([student], [class]) VALUES (N'E', N'Math')
GO
INSERT [dbo].[courses] ([student], [class]) VALUES (N'F', N'Computer')
GO
INSERT [dbo].[courses] ([student], [class]) VALUES (N'G', N'Math')
GO
INSERT [dbo].[courses] ([student], [class]) VALUES (N'H', N'Math')
GO
INSERT [dbo].[courses] ([student], [class]) VALUES (N'I', N'Math')
GO