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

1-) List all students name, surname from students table and takenDate from borrows table

Solution 1) 

Select name,surname,takenDate from students,borrows 
       where students.studentId = borrows.studentId

Solution 2) 

Select name,surname,takenDate from students 
       join borrows on students.studentId = borrows.studentId

ETİKETLER

Select - Join - Where - Çoklu Tablo -
2-) List all students name, surname, the name of the taken book and the taken time

Solution 1) 

Select students.name,students.surname,books.name,takenDate 
       from students,borrows,books 
       where students.studentId = borrows.studentId and 
                  books.bookId = borrows.bookId

Solution 2) 

Select students.name,students.surname,books.name,takenDate from students 
       join borrows on students.studentId = borrows.studentId 
       join books on books.bookId = borrows.bookId

ETİKETLER

Select - Join - Where - Çoklu Tablo -
3-) List all students name, surname, the name of the taken book,the taken date and the book's type

Solution 1) 

Select students.name as OgrenciAd,
	   students.surname,books.name as KitapAd,
	   takenDate,types.name as TurAd 
from students,borrows,books,types 
where students.studentId = borrows.studentId and 
	  books.bookId = borrows.bookId and 
	  books.typeId = types.typeId

Solution 2) 

Select students.name as studentName,
	   students.surname,books.name as BookName,
	   takenDate,types.name as TypeName 
from students 
join borrows on students.studentId = borrows.studentId 
join books on books.bookId = borrows.bookId
join types on books.typeId = types.typeId

ETİKETLER

Select - Join - Where - Çoklu Tablo -
4-) List all students name, surname, the name of the taken book, the taken date, the book's type and the name and surname of the author

Solution 1) 

Select students.name as studentName,
	   students.surname,books.name as BookName,
	   takenDate,types.name as TypeName, 
	   authors.name as AuthorName, authors.surname as AuthorSurname 
from students 
join borrows on students.studentId = borrows.studentId 
join books on books.bookId = borrows.bookId
join types on books.typeId = types.typeId
join authors on authors.authorId = books.authorId

Solution 2) 

Select students.name as studentName,students.surname,
           books.name as BookName,takenDate,types.name as TypeName ,
           authors.name as AuthorName,authors.surname as AuthorSurname 
from students, borrows, books, types, authors 
where students.studentId = borrows.studentId and 
           books.bookId = borrows.bookId and 
           books.typeId = types.typeId and  
           authors.authorId = books.authorId

ETİKETLER

Select - Join - Where - Çoklu Tablo -
5-) List the students name, surname, the name of the taken book and the taken time from 11B class

Solution 1) 

Select students.name as studentName,students.surname,
	   books.name as BookName,takenDate
from students,borrows,books,types,authors 
where students.studentId = borrows.studentId and 
	  books.bookId = borrows.bookId and sinif='11B'

Solution 2) 

Select students.name as studentName,students.surname,
	   books.name as BookName,takenDate
from students 
join borrows on students.studentId = borrows.studentId 
join books on books.bookId = borrows.bookId 
where sinif='11B'

ETİKETLER

Select - Join - Where - Çoklu Tablo -
6-) List the female students name, surname, the name of the taken book and the taken time from 11B class

Solution 1) 

Select students.name as studentName,students.surname,
	   books.name as BookName,takenDate
from students, borrows, books, types, authors 
where students.studentId = borrows.studentId and 
	  books.bookId = borrows.bookId and 
	  sinif='11B' and gender='F'

Solution 2) 

Select students.name as studentName,students.surname,
	   books.name as BookName,takenDate
from students 
join borrows on students.studentId = borrows.studentId 
join books on books.bookId = borrows.bookId 
where sinif='11B' and gender='F'

ETİKETLER

Select - Join - Where - Multi Condition - Çoklu Tablo -
7-) List the names, surnames and the names of the authors who wrote "Drama" type.

Solution 1) 

Select authors.name, authors.surname, books.name as bookName from authors 
join books on authors.authorId = books.authorId 
join types on types.typeId = books.typeId 
where types.name = 'Drama'

Solution 2) 

Select authors.name, authors.surname, books.name as bookName 
from authors, books, types
where authors.authorId = books.authorId and  
	  types.typeId = books.typeId and 
	  types.name = 'Drama'

ETİKETLER

Select - Join - Where - Multi Condition - Çoklu Tablo -
8-) List the book's name and its author information, its page count must be more than 300

Solution 1) 

Select books.name as bookName, authors.name, authors.surname 
from authors 
join books on authors.authorId = books.authorId 
where pagecount >300

Solution 2) 

Select authors.name, authors.surname, books.name as bookName 
from authors,books
where authors.authorId = books.authorId and pagecount >300

ETİKETLER

Select - Join - Where - Multi Condition - Çoklu Tablo -
9-) List the student's information who reads the book it's author name 'Isaac Asimov'

Solution 1) 

Select distinct students.* from students 
join borrows on students.studentId = borrows.studentId
join books on books.bookId = borrows.bookId
join authors on authors.authorId = books.authorId  
where authors.name = 'Isaac' and authors.surname = 'Asimov'

Solution 2) 

Select distinct students.* 
from students, borrows, books, authors
where students.studentId = borrows.studentId and 
	  books.bookId = borrows.bookId and 
	  authors.authorId = books.authorId and 
	  authors.name = 'Isaac' and authors.surname = 'Asimov'

ETİKETLER

Select - Join - Where - Multi Condition - Çoklu Tablo -
10-)

Solution 1) 

Select distinct students.* from students 
join borrows on students.studentId = borrows.studentId
join books on books.bookId = borrows.bookId
join authors on authors.authorId = books.authorId  
where authors.name = 'Fyodor' and authors.surname = 'Dostoevsky' and sinif = '10A'

Solution 2) 

Select distinct students.* 
from students, borrows, books, authors
where students.studentId = borrows.studentId and 
      books.bookId = borrows.bookId and 
	  authors.authorId = books.authorId and 
	  authors.name = 'Fyodor' and authors.surname = 'Dostoevsky' and 
	  sinif = '10A'

ETİKETLER

Select - Join - Where - Multi Condition - Sub Query - Çoklu Tablo -
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-)

Solution 1) 

Select top 1 students.*,takendate 
from students,borrows 
where students.studentId = borrows.studentId 
order by borrows.takenDate desc

Solution 2) 

Select top 1 students.*,takendate 
from students 
join borrows on students.studentId = borrows.studentId 
order by borrows.takenDate desc

Solution 3) 

Select students.*,takendate 
from students 
join borrows on students.studentId = borrows.studentId 
where takenDate = (Select max(takenDate) from borrows)

ETİKETLER

Select - Top - Join - Where - Order By - Sub Query - Çoklu Tablo -
21-) 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 -
22-) 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 -
Library Database
Database