SQL Where Examples

1-) List the student whose name Bill

Solution 1)

Select * from students 
       where name = 'Bill'

ETİKETLER

Select - Where -

2-) List the student whose name starts with "a" character

Solution 1)

Select * from students 
       where name like 'a%'

ETİKETLER

Select - Where - Like -

3-) List the student which name ends "a" character

Solution 1)

Select * from students 
       where name like '%a'

ETİKETLER

Select - Where - Like -

4-) List the student which name contains "a" character

Solution 1)

Select * from students 
       where name like '%a%'

ETİKETLER

Select - Where - Like -

5-) List the books that's pageCount Column contains 5 number

Solution 1)

Select * from books 
       where bookId like '%1%'

ETİKETLER

Select - Where - Like -

6-) List the students whose second character of name is a.

Solution 1)

Select * from students 
        where name like '_a%'

ETİKETLER

Select - Where - Like -

7-) List the students whose second character of name from end is "a".

Solution 1)

Select * from students 
       where name like '%a_'

ETİKETLER

Select - Where - Like -

8-) List the students whose school number between 20 and 30

Solution 1)

Select * from students 
       where studentId<= 20 and studentId< 30

Solution 2)

Select * from students 
       where studentId between 20 and 30

ETİKETLER

Select - Where - Between - Multi Condition -

9-) List the students whose name is "James" and surname dosn't contain "a" character

Solution 1)

select * from students 
     where name= 'James' and surname not like '%a%'

ETİKETLER

Select - Where - Like - Multi Condition -

10-) List the students whose name is "Jane" or "Kane" and school number less than 30

Solution 1)

select *from students 
     where (name='Kane' or name= 'Jane') and studentId <30

Solution 2)

Select * from students 
     where name='Kane' and studentId <30 or name='Jane' and studentId <30

Solution 3)

select * from students 
     where name in('Kane','Jane') and studentId<30

ETİKETLER

Select - Where - Brackets - Multi Condition -

11-) List the books with book numbers 3,4,8,9,11 and 23 in the books table

Solution 1)

Select * from books 
       where bookId in(3,4,8,9,11,23)

Solution 2)

Select * from books 
    where bookId = 3 or
	      bookId = 4 or 
		  bookId = 8 or 
		  bookId = 9 or 
		  bookId =11 or 
		  bookId = 23

ETİKETLER

Select - Where -

12-) List the books that's page count is even

Solution 1)

Select * from books 
       where bookId % 2 = 0

ETİKETLER

Select - Where -

13-) List the students whose name Perez and surname not contains "a" character

Solution 1)

select * from students 
       where name = 'Perez' and surname not like '%a%'

Solution 2)

select * from students 
       where name = 'Perez' and not surname like '%a%'

ETİKETLER

Select - Where - Multi Condition -

14-) List the students whose name Edwards or Baker and student number less than 30

Solution 1)

select * from students 
	where ("Edwards" or "Baker") and studentId<30

Solution 2)

select * from students 
	where name='Edwards' and studentId<30 or 
	      name='Baker' and studentId<30

ETİKETLER

Select - Where - Brackets - Multi Condition -

15-) List the students with student numbers 1,7,11 and 17 in the student table

Solution 1)

select * from students 
	where studentId=1 or studentId=7 or 
		  studentId=11 or studentId=17

Solution 2)

select * from student 
     where studentId in (1,7,11,17)

ETİKETLER

Select - Where -

16-) List the students whose school number is odd

Solution 1)

Select * from students 
        where studentId % 2 = 1

ETİKETLER

Select - Where -

17-) List the students whose school number is odd and name contains "a" character

Solution 1)

Select * from student 
       where name like 'a%' and studentId % 2 = 1

ETİKETLER

Select - Where - Multi Condition -

18-) List male students according to their names

Solution 1)

Select * from students 
       where gender = 'M' 
       order by name

Solution 2)

Select * from students 
       where gender = 'M' 
       order by 2

ETİKETLER

Select - Where - Order By -

19-) List male students randomly ordered

Solution 1)

Select * from students 
       where gender = 'M' 
       order by newid()

ETİKETLER

Select - Where - Order By -

20-) List the male students whose class "10A" randomly ordered

Solution 1)

select * from students 
	where sinif='10A' and gender='M' 
	order by newid()

ETİKETLER

Select - Where - Order By -

21-) List a student from 10A class, randomly selected

Solution 1)

Select top 1 * from students 
	where sinif= '10A' 
	order by newid()

ETİKETLER

Select - Top - Where - Order By -

22-) List a female student from 10A class, randomly selected

Solution 1)

Select top 1 * from students 
	where sinif= '10A' and gender= 'F' 
	order by newid()

ETİKETLER

Select - Top - Where - Order By - Multi Condition -

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

24-) Add the male students from class "9b" to the authors table

Solution 1)

Insert into authors(name,surname) 
       select name,surname from students
              where sinif='9A' and gender='M'

Solution 2)

Insert into authors 
       select name,surname from students 
              where sinif='9A' and gender='M'

ETİKETLER

Select - Insert - Where -

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

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

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

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

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

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

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

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

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

34-)

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 -

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

Solution 1)

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

ETİKETLER

Select - Where - Aggragate -

36-)

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 -

37-)

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 -

38-)

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 -

39-)

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 -

40-)

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 -

41-)

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 -

42-)

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 -

43-)

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 -

44-)

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 -

45-) Add the authors whose name contains "a" character from autors table to students table. The class of autors will have been '12M'

Solution 1)

Insert into students(name,surname,sinif) 
	Select name,surname,'12M' from authors where name like '%a%'

ETİKETLER

Select - Insert - Where -

46-)

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 -

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

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

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

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

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

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

53-) Delete the student whose number 5.

Solution 1)

Delete from students 
       where studentId = 5

ETİKETLER

Delete - Where -

54-) Delete the books, number of pages between 50 and 100 pages.

Solution 1)

Delete from books 
       where pageCount between 50 and 100

Solution 2)

Delete from books 
       where pagecount<=100 and pagecount>=50

ETİKETLER

Delete - Where -

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

56-) Delete the students whose name is Gray

Solution 1)

Delete from students 
       where name = 'Gray'

ETİKETLER

Delete - Where -

57-) Delete students whose name is Gray and surname is King

Solution 1)

Delete from students 
       where name = 'Gray' and surname = 'King'

ETİKETLER

Delete - Where - Multi Condition -

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

59-) Adı Ali olan öğrencilerin adını Veli olarak güncelleyiniz.

Solution 1)

Update students set name='Veli' 
       where name = 'Ali'

ETİKETLER

Update - Where -

60-) 20 Numaralı öğrencinin adını Süleyman olarak güncelleyiniz.

Solution 1)

Update students set name = 'Süleyman' where studentId = 20

ETİKETLER

Update - Where -

61-) Numarası 20 ile 30 arasında olan öğrencilerin sınıfını 10C olarak güncelleyiniz.

Solution 1)

Update students set sinif = '10C' where studentId between 20 and 30

ETİKETLER

Update - Where - Between -

62-) 10E sınıfının erkek öğrencilerinin puanını 5 puan arttıran sorguyu yazınız.

Solution 1)

Update students set point += 5 where sinif = '10E' and gender = 'E'

ETİKETLER

Update - Where - Multi Condition -

63-) 24 Numaralı öğrencinin adını Arzu, soyadını Çelik olarak güncelleyen sorguyu yazınız.

Solution 1)

Update students set name = 'Arzu', surname = 'Çelik' where studentId = 24

ETİKETLER

Update - Where -

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

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