database.sql

CREATE DATABASE IF NOT EXISTS get_search_select CHARACTER SET utf8 COLLATE utf8_general_ci;
USE get_search_select;

DROP TABLE IF EXISTS books;
DROP TABLE IF EXISTS genres;

CREATE TABLE genres (
    id INT AUTO_INCREMENT PRIMARY KEY,
    name VARCHAR(100) NOT NULL
);

CREATE TABLE books (
    id INT AUTO_INCREMENT PRIMARY KEY,
    title VARCHAR(100) NOT NULL,
    author VARCHAR(100) NOT NULL,
    genre_id INT NOT NULL
);

INSERT INTO genres (name) VALUES
('Fantasy'),
('Romance'),
('Drama'),
('Horror'),
('Classic');

INSERT INTO books (title, author, genre_id) VALUES
('Harry Potter', 'J. K. Rowling', 1),
('The Hobbit', 'J. R. R. Tolkien', 1),
('Pride and Prejudice', 'Jane Austen', 2),
('Romeo and Juliet', 'William Shakespeare', 3),
('Dracula', 'Bram Stoker', 4),
('1984', 'George Orwell', 5),
('Animal Farm', 'George Orwell', 5),
('The Shining', 'Stephen King', 4),
('Jane Eyre', 'Charlotte Bronte', 2),
('Hamlet', 'William Shakespeare', 3);