Library Database Sql Query Examples
This article is written for library database sql queries. There are 30 query examples in this article. In the future I will add more queries. Follow me…
YOU MAY WANT TO SEE OUR ALL EXAMPLES PAGE, THEN CLICK HERE
Simple Sql Query Examples
Example 1: List the books where page count bigger than 200
|
1 2 |
Select * from books where pageCount > 200 |
Result Of The Query
Example 2: List the books where pageCount is between 100 and 200
|
1 2 |
Select * from books where pageCount between 100 and 200 |
Result Of The Query
Example 3: List the boys from 11a or 11b
|
1 2 3 |
Select * from students where (class = '11A' or class = '11B') and gender='M' |
Result Of The Query
Example 4: List the book which have biggest page count
|
1 2 |
Select top 1 * from books order by pageCount desc |
Solution-2
|
1 2 |
Select * from books where pageCount= (Select max(pageCount) from books) |
Result Of The Query
Example 5: List all students class names,but the same class names are listed ones.
|
1 2 |
Select distinct class from students |
Result Of The Query
Group by Statement Examples
Example 6: List the class names and student count of each class.
|
1 2 3 |
Select class,count(*) as StudentCount from students group by class |
Result Of The Query
Listed 20 rows
Example 7: List the numbers of boys and girls in each class.
|
1 2 3 |
Select class,gender,count(*) as StudentCount from students group by gender,class |
Result Of The Query
Example 8: List only the number of female students in each class.
|
1 2 3 4 |
Select class,gender,count(*) as StudentCount from students where gender = 'F' group by gender,class |
Result Of The Query
Example 9: List the class names and number of students which the number of students more than 30.
|
1 2 3 4 |
Select class,count(*) as StudentCount from students group by class having count(*) >= 30 |
Result Of The Query
Sql Complex Query Examples
Example 10: List all students name, surname, the name of the taken book,the taken date and the book’s type
|
1 2 3 4 5 |
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 |
Result Of The Query
Example 11: 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
|
1 2 3 4 5 6 |
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 |
Result Of The Query
Example 12: List the students name, surname, the name of the taken book and the taken time from 11B class
|
1 2 3 4 5 |
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 class='11B' |
Result Of The Query
Example 13: List the name and surname of the students and the number of books they read.
|
1 2 3 4 |
Select name,surname,count(*) BookCount from students join borrows on students.studentId = borrows.studentId group by students.studentId,name,surname |
Result Of The Query
Example 14: List the name and surname of the students and the number of books they read sorted by BookCount.
|
1 2 3 4 5 |
Select name,surname,count(*) BookCount from students join borrows on students.studentId = borrows.studentId group by students.studentId,name,surname order by BookCount |
Result Of The Query
Example 15: Lists students who have not read books.
|
1 2 3 |
Select students.* from students left join borrows on students.studentId = borrows.studentId where borrowId is null |
Result Of The Query
Example 16: List the books, that the student named John has read. The same records are listed ones.
|
1 2 3 4 |
Select distinct books.* from books join borrows on books.bookId = borrows.bookId join students on borrows.studentId = students.studentId where students.name = 'John' |
Result Of The Query
Listed 167 rows
Example 17: List the author that has the most books.
|
1 2 3 4 5 |
Select top 1 Authors.name,surname,count(*) as Quantity from books join authors on books.authorId = authors.authorId group by Authors.name,surname,Authors.authorId order by Quantity desc |
Result Of The Query
Result Of The Query
Example 18: List all student’s name,surname and the borrow’s taken date.
|
1 2 |
Select name,surname,takenDate from students join borrows on students.studentId = borrows.studentId |
Result Of The Query
Example 19: List all student’s name,surname,book’s name and the borrow’s taken date.(three table)
|
1 2 3 4 |
Select students.name,surname,books.name,takenDate from students join borrows on students.studentId = borrows.studentId join books on books.bookId = borrows.bookId |
Result Of The Query
Example 20: List all student’s name,surname and the borrow’s taken date. Students who do not read books are also listed.
|
1 2 3 |
Select name,surname,takenDate from students left join borrows on students.studentId = borrows.studentId |
Result Of The Query
SQL SUBQUERY EXAMPLES
Example 21: List the book with the most page number
|
1 2 |
Select * from books where pageCount=(Select max(pageCount) from books) |
Result Of The Query
Example 22: List students who have not read books.
|
1 2 3 |
Select * from students where studentId not in (Select studentId from borrows) |
Result Of The Query
Example 23: Delete students who do not read books.
|
1 2 |
Delete from students where studentId not in (Select studentId from borrows) |
Result Of The Query: 1 row(s) efected
Example 24: 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.
|
1 2 3 4 |
Select students.name,students.surname,count(borrows.borrowId) from students left join borrows on students.studentId = borrows.studentId group by students.studentId,students.name,students.surname order by count(borrows.borrowId) |
Result Of The Query
SQL INSERT STATEMENT EXAMPLES
Example 25: Add the writer named Zane Grey to the authors table
|
1 2 3 |
Insert into authors values('x','y') or Insert into authors(name,surname) values('x','y') |
Result: 1 row(s) effected
Example 26: Add the student named Ace Collins to the students table
|
1 2 3 |
Insert into students values ('x','y',null,null,null,null) Or Insert into students(name,surname) values ('x','y') |
Result: 1 row(s) effected
Example 27: Add all authors to the students table
|
1 |
Insert into ogrenci(name,surname) Select name,surname from authors |
Result: 110 row(s) effected
Example 28: Add the writers named Ernest Dowson and Mother Goose to the authors table
|
1 2 3 |
Insert into authors(name,surname) values('Ernest','Dowson'),('Mother','Goose') Or Insert into authors values ('Ernest','Dowson'),('Mother','Goose') |
Result: 2 row(s) effected
Example 29: Insert into the authors table, Random selected five students
|
1 2 |
Insert into authors(name,surname) Select top 5 name,surname from students order by newid() |
Result: 5 row(s) effected
