cUSs2ck7

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');
18
19
20create table Authors (
21    AuthorName varchar(100) NOT NULL,
22    primary key(AuthorName)
23);
24
25INSERT INTO Authors(AuthorName) VALUES ('Andrzej Sapkowski');
26INSERT INTO Authors(AuthorName) VALUES ('Rick Riordan');
27INSERT INTO Authors(AuthorName) VALUES ('Roald Dahl');
28INSERT INTO Authors(AuthorName) VALUES ('Erich Kastner');
29INSERT INTO Authors(AuthorName) VALUES ('Kami Garcia');
30INSERT INTO Authors(AuthorName) VALUES ('Margaret Stohl');
31INSERT INTO Authors(AuthorName) VALUES ('Natsuo Fujii');
32
33
34create table WrittenBy(
35	ISBN varchar(13),
36    AuthorName varchar(150),
37    primary key(ISBN, AuthorName)
38);
39
40INSERT INTO WrittenBy(ISBN, AuthorName) VALUES ('123-456-78910', 'Andrzej Sapkowski');
41INSERT INTO WrittenBy(ISBN, AuthorName) VALUES ('0-7868-5629-7', 'Rick Riordan');
42INSERT INTO WrittenBy(ISBN, AuthorName) VALUES ('1-6789-2345-0', 'Roald Dahl');
43INSERT INTO WrittenBy(ISBN, AuthorName) VALUES ('2-1357-0468-9', 'Erich Kastner');
44INSERT INTO WrittenBy(ISBN, AuthorName) VALUES ('3-4567-1289-0', 'Kami Garcia');
45INSERT INTO WrittenBy(ISBN, AuthorName) VALUES ('3-4567-1289-0', 'Margaret Stohl');
46INSERT INTO WrittenBy(ISBN, AuthorName) VALUES ('4-5678-0123-9', 'Roald Dahl');
47
48
49SELECT b.Name, b.Year FROM Books b 
50WHERE b.Year LIKE '%1998%';
51
52
53SELECT b.Name, b.Year, b.ISBN FROM Books b 
54LEFT JOIN WrittenBy wb ON wb.ISBN = b.ISBN
55WHERE wb.AuthorName = 'Erich Kastner'
56GROUP BY b.Name, b.Year, b.ISBN;
57
58
59SELECT wb.AuthorName FROM WrittenBy wb
60LEFT JOIN Books b ON b.ISBN = wb.ISBN
61WHERE b.ISBN = '3-4567-1289-0'
62GROUP BY wb.AuthorName;
63*/
64
65SELECT wb.AuthorName, wb.ISBN FROM WrittenBy wb
66LEFT JOIN Books b ON wb.ISBN = b.ISBN
67GROUP BY wb.AuthorName, wb.ISBN
68ORDER BY(Count(wb.ISBN like wb.AuthorName)) ASC;