SQL Multi Condition Examples

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

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

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

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

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

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

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

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

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

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

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

12-)

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 -

13-)

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 -

14-)

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 -

15-)

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 -

16-)

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 -

17-)

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 -

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

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