· 10 years ago · Jun 25, 2016, 07:12 AM
1use hms;
2
3select * from room_allocation;
4select * from patient_master;
5select * from doctor_master;
6select * from room_master;
7
8
9
10/* 1: Display the patient_id, patient_name , phone number,
11( if phone number is not there display address) for the people who are staying more then 10 days.*/
12select p.pid,name,phoneno from patient_master p,room_allocation r where p.pid=r.pid and (release_date-adm_date)>10;
13
14
15/* 2:Display the patient_id, patient_name, phone number( +91-999-999-9999 format) , type_of_ailment .
16 type of ailement is decided with the number of days stay in hospital.
17 if stay <=5 ---- Minor
18 if stay >5 and <=15 days ----Medium
19 if stay >15 days ---- Major */
20
21select pid,name,phoneno
22from patient_master;
23select pid,sum(datediff(release_date,adm_date))
24from room_allocation
25group by pid;
26
27select p.pid,p.name,concat('+91-', substr(p.phoneno,1,3),'-',substr(p.phoneno,4,3),'-',substr(p.phoneno,7)) as 'Phone No',
28 case
29 when t.count<=5 or t.count is null then 'Minor'
30 when t.count>5 and t.count<=15 then 'Medium'
31 else 'Major'
32 end
33from patient_master p left outer join ( select pid,sum(datediff(release_date,adm_date)) as 'count'
34 from room_allocation
35 group by pid
36 )t on p.pid=t.pid;
37
38
39
40
41 select * from patient_master;
42 /* 9123456789 */
43 select concat('+91-', substr(phoneno,1,3),'-',substr(phoneno,4,3),'-',substr(phoneno,7))
44 from patient_master;
45
46
47
48
49
50/* 3:Display the doctor_id and doctor name who is treating maximum patients. */
51select distinct d.doctorid,doctorname from doctor_master d,patient_master p where p.doctorid=d.doctorid and d.doctorid=(
52select doctorid from patient_master group by doctorid having count(*)=(
53select max(c) from(
54select count(*)c from patient_master group by doctorid
55)a
56)
57);
58
59
60
61/* 4:Display the patients who were admitted in the month of january.*/
62select r.pid,p.name from room_allocation r,patient_master p where p.pid=r.pid and adm_date like '%-12-%';
63
64
65/* 5:Display the patient_id and patient_name who paid more then once.*/
66select distinct p.pid,p.name from patient_master p,bill_payment b where p.pid=b.pid and b.pid=(
67select pid from bill_payment group by pid having count(*)>1);
68
69
70/* 6:Display the doctor_id, doctor_name and count of patients.
71Display the data in descending order of the cont_of_patients. */
72select d.doctorid,d.doctorname,count(*) Count_no_patients
73from patient_master p,doctor_master d where p.doctorid=d.doctorid group by p.doctorid asc;
74
75
76/* 7: Display the room_no, room_type which are allocated more then once.*/
77select room_no,room_type from room_master where room_no=
78(select room_no from room_allocation group by room_no having count(*)>1);
79
80
81/*8:Display the room_no, room_type which are allocated more then once to the same patient. */
82
83insert into room_allocation values('R0003','P0005','12-6-22','12-6-24');
84 select * from room_allocation;
85 select * from room_master;
86
87
88select distinct a.room_no,m.room_type
89from room_allocation a,room_master m
90where a.room_no in
91 (select room_no
92 from room_allocation
93 group by pid
94 having count(*)>1) and a.room_no=m.room_no;
95
96
97
98 /* 9: display the patient_id,patient_name, doctor_id, doctor_name, room_id, room_type, adm_date, bill_id, amount .
99 Amount should be rounded of.*/
100select distinct p.pid,p.name,d.doctorid,d.doctorname,r.room_no,r.room_type,a.adm_date,b.billid,ROUND(amount,0) as Amount
101from patient_master p,doctor_master d,room_master r,room_allocation a,bill_payment b
102where b.pid=p.pid and d.doctorid=p.doctorid and p.pid=a.pid and r.room_no=a.room_no;
103
104
105/* 10: Display the patient_id, patient_name, billid, amount.
106Amount should be rounded of to single place of decimal.*/
107select p.pid,name,billid,ROUND(amount,1)
108from patient_master p,bill_payment b
109where p.pid=b.pid;
110
111/* 11: Display the room_no which was never allocated to any patient.*/
112select room_no from room_master where room_no not in(
113select r.room_no from room_allocation r,patient_master m where r.pid=m.pid group by room_no);
114
115/* 12: Display the the doctors_id who never treated any patients.*/
116select doctorid from doctor_master where doctorid not in(
117select p.doctorid from patient_master p,doctor_master d where d.doctorid=p.doctorid group by pid);
118
119/* 13:The depatment which are having the maximum number of doctors.*/
120select dept from doctor_master group by dept having count(*)=(
121select max(c) from(
122select count(*)c from doctor_master group by dept)a
123);
124
125
126/* 14: Count the number of male and female patients.*/
127select gender,count(*) as count from patient_master group by gender;
128select count(*) as F from patient_master group by gender having gender="F";
129select count(*) as F from patient_master group by gender having gender="M";
130
131/* 15:Count the %age of male and female MALE 20% FEMALE 80%*/
132select * from patient_master;
133 select gender,round((sum(age)/s.sm)*100) as '%age'
134 from patient_master,(select sum(age) as sm from patient_master)s
135 group by gender;
136
137
138select gender,concat('%',cast(round((sum(age)/t.sm)*100) as char))
139from patient_master,( select sum(age) as sm
140 from patient_master
141 )t
142group by gender;
143
144
145
146
147
148
149
150
151use lms;
152Create table LMS_MEMBERS
153(
154 MEMBER_ID Varchar(10),
155 MEMBER_NAME Varchar(30) NOT NULL,
156 CITY Varchar(20),
157 DATE_REGISTER Date NOT NULL,
158 DATE_EXPIRE Date ,
159 MEMBERSHIP_STATUS Varchar(15)NOT NULL,
160 Constraint LMS_cts1 PRIMARY KEY(MEMBER_ID)
161);
162Create table LMS_SUPPLIERS_DETAILS
163(
164 SUPPLIER_ID Varchar(3),
165 SUPPLIER_NAME Varchar(30) NOT NULL,
166 ADDRESS Varchar(50),
167 CONTACT bigint(10) NOT NULL,
168 EMAIL Varchar(15) NOT NULL,
169 Constraint LMS_cts2 PRIMARY KEY(SUPPLIER_ID)
170);
171Create table LMS_FINE_DETAILS
172(
173 FINE_RANGE Varchar(3),
174 FINE_AMOUNT decimal(10,2) NOT NULL,
175 Constraint LMS_cts3 PRIMARY KEY(FINE_RANGE)
176);
177Create table LMS_BOOK_DETAILS
178(
179 BOOK_CODE Varchar(10),
180 BOOK_TITLE Varchar(50) NOT NULL,
181 CATEGORY Varchar(15) NOT NULL,
182 AUTHOR Varchar(30) NOT NULL,
183 PUBLICATION Varchar(30),
184 PUBLISH_DATE Date,
185 BOOK_EDITION int(2),
186 PRICE decimal(8,2) NOT NULL,
187 RACK_NUM Varchar(3),
188 DATE_ARRIVAL Date NOT NULL,
189 SUPPLIER_ID Varchar(3) NOT NULL,
190 Constraint LMS_cts4 PRIMARY KEY(BOOK_CODE),
191 Constraint LMS_cts41 FOREIGN KEY(SUPPLIER_ID) References LMS_SUPPLIERS_DETAILS(SUPPLIER_ID)
192);
193Create table LMS_BOOK_ISSUE
194(
195 BOOK_ISSUE_NO int,
196 MEMBER_ID Varchar(10) NOT NULL,
197 BOOK_CODE Varchar(10) NOT NULL,
198 DATE_ISSUE Date NOT NULL,
199 DATE_RETURN Date NOT NULL,
200 DATE_RETURNED Date NULL,
201 FINE_RANGE Varchar(3),
202 Constraint LMS_cts5 PRIMARY KEY(BOOK_ISSUE_NO),
203 Constraint LMS_Mem FOREIGN KEY(MEMBER_ID) References LMS_MEMBERS(MEMBER_ID),
204 Constraint LMS_BookDetail FOREIGN KEY(BOOK_CODE) References LMS_BOOK_DETAILS(BOOK_CODE),
205 Constraint LMS_FineDetail FOREIGN KEY(FINE_RANGE) References LMS_FINE_DETAILS(FINE_RANGE)
206);
207Insert into LMS_MEMBERS
208Values('LM001', 'AMIT', 'CHENNAI', '2012-02-12', '2013-02-11','Temporary');
209Insert into LMS_MEMBERS
210Values('LM002', 'ABDHUL', 'DELHI', '2012-04-10', '2013-04-09','Temporary');
211Insert into LMS_MEMBERS
212Values('LM003', 'GAYAN', 'CHENNAI', '2012-05-13','2013-05-12', 'Permanent');
213Insert into LMS_MEMBERS
214Values('LM004', 'RADHA', 'CHENNAI', '2012-04-22', '2013-04-21', 'Temporary');
215Insert into LMS_MEMBERS
216Values('LM005', 'GURU', 'BANGALORE', '2012-03-30', '2013-05-16','Temporary');
217Insert into LMS_MEMBERS
218Values('LM006', 'MOHAN', 'CHENNAI', '2012-04-12', '2013-05-16','Temporary');
219
220
221Insert into LMS_SUPPLIERS_DETAILS
222Values ('S01','SINGAPORE SHOPPEE', 'CHENNAI', 9894123555,'sing@gmail.com');
223Insert into LMS_SUPPLIERS_DETAILS
224Values ('S02','JK Stores', 'MUMBAI', 9940123450 ,'jks@yahoo.com');
225Insert into LMS_SUPPLIERS_DETAILS
226Values ('S03','ROSE BOOK STORE', 'TRIVANDRUM', 9444411222,'rose@gmail.com');
227Insert into LMS_SUPPLIERS_DETAILS
228Values ('S04','KAVARI STORE', 'DELHI', 8630001452,'kavi@redif.com');
229Insert into LMS_SUPPLIERS_DETAILS
230Values ('S05','EINSTEN BOOK GALLARY', 'US', 9542000001,'eingal@aol.com');
231Insert into LMS_SUPPLIERS_DETAILS
232Values ('S06','AKBAR STORE', 'MUMBAI',7855623100 ,'akbakst@aol.com');
233
234Insert into LMS_FINE_DETAILS Values('R0', 0);
235Insert into LMS_FINE_DETAILS Values('R1', 20);
236insert into LMS_FINE_DETAILS Values('R2', 50);
237Insert into LMS_FINE_DETAILS Values('R3', 75);
238Insert into LMS_FINE_DETAILS Values('R4', 100);
239Insert into LMS_FINE_DETAILS Values('R5', 150);
240Insert into LMS_FINE_DETAILS Values('R6', 200);
241
242Insert into LMS_BOOK_DETAILS
243Values('BL000001', 'Java How To Do Program', 'JAVA', 'Paul J. Deitel', 'Prentice Hall', '1999-12-10', 6, 600.00, 'A1', '2011-05-10', 'S01');
244Insert into LMS_BOOK_DETAILS
245Values('BL000002', 'Java: The Complete Reference ', 'JAVA', 'Herbert Schildt', 'Tata Mcgraw Hill ', '2011-10-10', 5, 750.00, 'A1', '2011-05-10', 'S03');
246Insert into LMS_BOOK_DETAILS
247Values('BL000003', 'Java How To Do Program', 'JAVA', 'Paul J. Deitel', 'Prentice Hall', '1999-05-10', 6, 600.00, 'A1', '2012-05-10', 'S01');
248Insert into LMS_BOOK_DETAILS
249Values('BL000004', 'Java: The Complete Reference ', 'JAVA', 'Herbert Schildt', 'Tata Mcgraw Hill ', '2011-10-10', 5, 750.00, 'A1', '2012-05-11', 'S01');
250Insert into LMS_BOOK_DETAILS
251Values('BL000005', 'Java How To Do Program', 'JAVA', 'Paul J. Deitel', 'Prentice Hall', '1999-12-10', 6, 600.00, 'A1', '2012-05-11', 'S01');
252Insert into LMS_BOOK_DETAILS
253Values('BL000006', 'Java: The Complete Reference ', 'JAVA', 'Herbert Schildt', 'Tata Mcgraw Hill ', '2011-10-10', 5, 750.00, 'A1', '2012-05-12', 'S03');
254Insert into LMS_BOOK_DETAILS
255Values('BL000007', 'Let Us C', 'C', 'Yashavant Kanetkar ', 'BPB Publications', '2010-12-11', 9, 500.00 , 'A3', '2010-11-03', 'S03');
256Insert into LMS_BOOK_DETAILS
257Values('BL000008', 'Let Us C', 'C', 'Yashavant Kanetkar ','BPB Publications', '2010-05-12', 9, 500.00 , 'A3', '2011-08-09', 'S04');
258
259
260
261Insert into LMS_BOOK_ISSUE
262Values (001, 'LM001', 'BL000001', '2012-05-01', '2012-05-16', '2012-05-16', 'R0');
263Insert into LMS_BOOK_ISSUE
264Values (002, 'LM002', 'BL000002', '2012-05-01', '2012-05-06','2012-05-16', 'R2');
265Insert into LMS_BOOK_ISSUE
266Values (003, 'LM003', 'BL000007', '2012-04-01', '2012-04-16', '2012-04-20','R1');
267Insert into LMS_BOOK_ISSUE
268Values (004, 'LM004', 'BL000005', '2012-04-01', '2012-04-16','2012-04-20', 'R1');
269Insert into LMS_BOOK_ISSUE
270Values (005, 'LM005', 'BL000008', '2012-03-30', '2012-04-15','2012-04-20' , 'R1');
271Insert into LMS_BOOK_ISSUE
272Values (006, 'LM005', 'BL000008', '2012-04-20', '2012-05-05','2012-05-05' , 'R0');
273Insert into LMS_BOOK_ISSUE
274Values (007, 'LM003', 'BL000007', '2012-04-22', '2012-05-07','2012-05-25' , 'R4');
275
276
277
278/*-------------------Simple qry with solutions------------------------*/
279
280/* Problem # 1:
281Write a query to display the member id, member name, city and membership status who are all having life time membership.
282Hint: Life time membership status is “Permanentâ€.*/
283select member_id,member_name,city,membership_status from lms_members
284where membership_status="permanent";
285
286
287/*Problem # 2:
288Write a query to display the member id, member name who have not returned the books.
289Hint: Book return status is book_issue_status ='Y' or 'N'.*/
290select member_id from lms_members where member_id not in
291(select member_id from lms_book_issue where date_returned is not null);
292
293/*Problem # 3:
294Write a query to display the member id, member name who have taken the book with book code 'BL000002'.*/
295select b.member_id,m.member_name from lms_members m,lms_book_issue b
296where b.member_id=m.member_id and b.book_code="BL000002";
297
298
299/*Problem # 4:
300Write a query to display the book code, book title and author of the books whose author name begins with 'P'.*/
301select book_code,book_title,author from lms_book_details
302where author like "p%";
303
304/*Problem # 5:
305Write a query to display the total number of Java books available in library with alias name ‘NO_OF_BOOKS’.*/
306select distinct count(*) as 'NO_OF_BOOKS' from lms_book_details
307where category = 'JAVA' group by category;
308
309/*Problem # 6:
310Write a query to list the category and number of books in each category with alias name ‘NO_OF_BOOKS’.*/
311select count(*) as 'NO OF BOOKS',category from lms_book_details
312group by category;
313
314/*Problem # 7:
315Write a query to display the number of books published by "Prentice Hall†with the alias name “NO_OF_BOOKSâ€.*/
316select count(*) as 'NO OF BOOKS' from lms_book_details
317where publication='Prentice Hall' group by publication;
318
319/* Problem # 8:
320Write a query to display the book code, book title of the books which are issued on the date "1st April 2012".*/
321select i.book_code,d.book_title from lms_book_details d,lms_book_issue i
322where i.book_code=d.book_code and i.date_issue like '2012-04-01';
323
324/*Problem # 9:
325Write a query to display the member id, member name, date of registration and expiry date of the members
326whose membership expiry date is before APR 2013.*/
327select member_id from lms_members where date_expire<"2013-04-01";
328
329
330/* Problem # 10:
331write a query to display the member id, member name, date of registration, membership status of
332the members who registered before "March 2012" and membership status is "Temporary"*/
333select member_id,member_name,date_register,membership_status
334from lms_members where membership_status="temporary" and date_register>"2012-03-01";
335
336
337/*Problem #11:
338Write a query to display the member id, member name who’s City is CHENNAI or DELHI. Hint:
339Display the member name in title case with alias name 'Name'.*/
340select member_id,member_name as 'name' from lms_members
341where city="chennai" or city="delhi";
342
343/*Problem #12:
344Write a query to concatenate book title, author and display in the following format.
345Book_Title_is_written_by_Author
346Example: Let Us C_is_written_by_Yashavant Kanetkar
347Hint: display unique books. Use “BOOK_WRITTEN_BY†as alias name.*/
348select concat(book_title,'is written by',author) as 'book_written_by'
349from lms_book_details;
350
351/*Problem #13:
352Write a query to display the average price of books which is belonging to ‘JAVA’ category with alias
353name “AVERAGEPRICEâ€.*/
354select avg(price) from lms_book_details
355where category="java";
356
357/*Problem #14:
358Write a query to display the supplier id, supplier name and email of the suppliers who are all having gmail account.*/
359select supplier_id,supplier_name,email
360from lms_suppliers_details
361where email like "%@gmail.com";
362
363/*Problem#15:
364Write a query to display the supplier id, supplier name and contact details.
365Contact details can be either phone number or email or address with alias name “CONTACTDETAILSâ€.
366If phone number is null then display email, even if email also null then display the address of the supplier.
367Hint: Use Coalesce function.*/
368select supplier_id,supplier_name,coalesce(cast(contact as char),cast(email as char),cast(address as char)) as 'Contact'
369from lms_suppliers_details;
370
371/*Problem#16:
372Write a query to display the supplier id, supplier name and contact.
373If phone number is null then display ‘No’ else display ‘Yes’ with alias name “PHONENUMAVAILABLEâ€. Hint: Use NVL2.*/
374
375select supplier_id,supplier_name,
376 case
377when contact is null then 'no'
378else
379'yes'
380end as 'phoneavailable'
381from lms_suppliers_details;
382
383
384
385/*----------------------Average query-------------------------------------*/
386/*Problem # 1:
387Write a query to display the member id, member name of the members, book code and book title of the books taken
388by them.*/
389select m.member_id,m.member_name,d.book_code,d.book_title
390from lms_members m,lms_book_issue i,lms_book_details d
391where d.book_code=i.book_code and m.member_id=i.member_id;
392
393/*Problem # 2:
394Write a query to display the total number of books available in the library with alias name “NO_OF_BOOKS_AVAILABLEâ€
395(Which is not issued). Hint: The issued books details are available in the LMS_BOOK_ISSUE table.*/
396select count(*) as 'NO_of_books' from lms_book_details where book_code not in
397(select book_code from lms_book_issue) group by book_code;
398
399/*Problem # 3:
400Write a query to display the member id, member name, fine range and fine amount of the members
401whose fine amount is less than 100.*/
402select m.member_id,m.member_name,f.fine_range,f.fine_amount
403from lms_fine_details f,lms_members m,lms_book_issue i
404where i.member_id=m.member_id and i.fine_range=f.fine_range
405and f.fine_amount<100;
406
407/* Problem # 4:
408Write a query to display the book code, book title and availability status of the ‘JAVA’ books whose edition is "6â€.
409Show the availability status with alias name “AVAILABILITYSTATUSâ€. Hint: Book availability status can be fetched
410from “BOOK_ISSUE_STATUS†column of LMS_BOOK_ISSUE table.*/
411select book_code,book_title from lms_book_details
412where book_edition=6;
413-- no status available see from amol solutions--
414
415/*Problem # 5:
416Write a query to display the book code, book title and rack number of the books which are placed in rack 'A1'
417and sort by book title in ascending order.*/
418select book_code,book_title,rack_num
419from lms_book_details
420where rack_num='A1'
421order by book_tlms_memberslms_membersitle asc;
422
423/*Problem # 6:
424Write a query to display the member id, member name, due date and date returned of the members who has returned
425the books after the due date. Hint: Date_return is due date and Date_returned is actual book return date.*/
426select member_id,date_return,date_returned
427from lms_book_issue
428where date_returned>date_return;
429
430/*Problem # 7:
431Write a query to display the member id, member name and date of registration who
432have not taken any book.*/
433
434select member_id,member_name,date_register
435from lms_members where member_id not in
436(select member_id from lms_book_issue);
437
438
439/*Problem # 8:
440Write a Query to display the member id and member name of the members who has not paid any fine
441in the year 2012.*/
442select b.member_id,m.member_name from lms_book_issue b,lms_fine_details f,lms_members m
443where b.fine_range=f.fine_range and b.member_id=m.member_id
444and f.fine_range="R0" and b.date_returned like "2012-%-%";
445
446/*Problem # 9:
447Write a query to display the date on which the maximum numbers of books were issued and
448 the number of books issued with alias name “NOOFBOOKSâ€.*/
449select distinct date_issue,count(*) as 'NOOFBOOKS' from lms_book_issue
450group by date_issue having date_issue in
451(select date_issue from lms_book_issue group by date_issue having count(*) in
452(select max(c) from
453(select count(*)c from lms_book_issue
454group by date_issue)a
455)
456);
457
458
459/*Problem # 10:
460Write a query to list the book title and supplier id for the books authored by “Herbert Schildt"
461and the book edition is 5 and supplied by supplier ‘S01’.*/
462select d.book_title,s.supplier_id from lms_book_details d,lms_suppliers_details s
463where d.supplier_id=s.supplier_id and d.author="herbert Schildt"
464and d.book_edition='5' and s.supplier_id="S01";
465
466/*Problem # 11:
467Write a query to display the rack number and the number of books in each rack with alias
468 name “NOOFBOOKS†and sort by rack number in ascending order.*/
469select rack_num,count(*) as 'noofbooks' from lms_book_details group by rack_num asc;
470
471/*Problem # 12:
472Write a query to display book issue number, member name, date or registration, date of expiry,
473book title, category author, price, date of issue, date of return, actual returned date, issue status,
474fine amount.*/
475select s.book_issue_no,m.member_name,m.date_register,m.date_expire,d.book_title,d.category,d.author
476,d.price,s.date_issue,s.date_return,s.date_returned,f.fine_amount
477from lms_book_details d,lms_members m,lms_fine_details f,lms_book_issue s
478where d.book_code=s.book_code and s.member_id=m.member_id
479and f.fine_range=s.fine_range;
480
481/*Problem # 13:
482Write a query to display the book code, title, publish date of the books which is
483been published in the month of December.*/
484select book_code,book_title,publish_date
485from lms_book_details
486where publish_date like "%-12-%";
487
488/*Problem # 14:
489Write a query to display the book code, book title and availability status of the ‘JAVA’ books
490whose edition is "5â€. Show the availability status with alias name “AVAILABILITYSTATUSâ€.
491 Hint: Book availability status can be fetched from “BOOK_ISSUE_STATUS†column of LMS_BOOK_ISSUE table.*/
492select d.book_code,d.book_title,i.book_issue_status as 'AVAILABILITYSTATUS'
493from lms_book_details d,lms_book_issue i
494where d.book_code=i.book_code and d.book_edition="5"
495and d.category="java";
496
497/*--------------COMPLEX QUERY---------------------------*/
498
499/*Problem # 1:
500Write a query to display the book code, book title and supplier name of the supplier who
501has supplied maximum number of books. For example, if “ABC Store†supplied 3 books,
502“LM Store†has supplied 2 books and “XYZ Store†has supplied 1 book. So “ABC Store†has supplied
503maximum number of books, hence display the details as mentioned below.
504Example:BOOK_CODE BOOK_TITLE SUPPLIER_NAME
505BL000008 Easy Reference for Java ABC STORE
506BL000001 Easy Reference for C ABC STORE
507BL000003 Easy Reference for VB ABC STORE*/
508select d.book_code,d.book_title,s.supplier_name
509from lms_book_details d,lms_suppliers_details s
510where s.supplier_id=d.supplier_id and s.supplier_id in
511(select supplier_id from lms_book_details group by supplier_id having count(*)=
512(select max(c) from
513(select count(*)c from lms_book_details group by supplier_id)a
514)
515);
516
517/*Problem # 2:
518Write a query to display the member id, member name and number of remaining books he/she
519 can take with “REMAININGBOOKS†as alias name. Hint: Assuming a member can take maximum 3 books.
520For example, Ramesh has already taken 2 books; he can take only one book now.
521Hence display the remaining books as 1 in below format.
522Example:MEMBER_ID MEMBER_NAME REMAININGBOOKS
523LM001 RAMESH 1
524LM002 MOHAN 3*/
525select s.member_id,m.member_name,3-count(*) as 'Remainingbooks'
526from lms_book_issue s,lms_members m
527where m.member_id=s.member_id
528group by s.book_code;
529
530/*Problem # 3
531Write a query to display the supplier id and supplier name of the supplier who has
532supplied minimum number of books. For example, if “ABC Store†supplied 3 books, “LM Store†has supplied
5332 books and “XYZ Store†has supplied 1 book. So “XYZ Store†has supplied minimum number of books,
534 hence display the details as mentioned below.
535Example:
536SUPPLIER_ID SUPPLIER_NAME
537S04 XYZ STORE*/
538select s.supplier_id,s.supplier_name
539from lms_book_details d,lms_suppliers_details s
540where s.supplier_id=d.supplier_id and d.supplier_id=
541(select supplier_id from lms_book_details group by supplier_id having count(*)=
542(select min(c) from
543(select count(*)c from lms_book_details group by supplier_id)a
544)
545);