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

- - -
2-) List the genders and student count of each gender

Solution 1)

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

ETİKETLER

- - - -
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

- - - -
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

- - - - -
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

- - - -
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

- - - - - - -
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

- - - - - - - -
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

- - -
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

- - - -
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

- - - - -
Library Database