DLWYfeqF

1/*drop database if exists Library;
2create DATABASE Library
3*/
4/*
5create table Books (
6    Name varchar(100) NOT NULL,
7    Year date NOT NULL,
8    ISBN varchar(13),
9    primary key(ISBN)
10);
11 
12INSERT INTO Books (Name, Year, ISBN) VALUES ('The Witcher', '2017-11-04', '123-456-78910');
13INSERT INTO Books (Name, Year, ISBN) VALUES ('The Lightning Thief', '2005-07-23', '0-7868-5629-7');
14INSERT INTO Books (Name, Year, ISBN) VALUES ('Matilda', '1998-10-01', '1-6789-2345-0');
15INSERT INTO Books (Name, Year, ISBN) VALUES ('Dot And Anton ', '2001-01-27', '2-1357-0468-9');
16INSERT INTO Books (Name, Year, ISBN) VALUES ('Beautiful Creatures', '2007-06-13', '3-4567-1289-0');
17INSERT INTO Books (Name, Year, ISBN) VALUES ('Charlie and The Chocolate Factory', '1998-10-04', '4-5678-0123-9');
18INSERT INTO Books (Name, Year, ISBN) VALUES ('Good Omens', '2003-12-14', '5-6789-1234-0');
19INSERT INTO Books (Name, Year, ISBN) VALUES ('Tiny Pretty Things', '2007-12-14', '6-7890-1234-5');
20
21
22create table Authors (
23	Id Integer NOT NULL primary key auto_increment,
24    AuthorName varchar(100) NOT NULL
25);
26
27INSERT INTO Authors(AuthorName) VALUES ('Andrzej Sapkowski');
28INSERT INTO Authors(AuthorName) VALUES ('Rick Riordan');
29INSERT INTO Authors(AuthorName) VALUES ('Roald Dahl');
30INSERT INTO Authors(AuthorName) VALUES ('Erich Kastner');
31INSERT INTO Authors(AuthorName) VALUES ('Kami Garcia');
32INSERT INTO Authors(AuthorName) VALUES ('Margaret Stohl');
33INSERT INTO Authors(AuthorName) VALUES ('Natsuo Fujii');
34INSERT INTO Authors(AuthorName) VALUES ('Doug Dorst');
35INSERT INTO Authors(AuthorName) VALUES ('J. Abrams');
36INSERT INTO Authors(AuthorName) VALUES ('Sona Charaipotra');
37INSERT INTO Authors(AuthorName) VALUES ('Dhonielle Clayton');
38
39create table WrittenBy(
40    ISBN varchar(13),
41    AuthorName varchar(150),
42    primary key(ISBN, AuthorName)
43);
44 
45INSERT INTO WrittenBy(ISBN, AuthorName) VALUES ('123-456-78910', 'Andrzej Sapkowski');
46INSERT INTO WrittenBy(ISBN, AuthorName) VALUES ('0-7868-5629-7', 'Rick Riordan');
47INSERT INTO WrittenBy(ISBN, AuthorName) VALUES ('1-6789-2345-0', 'Roald Dahl');
48INSERT INTO WrittenBy(ISBN, AuthorName) VALUES ('2-1357-0468-9', 'Erich Kastner');
49INSERT INTO WrittenBy(ISBN, AuthorName) VALUES ('3-4567-1289-0', 'Kami Garcia');
50INSERT INTO WrittenBy(ISBN, AuthorName) VALUES ('3-4567-1289-0', 'Margaret Stohl');
51INSERT INTO WrittenBy(ISBN, AuthorName) VALUES ('4-5678-0123-9', 'Roald Dahl');
52INSERT INTO WrittenBy(ISBN, AuthorName) VALUES ('5-6789-1234-0', 'Doug Dorst');
53INSERT INTO WrittenBy(ISBN, AuthorName) VALUES ('5-6789-1234-0', 'J. Abrams');
54INSERT INTO WrittenBy(ISBN, AuthorName) VALUES ('6-7890-1234-5', 'Sona Charaipotra');
55INSERT INTO WrittenBy(ISBN, AuthorName) VALUES ('6-7890-1234-5', 'Dhonielle Clayton');
56
57SELECT b.Name, b.Year FROM Books b
58WHERE b.Year LIKE '%1998%';
59
60SELECT b.Name, b.Year, b.ISBN FROM Books b
61LEFT JOIN WrittenBy wb ON wb.ISBN = b.ISBN
62WHERE wb.AuthorName = 'Erich Kastner'
63GROUP BY b.Name, b.Year, b.ISBN;
64 
65 
66SELECT wb.AuthorName FROM WrittenBy wb
67LEFT JOIN Books b ON b.ISBN = wb.ISBN
68WHERE b.ISBN = '3-4567-1289-0'
69GROUP BY wb.AuthorName;
70
71/*
72SELECT a.AuthorName, Count(wb.ISBN) FROM WrittenBy wb
73LEFT JOIN Books b ON wb.ISBN = b.ISBN
74RIGHT JOIN Authors a ON wb.AuthorName = a.AuthorName
75GROUP BY wb.AuthorName
76ORDER BY(Count(wb.ISBN)) DESC;
77 
78SELECT b.Name, b.Year, b.ISBN, Count(wb.AuthorName) FROM Books b
79LEFT JOIN WrittenBy wb ON wb.ISBN = b.ISBN
80LEFT JOIN Authors a ON wb.AuthorName = a.AuthorName
81WHERE b.Year LIKE '%2007%' 
82GROUP BY Name
83HAVING Count(wb.AuthorName) >= 2
84ORDER BY Name;
85*/