The Queries in this page solved at the library database. It is a sample database generated by randomly records. You can download it from the link. You will see more than 500 examples in the future. You can filter from the search panel above by selecting sql statements. I hope you will like it

SQL Group By Examples

1-) List the class names and student count of each class.

Solution 1) 

Select sinif,count(*) as StudentCount 
from students 
group by sinif

ETİKETLER

Select - Group By - Aggragate -
2-) List the genders and student count of each gender

Solution 1) 

Select gender,count(*) as StudentCount 
from students 
group by gender

ETİKETLER

Select - Group By - Aggragate - Alias -
3-) List the numbers of boys and girls in each class.

Solution 1) 

Select sinif,gender,count(*) as StudentCount 
from students 
group by gender,sinif

ETİKETLER

Select - Group By - Aggragate - Alias -
4-) List only the number of female students in each class.

Solution 1) 

Select sinif,gender,count(*) as StudentCount 
from students 
where gender = 'F'
group by gender,sinif

ETİKETLER

Select - Where - Group By - Aggragate - Alias -
5-) List the class names and number of students which the number of students more than 30.

Solution 1) 

Select sinif,count(*) as StudentCount 
from students 
group by sinif
having count(*) >= 30

ETİKETLER

Select - Group By - Having - Alias -
6-) List the name and surname of the students and the number of books they read.

Solution 1) 

Select name,surname,count(*) BookCount 
from students,borrows 
where students.studentId = borrows.studentId 
group by students.studentId,name,surname

Solution 2) 

Select name,surname,count(*) BookCount 
from students
join borrows on students.studentId = borrows.studentId 
group by students.studentId,name,surname

ETİKETLER

Select - Join - Where - Group By - Aggragate - Alias - Çoklu Tablo -
7-) List the name and surname of the students and the number of books they read sorted by BookCount.

Solution 1) 

Select name,surname,count(*) BookCount 
from students
join borrows on students.studentId = borrows.studentId 
group by students.studentId,name,surname
order by BookCount desc

Solution 2) 

Select name,surname,count(*) BookCount 
from students,borrows 
where students.studentId = borrows.studentId 
group by students.studentId,name,surname
order by BookCount desc

ETİKETLER

Select - Join - Where - Group By - Order By - Aggragate - Alias - Çoklu Tablo -
8-) List the number of books read grouped by date quarter

Solution 1) 

Select Year(takendate) as Year, datepart(qq,takendate) as Quarter,count(*) as Count 
from borrows 
group by Year(takendate),datepart(qq,takendate)

ETİKETLER

Select - Group By - SQL Functions -
9-) List the student count gruped by birthdate quarter

Solution 1) 

Select Year(birthdate) as Year, datepart(qq,birthdate) as Quarter,count(*) as Count 
from students 
group by Year(birthdate),datepart(qq,birthdate)

ETİKETLER

Select - Group By - SQL Functions - Alias -
10-) Delete students who have read less than 5 books.

Solution 1) 

Delete from students 
where studentId in (Select studentId from borrows 
                group by studentId 
				having count(*)<5)

ETİKETLER

Delete - Where - Group By - Having - Sub Query -
Library Database
Database