tjEyy4Fd

1CREATE TABLE CUSTOMER (
2IDC NUMBER(4) PRIMARY KEY,
3SURNAME VARCHAR2(10),
4FIRSTNAME VARCHAR2(10),
5ADDRESS VARCHAR2(100),
6PHONE NUMBER(20));
7
8CREATE TABLE AUTHOR (
9ID_AUTHOR NUMBER(4) PRIMARY KEY,
10SURNAME VARCHAR2(10),
11FIRSTNAME VARCHAR2(10));
12
13CREATE TABLE CATEGORIES (
14ID_CAT NUMBER(4) PRIMARY KEY,
15CNAME VARCHAR2(20));
16
17CREATE TABLE STAFF (
18ID_STAFF NUMBER(4) PRIMARY KEY,
19SURNAME VARCHAR2(10),
20FIRSTNAME VARCHAR2(10),
21POSITION VARCHAR2(20),
22HIREDATE DATE,
23SAL NUMBER(7,2));
24
25CREATE TABLE BOOK(
26ID_BOOK NUMBER(4) PRIMARY KEY,
27TITLE VARCHAR2(250),
28THEAUTHOR NUMBER(4),
29FOREIGN KEY(THEAUTHOR) REFERENCES AUTHOR(ID_AUTHOR),
30DATEPUBLISH DATE,
31ISBN NUMBER(13),
32CAT NUMBER(4),
33FOREIGN KEY(CAT) REFERENCES CATEGORIES(ID_CAT));
34
35CREATE TABLE COPYOFBOOK(
36IDCOPY NUMBER(4) PRIMARY KEY,
37ID_BOOK NUMBER(4),
38FOREIGN KEY(ID_BOOK) REFERENCES BOOK(ID_BOOK));
39
40CREATE TABLE BORROWING(
41IDBORROW NUMBER(4) PRIMARY KEY,
42CUS NUMBER(4),
43FOREIGN KEY(CUS) REFERENCES CUSTOMER(IDC),
44THEBOOK NUMBER(4),
45FOREIGN KEY(THEBOOK) REFERENCES COPYOFBOOK(IDCOPY),
46DATEOFBORROW DATE,
47DATEOFRETURN DATE,
48THESTAFF NUMBER(4),
49FOREIGN KEY(THESTAFF) REFERENCES STAFF(ID_STAFF));
50
51SELECT *
52FROM CUSTOMER;
53DESC BORROWING;
54
55INSERT INTO CATEGORIES VALUES
56(6711, 'KIDS');
57INSERT INTO CATEGORIES VALUES
58(6712, 'NON-FICTION');
59INSERT INTO CATEGORIES VALUES
60(6713, 'FICTION');
61INSERT INTO CATEGORIES VALUES
62(6714, 'COMPUTER');
63
64INSERT INTO CUSTOMER VALUES
65(1345, 'WHITE', 'MARK', 'ul. Jelenia 141 Warszawa', '7972218389');
66INSERT INTO CUSTOMER VALUES
67(1346, 'PAWLOSKA', 'WOJCIECHA', 'ul. Czerwonego Kapturka 90 Warszawa', '5148313458');
68INSERT INTO CUSTOMER VALUES
69(1347, 'LADA', 'CIBOR', 'ul. Skalista 49 Warszawa', '5367049128');
70INSERT INTO CUSTOMER VALUES
71(1348, 'CZERWINSKI', 'URJASZ', 'ul. Maciejkowa 17 Warszawa', '8849842697');
72INSERT INTO CUSTOMER VALUES
73(1349, 'DABROWSKI', 'LIDIA', 'ul. Ojca Bika Karola 43 Warszawa', '5355231527');
74INSERT INTO CUSTOMER VALUES
75(1350, 'KAMINSKI', 'JUREK', 'ul. Bardzka 40 Warszawa', '6727071119');
76INSERT INTO CUSTOMER VALUES
77(1351, 'LADA', 'JOSEF', 'ul. Jedno?ci Narodowej 29 Warszawa', '6747776968');
78INSERT INTO CUSTOMER VALUES
79(1352, 'KUCHARSKI', 'MARCIN', 'ul. Bardzka 40 Warszawa', '8890790231');
80
81SELECT *
82FROM STAFF;
83
84DESC BOOK;
85
86INSERT INTO STAFF VALUES
87(7641, 'OSTRO', 'TADZIO', 'CHIEF', '83/11/10', 5000);
88INSERT INTO STAFF VALUES
89(7642, 'WISNISKI', 'PIOTR', 'COLLECTION DEVELOPER', '17/01/02' , 4000);
90INSERT INTO STAFF VALUES
91(7643, 'LADA', 'MACIEK', 'LIBRARIAN', '17/01/10', 3400);
92INSERT INTO STAFF VALUES
93(7644, 'SZAMOCKI', 'JAKUB', 'LIBRARIAN', '17/01/12', 3000);
94INSERT INTO STAFF VALUES
95(7645, 'DABROWSKI', 'MICHAL', 'CLEANER','17/01/16', 2000);
96
97SELECT *
98FROM COPYOFBOOK;
99INSERT INTO AUTHOR VALUES
100(5431, 'KING', 'STEPHEN');
101INSERT INTO AUTHOR VALUES
102(5432, 'COELHO', 'PAULO');
103INSERT INTO AUTHOR VALUES
104(5433, 'WALTZ', 'KENNETH');
105INSERT INTO AUTHOR VALUES
106(5434, 'CORMENT', 'THOMAS');
107INSERT INTO AUTHOR VALUES
108(5435, 'BROWN', 'MARGARET');
109
110
111INSERT INTO BOOK VALUES
112(3421, 'THE OUTSIDER', 5431, '18/05,22', '9781501180989', 6713);
113INSERT INTO BOOK VALUES
114(3422, 'THE ALCHEMIST', 5432, '93/05/01', '9780061122415', 6713);
115INSERT INTO BOOK VALUES
116(3423, 'THE STAND', 5431, '90/05/01', '9780385199575', 6713);
117INSERT INTO BOOK VALUES
118(3424, 'MAN, THE STATE, AND WAR', 5433, '01/04/15' , '9780231125376', 6712);
119INSERT INTO BOOK VALUES
120(3425, 'INTRODUCTION TO ALGORITHMS', 5434, '01/08/10' , '9780262531962', 6714);
121INSERT INTO BOOK VALUES
122(3426, 'GOODNIGHT MOON', 5435, '47/09/03' , ' 9780060775858', 6711);
123
124INSERT INTO COPYOFBOOK VALUES
125(2101, 3421);
126INSERT INTO COPYOFBOOK VALUES
127(2102, 3421);
128INSERT INTO COPYOFBOOK VALUES
129(2103, 3421);
130INSERT INTO COPYOFBOOK VALUES
131(2201, 3422);
132INSERT INTO COPYOFBOOK VALUES
133(2202, 3422);
134INSERT INTO COPYOFBOOK VALUES
135(2301, 3423);
136INSERT INTO COPYOFBOOK VALUES
137(2302, 3423);
138INSERT INTO COPYOFBOOK VALUES
139(2401, 3424);
140INSERT INTO COPYOFBOOK VALUES
141(2501, 3425);
142INSERT INTO COPYOFBOOK VALUES
143(2601, 3426);
144INSERT INTO COPYOFBOOK VALUES
145(2602, 3426);
146INSERT INTO COPYOFBOOK VALUES
147(2603, 3426);
148INSERT INTO COPYOFBOOK VALUES
149(2604, 3426);
150INSERT INTO COPYOFBOOK VALUES
151(2605, 3426);
152
153SELECT *
154FROM CUSTOMER;
155
156DESC BORROWING;
157
158INSERT INTO BORROWING VALUES
159(5661, 1345, 2601, '17/01/11', '17/01/18', 7643);
160INSERT INTO BORROWING VALUES
161(5662, 1346, 2101, '17/01/12', '17/01/19' , 7643);
162INSERT INTO BORROWING VALUES
163(5663, 1347, 2102, '17/01/13', '17/01/20', 7643);
164INSERT INTO BORROWING VALUES
165(5664, 1348, 2605, '17/01/14', '17/01/21', 7643);
166INSERT INTO BORROWING VALUES
167(5665, 1345, 2302, '17/01/14', '17/01/21', 7643);
168INSERT INTO BORROWING VALUES
169(5666, 1349, 2601, '17/01/15', '17/01/22', 7643);
170INSERT INTO BORROWING VALUES
171(5667, 1350, 2201, '17/01/16', '17/01/23', 7643);
172INSERT INTO BORROWING VALUES
173(5668, 1351, 2501, '17/01/16', '17/01/23', 7643);
174INSERT INTO BORROWING VALUES
175(5669, 1352, 2601,'17/01/17', '17/01/24', 7643);
176INSERT INTO BORROWING VALUES
177(5670, 1347, 2101, '17/01/17', '17/01/24', 7643);
178INSERT INTO BORROWING VALUES
179(5671, 1350, 2401, '17/01/20', '17/01/27', 7644);
180INSERT INTO BORROWING VALUES
181(5672, 1348, 2501, '17/01/22', '17/01/29', 7644);
182
183--GENERATE NEWIDC FOR CUSTOMER
184SET SERVEROUTPUT ON
185CREATE OR REPLACE TRIGGER NEWCUS
186BEFORE INSERT
187ON CUSTOMER
188FOR EACH ROW
189BEGIN
190SELECT NVL(MAX(IDC)+1,1345)
191INTO :NEW.IDC
192FROM CUSTOMER;
193END;
194
195DESC CUSTOMER;
196
197INSERT INTO CUSTOMER(SURNAME, FIRSTNAME, ADDRESS, PHONE) VALUES 
198('SZAMOCKA', 'INDAH', 'ul. Senatorska 34 Warszawa', 72629288);
199
200SELECT *
201FROM CUSTOMER
202ORDER BY IDC;
203
204--COUNTING THE NUMBER OF CUSTOMER AFTER ADDING NEW CUSTOMER
205CREATE OR REPLACE TRIGGER NUMCUS
206AFTER INSERT
207ON CUSTOMER
208DECLARE X INTEGER;
209BEGIN 
210SELECT COUNT(IDC)
211INTO X
212FROM CUSTOMER;
213DBMS_OUTPUT.PUT_LINE('WE HAVE '|| X || ' CUSTOMERS');
214END;
215
216INSERT INTO CUSTOMER(SURNAME, FIRSTNAME, ADDRESS, PHONE) VALUES 
217('DABROWSKA', 'ANA', 'ul. Senatorska 22 Warszawa', 7262738223);
218
219--GENERATE NEWID_CAT FOR CATEGORY
220CREATE OR REPLACE TRIGGER NEWCAT
221BEFORE INSERT
222ON CATEGORIES
223FOR EACH ROW
224BEGIN
225SELECT NVL(MAX(ID_CAT)+1,6711)
226INTO :NEW.ID_CAT
227FROM CATEGORIES;
228END;
229
230DESC CATEGORIES;
231SELECT *
232FROM CATEGORIES;
233
234INSERT INTO CATEGORIES(CNAME) VALUES ('HOBBY');
235
236--GENERATE NEWID_AUTHOR FOR AUTHOR
237CREATE OR REPLACE TRIGGER NEWAUTHOR
238BEFORE INSERT
239ON AUTHOR
240FOR EACH ROW
241BEGIN
242SELECT NVL(MAX(ID_AUTHOR)+1,5431)
243INTO :NEW.ID_AUTHOR
244FROM AUTHOR;
245END;
246
247SELECT *
248FROM AUTHOR
249ORDER BY ID_AUTHOR;
250
251INSERT INTO AUTHOR (SURNAME, FIRSTNAME) VALUES ('SZAMOCKA', 'OKTAVIANI');
252
253--GENERATE NEWID_STAFF 
254CREATE OR REPLACE TRIGGER NEWSTAFF
255BEFORE INSERT
256ON STAFF
257FOR EACH ROW
258BEGIN
259SELECT NVL(MAX(ID_STAFF)+1,7641)
260INTO :NEW.ID_STAFF
261FROM STAFF;
262END;
263
264--GENERATE THE HIREDATE OF STAFF ACCORDING CURRENT DATE
265CREATE OR REPLACE TRIGGER NEWSTAFFHIREDATE
266BEFORE INSERT
267ON STAFF
268FOR EACH ROW
269BEGIN
270:NEW.HIREDATE:=SYSDATE;
271END;
272
273SELECT *
274FROM STAFF;
275
276
277INSERT INTO STAFF(SURNAME, FIRSTNAME, POSITION, SAL) VALUES
278('BROWN', 'FRANK', 'SECURITY', '2500');
279
280--GENERATE NEWIDbOOK
281CREATE OR REPLACE TRIGGER NEWBOOK
282BEFORE INSERT
283ON BOOK
284FOR EACH ROW
285BEGIN
286SELECT NVL(MAX(ID_BOOK)+1,3421)
287INTO :NEW.ID_BOOK
288FROM BOOK;
289END;
290
291INSERT INTO AUTHOR(SURNAME, FIRSTNAME) VALUES ('HERSEY', 'JOHN');
292SELECT * FROM AUTHOR;
293
294DESC BOOK;
295
296INSERT INTO BOOK (TITLE, THEAUTHOR, DATEPUBLISH, ISBN,CAT) VALUES
297('HIROSHIMA', 5437, '89/03/04', 9780679721031, 6715); 
298
299--GENERATE NEWIDBORROW
300SET SERVEROUTPUT ON
301CREATE OR REPLACE TRIGGER NEWBORROW
302BEFORE INSERT
303ON BORROWING
304FOR EACH ROW
305BEGIN
306SELECT NVL(MAX(IDBORROW)+1,5661)
307INTO :NEW.IDBORROW
308FROM BORROWING;
309END;
310
311--GENERATE DATEOFBORROW ACCORDING CURRENT DATE
312CREATE OR REPLACE TRIGGER BORROWDATEOFBOR
313BEFORE INSERT
314ON BORROWING
315FOR EACH ROW
316BEGIN
317:NEW.DATEOFBORROW:=SYSDATE;
318END;
319
320--GENERATE DATEOFRETTURN ACCORDING CUR DATE PLUS 7 DAYS
321CREATE OR REPLACE TRIGGER BORROWDATEOFRET
322BEFORE INSERT
323ON BORROWING
324FOR EACH ROW
325BEGIN
326:NEW.DATEOFRETURN:= SYSDATE + 7;
327END;
328
329
330
331DESC BORROWING;
332
333
334SELECT *
335FROM BORROWING
336ORDER BY IDBORROW;
337
338SELECT * FROM COPYOFBOOK;
339
340INSERT INTO BORROWING (CUS, THEBOOK, THESTAFF) VALUES
341(1347, 2605, 7643);
342
343--INFORMING BOOK SUCCESSFULLY ADDED
344SET SERVEROUTPUT ON
345CREATE OR REPLACE TRIGGER NEWCOP
346AFTER INSERT
347ON COPYOFBOOK
348FOR EACH ROW
349BEGIN 
350DBMS_OUTPUT.PUT_LINE('BOOK SUCCESSFULLY ADDED');
351END;
352
353--INFORMING LENDING BOOK PROCESS FINISHED
354SET SERVEROUTPUT ON
355CREATE OR REPLACE TRIGGER NEWBORROWINGBOOK
356AFTER INSERT
357ON BORROWING
358FOR EACH ROW
359BEGIN 
360DBMS_OUTPUT.PUT_LINE('LENDING BOOK PROCESS FINISHED');
361END;
362
363--COUNTING NUMBER OF BOOK EACH TIME ADDING BOOK
364CREATE OR REPLACE TRIGGER NUMBOOK
365AFTER INSERT
366ON COPYOFBOOK
367DECLARE X INTEGER;
368BEGIN 
369SELECT COUNT(IDCOPY)
370INTO X
371FROM COPYOFBOOK;
372DBMS_OUTPUT.PUT_LINE('WE HAVE '|| X || ' BOOKS IN OUR COLLECTIONN');
373END;
374
375INSERT INTO COPYOFBOOK VALUES (2704, 3427);
376
377SELECT * FROM STAFF;
378
379--PREVENTING EXCEED THE MAX SAL
380CREATE OR REPLACE TRIGGER MAXSAL
381BEFORE UPDATE
382ON STAFF
383FOR EACH ROW
384BEGIN
385IF :NEW.SAL>8000
386THEN
387:NEW.SAL:=:OLD.SAL;
388RAISE_APPLICATION_ERROR(-20003, 'THE SALARY IS TOO MUCH!');
389END IF;
390END;
391
392UPDATE STAFF
393SET SAL=8100
394WHERE ID_STAFF=7641;
395
396SELECT *  FROM CATEGORIES;
397
398
399--NOTIFYING DELETE PROCCESS SUCCESSFULLY
400CREATE OR REPLACE TRIGGER DELCUS
401AFTER DELETE
402ON CUSTOMER
403FOR EACH ROW
404BEGIN 
405DBMS_OUTPUT.PUT_LINE('DELETED SUCCESSFULLY');
406END;
407
408--NOTIFYING UPDATE PROCCESS SUCCESSFULLY
409CREATE OR REPLACE TRIGGER UPCUS
410AFTER UPDATE
411ON CUSTOMER
412FOR EACH ROW
413BEGIN 
414DBMS_OUTPUT.PUT_LINE('UPDATE SUCCESSFULLY');
415END;
416
417
418--LOOKING THE DATA OF SPESIFIC CUSTOMER ACCORDING THEIR ID 
419SELECT *
420FROM CUSTOMER
421WHERE IDC=1346;
422
423
424--LOOKING THE NUMBER OF BORROWING OF SPESIFIC CUSTOMER ACCORDING THEIR ID 
425SELECT  COUNT(IDBORROW)
426FROM CUSTOMER, BORROWING
427WHERE IDC=1345 AND CUS=IDC;
428
429--LOOKING THE HISTORYOF BORROWING OF SPESIFIC CUSTOMER ACCORDING THEIR ID 
430SELECT CUSTOMER.FIRSTNAME, IDBORROW, TITLE, DATEOFBORROW, DATEOFRETURN, STAFF.FIRSTNAME
431FROM CUSTOMER, STAFF, BOOK, COPYOFBOOK, BORROWING
432WHERE IDC=1345 AND IDC=CUS AND THEBOOK=IDCOPY AND COPYOFBOOK.ID_BOOK=BOOK.ID_BOOK AND THESTAFF=ID_STAFF;
433
434--LOOKING THE HISTORYOF BORROWING OF each customer
435SELECT IDC, CUSTOMER.FIRSTNAME, CUSTOMER.SURNAME, IDBORROW, TITLE, DATEOFBORROW, DATEOFRETURN, STAFF.FIRSTNAME
436FROM CUSTOMER, STAFF, BOOK, COPYOFBOOK, BORROWING
437WHERE CUS=IDC AND THEBOOK=IDCOPY AND COPYOFBOOK.ID_BOOK=BOOK.ID_BOOK AND THESTAFF=ID_STAFF;
438
439--LOOKING THE INFORMATION ABOUT CERTAIN BOOK THE FORGET WHOLE TITLE
440SELECT *
441FROM BOOK
442WHERE TITLE LIKE 'MAN%';
443
444--FIND ALL LIBRARIAN
445SELECT FIRSTNAME, SURNAME
446FROM STAFF
447WHERE POSITION='LIBRARIAN';
448
449--COUNT NUMBER OF LIBRARIAN
450SELECT COUNT(ID_STAFF)
451FROM STAFF
452WHERE POSITION='LIBRARIAN';
453
454--FIND WHO EARN THE MOST
455SELECT *
456FROM STAFF
457WHERE SAL =(
458SELECT MAX(SAL)
459FROM STAFF);
460
461--FIND THE AVERAGE OF SAL OF EMPLOYEE
462SELECT AVG(SAL)
463FROM STAFF;
464
465--FIND EMPLOYEE WHO EARN BETWEEN SPESIFIC NUMBER
466SELECT FIRSTNAME, SURNAME, SAL
467FROM STAFF
468WHERE SAL BETWEEN 2000 AND 4000;
469
470--SHOWING MONTHLY AND ANNUAL SAL OF ALL EMPLOYEE
471SELECT ID_STAFF, FIRSTNAME, SURNAME, SAL, SAL*12 AS "ANNUAL SAL"
472FROM STAFF;
473
474--FIND THE STAFF THAT EMPLOYED IN 2017
475SELECT ID_STAFF, FIRSTNAME, SURNAME, HIREDATE
476FROM STAFF
477WHERE HIREDATE BETWEEN '17/01/01' AND '17/12/31';
478
479--FIND THE NEWEST EMPLOYEE
480SELECT ID_STAFF, FIRSTNAME, SURNAME, HIREDATE
481FROM STAFF
482WHERE HIREDATE=(
483SELECT MAX(HIREDATE)
484FROM STAFF);
485
486
487--SHOW BOOK ACCORDING THEIR CATEGORIES
488SELECT CNAME, TITLE
489FROM CATEGORIES, BOOK
490WHERE CAT (+)= ID_CAT;
491
492--COUNT NUMBER OF BOOK EACH CAT
493SELECT CNAME, COUNT(ID_BOOK)
494FROM BOOK, CATEGORIES
495WHERE CAT=ID_CAT
496GROUP BY CNAME;
497
498--FIND THE CUSTOMER ONLY WHO EVER BORROW
499SELECT *
500FROM CUSTOMER
501MINUS
502SELECT *
503FROM CUSTOMER
504WHERE NOT IDC IN (
505SELECT CUS
506FROM BORROWING, CUSTOMER
507WHERE CUS=IDC);
508
509--FIND THE NUMBER OF BORROWING
510SELECT COUNT(IDBORROW)
511FROM BORROWING;
512
513--FIND ANYBODY WHO EARN MORE THAN LIBRARIAN
514SELECT FIRSTNAME, SURNAME, SAL
515FROM STAFF
516WHERE SAL > ALL (
517SELECT SAL
518FROM STAFF
519WHERE POSITION='LIBRARIAN');
520
521--FIND SAL THAT EARN MORE THAN AVERAGE
522SELECT FIRSTNAME, SURNAME, SAL
523FROM STAFF
524WHERE SAL >(
525SELECT AVG(SAL)
526FROM STAFF);
527
528--FIND CATEGORIES THAT NO BOOK IN IT
529SELECT ID_CAT, CNAME
530FROM CATEGORIES C
531WHERE NOT EXISTS (
532SELECT ID_BOOK
533FROM BOOK
534WHERE CAT=C.ID_CAT);
535
536--FIND THE CATEGORIES WHICH HAS MIN 3 BOOK
537SELECT CNAME, COUNT(ID_BOOK)
538FROM BOOK, CATEGORIES
539WHERE CAT=ID_CAT
540GROUP BY CNAME
541HAVING COUNT(ID_BOOK)>=3;
542
543--SHOW THE AUTHOR OF EACH BOOK
544SELECT TITLE, FIRSTNAME, SURNAME
545FROM BOOK, AUTHOR
546WHERE THEAUTHOR=ID_AUTHOR;
547
548--FIND THE NUMBER OF BOOK EACH AUTHOR HAVE
549SELECT ID_AUTHOR, COUNT(ID_BOOK)
550FROM BOOK, AUTHOR
551WHERE THEAUTHOR=ID_AUTHOR
552GROUP BY ID_AUTHOR;
553
554--FIND THE AUTHOR WHO DONT HAVE BOOK
555SELECT FIRSTNAME, SURNAME
556FROM AUTHOR A
557WHERE NOT EXISTS (
558SELECT ID_BOOK
559FROM BOOK
560WHERE THEAUTHOR=A.ID_AUTHOR);
561
562--FIND THE NUMBER OF BORROWING EACH CUSTOMER
563SELECT IDC, COUNT(IDBORROW)
564FROM CUSTOMER, BORROWING
565WHERE CUS=IDC
566GROUP BY IDC
567ORDER BY COUNT(IDBORROW) DESC;
568
569--SORT THE BOOK ACCORDING THEIR DATE OF PUBLISH
570SELECT TITLE, DATEPUBLISH
571FROM BOOK
572ORDER BY DATEPUBLISH;
573
574--FIND THE OLDEST BOOK
575SELECT TITLE, DATEPUBLISH
576FROM BOOK
577WHERE DATEPUBLISH = (
578SELECT MIN(DATEPUBLISH)
579FROM BOOK);
580
581--SHOW THE NUMBER OF SERVICE EACH LIBRARIAN DID
582SELECT ID_STAFF, COUNT (IDBORROW)
583FROM STAFF, BORROWING
584WHERE THESTAFF=ID_STAFF
585GROUP BY ID_STAFF;
586
587--SORT ACCORDING THE FREQUENT BOOK TO BORROW
588SELECT TITLE, COUNT(IDBORROW)
589FROM BOOK, BORROWING, COPYOFBOOK
590WHERE THEBOOK=IDCOPY AND COPYOFBOOK.ID_BOOK=BOOK.ID_BOOK
591GROUP BY TITLE
592ORDER BY COUNT(IDBORROW) DESC ;
593
594--FIND THE MOST FAV BOOK HAVE BEEN BORROWED
595SELECT TITLE, COUNT(IDBORROW)
596FROM BOOK, BORROWING, COPYOFBOOK
597WHERE THEBOOK=IDCOPY AND COPYOFBOOK.ID_BOOK=BOOK.ID_BOOK
598GROUP BY TITLE
599HAVING COUNT(IDBORROW) = (
600SELECT MAX(COUNT(IDBORROW))
601FROM BOOK, BORROWING, COPYOFBOOK
602WHERE THEBOOK=IDCOPY AND COPYOFBOOK.ID_BOOK=BOOK.ID_BOOK
603GROUP BY TITLE);
604
605--FIND THE MOST FREQ CUSTOMER BORROWED BOOK
606SELECT IDC, COUNT(IDBORROW)
607FROM CUSTOMER, BORROWING
608WHERE CUS=IDC
609GROUP BY IDC
610HAVING COUNT(IDBORROW)=(
611SELECT MAX(COUNT(IDBORROW))
612FROM CUSTOMER, BORROWING
613WHERE CUS=IDC
614GROUP BY IDC);
615
616SELECT TITLE, COUNT(IDBORROW)
617FROM BORROWING, BOOK
618WHERE THEBOOK=IDCOPY AND COPYOFBOOK.ID_BOOK=BOOK.ID_BOOK
619GROUP BY TITLE;