-
Notifications
You must be signed in to change notification settings - Fork 0
/
Copy pathANSI_SQL_QB_Library_Mgmt_Practise_Lab_ANSWERS.sql
59 lines (58 loc) · 5.13 KB
/
ANSI_SQL_QB_Library_Mgmt_Practise_Lab_ANSWERS.sql
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
59
use lms_db;
select * from LMS_FINE_DETAILS;
select * from LMS_BOOK_ISSUE;
select * from LMS_MEMBERS;
select * from LMS_BOOK_DETAILS;
select * from LMS_SUPPLIERS_DETAILS;
select * from LMS_FINE_DETAILS;
--q0
select s.SUPPLIER_NAME,s.SUPPLIER_ID,count(BOOK_CODE) from LMS_SUPPLIERS_DETAILS as s join LMS_BOOK_DETAILS as l on s.SUPPLIER_ID=l.SUPPLIER_ID group by s.SUPPLIER_NAME,s.SUPPLIER_ID;
--q1
select d.BOOK_TITLE,s.SUPPLIER_NAME from LMS_BOOK_DETAILS as d join LMS_SUPPLIERS_DETAILS as s on d.SUPPLIER_ID=s.SUPPLIER_ID;
--q2
select s.SUPPLIER_ID,count(BOOK_CODE) from LMS_SUPPLIERS_DETAILS as s join LMS_BOOK_DETAILS as l on s.SUPPLIER_ID=l.SUPPLIER_ID group by s.SUPPLIER_NAME,s.SUPPLIER_ID;
--q2
select m.MEMBER_NAME,COUNT(d.BOOK_CODE) as bookcode from LMS_MEMBERS as m join LMS_BOOK_ISSUE as b on m.MEMBER_ID = b.MEMBER_ID join LMS_BOOK_DETAILS as d on b.BOOK_CODE=d.BOOK_CODE group by m.MEMBER_NAME;
--q3
select BOOK_TITLE,DATE_ISSUE from LMS_BOOK_DETAILS as b join LMS_BOOK_ISSUE as i on b.BOOK_CODE=i.BOOK_CODE;
--q4
select m.MEMBER_ID,d.PRICE from LMS_MEMBERS as m join LMS_BOOK_ISSUE as b on m.MEMBER_ID=b.MEMBER_ID join LMS_BOOK_DETAILS as d on b.BOOK_CODE=d.BOOK_CODE;
--q5
select m.MEMBER_NAME,DATEDIFF(day,i.DATE_RETURN,i.DATE_RETURNED) AS DateDiff from LMS_MEMBERS as m join LMS_BOOK_ISSUE as i on m.MEMBER_ID=i.MEMBER_ID;
--q6
select d.BOOK_TITLE,m.MEMBER_NAME from LMS_BOOK_DETAILS as d join LMS_BOOK_ISSUE as i on d.BOOK_CODE=i.BOOK_CODE join LMS_MEMBERS as m on i.MEMBER_ID=m.MEMBER_ID join LMS_SUPPLIERS_DETAILS as s on d.SUPPLIER_ID=s.SUPPLIER_ID;
--q7
select d.BOOK_TITLE,m.MEMBER_NAME,s.SUPPLIER_NAME from LMS_BOOK_DETAILS as d join LMS_BOOK_ISSUE as i on d.BOOK_CODE=i.BOOK_CODE join LMS_MEMBERS as m on i.MEMBER_ID=m.MEMBER_ID join LMS_SUPPLIERS_DETAILS as s on d.SUPPLIER_ID=s.SUPPLIER_ID;
--q8
select BOOK_TITLE,PRICE from LMS_BOOK_DETAILS;
--simple q17
select m.MEMBER_ID,m.MEMBER_NAME,m.CITY,m.MEMBERSHIP_STATUS,SUM(f.FINE_AMOUNT) as fine from LMS_MEMBERS as m join LMS_BOOK_ISSUE as s on m.MEMBER_ID=s.MEMBER_ID join LMS_BOOK_DETAILS as d on s.BOOK_CODE=d.BOOK_CODE join LMS_FINE_DETAILS as f on s.FINE_RANGE=f.FINE_RANGE group by m.MEMBER_ID,m.MEMBER_NAME,m.CITY,m.MEMBERSHIP_STATUS;
--average q1
select m.MEMBER_ID,m.MEMBER_NAME,d.BOOK_CODE,d.BOOK_TITLE from LMS_MEMBERS as m join LMS_BOOK_ISSUE as s on m.MEMBER_ID=s.MEMBER_ID join LMS_BOOK_DETAILS as d on s.BOOK_CODE=d.BOOK_CODE;
--q3
select m.MEMBER_ID,m.MEMBER_NAME,f.FINE_RANGE,f.FINE_AMOUNT from LMS_MEMBERS as m join LMS_BOOK_ISSUE as s on m.MEMBER_ID=s.MEMBER_ID join LMS_FINE_DETAILS as f on s.FINE_RANGE=f.FINE_RANGE where FINE_AMOUNT < 100;
--q5
select BOOK_CODE,BOOK_TITLE,RACK_NUM as Rack from LMS_BOOK_DETAILS where RACK_NUM = 'A1' order by BOOK_TITLE;
--q6
select m.MEMBER_ID,m.MEMBER_NAME,s.DATE_RETURN AS DUE_DATE,s.DATE_RETURNED from LMS_MEMBERS as m join LMS_BOOK_ISSUE as s on m.MEMBER_ID=s.MEMBER_ID where s.DATE_RETURN<s.DATE_RETURNED;
--q7
select m.MEMBER_ID,m.MEMBER_NAME,m.DATE_REGISTER from LMS_MEMBERS as m join LMS_BOOK_ISSUE as s on m.MEMBER_ID=s.MEMBER_ID where s.BOOK_ISSUE_NO=NULL;
--q8(((doubt))
select m.MEMBER_ID,m.MEMBER_NAME,b.FINE_AMOUNT from LMS_MEMBERS as m join LMS_BOOK_ISSUE as s on m.MEMBER_ID=s.MEMBER_ID join LMS_FINE_DETAILS as b on s.FINE_RANGE=b.FINE_RANGE where FINE_AMOUNT=0;
--q10
select d.BOOK_TITLE,d.SUPPLIER_ID,d.AUTHOR from LMS_BOOK_DETAILS as d join LMS_SUPPLIERS_DETAILS as s on d.SUPPLIER_ID=s.SUPPLIER_ID where d.AUTHOR = 'Herbert Schildt' and d.BOOK_EDITION =5 and d.SUPPLIER_ID='S01';
--q11
select RACK_NUM,COUNT(BOOK_CODE) from LMS_BOOK_DETAILS as numbe_of_books group by RACK_NUM order by RACK_NUM asc;
--q12
select d.BOOK_TITLE,d.CATEGORY,d.AUTHOR,d.PRICE,s.DATE_ISSUE as date_of_issue,s.DATE_RETURN as Date_Of_Return,s.DATE_RETURNED as Actuall_Returned_Date,m.MEMBERSHIP_STATUS as Issue_Status,s.FINE_RANGE as Fine_Amount from LMS_BOOK_DETAILS as d join LMS_BOOK_ISSUE as s on d.BOOK_CODE=s.BOOK_CODE join LMS_MEMBERS as m on s.MEMBER_ID=m.MEMBER_ID;
--q13
select BOOK_CODE,BOOK_TITLE,PUBLISH_DATE from LMS_BOOK_DETAILS where MONTH(PUBLISH_DATE)=12;
--q15
select BOOk_CODE,BOOK_TITLE,PUBLICATION,PUBLISH_DATE from LMS_BOOK_DETAILS order by PUBLISH_DATE asc;
--complex q1
select BOOK_CODE,BOOK_TITLE,SUPPLIER_NAME,l1.SUPPLIER_ID from LMS_BOOK_DETAILS as l1 join LMS_SUPPLIERS_DETAILS as l2 on l1.SUPPLIER_ID=l2.SUPPLIER_ID where l1.SUPPLIER_ID = (select s1.SUPPLIER_ID from LMS_BOOK_DETAILS as s1 group by s1.SUPPLIER_ID having COUNT(s1.SUPPLIER_ID) >= all(select COUNT(SUPPLIER_ID) from LMS_BOOK_DETAILS group by SUPPLIER_ID));
--complex q2
select L1.MEMBER_ID,L1.MEMBER_NAME,(3- COUNT(L2.MEMBER_ID) ) AS Remaining_Books from LMS_MEMBERS as l1 join LMS_BOOK_ISSUE as l2 on l1.MEMBER_ID=l2.MEMBER_ID GROUP BY l1.MEMBER_ID,l1.MEMBER_NAME;
--complex q3
select BOOK_CODE,BOOK_TITLE,SUPPLIER_NAME,l1.SUPPLIER_ID from LMS_BOOK_DETAILS as l1 join LMS_SUPPLIERS_DETAILS as l2 on l1.SUPPLIER_ID=l2.SUPPLIER_ID where l1.SUPPLIER_ID = (select s1.SUPPLIER_ID from LMS_BOOK_DETAILS as s1 group by s1.SUPPLIER_ID having COUNT(s1.SUPPLIER_ID) <= all(select COUNT(SUPPLIER_ID) from LMS_BOOK_DETAILS group by SUPPLIER_ID));
select * from LMS_BOOK_DETAILS;