Book authors, publishers, and genres example

Based on https://www.reddit.com/r/SQL/comments/1gewtsn/joins_in_sqlite/ question

You must execute the code again to save those values in the database.

CREATE TABLE Book (
    book_id INTEGER PRIMARY KEY AUTOINCREMENT,
    title TEXT
);

CREATE TABLE Author (
    author_id INTEGER PRIMARY KEY AUTOINCREMENT,
    first_name varchar(100),
    last_name varchar(100)
);

CREATE TABLE BookAuthors (
    book_id INTEGER REFERENCES Book(book_id),
    author_id INTEGER REFERENCES Author(author_id),
    UNIQUE (book_id, author_id)
);

CREATE TABLE Publisher (
    publisher_id INTEGER PRIMARY KEY AUTOINCREMENT,
    name VARCHAR(100)
);

CREATE TABLE BookPublisher (
    book_id INTEGER REFERENCES Book(book_id),
    publisher_id INTEGER REFERENCES Publisher(publisher_id),
    UNIQUE (book_id, publisher_id)
);

CREATE TABLE Genre (
    genre_id INTEGER PRIMARY KEY AUTOINCREMENT,
    name VARCHAR(100)
);

CREATE TABLE BookGenre (
    book_id INTEGER REFERENCES Book(book_id),
    genre_id INTEGER REFERENCES Genre(genre_id),
    UNIQUE (book_id, genre_id)
);

INSERT INTO Book VALUES (1, 'Charlotte''s Web');
INSERT INTO Book VALUES (2, 'Elements of Style');
INSERT INTO Book VALUES (3, 'Alice in Wonderland');

INSERT INTO Author VALUES (1, 'E.B.', 'White');
INSERT INTO Author VALUES (2, 'William', 'Strunk');
INSERT INTO Author VALUES (3, 'Lewis', 'Carrol');

INSERT INTO BookAuthors VALUES (1,1);
INSERT INTO BookAuthors VALUES (2,1);
INSERT INTO BookAuthors VALUES (2,2);
INSERT INTO BookAuthors VALUES (3,3);

INSERT INTO Publisher VALUES (1, 'Penguin Classics');
INSERT INTO Publisher VALUES (2, 'Harcourt');

INSERT INTO BookPublisher VALUES (1, 1);
INSERT INTO BookPublisher VALUES (2, 2);
INSERT INTO BookPublisher VALUES (3, 1);
INSERT INTO BookPublisher VALUES (3, 2);

INSERT INTO Genre VALUES (1, 'Classic');
INSERT INTO Genre VALUES (2, 'Writing');
INSERT INTO Genre VALUES (3, 'Fiction');

INSERT INTO BookGenre VALUES (1, 1);
INSERT INTO BookGenre VALUES (1, 3);
INSERT INTO BookGenre VALUES (2, 1);
INSERT INTO BookGenre VALUES (2, 2);
INSERT INTO BookGenre VALUES (3, 1);
INSERT INTO BookGenre VALUES (3, 3);

SELECT
    b.Title,
    a.first_name,
    a.last_name,
    p.name AS Publisher,
    g.name AS Genre
FROM Book b
    INNER JOIN BookAuthors ba
    ON b.book_id = ba.book_id
    INNER JOIN Author a
    ON ba.author_id = a.author_id
    INNER JOIN BookPublisher bp
    ON b.book_id = bp.book_id
    INNER JOIN Publisher p
    ON bp.publisher_id = p.publisher_id
    INNER JOIN BookGenre bg
    ON b.book_id = bg.book_id
    INNER JOIN Genre g
    ON bg.genre_id = g.genre_id
;

title	first_name	last_name	Publisher	Genre
Charlotte's Web	E.B.	White	Penguin Classics	Classic
Charlotte's Web	E.B.	White	Penguin Classics	Fiction
Elements of Style	E.B.	White	Harcourt	Classic
Elements of Style	E.B.	White	Harcourt	Writing
Elements of Style	William	Strunk	Harcourt	Classic
Elements of Style	William	Strunk	Harcourt	Writing
Alice in Wonderland	Lewis	Carrol	Penguin Classics	Classic
Alice in Wonderland	Lewis	Carrol	Penguin Classics	Fiction
Alice in Wonderland	Lewis	Carrol	Harcourt	Classic
Alice in Wonderland	Lewis	Carrol	Harcourt	Fiction