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

##### 1-) List the student whose name Bill

Solution 1)

``````Select * from students
where name = 'Bill'``````

ETİKETLER

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

Solution 1)

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

ETİKETLER

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

Solution 1)

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

ETİKETLER

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

Solution 1)

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

ETİKETLER

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

Solution 1)

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

ETİKETLER

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

Solution 1)

``````Select * from students
where name like '_a%'``````

ETİKETLER

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

Solution 1)

``````Select * from students
where name like '%a_'``````

ETİKETLER

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

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

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

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

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

Solution 1)

``````Select * from books
where bookId % 2 = 0``````

ETİKETLER

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

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

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

- -
##### 16-) List the students whose school number is odd

Solution 1)

``````Select * from students
where studentId % 2 = 1``````

ETİKETLER

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

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

- - -
##### 19-) List male students randomly ordered

Solution 1)

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

ETİKETLER

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

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

Solution 1)

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

ETİKETLER

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

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

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

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

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

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

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

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

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

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

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

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

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

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

Solution 1)

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

ETİKETLER

- - -
##### 36-)

Solution 1)

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

ETİKETLER

- - - - -
##### 37-)

Solution 1)

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

ETİKETLER

- - - - -
##### 38-)

Solution 1)

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

ETİKETLER

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

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

- - - - - -
##### 41-)

Solution 1)

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

ETİKETLER

- - - - - -
##### 42-)

Solution 1)

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

ETİKETLER

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

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

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

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

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

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

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

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

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

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

- - - - -
Library Database