SQL Sub Query 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-)

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 -

3-)

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 -

4-) List the name and surname of the students and the number of books they read sorted by BookCount. Also list the students who have never read a book.

Solution 1)

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

Solution 2)

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

ETİKETLER

Select - Top - Where - Having - Sub Query -

5-) Lists students who have not read books.

Solution 1)

Select students.* from students 
left join borrows on students.studentId = borrows.studentId 
where borrowsno is null

Solution 2)

Select * from students 
where studentId not in 
	(Select studentId from borrows)

ETİKETLER

Select - Where - Sub Query - Çoklu Tablo - Left Join -

6-) List books that have never been read

Solution 1)

Select books.* from books 
left join borrows on books.bookId = borrows.bookId 
where borrowsno is null

Solution 2)

Select * from books 
where bookId not in 
	(Select bookId from borrows)

ETİKETLER

Select - Where - Sub Query - Çoklu Tablo - Left Join -

7-) Delete students who do not read books.

Solution 1)

Delete from students 
where studentId not in 
	(Select studentId from borrows)

ETİKETLER

Delete - Where - Sub Query -

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

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

10-) Dram türündeki kitapların puanını 1 arttıran sorguyu yazınız.

Solution 1)

Update books set point += 1 
       where typeId = (Select typeId from books where name = 'Horror')

Solution 2)

Update books set point += 1 
       where typeId in (Select typeId from books where name = 'Horror')

ETİKETLER

Update - Where - Sub Query -