dA2pZwZR

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 
60 
61SELECT b.Name, b.Year, b.ISBN FROM Books b
62LEFT JOIN WrittenBy wb ON wb.ISBN = b.ISBN
63WHERE wb.AuthorName = 'Erich Kastner'
64GROUP BY b.Name, b.Year, b.ISBN;
65 
66 
67SELECT wb.AuthorName FROM WrittenBy wb
68LEFT JOIN Books b ON b.ISBN = wb.ISBN
69WHERE b.ISBN = '3-4567-1289-0'
70GROUP BY wb.AuthorName;
71
72/*Almost ready*/
73/*
74SELECT wb.AuthorName, Count(wb.ISBN) FROM WrittenBy wb
75LEFT JOIN Books b ON wb.ISBN = b.ISBN
76LEFT JOIN Authors a ON wb.AuthorName = a.AuthorName
77GROUP BY wb.AuthorName
78ORDER BY(Count(wb.ISBN)) DESC;
79
80 */
81SELECT b.Name, b.Year, b.ISBN FROM Books b
82LEFT JOIN WrittenBy wb ON wb.ISBN = b.ISBN
83WHERE b.Year LIKE '%2007%' 
84GROUP BY Name
85HAVING Count(wb.AuthorName) >= 2
86ORDER BY Name;