Sql Query Examples

1-)

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 -

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

Procedure - Declare -

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

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

Update -

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

Select - Group By - SQL Functions -

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

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

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

9-) List all students

Solution 1)

Select * from students

ETİKETLER

Select -

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

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

Solution 1)

Select avg(point) from students

ETİKETLER

Select - Aggragate -

12-) List the total point of all students.

Solution 1)

Select sum(point) from students

ETİKETLER

Select - Aggragate -

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

Solution 1)

Select * from students 
        where name like '_a%'

ETİKETLER

Select - Where - Like -

14-) List 3 students, randomly selected

Solution 1)

Select top 3 * from students 
       order by newid()

ETİKETLER

Select - Top - Order By -

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

16-) Delete the student whose number 5.

Solution 1)

Delete from students 
       where studentId = 5

ETİKETLER

Delete - Where -

17-) List the student whose age is the biggest

Solution 1)

Select * from students 
       order by birthdate

ETİKETLER

Select - Order By -

18-)

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 -

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

Procedure - Declare -

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

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

22-)

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 -

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

Select - Insert - Top - Order By -

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

Select - Alias -

25-) 10 tane yazar listeletiniz.

Solution 1)

Select top 10 * from authors

ETİKETLER

Select - Top -

26-) What is the minimum value of pageCount column

Solution 1)

Select min(pageCount) from books

ETİKETLER

Select - Aggragate -

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

28-) Öğrencilerin sadece adını, soyadını listeletiniz. Aynı isim soyisimde olanlar bir kere listelensin

Solution 1)

Select distinct name, surname from students

ETİKETLER

Select - Distinct -

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

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

Solution 1)

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

ETİKETLER

Update - Where -

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

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

Solution 1)

Select distinct class from students

ETİKETLER

Select - Distinct -

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

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

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

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

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

Solution 1)

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

ETİKETLER

Select - Where - Aggragate -

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

Select - Order By -

39-) 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
Execute AddTwoNumber 20,25,@r output
Select @r as Result

ETİKETLER

Procedure - Declare -

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

41-) List all students according to their names reverse

Solution 1)

Select * from students 
       order by name desc

ETİKETLER

Select - Order By -

42-)

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 -

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

Solution 1)

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

ETİKETLER

Select - Group By - Aggragate -

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

Solution 1)

Select top 5 * from students

ETİKETLER

Select - Top -

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

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

Solution 1)

Select distinct name from students

ETİKETLER

Select - Distinct -

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

Select - Group By - SQL Functions - Alias -

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

Solution 1)

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

ETİKETLER

Select - Where - Like -

49-) List count of the books on the library

Solution 1)

Select count(*) from books

Solution 2)

Select count(bookId) from books

ETİKETLER

Select - Aggragate -

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

51-)

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 -

52-)

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 -

53-) List the total page count for all books.

Solution 1)

Select sum(pageCount) from books

ETİKETLER

Select - Aggragate -

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

Procedure - Declare -

55-) 3 tane farklı kitap adı listeleyiniz.

Solution 1)

Select distinct top 3  name from books

ETİKETLER

Select - Top - Distinct -

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

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

Procedure - Declare -

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

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

Solution 1)

Select * from books 
       where bookId % 2 = 0

ETİKETLER

Select - Where -

60-)

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 -

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

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

63-)

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 -

64-)

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 -

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

66-) List the student whose name Bill

Solution 1)

Select * from students 
       where name = 'Bill'

ETİKETLER

Select - Where -

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

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

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

Solution 1)

Update students set point=100

ETİKETLER

Update -

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

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

Select - Insert - Procedure - Declare -

72-) Delete all borrows

Solution 1)

Delete from borrows

ETİKETLER

Delete -

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

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

Insert -

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

76-) What is the maximum points in the book table.

Solution 1)

Select max(point) from books

ETİKETLER

Select - Aggragate -

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

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

Solution 1)

Select * from students 
       where name like 'a%'

ETİKETLER

Select - Where - Like -

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

80-) List the count of students

Solution 1)

Select count(*) from students

Solution 2)

Select count(studentId) from students

ETİKETLER

Select - Aggragate -

81-) List all books

Solution 1)

Select * from books

ETİKETLER

Select -

82-) Delete the students whose name is Gray

Solution 1)

Delete from students 
       where name = 'Gray'

ETİKETLER

Delete - Where -

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

Procedure - Declare -

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

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

Solution 1)

Select * from students 
       order by sinif,gender

ETİKETLER

Select - Order By -

86-) Delete all books

Solution 1)

Delete from books

ETİKETLER

Delete -

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

Select - Group By - Aggragate - Alias -

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

Solution 1)

Select distinct top 5  name from students

ETİKETLER

Select - Top - Distinct -

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

Select - Group By - Having - Alias -

90-)

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 -

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

Insert -

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

Solution 1)

Select * from students 
       where name like '%a'

ETİKETLER

Select - Where - Like -

93-) List the students whose school number is odd

Solution 1)

Select * from students 
        where studentId % 2 = 1

ETİKETLER

Select - Where -

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

95-) List all students randomly ordered

Solution 1)

Select * from students 
       order by newid()

ETİKETLER

Select - Order By -

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

97-) List male students randomly ordered

Solution 1)

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

ETİKETLER

Select - Where - Order By -

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

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

100-) What is the average pageCount of all books.

Solution 1)

Select avg(pageCount) from books

ETİKETLER

Select - Aggragate -

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

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

Insert -

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

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

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

Procedure - Declare -

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

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

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

109-)

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 -

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

Solution 1)

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

ETİKETLER

Select - Where - Like -

111-) List the genders and student count of each gender

Solution 1)

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

ETİKETLER

Select - Group By - Aggragate - Alias -

112-) Add all authors to the students table

Solution 1)

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

ETİKETLER

Select - Insert -