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

1-) List the book with the most page number

Solution 1) 

Select top 1 * from books 
       order by sayfaSayisi desc

Solution 2) 

Select * from books 
     where sayafaSayisi=(Select max(sayfaSayisi) from books)

ETİKETLER

Select - Top - Where - Order By - Sub Query - Aggragate -
2-) List count of the books on the library

Solution 1) 

Select count(*) from books

Solution 2) 

Select count(bookId) from books

ETİKETLER

Select - Aggragate -
3-) List the count of students

Solution 1) 

Select count(*) from students

Solution 2) 

Select count(studentId) from students

ETİKETLER

Select - Aggragate -
4-) List the total page count for all books.

Solution 1) 

Select sum(pageCount) from books

ETİKETLER

Select - Aggragate -
5-) List the total point of all students.

Solution 1) 

Select sum(point) from students

ETİKETLER

Select - Aggragate -
6-) What is the average of all students' points.

Solution 1) 

Select avg(point) from students

ETİKETLER

Select - Aggragate -
7-) What is the minimum value of pageCount column

Solution 1) 

Select min(pageCount) from books

ETİKETLER

Select - Aggragate -
8-) What is the maximum points in the book table.

Solution 1) 

Select max(point) from books

ETİKETLER

Select - Aggragate -
9-) What is the average pageCount of all books.

Solution 1) 

Select avg(pageCount) from books

ETİKETLER

Select - Aggragate -
10-) List the students count of the '9B' Class

Solution 1) 

Select count(*) from students 
       where sinif = '9B'

ETİKETLER

Select - Where - Aggragate -
11-)

Solution 1) 

Select sum(pageCount) from books 
join types on types.typeId = books.typeId 
where types.name = 'Romance'

ETİKETLER

Select - Join - Where - Aggragate - Çoklu Tablo -
12-)

Solution 1) 

Select count(*) from books 
join types on types.typeId = books.typeId 
where types.name = 'Horror'

ETİKETLER

Select - Join - Where - Aggragate - Çoklu Tablo -
13-)

Solution 1) 

Select avg(pageCount) from books 
join types on types.typeId = books.typeId 
where types.name = 'Comics'

ETİKETLER

Select - Join - Where - Aggragate - Çoklu Tablo -
14-)

Solution 1) 

Select sum(pageCount) from books 
join authors on authors.authorId = books.authorId 
where authors.name = 'John' and authors.surname = 'DosPassos'

ETİKETLER

Select - Join - Where - Aggragate - Çoklu Tablo -
15-)

Solution 1) 

Select sum(point) from books 
join authors on authors.authorId = books.authorId 
where authors.name = 'Emily' and authors.surname = 'Dickinson'

ETİKETLER

Select - Join - Where - Multi Condition - Aggragate - Çoklu Tablo -
16-)

Solution 1) 

Select count(*) from books 
join authors on authors.authorId = books.authorId 
where authors.name = 'Edna' and authors.surname = 'Ferber'

ETİKETLER

Select - Join - Where - Multi Condition - Aggragate - Çoklu Tablo -
17-)

Solution 1) 

Select count(*) from students 
join borrows on students.studentId = borrows.studentId 
where students.name = 'Alice' and students.surname = 'Wood'

ETİKETLER

Select - Join - Where - Multi Condition - Aggragate - Çoklu Tablo -
18-)

Solution 1) 

Select sum(pageCount) from students 
join borrows on students.studentId = borrows.studentId 
join books on books.bookId = borrows.bookId 
where students.name = 'Ainsley' and students.surname = 'Cooper'

ETİKETLER

Select - Join - Where - Multi Condition - Aggragate - Çoklu Tablo -
19-)

Solution 1) 

Select count(distinct authorId) from students 
join borrows on students.studentId = borrows.studentId 
join books on books.bookId = borrows.bookId 
where students.name = 'Ida' and students.surname = 'Gray'

ETİKETLER

Select - Distinct - Join - Where - Multi Condition - Aggragate - Çoklu Tablo -
20-) 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 -
21-) 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 -
22-) 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 -
23-) 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 -
24-) 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 -
25-) 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 -
26-) Sayfasayısı en fazla oaln kitapbın puanını 10 arttırın

Solution 1) 

Update books set point +=10 
       where pagecount =  (Select MAX(pagecount) from books)

ETİKETLER

Update - Where - Sub Query - Aggragate -
Library Database
Database