3bd7FCaR

1DROP TABLE IF EXISTS author_book;
2DROP TABLE IF EXISTS book;
3DROP TABLE IF EXISTS author;
4DROP TABLE IF EXISTS bookshelf;
5
6
7
8
9CREATE  TABLE  IF NOT EXISTS  bookshelf(
10    id INT ,
11    PRIMARY KEY (id)
12    );
13
14CREATE  TABLE IF NOT EXISTS  author(
15id INT,
16name VARCHAR(20),
17PRIMARY KEY (id)
18
19);
20
21CREATE TABLE IF NOT EXISTS  book(
22    id INT,
23    book_price INT NOT NULL,
24    date_published INT NOT NULL ,
25    bookshelf_id INT NOT NULL ,
26    PRIMARY KEY (id),
27    FOREIGN KEY (bookshelf_id) REFERENCES bookshelf(id)
28
29);
30
31
32CREATE TABLE  author_book(
33id INT,
34book_id INT NOT NULL,
35author_id INT NOT NULL,
36PRIMARY KEY (id),
37FOREIGN KEY (book_id) REFERENCES book(id),
38FOREIGN KEY  (author_id) REFERENCES author(id)
39
40);
41
42
43INSERT author VALUE (1,'Tomasz');
44INSERT author VALUE (2,'Pawel');
45
46INSERT bookshelf VALUE (1);
47INSERT bookshelf VALUE (2);
48INSERT book VALUE (1,100,2018,1);
49INSERT book VALUE (2,80,2018,1);
50INSERT book VALUE (3,200,2019,1);
51INSERT book VALUE (4,50,2019,1);
52INSERT book VALUE (5,90,2017,1);
53INSERT  author_book VALUE (1,1,1);
54INSERT  author_book VALUE  (2,2,1);
55INSERT  author_book VALUE (3,3,1);
56INSERT  author_book VALUE (4,4,2);
57INSERT  author_book VALUE (5,5,2);
58
59
60SELECT e.id, e.book_price, a.name FROM book e
61INNER JOIN author_book ab ON  e.id = ab.book_id
62INNER JOIN author a ON ab.author_id = a.id
63WHERE e.book_price < 100
64
65
66SELECT e.id, e.book_price, a.name FROM book e
67INNER JOIN author_book ab ON  e.id = ab.book_id
68INNER JOIN author a ON ab.author_id = a.id
69GROUP BY e.book_price
70HAVING e.book_price < 100
71
72
73
74/*
75LEFT JOIN
76LEFT OUTER JOIN
77
78RIGHT JOIN
79RIGHT OUTER JOIN
80
81UNION
82*/