CREATE TABLE `books_authors` (
    `book_id` INTEGER UNSIGNED,
    `author_id` INTEGER UNSIGNED,
    `author_order` TINYINT NULL DEFAULT NULL,
    UNIQUE KEY (book_id,author_id)
    )ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

    CREATE TABLE `books` (
      `book_id` INTEGER UNSIGNED AUTO_INCREMENT,
      `title` VARCHAR(200) NULL DEFAULT NULL,
      `yearpub` YEAR NULL DEFAULT NULL,
      PRIMARY KEY (`book_id`)
    ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

    CREATE TABLE `authors` (
      `author_id` INTEGER UNSIGNED AUTO_INCREMENT,
      `first_name` VARCHAR(30) NULL DEFAULT NULL,
      `last_name` VARCHAR(30) NULL DEFAULT NULL,
      `whereborn` VARCHAR(10) NULL DEFAULT NULL,
      PRIMARY KEY (`author_id`)
    )ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

    ALTER TABLE `books_authors` ADD FOREIGN KEY (book_id) REFERENCES `books` (`book_id`);
    ALTER TABLE `books_authors` ADD FOREIGN KEY (author_id) REFERENCES `authors` (`author_id`);


INSERT INTO books(title,yearpub) values
    ('Black Holes','1984'),
    ('1984', '1954'),
    ('MySQL for Relaxation','2022'),
    ('Sorcerors Stone', '1999'),
    ('Spellng for Dummys', '2017');

    INSERT INTO authors (first_name,last_name,whereborn) values
    ('Steven','Hawking','Oxford'),
    ('George','Orwell','England'),
    ('Albert','Einstein','NJ'),
    ('J.K.','Rowling','England'),
    ('Phil','Waclawski','MI'),
    ('Stan','Lee', 'NY');

    INSERT INTO books_authors (book_id,author_id,author_order) values
    (1,1,1),
    (1,3,2),
    (1,6,3),
    (2,2,1),
    (2,4,2),
    (3,5,1),
    (4,4,1),
    (4,6,2),
    (5,5,1),
    (5,2,2);
    
create table publishers (
publisher_id INT UNSIGNED PRIMARY KEY auto_increment,
pubname varchar(50),
pubstate varchar(2)
) ENGINE = InnoDB DEFAULT CHARSET=utf8mb4 collate=utf8mb4_unicode_ci;

INSERT INTO publishers (pubname,pubstate) values
('O\'Reilly','NY'),
('Addison Wesley',"OH"),
('Marvel Comics','NY');

ALTER TABLE books ADD publisher_id INT UNSIGNED;

ALTER TABLE books ADD FOREIGN KEY (publisher_id) 
REFERENCES publishers(publisher_id);

UPDATE books set publisher_id = 2 where book_id=1;
UPDATE books set publisher_id = 3 where book_id=2;
UPDATE books set publisher_id = 1 where book_id=3;
UPDATE books set publisher_id = 3 where book_id=4;
UPDATE books set publisher_id = 2 where book_id=5;

-- This creates some orphaned books

Insert into books (title, yearpub) values
('How to be bad at the bass guitar', 2017),
('How to be a great bass fisher person', 2017);