Introduction

As we all know, database plays a vital role to persist the data. Almost every application uses a database to persist its records. The mostly used Relational databases are SQL Server, Oracle, and MySQL. The mechanism, use to fetch data from various tables in the database is called query. All mentioned database uses queries to fetch records with the tiny difference in syntax.

Audience
  • Student
  • Developer
  • Interviewer
Background

Sometimes, we forget to write queries due to syntax memorization at the time of interview, training or development; although we have a basic understanding of that query and have already implemented those queries in our previous work experience. To keep these points which become hazardous in an interview, I decided to write some understanding pertaining to queries in a scenario-based manner which can be equally fruitful for not only experienced resources but also for new professionals and students.

Database

For our discussion, we have to develop a basic database along with a few relations and at last, we will populating some data in it. Here, we are considering the basic Library Management Systems.

Tables

We have four tables

  • BookCategory
  • Book
  • Student
  • BookBorrow

For a bit experienced resources, the tables are self-explanatory that we have a table of books. Each book belongs to particular book category. A student will borrow the book that will be mentioned in the bookBorrow table which will hold borrowed books information.

Relationship Between Tables

Attachment

The scripts of database tables along with data are attached.

Queries

Basic Queries

The first and basic scenario would be simple records fetching.

  1. SELECT * FROM BOOK
  2. SELECT * FROM STUDENT
  3. SELECT * FROM BOOKCATEGORY
  4. SELECT * FROM BOOKBORROW

Where clause

If we want to restrict result or apply some filtration on data.

  1. SELECT * FROM BOOK WHERE BOOKID = 1
  2. SELECT * FROM STUDENT WHERE STUDENTID = 1
  3. SELECT * FROM BOOKCATEGORY WHERE BOOKCATEGORYNAME = 'DATABASE'
  4. SELECT * FROM BOOKBORROW WHERE STUDENTID = 3

Order clause

The arrangement of the result set in ascending and descending way.

  1. SELECT * FROM BOOK ORDER BY BOOKNAME, BOOKID DESC
  2. SELECT * FROM STUDENT ORDER BY STUDENTDESCIPLINE, STUDENTNAME
  3. SELECT * FROM BOOKCATEGORY ORDER BY BOOKCATEGORYNAME
  4. SELECT * FROM BOOKBORROW ORDER BY STUDENTID, BOOKID

Join Result

In most of the cases, we are required to fetch data from multiple tables. I have tried to cover few cases like

Fetch Student’s id, name along with book id and book borrow id (student name with book id borrowed by the student),

  1. SELECT S.STUDENTID, S.STUDENTNAME, B.BOOKID, B.BOOKBORROWID
  2. FROM STUDENT S
  3. INNER JOIN BOOKBORROW B
  4. ON S.STUDENTID = B.STUDENTID
  5. WHERE S.STUDENTID = 1

Fetch Book name with their Category,

  1. SELECT B.BOOKID, B.BOOKNAME, C.BOOKCATEGORYID, C.BOOKCATEGORYNAME
  2. FROM BOOK B INNER JOIN BOOKCATEGORY C
  3. ON B.BOOKCATEGORYID = C.BOOKCATEGORYID

Fetch student name, book name and book borrow id (the books borrowed by the student),

  1. SELECT BB.BOOKBORROWID, BB.BOOKID, B.BOOKNAME, BB.STUDENTID, S.STUDENTNAME, BB.BORROWDATE, BB.BOOKRETURN,
  2. BB.ISACTIVE
  3. FROM BOOKBORROW BB INNER JOIN BOOK B
  4. ON BB.BOOKID = B.BOOKID
  5. INNER JOIN STUDENT S
  6. ON S.STUDENTID = BB.STUDENTID

Fetch student name, book name, book category and book borrow id (the books with their category name, borrowed by the student),

  1. SELECT BB.BOOKBORROWID, BB.BOOKID, B.BOOKNAME, BC.BOOKCATEGORYNAME, BB.STUDENTID, S.STUDENTNAME, BB.BORROWDATE, BB.BOOKRETURN, BB.ISACTIVE
  2. FROM BOOKBORROW BB INNER JOIN BOOK B
  3. ON BB.BOOKID = B.BOOKID
  4. INNER JOIN STUDENT S
  5. ON S.STUDENTID = BB.STUDENTID
  6. INNER JOIN BOOKCATEGORY BC
  7. ON BC.BOOKCATEGORYID = B.BOOKCATEGORYID

Group By

Often, we are required to apply an aggregated function on a result set to fetch data like count, sum or average. Following are some of the group queries.

Count of books borrowed by student id

  1. SELECT BB.STUDENTID, COUNT(BB.BOOKID) AS BOOKCOUNT
  2. FROM BOOKBORROW BB
  3. GROUP BY BB.STUDENTID

Fetch book count against each book category,

  1. SELECT BC.BOOKCATEGORYID, BC.BOOKCATEGORYNAME, COUNT(B.BOOKID) BOOKCATEGORYCOUNT
  2. FROM BOOKCATEGORY BC
  3. INNER JOIN BOOK B
  4. ON B.BOOKCATEGORYID = BC.BOOKCATEGORYID
  5. GROUP BY BC.BOOKCATEGORYID, BC.BOOKCATEGORYNAME

Because of Inner join, the above query will return those categories count which has at least one record(one book borrow) in book borrow table. What if, we required all categories list irrespective of records in book borrow table. Here we will use left join instead of inner

  1. SELECT BC.BOOKCATEGORYID, BC.BOOKCATEGORYNAME, COUNT(B.BOOKID) BOOKCATEGORYCOUNT
  2. FROM BOOKCATEGORY BC
  3. LEFT JOIN BOOK B
  4. ON B.BOOKCATEGORYID = BC.BOOKCATEGORYID
  5. GROUP BY BC.BOOKCATEGORYID, BC.BOOKCATEGORYNAME

The output of both queries will be as follow,

The student may be from various discipline/ technologies. (Count Students in each discipline)

  1. SELECT S.STUDENTDESCIPLINE, COUNT(S.STUDENTDESCIPLINE) DISCIPLINECOUNT
  2. FROM STUDENT S
  3. GROUP BY STUDENTDESCIPLINE

Fetch students name with their book borrows count (count of the books borrowed by students)

  1. SELECT S.studentid,
  2. studentname,
  3. Count(BB.bookborrowid) STUDENTBOOKBORROWCOUNT
  4. FROM student S
  5. LEFT JOIN bookborrow BB
  6. ON S.studentid = BB.studentid
  7. GROUP BY S.studentid,
  8. studentname

Would you like to guess, why I am using left join instead of inner one? Yes, you are right because there may be any student who has not borrowed a single book.

Having

In case of filtration or restriction on group data (count, sum, average), we have to use “having” to apply filtration/restriction on aggregate data. Following are some of the having queries.

Fetch Student id who has borrowed at least 1 book

  1. SELECT BB.studentid,
  2. Count(BB.bookid) AS BOOKCOUNT
  3. FROM bookborrow BB
  4. GROUP BY BB.studentid
  5. HAVING Count(BB.bookid) >= 1

Fetch Student id who has borrowed at least 3 book

  1. SELECT BB.STUDENTID, COUNT(BB.BOOKID) AS BOOKCOUNT
  2. FROM BOOKBORROW BB
  3. GROUP BY BB.STUDENTID
  4. HAVING COUNT(BB.BOOKID) >= 3

Fetch book’s categories list containing more than 1 books

  1. SELECT BC.BOOKCATEGORYID, BC.BOOKCATEGORYNAME, COUNT(B.BOOKID) BOOKCATEGORYCOUNT
  2. FROM BOOK B
  3. INNER JOIN BOOKCATEGORY BC
  4. ON B.BOOKCATEGORYID = BC.BOOKCATEGORYID
  5. WHERE B.ISACTIVE = 1
  6. GROUP BY BC.BOOKCATEGORYID, BC.BOOKCATEGORYNAME
  7. HAVING COUNT(B.BOOKID) > 1

Fetch book categories containing more than 3 books

  1. SELECT BC.bookcategoryid,
  2. BC.bookcategoryname,
  3. Count(B.bookid) BOOKCATEGORYCOUNT
  4. FROM book B
  5. INNER JOIN bookcategory BC
  6. ON B.bookcategoryid = BC.bookcategoryid
  7. WHERE B.isactive = 1
  8. GROUP BY BC.bookcategoryid,
  9. BC.bookcategoryname
  10. HAVING Count(B.bookid) > 3

Fetch Categories from which more than 3 books have been borrowed

  1. SELECT BC.bookcategoryid,
  2. BC.bookcategoryname,
  3. Count(BB.bookid) CATEGORYBOOKBORROW
  4. FROM bookcategory BC
  5. INNER JOIN book B
  6. ON BC.bookcategoryid = B.bookcategoryid
  7. INNER JOIN bookborrow BB
  8. ON B.bookid = BB.bookid
  9. GROUP BY BC.bookcategoryid,
  10. BC.bookcategoryname
  11. HAVING Count(BB.bookid) > 3

Not Exists

The EXISTS operator is used to check the existence of any record in a subquery. The best place to use exists/ Not exists is when you have to look data in other tables but don’t fetch any data (column) from that table to output records.

Fetch student who has not borrowed any book

  1. SELECT S.studentid,
  2. S.studentname
  3. FROM student S
  4. WHERE NOT EXISTS (SELECT 1
  5. FROM bookborrow BB
  6. WHERE BB.studentid = S.studentid)

Sub Query/ Sub Result

Sometimes, we are required to fetch data where we have to write the query within another query. Some are as following.

Fetch student borrow book in different Category Count. (In how many categories, student have borrowed book)

  1. SELECT A.studentid,
  2. A.studentname,
  3. Count(bookcategoryid) CATEGORYCOUNTBORROW
  4. FROM (SELECT DISTINCT S.studentid,
  5. S.studentname,
  6. BC.bookcategoryid
  7. FROM student S
  8. LEFT JOIN bookborrow BB
  9. ON S.studentid = BB.studentid
  10. LEFT JOIN book B
  11. ON B.bookid = BB.bookid
  12. LEFT JOIN bookcategory BC
  13. ON B.bookcategoryid = BC.bookcategoryid) A
  14. GROUP BY A.studentid,
  15. A.studentname

Fetch Student borrowed book in more than 1 categories (Student who has borrowed in at least two categories)

  1. SELECT A.studentid,
  2. A.studentname,
  3. Count(bookcategoryid) CATEGORYCOUNTBORROW
  4. FROM (SELECT DISTINCT S.studentid,
  5. S.studentname,
  6. BC.bookcategoryid
  7. FROM student S
  8. LEFT JOIN bookborrow BB
  9. ON S.studentid = BB.studentid
  10. LEFT JOIN book B
  11. ON B.bookid = BB.bookid
  12. LEFT JOIN bookcategory BC
  13. ON B.bookcategoryid = BC.bookcategoryid) A
  14. GROUP BY A.studentid,
  15. A.studentname
  16. HAVING Count(bookcategoryid) > 1

Fetch Student borrow book in different Category Name concatenated (in comma separated)

  1. SELECT DISTINCT S.studentid,
  2. S.studentname,
  3. (SELECT DISTINCT bookcategoryname + ', '
  4. FROM bookcategory BC
  5. LEFT JOIN book B
  6. ON B.bookcategoryid = BC.bookcategoryid
  7. LEFT JOIN bookborrow BB
  8. ON B.bookid = BB.bookid
  9. LEFT JOIN student SS
  10. ON SS.studentid = BB.studentid
  11. WHERE SS.studentid = S.studentid
  12. FOR xml path('')) CATEGORIES
  13. FROM student S

Fetch Student carrying distinct book (student with all borrowed book name in comma separated)

  1. SELECT DISTINCT S.studentid,
  2. S.studentname,
  3. (SELECT DISTINCT B.bookname + ', '
  4. FROM book B
  5. LEFT JOIN bookborrow BB
  6. ON B.bookid = BB.bookid
  7. LEFT JOIN student SS
  8. ON SS.studentid = BB.studentid
  9. WHERE SS.studentid = S.studentid
  10. FOR xml path('')) BOOKNAME
  11. FROM student S

Fetch a list of a book along with students who have borrowed the book in comma separated manner.

  1. SELECT B.bookname,
  2. (SELECT DISTINCT S.studentname + ', '
  3. FROM student S
  4. LEFT JOIN bookborrow BB
  5. ON S.studentid = BB.studentid
  6. WHERE B.bookid = BB.bookid
  7. FOR xml path('')) STUDENTNAME
  8. FROM book B

NOT IN

The “IN/ NOT IN” is also used when we have to look data in other tables but don’t want any column or record to fetch from that table to display.

Fetch students who did not borrow the book.

  1. SELECT *
  2. FROM student S
  3. WHERE studentid NOT IN (SELECT DISTINCT studentid
  4. FROM bookborrow)

Fetch Book which is not borrowed by anyone.

  1. SELECT *
  2. FROM book B
  3. WHERE B.bookid NOT IN (SELECT DISTINCT bookid
  4. FROM bookborrow)

Note

Didn’t you find similarity between “Not in” and “Not Exists”?

Yes, but there is a difference between both of them. unlike IN and Exists, they are not equal in all the cases especially when a null value is involved. when the subquery returns even one null, NOT IN will not match any rows.Because inside of not in, its use AND logical gate.According to AND gate, all the options/values must be true but if its find any NULL value it returns UNKNOWN.

Hence, if any row of that subquery returns NULL, the entire NOT IN operator will evaluate to either FALSE or NULL and no records will be returned.On the other side, Exists cannot return null. It will only return true or false by checking presence or absence of a row.

For example, the below queries will display different result because, in book's table, I have added NULL book name.

  1. SELECT *
  2. FROM book B
  3. WHERE NOT EXISTS (SELECT bookname
  4. FROM book
  5. WHERE bookname = B.bookname)
  7. SELECT *
  8. FROM book B
  9. WHERE B.bookname NOT IN (SELECT bookname
  10. FROM book
  11. WHERE bookid = B.bookid)

Rank

Ranking functions are those who return/associates a ranking number (Value) for each row in a partition. For example, if I want to fetch records of most borrow book name and count. The following query will fetch the result.

  1. SELECT TOP 1 BB.bookid,
  2. B.bookname,
  3. Count(BB.bookid) BORROWCOUNT
  4. FROM book B
  5. LEFT JOIN bookborrow BB
  6. ON B.bookid = BB.bookid
  7. GROUP BY BB.bookid,
  8. B.bookname
  9. ORDER BY Count(BB.bookid) DESC

But what if I have more than 1 same book count. At this moment, we will have to implement rank.

  1. SELECT *
  2. FROM (SELECT BB.bookid,
  3. B.bookname,
  4. Count(BB.bookid) BORROWCOUNT,
  5. Rank()
  6. OVER (
  7. ORDER BY Count(BB.bookid) DESC) AS RANK1
  8. FROM book B
  9. LEFT JOIN bookborrow BB
  10. ON B.bookid = BB.bookid
  11. GROUP BY BB.bookid,
  12. B.bookname) TBL
  13. WHERE rank1 = 1

Fetch top 3 most borrow book

  1. SELECT TOP 3 BB.bookid,
  2. B.bookname,
  3. Count(BB.bookid) BORROWCOUNT
  4. FROM book B
  5. LEFT JOIN bookborrow BB
  6. ON B.bookid = BB.bookid
  7. GROUP BY BB.bookid,
  8. B.bookname
  9. ORDER BY borrowcount DESC

Fetch Person borrow most book

  1. SELECT TOP 1 S.studentname,
  2. Count(BB.bookid) BORROWCOUNT
  3. FROM student S
  4. LEFT JOIN bookborrow BB
  5. ON BB.studentid = S.studentid
  6. GROUP BY S.studentname
  7. ORDER BY borrowcount DESC

Again, what if we have more than 1 person borrowing same books. The appropriate query would be.

  1. SELECT *
  2. FROM (SELECT S.studentname,
  3. Count(BB.bookid) BORROWCOUNT,
  4. Rank()
  5. OVER (
  6. ORDER BY Count(BB.bookid) DESC) AS RANKVALUE
  7. FROM student S
  8. LEFT JOIN bookborrow BB
  9. ON BB.studentid = S.studentid
  10. GROUP BY S.studentname)TBL
  11. WHERE TBL.rankvalue = 1
  12. ORDER BY TBL.borrowcount DESC

Fetch least borrow book Name and count

  1. SELECT *
  2. FROM (SELECT BB.bookid,
  3. B.bookname,
  4. Count(BB.bookid) BORROWCOUNT,
  5. Rank()
  6. OVER (
  7. ORDER BY Count(BB.bookid) ) AS RANK1
  8. FROM book B
  9. LEFT JOIN bookborrow BB
  10. ON B.bookid = BB.bookid
  11. GROUP BY BB.bookid,
  12. B.bookname) TBL
  13. WHERE rank1 = 1

Fetch top 3 least books, borrow by student

  1. SELECT *
  2. FROM (SELECT BB.bookid,
  3. B.bookname,
  4. Count(BB.bookid) BORROWCOUNT,
  5. Rank()
  6. OVER (
  7. ORDER BY Count(BB.bookid) ) AS RANK1
  8. FROM book B
  9. LEFT JOIN bookborrow BB
  10. ON B.bookid = BB.bookid
  11. GROUP BY BB.bookid,
  12. B.bookname) TBL
  13. WHERE rank1 <= 3

Fetch least 1 person who has borrow book (or didn’t borrow any book)

  1. SELECT *
  2. FROM (SELECT S.studentname,
  3. Count(BB.bookid) BORROWCOUNT,
  4. Rank()
  5. OVER (
  6. ORDER BY Count(BB.bookid)) AS RANKVALUE
  7. FROM student S
  8. LEFT JOIN bookborrow BB
  9. ON BB.studentid = S.studentid
  10. GROUP BY S.studentname)TBL
  11. WHERE TBL.rankvalue = 1
  12. ORDER BY TBL.borrowcount DESC

Fetch list of books which at least one student has borrowed

  1. SELECT B.bookid,
  2. B.bookname,
  3. Count(BB.bookid) AS BOOKBORROWCOUNT
  4. FROM book B
  5. INNER JOIN bookborrow BB
  6. ON B.bookid = BB.bookid
  7. GROUP BY B.bookid,
  8. B.bookname
  9. HAVING Count(BB.bookid) > 1
  10. ORDER BY Count(BB.bookid) DESC

Fetch most book borrowed by categories(Count all book against each category)

  1. SELECT *
  2. FROM (SELECT BC.bookcategoryid,
  3. BC.bookcategoryname,
  4. Count(BB.bookid) BOOKBORROWCOUNT,
  5. Rank()
  6. OVER (
  7. ORDER BY Count(BB.bookid) DESC) RANKCOUNT
  8. FROM bookcategory BC
  9. LEFT JOIN book B
  10. ON B.bookcategoryid = BC.bookcategoryid
  11. LEFT JOIN bookborrow BB
  12. ON BB.bookid = B.bookid
  13. GROUP BY BC.bookcategoryid,
  14. BC.bookcategoryname) TBL
  15. WHERE TBL.rankcount = 1

Fetch the least book borrowed by category

  1. SELECT *
  2. FROM (SELECT BC.bookcategoryid,
  3. BC.bookcategoryname,
  4. Count(BB.bookid) BOOKBORROWCOUNT,
  5. Rank()
  6. OVER (
  7. ORDER BY Count(BB.bookid)) RANKCOUNT
  8. FROM bookcategory BC
  9. LEFT JOIN book B
  10. ON B.bookcategoryid = BC.bookcategoryid
  11. LEFT JOIN bookborrow BB
  12. ON BB.bookid = B.bookid
  13. GROUP BY BC.bookcategoryid,
  14. BC.bookcategoryname) TBL
  15. WHERE TBL.rankcount = 1

You can explore more dimension out of the discussed relational database. More queries can be written with multiple condition and criteria.