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

- - - - - -
2-) List count of the books on the library

Solution 1)

``Select count(*) from books``

Solution 2)

``Select count(bookId) from books``

ETİKETLER

- -
3-) List the count of students

Solution 1)

``Select count(*) from students``

Solution 2)

``Select count(studentId) from students``

ETİKETLER

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

Solution 1)

``Select sum(pageCount) from books``

ETİKETLER

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

Solution 1)

``Select sum(point) from students``

ETİKETLER

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

Solution 1)

``Select avg(point) from students``

ETİKETLER

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

Solution 1)

``Select min(pageCount) from books``

ETİKETLER

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

Solution 1)

``Select max(point) from books``

ETİKETLER

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

Solution 1)

``Select avg(pageCount) from books``

ETİKETLER

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

Solution 1)

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

ETİKETLER

- - -
11-)

Solution 1)

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

ETİKETLER

- - - - -
12-)

Solution 1)

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

ETİKETLER

- - - - -
13-)

Solution 1)

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

ETİKETLER

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

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

- - - - - -
16-)

Solution 1)

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

ETİKETLER

- - - - - -
17-)

Solution 1)

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

ETİKETLER

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

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

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

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

Solution 1)

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

ETİKETLER

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

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

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

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

- - - - - - - -
Library Database