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.
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
Example 18: List all student’s name,surname and the borrow’s taken date.
|
1 |
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
I appreciate your rendering these details for individuals seeking to get
more information on issues similar to this. Your blog was well written and very
well investigated, and that is very much appreciated.
I am generally looking for new sites to follow and read regularly.