jBMrMGfs

1DROP DATABASE IF EXISTS library;
2CREATE DATABASE library;
3USE library;
4
5CREATE TABLE Books(
6    isbn INTEGER NOT NULL,
7    year INTEGER NOT NULL,
8    name VARCHAR(50) NOT NULL, 
9    
10    PRIMARY KEY(isbn)
11);
12
13CREATE TABLE Authors(
14    id INTEGER,
15    name VARCHAR(50),
16    
17    PRIMARY KEY(id)
18);
19
20CREATE TABLE AuthorBook(
21    bookId INTEGER,
22    authorId INTEGER,
23    
24    FOREIGN KEY (bookId) REFERENCES Books(isbn),
25	FOREIGN KEY (authorId) REFERENCES Authors(id)
26);
27
28INSERT INTO Books VALUES(1,2000,"The dark tower");
29INSERT INTO Books VALUES(2,2000,"IT");
30INSERT INTO Books VALUES(3,2001,"LOTR");
31INSERT INTO Books VALUES(4,2002,"The dark matter");
32
33INSERT INTO Authors VALUES(1,"Stephen King");
34INSERT INTO Authors VALUES(2,"J. R. R. Tolkien");
35INSERT INTO Authors VALUES(3,"Blake Crouch");
36INSERT INTO Authors VALUES(4,"Dani");
37
38INSERT INTO AuthorBook VALUES(1,1);
39INSERT INTO AuthorBook VALUES(2,1);
40INSERT INTO AuthorBook VALUES(3,2);
41INSERT INTO AuthorBook VALUES(4,3);
42INSERT INTO AuthorBook VALUES(4,2);
43
44SELECT name FROM Books WHERE year = 2000;
45
46SELECT B.name,B.year,B.isbn FROM AuthorBook Ab LEFT JOIN Books B ON Ab.bookId = B.isbn LEFT JOIN Authors A ON Ab.authorId = A.id WHERE A.name = "Stephen King"
47
48SELECT A.name FROM AuthorBook Ab LEFT JOIN Authors A ON Ab.AuthorId = A.id WHERE Ab.bookId = 3;