In this page there are more than 100 Sql Query Examples With Answers. You can find any examples easily with sql query search engine. To use it will be very interesting for you. If you want to solve the queries, you can download the sample library database with more than 10000 records data. To download the database click here.

# Sql Query Examples With Answers

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

Solution 1)

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

ETİKETLER

- - - -
##### 2-) List the numbers of boys and girls in each class.

Solution 1)

``````Select sinif,gender,count(*) as StudentCount
from students
group by gender,sinif``````

ETİKETLER

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

Solution 1)

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

ETİKETLER

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

- - - - - - - -
##### 5-) List the genders and student count of each gender

Solution 1)

``````Select gender,count(*) as StudentCount
from students
group by gender``````

ETİKETLER

- - - -
##### 6-) Öğrenci isimlerini listeleyiniz. Aynı isimler bir kere listelensin

Solution 1)

``Select distinct name from students``

ETİKETLER

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

Solution 1)

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

ETİKETLER

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

- - - -
##### 9-)

Solution 1)

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

ETİKETLER

- - - - - -
##### 10-) Generate Unique Random Numbers In Sql With Stored Procedure

Solution 1)

``````Create procedure randomGenerate(@unit int,@min int,@max int)
as
Begin
Declare @numbers table(number int)
Declare @i int = 0
Declare @number int
while (@i<@unit)
Begin
Set @number = floor(rand()*(@max-@min+1))+@min
if(not exists(Select * from @numbers where number = @number))
begin
insert into @numbers values(@number)
Set @i = @i + 1
end
end
Select * from @numbers order by 1
End
--To Execute Stored Procedure
Execute randomGenerate 5,20,30``````

ETİKETLER

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

Solution 1)

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

ETİKETLER

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

Solution 1)

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

ETİKETLER

- - -
##### 13-) What is the average pageCount of all books.

Solution 1)

``Select avg(pageCount) from books``

ETİKETLER

- -
##### 14-)

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

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

- - - -
##### 16-)

Solution 1)

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

ETİKETLER

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

- - - - -
##### 18-)

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

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

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

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

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

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

Solution 1)

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

ETİKETLER

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

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

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

Solution 1)

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

ETİKETLER

- -
##### 27-) Add all authors to the students table

Solution 1)

``````Insert into students(name,surname)
Select name,surname from authors``````

ETİKETLER

- -
##### 28-) 3 tane farklı kitap adı listeleyiniz.

Solution 1)

``Select distinct top 3  name from books``

ETİKETLER

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

- -
##### 30-) Stored Procedure To Divide Numbers And Get Divided Value And Remaining Value

Solution 1)

``````Create Procedure sp_Devide(@n1 int,@n2 int,@division int output,@remaining int output) as
Begin
SEt @division = 0
While(@n2<=@n1)
Begin
Set @n1 = @n1-@n2
Set @division+=1
End
Set @remaining = @n1
End
--To Execute the Stored Procedure
Declare @d int,@r int
Execute sp_Devide 34,7,@d output,@r output
Select @d Division,@r Remaining``````

ETİKETLER

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

Solution 1)

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

ETİKETLER

- - -

Solution 1)

``Select distinct name, surname from students``

ETİKETLER

- -
##### 33-) Sınıf isimlerini listeleyiniz. Aynı sınıf ismi bir kere listelensin?

Solution 1)

``Select distinct class from students``

ETİKETLER

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

- - - -
##### 35-) List 3 students, randomly selected

Solution 1)

``````Select top 3 * from students
order by newid()``````

ETİKETLER

- - -
##### 36-) Insert into the authors table, Random selected five students

Solution 1)

``````Insert into authors(name,surname)
Select top 5 name,surname from students order by newid()``````

ETİKETLER

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

- - - - - - -
##### 38-) Bütün öğrencilerin puanını 100 olarak güncelleyiniz.

Solution 1)

``Update students set point=100``

ETİKETLER

-
##### 39-) Calculating Factorial of Given Number with Stored Procedure

Solution 1)

``````Create procedure factor(@number int)
as begin
Declare @i int = 1,@result int=1
while (@i<=@number)
Begin
Set @result = @result * @i
Set @i += 1
End
Select @result
End``````

ETİKETLER

- -
##### 40-) List all books

Solution 1)

``Select * from books``

ETİKETLER

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

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

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

- - - - -
##### 44-) 5 tane farklı öğrenci adı listeletiniz.

Solution 1)

``Select distinct top 5  name from students``

ETİKETLER

- - -
##### 45-) Add the writers named Ernest Dowson and Mother Goose to the authors table

Solution 1)

``````Insert into authors(name,surname)
values('Ernest','Dowson'),
('Mother','Goose')``````

Solution 2)

``````Insert into authors
values ('Ernest','Dowson'),
('Mother','Goose')``````

ETİKETLER

-
##### 46-) Delete all borrows

Solution 1)

``Delete from borrows``

ETİKETLER

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

- - - - -
##### 48-) What is the maximum points in the book table.

Solution 1)

``Select max(point) from books``

ETİKETLER

- -
##### 49-) Add the writer named Zane Grey to the authors table

Solution 1)

``````Insert into authors
values('x','y')``````

Solution 2)

``````Insert into authors(name,surname)
values('x','y')``````

ETİKETLER

-
##### 50-) Delete students who do not read books.

Solution 1)

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

ETİKETLER

- - -
##### 51-) Delete the student whose number 5.

Solution 1)

``````Delete from students
where studentId = 5``````

ETİKETLER

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

Solution 1)

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

ETİKETLER

- -
##### 53-) List the count of students

Solution 1)

``Select count(*) from students``

Solution 2)

``Select count(studentId) from students``

ETİKETLER

- -
##### 54-) Öğrenci tablosundan sadece 5 kayıt listeletiniz.

Solution 1)

``Select top 5 * from students``

ETİKETLER

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

- - - -
##### 56-) Finding The Sum Of The Three Numbers With Stored Procedure

Solution 1)

``````Create Procedure sumThree
@n1 int,
@n2 int,
@n3 int,
@result int output as
Begin
Set @result = @n1+@n2+@n3
End
--To Execute The Procedure
Declare @result int
Execute sumThree 5,30,12,@result output
Select @result``````

ETİKETLER

- -
##### 57-) Add the student named Ace Collins to the students table

Solution 1)

``````Insert into students
values ('x','y',null,null,null,null)``````

Solution 2)

``````Insert into students(name,surname)
values ('x','y')``````

ETİKETLER

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

- - -
##### 59-) Create A Stored Procedure Calculate Power Of A Number In Sql

Solution 1)

``````Create Procedure myPower(@num int,@pow int, @result int output) As
Begin
Declare @i int = 0;
Set @result = 1
while(@i<@pow)
Begin
Set @result = @result * @num
Set @i += 1
End
End``````

ETİKETLER

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

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

- - - -
##### 62-) 10 tane yazar listeletiniz.

Solution 1)

``Select top 10 * from authors``

ETİKETLER

- -
##### 63-) List all students

Solution 1)

``Select * from students``

ETİKETLER

-
##### 64-) List count of the books on the library

Solution 1)

``Select count(*) from books``

Solution 2)

``Select count(bookId) from books``

ETİKETLER

- -
##### 65-)

Solution 1)

``````Select sum(point) from books
join authors on authors.authorId = books.authorId
where authors.name = 'Emily' and authors.surname = 'Dickinson'``````

ETİKETLER

- - - - - -
##### 66-)

Solution 1)

``````Select sum(pageCount) from books
join authors on authors.authorId = books.authorId
where authors.name = 'John' and authors.surname = 'DosPassos'``````

ETİKETLER

- - - - -
##### 67-)

Solution 1)

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

ETİKETLER

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

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

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

- - -
##### 71-) List the total page count for all books.

Solution 1)

``Select sum(pageCount) from books``

ETİKETLER

- -
##### 72-) List the students according to their names

Solution 1)

``````Select * from students
order by name``````

Solution 2)

``````Select * from students
order by 2``````

ETİKETLER

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

- - -
##### 74-) List the number of books read grouped by date quarter

Solution 1)

``````Select Year(takendate) as Year, datepart(qq,takendate) as Quarter,count(*) as Count
from borrows
group by Year(takendate),datepart(qq,takendate)``````

ETİKETLER

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

Solution 1)

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

ETİKETLER

- - -
##### 76-) The Stored Procedure That Selects Random Number Between the Two Numbers Entered as Parameters In Sql

Solution 1)

``````Create Procedure Sp_Random_Value
@first int,
@second int,
@result int output
As
Begin
Set @result =Floor(RAND() * (@second-@first))+@first
End
--To Execute the Procedure
Declare @r int
Execute Sp_Random_Value 20,30,@r output
Select @r``````

ETİKETLER

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

- - - - - -
##### 78-) Delete students whose name is Gray and surname is King

Solution 1)

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

ETİKETLER

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

Solution 1)

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

ETİKETLER

- - -
##### 80-) List the class names and student count of each class.

Solution 1)

``````Select sinif,count(*) as StudentCount
from students
group by sinif``````

ETİKETLER

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

- -
##### 82-) Calling a Stored Procedure To Add Two Numbers With Input Output Parameters Example

Solution 1)

``````Create Procedure AddTwoNumber(@p1 int,@p2 int,@Result int output)
as
Begin
Set @Result = @p1+ @p2
End
--To Execute The Procedure
Declare @r int
Select @r as Result``````

ETİKETLER

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

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

- - -
##### 85-) List the total point of all students.

Solution 1)

``Select sum(point) from students``

ETİKETLER

- -
##### 86-) Delete all books

Solution 1)

``Delete from books``

ETİKETLER

-
##### 87-)

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

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

Solution 1)

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

ETİKETLER

- - -
##### 89-) List the student count gruped by birthdate quarter

Solution 1)

``````Select Year(birthdate) as Year, datepart(qq,birthdate) as Quarter,count(*) as Count
from students
group by Year(birthdate),datepart(qq,birthdate)``````

ETİKETLER

- - - -
##### 90-) List all students according to their names reverse

Solution 1)

``````Select * from students
order by name desc``````

ETİKETLER

- -
##### 91-)

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

- - - - - - -
##### 92-) What is the average of all students' points.

Solution 1)

``Select avg(point) from students``

ETİKETLER

- -
##### 93-) List the class names and number of students which the number of students more than 30.

Solution 1)

``````Select sinif,count(*) as StudentCount
from students
group by sinif
having count(*) >= 30``````

ETİKETLER

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

- -

Solution 1)

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

ETİKETLER

- -
##### 96-) List the student whose age is the biggest

Solution 1)

``````Select * from students
order by birthdate``````

ETİKETLER

- -
##### 97-) List all students according to their class then by name

Solution 1)

``````Select * from students
order by sinif,gender``````

ETİKETLER

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

- - - -
##### 99-) Stored Procedure To Find A Number Is Prime In Sql

Solution 1)

``````Create Procedure sp_isPrime (@number int,@result bit output) as
Begin
Set @result = 1
Declare @i int = 2
While (@i<@number)
Begin
if(@number % @i = 0)
Begin
Set @result = 0
break
End
Set @i += 1
End
return @result
End
--To Execute The Stroed Procedure
Declare @result bit
Execute sp_isPrime 11,@result output
Select @result``````

ETİKETLER

- -
##### 100-) List all students randomly ordered

Solution 1)

``````Select * from students
order by newid()``````

ETİKETLER

- -
##### 101-)

Solution 1)

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

ETİKETLER

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

- - - -
##### 103-) Bütün kitapların puanını 5 puan arttırınız.

Solution 1)

``Update books set point +=5``

Solution 2)

``Update books set point = point + 5``

ETİKETLER

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

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

- - - - -
##### 106-) List all students by mergin their name and surname

Solution 1)

``Select name + surname from students``

Solution 2)

``Select name + surname as ns from students``

Solution 3)

``Select name + ' ' + surname as ns from students``

ETİKETLER

- -
##### 107-) What is the minimum value of pageCount column

Solution 1)

``Select min(pageCount) from books``

ETİKETLER

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

Solution 1)

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

ETİKETLER

- - -
##### 109-)

Solution 1)

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

ETİKETLER

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

- - - - -
##### 111-) Delete the students whose name is Gray

Solution 1)

``````Delete from students
where name = 'Gray'``````

ETİKETLER

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

- - - -
Library Database