Wxi1NTN2

1Simple Questions:
2Problem # 1:
3Write 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”.
4
5SELECT MEMBER_ID, MEMBER_NAME, CITY, MEMBERSHIP_STATUS FROM LMS_MEMBERS WHERE MEMBERSHIP_STATUS = 'Permanent'
6
7Problem # 2:
8Write a query to display the book code, publication, price and supplier name of the book witch is taken frequently.
9
10SELECT B.PUBLICATION, B.PRICE,S.SUPPLIER_NAME, I.BOOK_CODE FROM LMS_BOOK_DETAILS B JOIN LMS_BOOK_ISSUE I ON I.BOOK_CODE = B.BOOK_CODE JOIN LMS_SUPPLIERS_DETAILS S ON S.SUPPLIER_ID = B.SUPPLIER_ID GROUP BY B.PUBLICATION, B.PRICE,S.SUPPLIER_NAME, I.BOOK_CODE
11 HAVING COUNT(I.BOOK_CODE) >= ALL( SELECT COUNT(BOOK_CODE) FROM LMS_BOOK_ISSUE GROUP BY BOOK_CODE )
12
13
14Problem # 3:
15Write a query to display the member id, member name who have taken the book with book code 'BL000002'. 
16
17SELECT M.MEMBER_ID, M.MEMBER_NAME FROM LMS_MEMBERS M JOIN LMS_BOOK_ISSUE I ON M.MEMBER_ID = I.MEMBER_ID WHERE I.BOOK_CODE = 'BL000002'
18
19
20Problem # 4:
21Write a query to display the book code, book title and author of the books whose author name begins with 'P'.
22
23SELECT BOOK_CODE, BOOK_TITLE, AUTHOR FROM LMS_BOOK_DETAILS WHERE AUTHOR LIKE 'P%'
24
25
26Problem # 5:
27Write a query to display the total number of Java books available in library with alias name ‘NO_OF_BOOKS’. 
28
29SELECT COUNT(BOOK_TITLE) AS NO_OF_BOOKS FROM LMS_BOOK_DETAILS WHERE BOOK_TITLE LIKE '%Java%'
30
31
32Problem # 6:
33Write a query to list the category and number of books in each category with alias name ‘NO_OF_BOOKS’.
34
35SELECT COUNT(DISTINCT CATEGORY) AS NO_OF_BOOKS FROM LMS_BOOK_DETAILS
36
37
38
39
40
41
42Problem # 7:
43Write a query to display the number of books published by "Prentice Hall” with the alias name “NO_OF_BOOKS”.
44
45SELECT COUNT(BOOK_CODE) AS NO_OF_BOOKS FROM LMS_BOOK_DETAILS WHERE PUBLICATION = 'Prentice Hall'
46
47Problem # 8:
48Write a query to display the book code, book title of the books which are issued on the date "1st April 2012".
49
50SELECT B.BOOK_CODE, B.BOOK_TITLE,I.DATE_ISSUE FROM LMS_BOOK_DETAILS B JOIN LMS_BOOK_ISSUE I ON B.BOOK_CODE = I.BOOK_CODE  WHERE I.DATE_ISSUE = TO_DATE('04/01/2012','mm/dd/yyyy')
51
52Problem # 9:
53Write 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.
54
55SELECT MEMBER_ID, MEMBER_NAME, DATE_REGISTER, DATE_EXPIRE FROM LMS_MEMBERS WHERE DATE_EXPIRE <= TO_DATE ('04/01/2013','mm/dd/yyyy')
56
57Problem # 10:
58write 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" 
59
60SELECT MEMBER_ID, MEMBER_NAME, DATE_REGISTER, MEMBERSHIP_STATUS FROM LMS_MEMBERS WHERE DATE_REGISTER > TO_DATE('03/01/2012','mm/dd/yyyy') AND MEMBERSHIP_STATUS = 'Temporary'
61
62Problem #11:
63Write 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'. 
64
65SELECT MEMBER_ID, MEMBER_NAME AS Name FROM LMS_MEMBERS WHERE CITY ='CHENNAI' OR CITY = 'DELHI'
66
67Problem #12:
68Write a query to concatenate book title, author and display in the following format.
69Book_Title_is_written_by_Author
70Example:  Let Us C_is_written_by_Yashavant Kanetkar
71Hint: display unique books. Use “BOOK_WRITTEN_BY” as alias name. 
72
73SELECT BOOK_TITLE||' is written by '||AUTHOR AS BOOK_WRITTEN_BY FROM LMS_BOOK_DETAILS
74
75
76
77
78
79
80Problem #13:
81Write a query to display the average price of books which is belonging to ‘JAVA’ category with alias name “AVERAGEPRICE”.
82
83SELECT AVG(PRICE) AS AVERAGEPRICE FROM LMS_BOOK_DETAILS WHERE CATEGORY = 'JAVA'
84
85
86Problem #14:
87Write a query to display the supplier id, supplier name and email of the suppliers who are all having gmail account.
88
89SELECT SUPPLIER_ID, SUPPLIER_NAME, EMAIL FROM LMS_SUPPLIERS_DETAILS WHERE EMAIL LIKE '%gmail.com%'
90
91Problem#15:
92Write 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. 
93
94SELECT SUPPLIER_ID, SUPPLIER_NAME, COALESCE( CONTACT||'', EMAIL, ADDRESS) AS CONTACTDETAILS FROM LMS_SUPPLIERS_DETAILS
95
96Problem#16:
97Write 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.
98
99SELECT SUPPLIER_ID, SUPPLIER_NAME, CASE WHEN CONTACT IS NULL THEN 'No' ELSE 'Yes' END  AS PHONE_NUM_AVAILABLE FROM LMS_SUPPLIERS_DETAILS
100
101Problem#17:
102Write 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”.
103
104SELECT M.MEMBER_ID, M.MEMBER_NAME, M.CITY, M.MEMBERSHIP_STATUS, SUM(F.FINE_AMOUNT) AS Fine  FROM LMS_MEMBERS M
105JOIN LMS_BOOK_ISSUE I ON I.MEMBER_ID = M.MEMBER_ID 
106JOIN LMS_FINE_DETAILS F ON F.FINE_RANGE = I.FINE_RANGE GROUP BY M.MEMBER_ID, M.MEMBER_NAME, M.MEMBERSHIP_STATUS, M.CITY
107
108
109
110
111
112
113Average Questions:
114Problem # 1:
115Write a query to display the member id, member name of the members, book code and book title of the books taken by them.
116SELECT DISTINCT M.MEMBER_ID, M.MEMBER_NAME, B.BOOK_CODE, B.BOOK_TITLE FROM LMS_MEMBERS M JOIN LMS_BOOK_ISSUE I ON M.MEMBER_ID = I.MEMBER_ID
117
118JOIN LMS_BOOK_DETAILS B ON B.BOOK_CODE = I.BOOK_CODE
119
120Problem # 2:
121Write 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. 
122
123SELECT B.BOOK_CODE FROM LMS_BOOK_DETAILS B FULL JOIN LMS_BOOK_ISSUE I ON B.BOOK_CODE = I.BOOK_CODE WHERE B.BOOK_CODE NOT IN ( SELECT BOOK_CODE FROM LMS_BOOK_ISSUE)
124
125Problem # 3:
126Write a query to display the member id, member name, fine range and fine amount of the members whose fine amount is less than 100. 
127
128SELECT M.MEMBER_ID, M.MEMBER_NAME, M.CITY, I.FINE_RANGE , SUM(FINE_AMOUNT) FROM LMS_MEMBERS M
129JOIN LMS_BOOK_ISSUE I ON I.MEMBER_ID = M.MEMBER_ID 
130JOIN LMS_FINE_DETAILS F ON F.FINE_RANGE = I.FINE_RANGE GROUP BY M.MEMBER_ID, M.MEMBER_NAME, M.CITY, I.FINE_RANGE HAVING SUM(FINE_AMOUNT) < 100
131
132Problem # 4:
133Write 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.
134
135SELECT B.BOOK_CODE, B.BOOK_TITLE, B.PUBLICATION, B.BOOK_EDITION, B.PRICE, B.PUBLISH_DATE FROM LMS_BOOK_DETAILS B ORDER BY B.PUBLISH_DATE, B.PUBLICATION, B.BOOK_EDITION
136
137Problem # 5:
138Write 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. 
139
140SELECT B.BOOK_CODE, B.BOOK_TITLE, B.RACK_NUM FROM LMS_BOOK_DETAILS B WHERE B.RACK_NUM = 'A1' ORDER BY B.BOOK_TITLE
141
142
143
144
145
146
147Problem # 6:
148Write 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. 
149
150SELECT M.MEMBER_ID , M.MEMBER_NAME, I.DATE_RETURN, I.DATE_RETURNED FROM LMS_MEMBERS M JOIN LMS_BOOK_ISSUE I ON M.MEMBER_ID = I.MEMBER_ID
151
152WHERE I.DATE_RETURN < I.DATE_RETURNED
153
154Problem # 7:
155Write a query to display the member id, member name and date of registration who have not taken any book. 
156
157SELECT M.MEMBER_ID, M.MEMBER_NAME, M.DATE_REGISTER FROM LMS_MEMBERS M FULL JOIN LMS_BOOK_ISSUE I ON M.MEMBER_ID = I.MEMBER_ID WHERE M.MEMBER_ID NOT IN ( SELECT MEMBER_ID FROM LMS_BOOK_ISSUE)
158
159Problem # 8:
160Write a Query to display the member id and member name of the members who has not paid any fine in the year 2012.
161
162SELECT M.MEMBER_ID, M.MEMBER_NAME, M.DATE_REGISTER FROM LMS_MEMBERS M FULL JOIN LMS_BOOK_ISSUE I ON M.MEMBER_ID = I.MEMBER_ID WHERE M.MEMBER_ID NOT IN ( SELECT MEMBER_ID FROM LMS_BOOK_ISSUE) OR I.FINE_RANGE = 'R0'
163
164Problem # 9:
165Write 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”.
166
167SELECT DATE_ISSUE, COUNT(DATE_ISSUE)AS NOOFBOOKS FROM LMS_BOOK_ISSUE GROUP BY DATE_ISSUE HAVING COUNT(DATE_ISSUE) IN ( SELECT MAX(COUNT(DATE_ISSUE)) FROM LMS_BOOK_ISSUE GROUP BY DATE_ISSUE )
168
169Problem # 10:
170Write 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’. 
171
172SELECT B.BOOK_TITLE, S.SUPPLIER_ID FROM LMS_BOOK_DETAILS B JOIN LMS_SUPPLIERS_DETAILS S ON B.SUPPLIER_ID = S.SUPPLIER_ID WHERE B.AUTHOR = 'Herbert Schildt' AND B.BOOK_EDITION =5 AND S.SUPPLIER_ID = 'S01'
173
174Problem # 11:
175Write 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.
176
177SELECT RACK_NUM, COUNT(RACK_NUM) AS NOOFBOOKS FROM LMS_BOOK_DETAILS GROUP BY RACK_NUM ORDER BY RACK_NUM
178
179Problem # 12:
180Write 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, issue status, fine amount. 
181
182SELECT I.BOOK_ISSUE_NO, M.MEMBER_NAME, M.DATE_REGISTER, M.DATE_EXPIRE, B.BOOK_TITLE, B.CATEGORY, B.PRICE, I.DATE_ISSUE, I.DATE_RETURN, I.DATE_RETURNED, 
183CASE WHEN DATE_RETURNED||'' IS NULL THEN 'NOT AVAILABLE'
184     ELSE 'AVAILABLE'
185     END
186AS ISSUE_STATUS,  F.FINE_AMOUNT
187FROM LMS_BOOK_DETAILS B JOIN LMS_BOOK_ISSUE I ON B.BOOK_CODE = I.BOOK_CODE 
188JOIN LMS_MEMBERS M ON M.MEMBER_ID = I.MEMBER_ID
189JOIN LMS_FINE_DETAILS F ON F.FINE_RANGE = I.FINE_RANGE
190
191
192Problem # 13:
193Write a query to display the book code, title, publish date of the books which is been published in the month of December. 
194
195SELECT B.BOOK_CODE, B.BOOK_TITLE, B.PUBLISH_DATE FROM LMS_BOOK_DETAILS B WHERE B.PUBLISH_DATE||'' LIKE '12/%%/%%%%'
196
197Problem # 14:
198Write a query to display the book code, book title ,supplier name and price of the book witch takes maximum price based on each supplier.
199
200SELECT B.BOOK_CODE, B.BOOK_TITLE, S.SUPPLIER_NAME, B.PRICE FROM LMS_BOOK_DETAILS B JOIN LMS_SUPPLIERS_DETAILS S ON S.SUPPLIER_ID = B.SUPPLIER_ID WHERE 
201( S.SUPPLIER_ID, PRICE) IN ( SELECT S.SUPPLIER_ID, MAX(B.PRICE) FROM LMS_BOOK_DETAILS B JOIN LMS_SUPPLIERS_DETAILS S ON S.SUPPLIER_ID = B.SUPPLIER_ID GROUP BY S.SUPPLIER_ID )
202
203
204
205
206
207
208
209
210
211
212Problem # 15:
213
214Write a query to display book code, book name, and publisher, how old the book is. Sorted as older to newer.
215
216
217SELECT BOOK_CODE, BOOK_TITLE, PUBLICATION, FLOOR((CURRENT_DATE-PUBLISH_DATE)/365)YEARSOLD FROM LMS_BOOK_DETAILS ORDER BY YEARSOLD DESC
218
219
220
221
222
223
224
225Complex Questions:
226Problem # 1:
227Write 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. 
228Example:
229BOOK_CODE	BOOK_TITLE	SUPPLIER_NAME
230BL000008	Easy Reference for Java   	ABC STORE
231BL000001	Easy Reference for C     	               ABC STORE
232BL000003	Easy Reference for VB    	ABC STORE
233
234SELECT B.BOOK_CODE, S.SUPPLIER_NAME, B.BOOK_TITLE FROM LMS_BOOK_DETAILS B 
235
236JOIN LMS_SUPPLIERS_DETAILS S ON B.SUPPLIER_ID = S.SUPPLIER_ID
237WHERE S.SUPPLIER_ID IN ( SELECT S.SUPPLIER_ID FROM LMS_BOOK_DETAILS B 
238
239JOIN LMS_SUPPLIERS_DETAILS S ON B.SUPPLIER_ID = S.SUPPLIER_ID GROUP BY S.SUPPLIER_ID HAVING COUNT(S.SUPPLIER_ID) IN
240
241(SELECT max(COUNT(S.SUPPLIER_ID)) FROM LMS_BOOK_DETAILS B 
242JOIN LMS_SUPPLIERS_DETAILS S ON B.SUPPLIER_ID = S.SUPPLIER_ID GROUP BY S.SUPPLIER_ID) )
243
244
245
246
247
248
249
250
251
252
253Problem # 2:
254Write 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. 
255
256Example: 
257MEMBER_ID             MEMBER_NAME           REMAININGBOOKS
258LM001                        RAMESH	                    1
259LM002                        MOHAN	                    3
260
261SELECT  M.MEMBER_NAME, M.MEMBER_ID, 3-COUNT(M.MEMBER_ID) AS REMAINING_BOOKS  FROM LMS_MEMBERS M 
262JOIN  LMS_BOOK_ISSUE I ON M.MEMBER_ID = I.MEMBER_ID GROUP BY M.MEMBER_NAME, M.MEMBER_ID
263
264
265Problem # 3 
266Write 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. 
267Example:
268SUPPLIER_ID	SUPPLIER_NAME
269S04	               XYZ STORE
270
271
272SELECT S.SUPPLIER_ID, S.SUPPLIER_NAME FROM LMS_BOOK_DETAILS B 
273
274JOIN LMS_SUPPLIERS_DETAILS S ON B.SUPPLIER_ID = S.SUPPLIER_ID
275WHERE S.SUPPLIER_ID IN ( SELECT S.SUPPLIER_ID FROM LMS_BOOK_DETAILS B 
276
277JOIN LMS_SUPPLIERS_DETAILS S ON B.SUPPLIER_ID = S.SUPPLIER_ID GROUP BY S.SUPPLIER_ID HAVING COUNT(S.SUPPLIER_ID) IN
278
279(SELECT MIN(COUNT(S.SUPPLIER_ID)) FROM LMS_BOOK_DETAILS B 
280JOIN LMS_SUPPLIERS_DETAILS S ON B.SUPPLIER_ID = S.SUPPLIER_ID GROUP BY S.SUPPLIER_ID) )