CREATE TABLE IF NOT EXISTS publishers (
publisher_id INT AUTO_INCREMENT,
name VARCHAR(255) NOT NULL,
PRIMARY KEY (publisher_id)
);
CREATE TABLE IF NOT EXISTS books (
book_id INT AUTO_INCREMENT,
title VARCHAR(255) NOT NULL,
isbn VARCHAR(13) NULL,
published_date DATE NULL,
publisher_id INT NULL,
PRIMARY KEY (book_id)
);
INSERT INTO books(title, isbn, published_date,publisher_id)
VALUES
('Goodbye to All That','9781541619883','2013-01-05', 3),
('The Mercies','9780316529235','2020-01-28', 3),
('On the Farm','9780763655914','2012-03-27', 2),
('Joseph Had a Little Overcoat','9780140563580','1977-03-15', 2);
DELIMITER $$
CREATE PROCEDURE `get_books_published_after` (IN published_year INT)
BEGIN
SELECT
book_id, title, isbn, published_date, name as publisher
FROM
books b
INNER JOIN publishers p
ON p.publisher_id = b.publisher_id
WHERE year(published_date) > published_year;
END$$
DELIMITER ;
CALL get_books_published_after(2010);