A Scenario Based SQL Interview Queries/ Test Model/Training

A scenario-based SQL interview queries with the schema which is equally meaningful for new students as training material.

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.

SELECT * FROM BOOK
SELECT * FROM STUDENT
SELECT * FROM BOOKCATEGORY
SELECT * FROM BOOKBORROW

Where clause

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

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

Order clause

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

SELECT * FROM BOOK ORDER BY BOOKNAME, BOOKID DESC
SELECT * FROM STUDENT ORDER BY STUDENTDESCIPLINE, STUDENTNAME
SELECT * FROM BOOKCATEGORY ORDER BY BOOKCATEGORYNAME
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),

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

Fetch Book name with their Category, 

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

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

SELECT BB.BOOKBORROWID, BB.BOOKID, B.BOOKNAME, BB.STUDENTID, S.STUDENTNAME, BB.BORROWDATE, BB.BOOKRETURN,
    BB.ISACTIVE
FROM BOOKBORROW BB INNER JOIN BOOK B
ON BB.BOOKID = B.BOOKID
INNER JOIN STUDENT S
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), 

SELECT BB.BOOKBORROWID, BB.BOOKID, B.BOOKNAME, BC.BOOKCATEGORYNAME, BB.STUDENTID, S.STUDENTNAME, BB.BORROWDATE, BB.BOOKRETURN, BB.ISACTIVE
FROM BOOKBORROW BB INNER JOIN BOOK B
ON BB.BOOKID = B.BOOKID
INNER JOIN STUDENT S
ON S.STUDENTID = BB.STUDENTID
INNER JOIN BOOKCATEGORY BC
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 

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

Fetch book count against each book category, 

SELECT BC.BOOKCATEGORYID, BC.BOOKCATEGORYNAME, COUNT(B.BOOKID) BOOKCATEGORYCOUNT
FROM BOOKCATEGORY BC
INNER JOIN BOOK B
ON B.BOOKCATEGORYID = BC.BOOKCATEGORYID
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 

SELECT BC.BOOKCATEGORYID, BC.BOOKCATEGORYNAME, COUNT(B.BOOKID) BOOKCATEGORYCOUNT
FROM BOOKCATEGORY BC
LEFT JOIN BOOK B
ON B.BOOKCATEGORYID = BC.BOOKCATEGORYID
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)

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

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

SELECT S.studentid,
       studentname,
       Count(BB.bookborrowid) STUDENTBOOKBORROWCOUNT
FROM   student S
       LEFT JOIN bookborrow BB
              ON S.studentid = BB.studentid
GROUP  BY S.studentid,
          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

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

Fetch Student id who has borrowed at least 3 book

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

Fetch book’s categories list containing more than 1 books

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

Fetch book categories containing more than 3 books

SELECT BC.bookcategoryid,
       BC.bookcategoryname,
       Count(B.bookid) BOOKCATEGORYCOUNT
FROM   book B
       INNER JOIN bookcategory BC
               ON B.bookcategoryid = BC.bookcategoryid
WHERE  B.isactive = 1
GROUP  BY BC.bookcategoryid,
          BC.bookcategoryname
HAVING Count(B.bookid) > 3

Fetch Categories from which more than 3 books have been borrowed

SELECT BC.bookcategoryid,
       BC.bookcategoryname,
       Count(BB.bookid) CATEGORYBOOKBORROW
FROM   bookcategory BC
       INNER JOIN book B
               ON BC.bookcategoryid = B.bookcategoryid
       INNER JOIN bookborrow BB
               ON B.bookid = BB.bookid
GROUP  BY BC.bookcategoryid,
          BC.bookcategoryname
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

SELECT S.studentid,
       S.studentname
FROM   student S
WHERE  NOT EXISTS (SELECT 1
                   FROM   bookborrow BB
                   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)

SELECT A.studentid,
       A.studentname,
       Count(bookcategoryid) CATEGORYCOUNTBORROW
FROM   (SELECT DISTINCT S.studentid,
                        S.studentname,
                        BC.bookcategoryid
        FROM   student S
               LEFT JOIN bookborrow BB
                      ON S.studentid = BB.studentid
               LEFT JOIN book B
                      ON B.bookid = BB.bookid
               LEFT JOIN bookcategory BC
                      ON B.bookcategoryid = BC.bookcategoryid) A
GROUP  BY A.studentid,
          A.studentname

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

SELECT A.studentid,
       A.studentname,
       Count(bookcategoryid) CATEGORYCOUNTBORROW
FROM   (SELECT DISTINCT S.studentid,
                        S.studentname,
                        BC.bookcategoryid
        FROM   student S
               LEFT JOIN bookborrow BB
                      ON S.studentid = BB.studentid
               LEFT JOIN book B
                      ON B.bookid = BB.bookid
               LEFT JOIN bookcategory BC
                      ON B.bookcategoryid = BC.bookcategoryid) A
GROUP  BY A.studentid,
          A.studentname
HAVING Count(bookcategoryid) > 1

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

SELECT DISTINCT S.studentid,
                S.studentname,
                (SELECT DISTINCT bookcategoryname + ', '
                 FROM   bookcategory BC
                        LEFT JOIN book B
                               ON B.bookcategoryid = BC.bookcategoryid
                        LEFT JOIN bookborrow BB
                               ON B.bookid = BB.bookid
                        LEFT JOIN student SS
                               ON SS.studentid = BB.studentid
                 WHERE  SS.studentid = S.studentid
                 FOR xml path('')) CATEGORIES
FROM   student S

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

SELECT DISTINCT S.studentid,
                S.studentname,
                (SELECT DISTINCT B.bookname + ', '
                 FROM   book B
                        LEFT JOIN bookborrow BB
                               ON B.bookid = BB.bookid
                        LEFT JOIN student SS
                               ON SS.studentid = BB.studentid
                 WHERE  SS.studentid = S.studentid
                 FOR xml path('')) BOOKNAME
FROM   student S

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

SELECT B.bookname,
       (SELECT DISTINCT S.studentname + ', '
        FROM   student S
               LEFT JOIN bookborrow BB
                      ON S.studentid = BB.studentid
        WHERE  B.bookid = BB.bookid
        FOR xml path('')) STUDENTNAME
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.

SELECT *
FROM   student S
WHERE  studentid NOT IN (SELECT DISTINCT studentid
                         FROM   bookborrow)

Fetch Book which is not borrowed by anyone.

SELECT *
FROM   book B
WHERE  B.bookid NOT IN (SELECT DISTINCT bookid
                        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.

SELECT *
FROM   book B
WHERE  NOT EXISTS (SELECT bookname
                   FROM   book
                   WHERE  bookname = B.bookname)

SELECT *
FROM   book B
WHERE  B.bookname NOT IN (SELECT bookname
                          FROM   book
                          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.

SELECT TOP 1 BB.bookid,
             B.bookname,
             Count(BB.bookid) BORROWCOUNT
FROM   book B
       LEFT JOIN bookborrow BB
              ON B.bookid = BB.bookid
GROUP  BY BB.bookid,
          B.bookname
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.

SELECT *
FROM   (SELECT BB.bookid,
               B.bookname,
               Count(BB.bookid)                    BORROWCOUNT,
               Rank()
                 OVER (
                   ORDER BY Count(BB.bookid) DESC) AS RANK1
        FROM   book B
               LEFT JOIN bookborrow BB
                      ON B.bookid = BB.bookid
        GROUP  BY BB.bookid,
                  B.bookname) TBL
WHERE  rank1 = 1

Fetch top 3 most borrow book

SELECT TOP 3 BB.bookid,
             B.bookname,
             Count(BB.bookid) BORROWCOUNT
FROM   book B
       LEFT JOIN bookborrow BB
              ON B.bookid = BB.bookid
GROUP  BY BB.bookid,
          B.bookname
ORDER  BY borrowcount DESC

Fetch Person borrow most book

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

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

SELECT *
FROM   (SELECT S.studentname,
               Count(BB.bookid)                    BORROWCOUNT,
               Rank()
                 OVER (
                   ORDER BY Count(BB.bookid) DESC) AS RANKVALUE
        FROM   student S
               LEFT JOIN bookborrow BB
                      ON BB.studentid = S.studentid
        GROUP  BY S.studentname)TBL
WHERE  TBL.rankvalue = 1
ORDER  BY TBL.borrowcount DESC

Fetch least borrow book Name and count

SELECT *
FROM   (SELECT BB.bookid,
               B.bookname,
               Count(BB.bookid)                BORROWCOUNT,
               Rank()
                 OVER (
                   ORDER BY Count(BB.bookid) ) AS RANK1
        FROM   book B
               LEFT JOIN bookborrow BB
                      ON B.bookid = BB.bookid
        GROUP  BY BB.bookid,
                  B.bookname) TBL
WHERE  rank1 = 1

Fetch top 3 least books, borrow by student

SELECT *
FROM   (SELECT BB.bookid,
               B.bookname,
               Count(BB.bookid)                BORROWCOUNT,
               Rank()
                 OVER (
                   ORDER BY Count(BB.bookid) ) AS RANK1
        FROM   book B
               LEFT JOIN bookborrow BB
                      ON B.bookid = BB.bookid
        GROUP  BY BB.bookid,
                  B.bookname) TBL
WHERE  rank1 <= 3

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

SELECT *
FROM   (SELECT S.studentname,
               Count(BB.bookid)               BORROWCOUNT,
               Rank()
                 OVER (
                   ORDER BY Count(BB.bookid)) AS RANKVALUE
        FROM   student S
               LEFT JOIN bookborrow BB
                      ON BB.studentid = S.studentid
        GROUP  BY S.studentname)TBL
WHERE  TBL.rankvalue = 1
ORDER  BY TBL.borrowcount DESC

Fetch list of books which at least one student has borrowed

SELECT B.bookid,
       B.bookname,
       Count(BB.bookid) AS BOOKBORROWCOUNT
FROM   book B
       INNER JOIN bookborrow BB
               ON B.bookid = BB.bookid
GROUP  BY B.bookid,
          B.bookname
HAVING Count(BB.bookid) > 1
ORDER  BY Count(BB.bookid) DESC

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

SELECT *
FROM   (SELECT BC.bookcategoryid,
               BC.bookcategoryname,
               Count(BB.bookid)                    BOOKBORROWCOUNT,
               Rank()
                 OVER (
                   ORDER BY Count(BB.bookid) DESC) RANKCOUNT
        FROM   bookcategory BC
               LEFT JOIN book B
                      ON B.bookcategoryid = BC.bookcategoryid
               LEFT JOIN bookborrow BB
                      ON BB.bookid = B.bookid
        GROUP  BY BC.bookcategoryid,
                  BC.bookcategoryname) TBL
WHERE  TBL.rankcount = 1

Fetch the least book borrowed by category

SELECT *
FROM   (SELECT BC.bookcategoryid,
               BC.bookcategoryname,
               Count(BB.bookid)               BOOKBORROWCOUNT,
               Rank()
                 OVER (
                   ORDER BY Count(BB.bookid)) RANKCOUNT
        FROM   bookcategory BC
               LEFT JOIN book B
                      ON B.bookcategoryid = BC.bookcategoryid
               LEFT JOIN bookborrow BB
                      ON BB.bookid = B.bookid
        GROUP  BY BC.bookcategoryid,
                  BC.bookcategoryname) TBL
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.