SQL Select Examples

1-) List all students

Solution 1)

Select * from students

ETİKETLER

Select -

2-) List all books

Solution 1)

Select * from books

ETİKETLER

Select -

3-) List the student whose name Bill

Solution 1)

Select * from students 
       where name = 'Bill'

ETİKETLER

Select - Where -

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

Solution 1)

Select * from students 
       where name like 'a%'

ETİKETLER

Select - Where - Like -

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

Solution 1)

Select * from students 
       where name like '%a'

ETİKETLER

Select - Where - Like -

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

Solution 1)

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

ETİKETLER

Select - Where - Like -

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

Solution 1)

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

ETİKETLER

Select - Where - Like -

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

Solution 1)

Select * from students 
        where name like '_a%'

ETİKETLER

Select - Where - Like -

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

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

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

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

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

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

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

Solution 1)

Select * from books 
       where bookId % 2 = 0

ETİKETLER

Select - Where -

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

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

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

19-) List the students whose school number is odd

Solution 1)

Select * from students 
        where studentId % 2 = 1

ETİKETLER

Select - Where -

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

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

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

23-) List all students according to their names reverse

Solution 1)

Select * from students 
       order by name desc

ETİKETLER

Select - Order By -

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

Solution 1)

Select * from students 
       order by sinif,gender

ETİKETLER

Select - Order By -

25-) List all students randomly ordered

Solution 1)

Select * from students 
       order by newid()

ETİKETLER

Select - Order By -

26-) List male students randomly ordered

Solution 1)

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

ETİKETLER

Select - Where - Order By -

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

28-) List 3 students, randomly selected

Solution 1)

Select top 3 * from students 
       order by newid()

ETİKETLER

Select - Top - Order By -

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

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

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

32-) List the student whose age is the biggest

Solution 1)

Select * from students 
       order by birthdate

ETİKETLER

Select - Order By -

33-) Add all authors to the students table

Solution 1)

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

ETİKETLER

Select - Insert -

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

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

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

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

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

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

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

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

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

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

44-)

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 -

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

46-) List the count of students

Solution 1)

Select count(*) from students

Solution 2)

Select count(studentId) from students

ETİKETLER

Select - Aggragate -

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

Solution 1)

Select sum(pageCount) from books

ETİKETLER

Select - Aggragate -

48-) List the total point of all students.

Solution 1)

Select sum(point) from students

ETİKETLER

Select - Aggragate -

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

Solution 1)

Select avg(point) from students

ETİKETLER

Select - Aggragate -

50-) What is the minimum value of pageCount column

Solution 1)

Select min(pageCount) from books

ETİKETLER

Select - Aggragate -

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

Solution 1)

Select max(point) from books

ETİKETLER

Select - Aggragate -

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

Solution 1)

Select avg(pageCount) from books

ETİKETLER

Select - Aggragate -

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

Solution 1)

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

ETİKETLER

Select - Where - Aggragate -

54-)

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 -

55-)

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 -

56-)

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 -

57-)

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 -

58-)

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 -

59-)

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 -

60-)

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 -

61-)

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 -

62-)

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 -

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

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

65-)

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 -

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

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

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

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

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

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

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

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

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

Select - Group By - SQL Functions -

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

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

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

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

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

Solution 1)

Select distinct name from students

ETİKETLER

Select - Distinct -

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

Solution 1)

Select distinct class from students

ETİKETLER

Select - Distinct -

81-) Öğ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 -

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

Solution 1)

Select top 5 * from students

ETİKETLER

Select - Top -

83-) 10 tane yazar listeletiniz.

Solution 1)

Select top 10 * from authors

ETİKETLER

Select - Top -

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

Solution 1)

Select distinct top 5  name from students

ETİKETLER

Select - Top - Distinct -

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

Solution 1)

Select distinct top 3  name from books

ETİKETLER

Select - Top - Distinct -