· 4 years ago · Jul 10, 2021, 03:44 PM
1DROP TABLE IF EXISTS book;
2DROP TABLE IF EXISTS author;
3
4CREATE TABLE book
5(
6 book_id int PRIMARY KEY,
7 title text NOT NULL,
8 isbn text NOT NULL
9);
10
11CREATE TABLE author
12(
13 author_id int PRIMARY KEY,
14 full_name text NOT NULL,
15 rating real
16);
17
18CREATE TABLE book_author
19(
20 book_id int REFERENCES book(book_id),
21 author_id int REFERENCES author(author_id)
22
23 CONSTRAINT book_author_pkey PRIMARY KEY (book_id, author_id)
24);
25
26INSERT INTO book
27VALUES
28(1, 'Book for dummies', '1234567'),
29(2, 'Book for Smart guies', '89101232'),
30(3, 'Book for happy', '253463464'),
31(4, 'Book for unhappy', '869879789');
32
33INSERT INTO author
34VALUES
35(1,'Bob',4.5),
36(2,'Alice',4.0),
37(3,'John',4.7);
38
39INSERT INTO book_author
40VALUES
41(1,1),
42(2,1),
43(3,1),
44(3,2),
45(4,1),
46(4,2),
47(4,3);
48