Problem Scenario: This Scenario is to develop a Library Management System (LMS) to store information of the members, books, status of books issue, book availability and suppliers details.
| List of Tables: |
| Table 1: LMS_MEMBERS |
| Table 2: LMS_SUPPLIERS_DETAILS |
| Table 3: LMS_FINE_DETAILS |
| Table 4: |
Table 5: LMS_BOOK_ISSUE
ER Diagram:
| Database and Table Creations - DDL Queries: | Download LMS_DDL.sql |
| Loading Data - DML Queries: | Download LMS_DML.sql |
Solve this Scenario.
Simple Questions:
Problem # 1:
Write a query to display the member id, member name, city and membership status who are all having life time membership. Hint: Life time membership status is “Permanent”.
Ans: select member_id,member_name,city,membership_status from LMS_MEMBERS where membership_status='permanent'
Problem # 2:
Write a query to display the book code, publication, price and supplier name of the book witch is taken frequently.
Ans: SELECT b1.book_code,b2.publication,b2.price,b3.supplier_name FROM(SELECT b1.book_code FROM LMS_BOOK_ISSUE b1 GROUP BY book_code HAVING COUNT(b1.BOOK_CODE) > 1) b1 JOIN LMS_BOOK_DETAILS b2 ON b1.book_code = b2.book_code JOIN LMS_SUPPLIERS_DETAILS b3 ON b2.supplier_id = b3.supplier_id
Problem # 3:
Write a query to display the member id, member name who have taken the book with book code 'BL000002'.
Ans: select b1. member_id,b1.member_name,b2.book_code from LMS_MEMBERS b1,LMS_BOOK_ISSUE b2 where b2.BOOK_CODE='BL000002'
Problem # 4:
Write a query to display the book code, book title and author of the books whose author name begins with 'P'.
Ans: select book_code,book_title,author from LMS_BOOK_DETAILS where AUTHOR like 'P%'
Problem # 5:
Write a query to display the total number of Java books available in library with alias name ‘NO_OF_BOOKS’.
Ans: select COUNT(category)as NO_OF_BOOKS from LMS_BOOK_DETAILS where CATEGORY='JAVA'
Problem # 6:
Write a query to list the category and number of books in each category with alias name ‘NO_OF_BOOKS’.
Ans: select category,COUNT(category)as NO_OF_BOOKS from LMS_BOOK_DETAILS group by CATEGORY
Problem # 7:
Write a query to display the number of books published by "Prentice Hall” with the alias name “NO_OF_BOOKS”.
Ans: select COUNT(publication) as NO_OF_BOOKS from LMS_BOOK_DETAILS where PUBLICATION='Prentice Hall'
Problem # 8:
Write a query to display the book code, book title of the books which are issued on the date "1st April 2012".
Ans(without issue_date): select b1.book_code,b2.book_title from LMS_BOOK_ISSUE b1 inner join LMS_BOOK_DETAILS b2 on b1.BOOK_CODE=b2.BOOK_CODE where b1.DATE_ISSUE='2012-04-01'
Ans(with issue date): select b1.book_code,b1.DATE_ISSUE,b2.book_title from LMS_BOOK_ISSUE b1 inner join LMS_BOOK_DETAILS b2 on b1.BOOK_CODE=b2.BOOK_CODE where b1.DATE_ISSUE='2012-04-01'
Problem # 9:
Write a query to display the member id, member name, date of registration and expiry date of the members whose membership expiry date is before APR 2013.
Ans: select member_id,member_name,DATE_REGISTER,DATE_EXPIRE from LMS_MEMBERS where DATE_EXPIRE < '2013-04-01'
Problem # 10:
write a query to display the member id, member name, date of registration, membership status of the members who registered before "March 2012" and membership status is "Temporary"
Ans: select member_id,member_name,DATE_REGISTER,MEMBERSHIP_STATUS from LMS_MEMBERS where DATE_REGISTER < '2012-03-01' and MEMBERSHIP_STATUS='Temporary'
Problem #11:
Write a query to display the member id, member name who’s City is CHENNAI or DELHI. Hint: Display the member name in title case with alias name 'Name'.
Ans: select member_id,member_name as 'Name' from LMS_MEMBERS where CITY='chennai' or CITY='delhi'
Problem #12:
Write a query to concatenate book title, author and display in the following format.
Book_Title_is_written_by_Author
Example: Let Us C_is_written_by_Yashavant Kanetkar
Hint: display unique books. Use “BOOK_WRITTEN_BY” as alias name.
Ans: select book_title + '_is_written_by' + author as 'BOOK_WRITTEN_BY' from LMS_BOOK_DETAILS
Problem #13:
Write a query to display the average price of books which is belonging to ‘JAVA’ category with alias name “AVERAGEPRICE”.
Ans: select AVG(price) as 'AVERAGEPRICE' from LMS_BOOK_DETAILS where CATEGORY='java'
Problem #14:
Write a query to display the supplier id, supplier name and email of the suppliers who are all having gmail account.
Ans: select supplier_id,SUPPLIER_NAME,EMAIL from LMS_SUPPLIERS_DETAILS where EMAIL like '%gmail.com'
Problem#15:
Write a query to display the supplier id, supplier name and contact details. Contact details can be either phone number or email or address with alias name “CONTACTDETAILS”. If phone number is null then display email, even if email also null then display the address of the supplier. Hint: Use Coalesce function.
ANS: select Supplier_id,supplier_name,coalesce(contact,email,address) as contactdetails from Lms_suppliers_details
Problem#16:
Write a query to display the supplier id, supplier name and contact. If phone number is null then display ‘No’ else display ‘Yes’ with alias name “PHONENUMAVAILABLE”. Hint: Use ISNULL.
ANS: select SUPPLIER_ID,SUPPLIER_NAME,case when CONTACT is NULL then 'NO' when CONTACT is not null then 'YES' end as PHONE_NUM_AVAILABLE from LMS_SUPPLIERS_DETAILS
Problem#17:
Write a query to display the member id, member name, city and member status of members with the total fine paid by them with alias name “Fine”.
ANS: SELECT m.MEMBER_ID, m.MEMBER_NAME, m.CITY, SUM(f.FINE_AMOUNT) AS FINE FROM LMS_MEMBERS m INNER JOIN LMS_BOOK_ISSUE i ON m.MEMBER_ID = i.MEMBER_ID INNER JOIN LMS_FINE_DETAILS f ON i.FINE_RANGE = f.FINE_RANGE GROUP BY m.MEMBER_ID, m.MEMBER_NAME, m.CITY
Average Questions:
Problem # 1:
Write a query to display the member id, member name of the members, book code and book title of the books taken by them.
ANS: select MEMBER_ID,(select MEMBER_NAME from LMS_MEMBERS where MEMBER_ID=t1.MEMBER_ID )as MEMBER_NAME,BOOK_CODE,(select BOOK_TITLE from LMS_BOOK_DETAILS where BOOK_CODE=t1.BOOK_CODE)as BOOK_TITLE from LMS_BOOK_ISSUE t1
Problem # 2:
Write a query to display the total number of books available in the library with alias name “NO_OF_BOOKS_AVAILABLE” (Which is not issued). Hint: The issued books details are available in the LMS_BOOK_ISSUE table.
ANS: SELECT COUNT(t1.BOOK_CODE)AS NO_OF_BOOKS_AVAILABLE FROM LMS_BOOK_DETAILS t1 LEFT JOIN LMS_BOOK_ISSUE t2 ON t1.BOOK_CODE = t2.BOOK_CODE WHERE t2.BOOK_CODE IS NULL
Problem # 3:
Write a query to display the member id, member name, fine range and fine amount of the members whose fine amount is less than 100.
ANS: SELECT T1.MEMBER_ID,(SELECT MEMBER_NAME FROM LMS_MEMBERS WHERE MEMBER_ID=T1.MEMBER_ID)AS MEMBER_NAME,(SELECT T2.FINE_RANGE FROM LMS_MEMBERS WHERE MEMBER_ID=T1.MEMBER_ID)AS FINE_RANGE,(SELECT FINE_AMOUNT FROM LMS_FINE_DETAILS T2 WHERE FINE_RANGE= T1.FINE_RANGE)AS FINE_AMOUNT FROM LMS_BOOK_ISSUE T1 LEFT JOIN LMS_FINE_DETAILS T2 ON T1.FINE_RANGE = T2.FINE_RANGE WHERE T2.FINE_AMOUNT < 100
Problem # 4:
Write a query to display the book code, book title, publisher, edition, price and year of publication and sort based on year of publication, publisher and edition.
ANS: SELECT BOOK_CODE,BOOK_TITLE,PUBLICATION,BOOK_EDITION,PRICE,YEAR(PUBLISH_DATE) AS YEAR_OF_PUBLICATION FROM LMS_BOOK_DETAILS ORDER BY YEAR_OF_PUBLICATION
Problem # 5:
Write a query to display the book code, book title and rack number of the books which are placed in rack 'A1' and sort by book title in ascending order.
ANS: SELECT BOOK_CODE,BOOK_TITLE,RACK_NUM FROM LMS_BOOK_DETAILS where RACK_NUM='A1' ORDER BY BOOK_TITLE ASC
Problem # 6:
Write a query to display the member id, member name, due date and date returned of the members who has returned the books after the due date. Hint: Date_return is due date and Date_returned is actual book return date.
ANS: SELECT MEMBER_ID,(SELECT MEMBER_NAME FROM LMS_MEMBERS WHERE MEMBER_ID=T1.MEMBER_ID)AS MEMBER_NAME,DATE_RETURN AS DUE_DATE,DATE_RETURNED FROM LMS_BOOK_ISSUE T1 WHERE DATE_RETURN < DATE_RETURNED
Problem # 7:
Write a query to display the member id, member name and date of registration who have not taken any book.
ANS: SELECT T1.MEMBER_ID,MEMBER_NAME,DATE_REGISTER FROM LMS_MEMBERS T1 LEFT JOIN LMS_BOOK_ISSUE T2 ON T1.MEMBER_ID = T2.MEMBER_ID WHERE T2.MEMBER_ID IS NULL
Problem # 8:
Write a Query to display the member id and member name of the members who has not paid any fine in the year 2012.
ANS:
Using Sub Query:
select t1.MEMBER_ID,(select t2.MEMBER_NAME from LMS_MEMBERS t2 where t1.MEMBER_ID=t2.MEMBER_ID)as MEMBER_NAME from LMS_BOOK_ISSUE t1 where t1.DATE_RETURN >=t1.DATE_RETURNED and year(t1.DATE_RETURNED)=2012
(or)
Using Join:
select t1.MEMBER_ID,t2.MEMBER_NAME from LMS_BOOK_ISSUE t1 join LMS_MEMBERS t2 on t1.MEMBER_ID=t2.MEMBER_ID where t1.DATE_RETURN>=t1.DATE_RETURNED and YEAR(t1.DATE_RETURNED)=2012
Problem # 9:
Write a query to display the date on which the maximum numbers of books were issued and the number of books issued with alias name “NOOFBOOKS”.
ANS: select DATE_ISSUE,count(DATE_ISSUE) as NO_OF_BOOKS from LMS_BOOK_ISSUE group by DATE_ISSUE having COUNT(DATE_ISSUE)=(select MAX(counted) from (select COUNT(DATE_ISSUE) as counted from LMS_BOOK_ISSUE group by DATE_ISSUE) as t)
Problem # 10:
Write a query to list the book title and supplier id for the books authored by “Herbert Schildt" and the book edition is 5 and supplied by supplier ‘S01’.
ANS: select BOOK_TITLE,SUPPLIER_ID from LMS_BOOK_DETAILS where AUTHOR='Herbert Schildt' and BOOK_EDITION='5' and SUPPLIER_ID='S01'
Problem # 11:
Write a query to display the rack number and the number of books in each rack with alias name “NOOFBOOKS” and sort by rack number in ascending order.
ANS: select RACK_NUM,COUNT(book_code) as NO_OF_BOOKS from LMS_BOOK_DETAILS group by RACK_NUM
Problem # 12:
Write a query to display book issue number, member name, date or registration, date of expiry, book title, category author, price, date of issue, date of return, actual returned date, fine amount.
ANS: Select BOOK_ISSUE_NO,MEMBER_NAME,DATE_REGISTER,DATE_EXPIRE,BOOK_TITLE,CATEGORY, PRICE, DATE_ISSUE,DATE_RETURN,DATE_RETURNED,(select FINE_AMOUNT from LMS_FINE_DETAILS f where i.FINE_RANGE= f.FINE_RANGE)as FINE_AMOUNT from LMS_BOOK_ISSUE i,LMS_MEMBERS m,LMS_BOOK_DETAILS b where i.MEMBER_ID=m.MEMBER_ID and b.BOOK_CODE=i.BOOK_CODE
Problem # 13:
Write a query to display the book code, title, publish date of the books which is been published in the month of
December.
ANS: select BOOK_CODE,BOOK_TITLE,PUBLISH_DATE from LMS_BOOK_DETAILS where MONTH(PUBLISH_DATE)='12'
Problem # 14:
Write a query to display the book code, book title ,supplier name and price of the book witch takes maximum price based on each supplier.
ANS: select BOOK_CODE,BOOK_TITLE,SUPPLIER_NAME,PRICE from LMS_BOOK_DETAILS b inner join LMS_SUPPLIERS_DETAILS s on b.SUPPLIER_ID=s.SUPPLIER_ID where b.PRICE=(select MAX(PRICE) from LMS_BOOK_DETAILS k where b.SUPPLIER_ID=k.SUPPLIER_ID)
Problem # 15:
Write a query to display book code, book name, and publisher, how old the book is. Sorted as older to newer.
ANS: select BOOK_CODE,BOOK_TITLE,PUBLICATION,YEAR(GETDATE())-YEAR(DATE_ARRIVAL)as YEARS from LMS_BOOK_DETAILS order by YEARS desc
Complex Questions:
Problem # 1:
Write a query to display the book code, book title and supplier name of the supplier who has supplied maximum number of books. For example, if “ABC Store” supplied 3 books, “LM Store” has supplied 2 books and “XYZ Store” has supplied 1 book. So “ABC Store” has supplied maximum number of books, hence display the details as mentioned below.
Example:
BOOK_CODE BOOK_TITLE SUPPLIER_NAME
BL000008 Easy Reference for Java ABC STORE
BL000001 Easy Reference for C ABC STORE
BL000003 Easy Reference for VB ABC STORE
ANS: select BOOK_CODE,BOOK_TITLE,SUPPLIER_NAME from LMS_BOOK_DETAILS B inner join LMS_SUPPLIERS_DETAILS S on B.SUPPLIER_ID = S.SUPPLIER_ID where S.SUPPLIER_ID in (select SUPPLIER_ID from LMS_BOOK_DETAILS group by SUPPLIER_ID having COUNT(SUPPLIER_ID)=(select MAX(cnt) from (select COUNT(*) as cnt from LMS_BOOK_DETAILS group by SUPPLIER_ID)as T))
Problem # 2:
Write a query to display the member id, member name and number of remaining books he/she can take with “REMAININGBOOKS” as alias name. Hint: Assuming a member can take maximum 3 books. For example, Ramesh has already taken 2 books; he can take only one book now. Hence display the remaining books as 1 in below format.
Example:
MEMBER_ID MEMBER_NAME REMAININGBOOKS
LM001 RAMESH 1
LM002 MOHAN 3
ANS: select m.MEMBER_ID,MEMBER_NAME,T.cb as REMAINING_BOOKS from LMS_MEMBERS m inner join (select a.member_id,COUNT(i.MEMBER_ID) as cb from LMS_MEMBERS a left outer join LMS_BOOK_ISSUE i on a.MEMBER_ID= i.MEMBER_ID group by a.MEMBER_ID)T on T.MEMBER_ID=m.MEMBER_ID
Problem # 3
Write a query to display the supplier id and supplier name of the supplier who has supplied minimum number of books. For example, if “ABC Store” supplied 3 books, “LM Store” has supplied 2 books and “XYZ Store” has supplied 1 book. So “XYZ Store” has supplied minimum number of books, hence display the details as mentioned below.
Example:
SUPPLIER_ID SUPPLIER_NAME
S04 XYZ STORE
ANS: select S.SUPPLIER_ID,SUPPLIER_NAME from LMS_BOOK_DETAILS B inner join LMS_SUPPLIERS_DETAILS S on B.SUPPLIER_ID = S.SUPPLIER_ID where S.SUPPLIER_ID in (select SUPPLIER_ID from LMS_BOOK_DETAILS group by SUPPLIER_ID having COUNT(SUPPLIER_ID)=(select MIN(cnt) from (select COUNT(*) as cnt from LMS_BOOK_DETAILS group by SUPPLIER_ID)as T))
Download Question & Answer: LMS.doc
Thanks a lot !! :)
ReplyDelete