f8ZCdis6

1DROP DATABASE IF EXISTS Library;
2CREATE database Library CHARSET 'utf8';
3USE Library;
4
5create TABLE Authors(
6	Id INTEGER PRIMARY KEY AUTO_INCREMENT,
7    Name VARCHAR(60) NOT NULL
8);
9
10CREATE TABLE Books(
11	Id INTEGER NOT NULL PRIMARY KEY AUTO_INCREMENT,
12    Name varchar(60) NOT NULL,
13    ISBN INTEGER NOT NULL,
14    BookDate DATE NOT NULL
15);
16
17CREATE TABLE AuthorsBooks(
18	AuthorId INTEGER NOT NULL,
19    BookId INTEGER NOT NULL,
20    primary key(AuthorId, BookId),
21    
22    FOREIGN KEY(AuthorId) references Authors(Id) ON UPDATE CASCADE,
23    foreign key(BookId) references Books(Id) ON UPDATE CASCADE
24);
25
26INSERT INTO Authors(Name) VALUES('Стела Стефанова');
27INSERT INTO Authors(Name) VALUES('Минчо Празников');
28INSERT INTO Authors(Name) VALUES('доц. др. инж. гл. ас. с.н.с и.д. ректор Денис Бързанов');
29INSERT INTO Authors(Name) VALUES('маг. инж. Росен Витанов');
30INSERT INTO Books(Name, ISBN, BookDate) VALUES('Програмиране на Котлин', 453534, '2016-05-12');
31INSERT INTO Books(Name, ISBN, BookDate) VALUES('Времето в България', 480534, '2018-05-12');
32INSERT INTO Books(Name, ISBN, BookDate) VALUES('Времето в Европа', 790534, '1994-12-02');
33#INSERT INTO Books(Name, ISBN, BookDate) VALUES('Ардуино за идиоти', 490534, '2009-12-02');
34
35INSERT INTO AuthorsBooks(AuthorId, BookId) VALUES (1, 1);
36INSERT INTO AuthorsBooks(AuthorId, BookId) VALUES (1, 2);
37INSERT INTO AuthorsBooks(AuthorId, BookId) VALUES (3, 1);
38INSERT INTO AuthorsBooks(AuthorId, BookId) VALUES (2, 2);
39INSERT INTO AuthorsBooks(AuthorId, BookId) VALUES (2, 3);
40INSERT INTO AuthorsBooks(AuthorId, BookId) VALUES (4, 1);
41
42SELECT b.Name FROM Books b WHERE YEAR(BookDate) = 2016;
43SELECT b.Name, b.BookDate, b.ISBN FROM Books b INNER JOIN AuthorsBooks ab ON ab.BookId = b.Id INNER JOIN Authors a ON a.id = ab.AuthorId WHERE a.name = 'Минчо Празников';
44SELECT a.Name FROM Authors a INNER JOIN AuthorsBooks ab ON ab.AuthorId = a.Id INNER JOIN Books b ON ab.BookId = b.Id WHERE b.ISBN = 790534;
45SELECT COUNT(*), a.Name FROM Authors a LEFT JOIN AuthorsBooks ab ON ab.AuthorId = a.Id LEFT JOIN Books b ON ab.BookId = b.Id GROUP BY a.Id ORDER BY COUNT(*) DESC;
46SELECT b.Name, COUNT(*) FROM Books b INNER JOIN AuthorsBooks ab on ab.BookId = b.Id INNER JOIN Authors a on ab.AuthorId = a.Id GROUP BY b.Id HAVING COUNT(*) >= 2 ORDER BY b.Name;