create table Library (div TEXT PRIMARY KEY,floor int, num INT, responsible TEXT);
insert into Library values 
("fiction",2, 10000, "Adam"),
("non-fiction",2, 5000, "Zaqsy"),
("tech",1, 30000, "Gulzhamal"),
("history",3, 5000, "Zaman");
 
CREATE TABLE Books (name TEXT PRIMARY KEY, year INT, author TEXT, div TEXT);
insert into Books values 
("algo", 2020, "Adam", 'tech'),
("c++", 1997, "Ritchie", 'tech'),
("python", 2005, "S", 'tech'),
("Harry Potter",1993,"Rouling","fiction"),
("The Dawns Here Are Quiet",1969,"Vasiliev","non-fiction"),
('Java',2008,"Kuke","tech"),
("SQL",2007,"Loiely","tech"),
("War and peace",1807,"Tolstoy","non-fiction"),
("The Decline and Destruction of Britain",1887,"Bradly","history"),
("A Hero of Our Time",1837,"Lermontov","fiction");
----SELECT *FROM Books;
---SELECT div, MAX(year) FROM Books GROUP BY div;--- MAX MIN COUNT AVG SUM
---SELECT * FROM Library JOIN Books ON Library.div=Books.div;
---все названия разделов в которых больше 5000 книг
 
---вывести этажи на которых находятся книги, написанные в 20м веке
SELECT DISTINCT Library.floor FROM Library 
INNER JOIN Books 
ON Library.div=Books.div
WHERE Books.year BETWEEN 1900 AND 2000;
 
 
 

Y3JlYXRlIHRhYmxlIExpYnJhcnkgKGRpdiBURVhUIFBSSU1BUlkgS0VZLGZsb29yIGludCwgbnVtIElOVCwgcmVzcG9uc2libGUgVEVYVCk7Cmluc2VydCBpbnRvIExpYnJhcnkgdmFsdWVzIAooImZpY3Rpb24iLDIsIDEwMDAwLCAiQWRhbSIpLAooIm5vbi1maWN0aW9uIiwyLCA1MDAwLCAiWmFxc3kiKSwKKCJ0ZWNoIiwxLCAzMDAwMCwgIkd1bHpoYW1hbCIpLAooImhpc3RvcnkiLDMsIDUwMDAsICJaYW1hbiIpOwoKQ1JFQVRFIFRBQkxFIEJvb2tzIChuYW1lIFRFWFQgUFJJTUFSWSBLRVksIHllYXIgSU5ULCBhdXRob3IgVEVYVCwgZGl2IFRFWFQpOwppbnNlcnQgaW50byBCb29rcyB2YWx1ZXMgCigiYWxnbyIsIDIwMjAsICJBZGFtIiwgJ3RlY2gnKSwKKCJjKysiLCAxOTk3LCAiUml0Y2hpZSIsICd0ZWNoJyksCigicHl0aG9uIiwgMjAwNSwgIlMiLCAndGVjaCcpLAooIkhhcnJ5IFBvdHRlciIsMTk5MywiUm91bGluZyIsImZpY3Rpb24iKSwKKCJUaGUgRGF3bnMgSGVyZSBBcmUgUXVpZXQiLDE5NjksIlZhc2lsaWV2Iiwibm9uLWZpY3Rpb24iKSwKKCdKYXZhJywyMDA4LCJLdWtlIiwidGVjaCIpLAooIlNRTCIsMjAwNywiTG9pZWx5IiwidGVjaCIpLAooIldhciBhbmQgcGVhY2UiLDE4MDcsIlRvbHN0b3kiLCJub24tZmljdGlvbiIpLAooIlRoZSBEZWNsaW5lIGFuZCBEZXN0cnVjdGlvbiBvZiBCcml0YWluIiwxODg3LCJCcmFkbHkiLCJoaXN0b3J5IiksCigiQSBIZXJvIG9mIE91ciBUaW1lIiwxODM3LCJMZXJtb250b3YiLCJmaWN0aW9uIik7Ci0tLS1TRUxFQ1QgKkZST00gQm9va3M7Ci0tLVNFTEVDVCBkaXYsIE1BWCh5ZWFyKSBGUk9NIEJvb2tzIEdST1VQIEJZIGRpdjstLS0gTUFYIE1JTiBDT1VOVCBBVkcgU1VNCi0tLVNFTEVDVCAqIEZST00gTGlicmFyeSBKT0lOIEJvb2tzIE9OIExpYnJhcnkuZGl2PUJvb2tzLmRpdjsKLS0t0LLRgdC1INC90LDQt9Cy0LDQvdC40Y8g0YDQsNC30LTQtdC70L7QsiDQsiDQutC+0YLQvtGA0YvRhSDQsdC+0LvRjNGI0LUgNTAwMCDQutC90LjQswoKLS0t0LLRi9Cy0LXRgdGC0Lgg0Y3RgtCw0LbQuCDQvdCwINC60L7RgtC+0YDRi9GFINC90LDRhdC+0LTRj9GC0YHRjyDQutC90LjQs9C4LCDQvdCw0L/QuNGB0LDQvdC90YvQtSDQsiAyMNC8INCy0LXQutC1ClNFTEVDVCBESVNUSU5DVCBMaWJyYXJ5LmZsb29yIEZST00gTGlicmFyeSAKSU5ORVIgSk9JTiBCb29rcyAKT04gTGlicmFyeS5kaXY9Qm9va3MuZGl2CldIRVJFIEJvb2tzLnllYXIgQkVUV0VFTiAxOTAwIEFORCAyMDAwOwoKCg==