Personal Library

-- Create the schema if it doesn't exist
CREATE SCHEMA IF NOT EXISTS Personal_Library;

-- Create the Books table within the Personal_Library schema
CREATE TABLE IF NOT EXISTS Personal_Library.Books (
    Title VARCHAR(500) NOT NULL,
    Author VARCHAR(255) NOT NULL,
    Genre VARCHAR(50),
    ISBN VARCHAR(13) UNIQUE,
    Page_Count INT,
    Read_Status VARCHAR(8) CHECK (Read_Status IN ('read', 'not read')) DEFAULT 'read'
);
-- Insert book values into Books table
INSERT INTO Personal_Library.Books VALUES
('Rouge', 'Mona Awad', 'Horror', '9781982169695', 384,'read'),
('Desperation', 'Stephen King', 'Horror', '9780670868360', 690,'read'),
('Brother', 'Ania Ahlborn', 'Horror', '9781476783734', 319,'read'),
('We Know You Remember', 'Tove Alsterdal', 'Thriller', '9780063115064', 356,'not read'),
('The Highly Sensitive Person', 'Elaine N. Aron', 'Non-Fiction', '9780553062182', 251,'not read'),
('Penpal', 'Dathan Auerbach', 'Horror', '9780985545505', 243,'read'),
('Hull Zero Three', 'Greg Bear', 'Science Fiction', '9780316072816', 307,'read'),
('The Exorcist', 'William Peter Blatty', 'Horror', '9780062094360', 400,'read'),
('The Indifferent Stars Above', 'Daniel James Brown', 'Non-Fiction', '9780061348105', 288,'not read'),
('The Study of American Folklore', 'Jan Harold Brunvand', 'Non-Fiction', '9780393090482', 460,'not read'),
('Enders Game', 'Orson Scott Card', 'Science Fiction', '9780812550702', 324,'read'),
('Xenocide', 'Orson Scott Card', 'Science Fiction', '9780312861872', 592,'read'),
('To Rouse Leviathan', 'Matt Cardin', 'Horror', '9781614982715', 315,'read'),
('Mary', 'Nat Cassidy', 'Horror', '9781250265234', 405,'read'),
('The Haunting of Alejandra', 'V Castro', 'Horror', '9780593499696', 272,'not read'),
('Such Pretty Flowers', 'KL Cerra', 'Horror', '9780593500255', 320,'not read'),
('A Psalm for the Wild-Built', 'Becky Chambers', 'Fiction', '9781250236210', 147,'not read'),
('Imaginary Friends', 'Stephen Chbosky', 'Horror', '9781538731352', 736,'not read'),
('The Hunt for Red October', 'Tom Clancy', 'Thriller', '9780425172902', 469,'read'),
('Hunted', 'Darcy Coates', 'Horror', '9780994630650', 408,'read'),
('The Handyman Method', 'Nick Cutter', 'Horror', '9781982196721', 304,'not read'),
('Wild Spaces', 'SL Coney', 'Horror', '9781250866837', 122,'not read'),
('Dark Matter', 'Blake Crouch', 'Science Fiction', '9781101904244', 352,'read'),
('Recursion', 'Blake Crouch', 'Science Fiction', '9781524759797', 336,'read'),
('No Longer Human', 'Osamu Dazai', 'Asian Literature', '9780811204811', 177,'read'),
('From Here to Eternity', 'Caitlyn Doughty', 'Non-Fiction', '9780393356281', 256,'not read'),
('A God in the Shed', 'JF Dubeau', 'Horror', '9781942645351', 450,'read'),
('The Paleontologist', 'Luke Dumas', 'Horror', '9781668018262', 356,'not read'),
('American Psycho', 'Bret Easton Ellis', 'Fiction', '9780679735779', 399,'read'),
('High Planes Drifter', 'Edward M Erdelac', 'Weird West', '9781721011230', 296,'read'),
('The Mensch with No Name', 'Edward M Erdelac', 'Weird West', '9781726454827', 312,'read'),
('Have Glyphs Will Travel', 'Edward M Erdelac', 'Weird West', '9781702792615', 447,'read'),
('Once Upon a Time in the Weird West', 'Edward M Erdelac', 'Weird West', '9798645547653', 472,'not read'),
('Neverwhere', 'Neil Gaiman', 'Fantasy', '9780380789016', 370,'read'),
('The Wager', 'David Grann', 'Non-fiction', '9780385534260', 354,'read'),
('Revelator', 'Daryl Gregory', 'Horror', '9780525657385', 337,'read'),
('A Haunting on the Hill', 'Elizabeth Hand', 'Horror', '9780316527323', 326,'not read'),
('101 Horror Books to Read Before You''re Murdered', 'Sadie Hartmann', 'Books about books', '9781645677802', 168,'not read'),
('Christians Against Christianity: How Right-Wing Evangelicals Are Destroying Our Nation and Our Faith', 'Obery M Hendricks Jr', 'Religion', '9780807057407', 204,'not read'),
('My Best Friend''s Exorcism', 'Grady Hendrix', 'Horror', '9781594749766', 332,'read'),
('Stranger in a Strange Land', 'Robert A Heinlein', 'Science Fiction', '9780399135866', 525,'not read'),
('Dune', 'Frank Herbert', 'Science Fiction', '9780441005901', 517,'read'),
('Things We Do in the Dark', 'Jennifer Hillier', 'Thriller', '9781250763167', 352,'not read'),
('All About Love', 'Bell Hooks', 'Non-Fiction', '9780060959470', 238,'not read'),
('Black Paradox', 'Junji Ito', 'Horror', '9781974728633', 208,'read'),
('Uzumaki', 'Junji Ito', 'Horror', '9781421561325', 653,'read'),
('How to Be an Antiracist', 'Ibram X Kendi', 'Non-Fiction', '9780525509288', 305,'not read'),
('The Salt Grows Heavy', 'Cassandra Khaw', 'Horror', '9781250830913', 106,'read'),
('Black House', 'Stephen King', 'Horror', '9781451697735', 833,'not read'),
('Holly', 'Stephen King', 'Horror', '9781668016138', 449,'not read'),
('Pet Sematary', 'Stephen King', 'Horror', '9780385182447', 411,'read'),
('The Haunting of Velkwood', 'Gwendolyn Kiste', 'Horror', '9781982172374', 256,'not read'),
('Phantoms', 'Dean Koontz', 'Horror', '9780425181102', 448,'read'),
('Strangers', 'Deen Koontz', 'Horror', '9780425181119', 689,'not read'),
('The Hollow Places', 'T Kingfisher', 'Horror', '9781534451124', 337,'read'),
('What Moves the Dead', 'T Kingfisher', 'Horror', '9781250830753', 165,'read'),
('Beholder', 'Ryan La Sala', 'Horror', '9781338745344', 343,'not read'),
('Joe R. Lansdale’s The Drive-In: Multiplex','Multiple', 'Anthology', '9780986259456', 299,'not read'),
('Lone Women', 'Victor LaValle', 'Horror', '9780525512080', 285,'read'),
('Maeve Fly', 'CJ Leede', 'Horror', '9781250857859', 288,'read'),
('Shutter Island', 'Dennis Lehane', 'Thriller', '9780061703256', 385,'read'),
('Waking the Tiger: Healing Trauma', 'Peter A Levine', 'Non-Fiction', '9781556432330', 288,'read'),
('The Girl Beneath the Sea', 'Andrew Mayne', 'Thriller', '9781542009577', 331,'read'),
('The Soul of an Octopus', 'Sy Montgomery', 'Non-Fiction', '9781451697711', 262,'not read'),
('The Wind-Up Bird Chronicle', 'Haruki Murakami', 'Asian Literature', '9780679775430', 607, 'read'),
('The Vessel', 'Adam LG Nevill', 'Horror', '9781739788612', 172,'read'),
('The Whisper Man', 'Alex North', 'Thriller', '9781250318008', 384, 'read'),
('Intercepts', 'TJ Payne', 'Horror', '9781098943851', 325,'read'),
('Forgotten Sisters', 'Cynthia Pelayo', 'Horror', '9781662513916', 303,'not read'),
('Strange Planet', 'Nathan W Pyle', 'Humor', '9780062970701', 144,'read'),
('Root Rot & Other Grim Tales', 'Sara Read', 'Horror', '9798988128625', 308,'not read'),
('Foe', 'Iain Reid', 'Horror', '9781501127441', 288,'not read'),
('Effin'' Birds: A Field Guide to Identification', 'Aaron Reynolds', 'Humor', '9781984856289', 208,'read'),
('The Violence of Love', 'Oscar Romero', 'Theology', '9781570755354', 240,'not read'),
('The Collapsing Empire', 'John Scalzi', 'Science Fiction', '9780765388889', 372,'not read'),
('The Consuming Fire', 'John Scalzi', 'Science Fiction', '9780765388995', 360,'not read'),
('Old Man''s War', 'John Scalzi', 'Science Fiction', '9780765348272', 355,'read'),
('The Last Emperox', 'John Scalzi', 'Science Fiction', '9780765389183', 356,'not read'),
('The Kaiju Preservation Society', 'John Scalzi', 'Science Fiction', '9780765389121', 264,'not read'),
('Frankenstein', 'Mary Shelley', 'Horror', '9781435171442', 221,'read'),
('Hyperion', 'Dan Simmons', 'Science Fiction', '9780553283686', 500,'not read'),
('Ghost Story', 'Peter Straub', 'Horror', '9781101989197', 528,'not read'),
('The Haar', 'David Sodergren', 'Horror', '9798800159837', 220,'read'),
('The Chestnut Man', 'Søren Sveistrup', 'Thriller', '9780062895363', 519,'not read'),
('Children of Time', 'Adrian Tchaikovsky', 'Science Fiction', '9780316452502', 600,'read'),
('Children of Ruin', 'Adrian Tchaikovsky', 'Science Fiction', '9780316452533', 597,'read'),
('Children of Memory', 'Adrian Tchaikovsky', 'Science Fiction', '9780316466400', 503,'not read'),
('The Cabin at the End of the World', 'Paul Tremblay', 'Horror', '9780062679116', 304,'read'),
('Ballad of the Werevixens', 'Kristopher Triana', 'Weird West', '9781961758056', 420,'read'),
('The Thirteenth Koyote','Kristopher Triana','Weird West','9781950259335',502,'read'),
('The Burning Girls','C.J. Tudor','Horror','9781984825025',352,'read'),
('A Deepness in the Sky','Vernor Vinge','Science Ficton','9780812536355',755,'not read'),
('A Fine Upon the Deep', 'Vernor Vinge','Science Fiction','9780812515282', 613,'not read'),
('God Bless You, Mr. Rosewater','Kurt Vonnegut Jr.','Fiction','9780385333474',275,'read'),
('Hocus Pocus','Kurt Vonnegut Jr.', 'Fiction','9780425130216',234,'read'),
('The Sirens of Titan','Kurt Vonnegut Jr.','Fiction','9780385333498',319,'read'),
('Slaughterhouse-Five','Kurt Vonnegut Jr.','Fiction','9780385333849',275,'read'),
('Welcome to the Monkey House','Kurt Vonnegut Jr.','Fiction','9780440194781',308,'read'),
('Just Like Someone Without Mental Illness Only More So','Mark Vonnegut','Memoir','9780385343794',224,'read'),
('Dead Astronauts','Jeff Vandermeer','Science Fiction','9780008375362',323,'not read'),
('African Myths & Legends: Tales of Heroes, Gods & Monsters', 'Jake Jackson', 'Mythology', '9781839648885', 416,'not read'),
('If This Book Exists You''re in the Wrong Universe', 'Jason Pargin', 'Horror', '9781803360119', 432,'not read'),
('Blindsight','Peter Watts','Science Fiction','9780765319647',384,'read'),
('The Last House on Needless Street','Catronia Ward','Horror','9781250832764',341,'read'),
('The Martian','Andy Weir','Science Fiction','9780804139021',369,'not read'),
('John the Balladeer','Manly Wade Wellman', 'Fantasy','9781960241030', 262,'not read'),
('Where the Dead Wait','Ally Wilkes','Horror','9781982182823',400,'not read'),
('Out There Screaming: An Anthology of New Black Horror','Jordan Peele','Horror','9780593243794',387,'not read'),
('Fairytale','Stephen King','Fantasy','9781668002179',608,'not read'),
('Nestlings','Nat Cassidy','Horror','9781250265258',293,'not read'),
('The Spirit Bares It''s Teeth','Andrew Joseph White','Horror','9781682636114',387,'not read'),
('Vampires of El Norte','Isabel Cañas','Horror','9780593436721',317,'not read'),
('The Only Good Indians','Stephen Graham Jones','Horror','9781982136451',305,'not read'),
('Thirst','Marina Yuszczuk','Horror','9780593472064',256,'not read'),
('A Botanical Daughter','Noah Medlock','Horror','9781803365909',384,'not read'),
('A Gentleman in Moscow','Amor Towles','Historical Fiction','9783548290720',496,'not read'),
('Wool','Hugh Howey','Science Fiction','9780358447849',588,'not read'),
('Camp Damascus','Chuck Tingle','Horror','9781803365121',324,'not read'),
('From Below','Darcy Coates','Horror','9781728220239',469,'not read'),
('Mothered','Zoje Stage','Horror','9781662506239',319,'not read'),
('Boys in the Valley','Philip Fracassi','Horror','9781250879035',335,'not read'),
('The Devil Aspect','Craig Russell','Horror','9780525564782',432,'not read'),
('Last Days','Adam Nevill','Horror','9781250018182',531,'not read'),
('Lovecraft Country','Matt Ruff','Horror','9780062292070',372, 'not read'),
('All the Fiends of Hell','Adam Nevill','Horror','9781739378417',344,'not read'),
('Poor Things','Alasdair Gray','Fiction','9780747562283',318,'not read'),
('Let the Woods Keep Our Bodies','E.M. Roy','Horror','9781943720897',196,'not read'),
('The Bed Makers','Chad Lutzke','Horror','9781957133713',172,'not read'),
('The Woodwitch','Stephen Gregory','Horror','9781941147450',212,'not read'),
('Candy Cain Kills','Brian McAuley','Horror','9781959565192',172,'not read'),
('Subversive Jesus: An Adventure in Justice, Mercy, and Faithfulness in a Broken World','Craig Greenfield','Non-Fiction','9780310346234',192,'not read'),
('Life Ceremony','Sayaka Murata','Asian Literature','9781783787388',272,'read');
SELECT COUNT(*) FROM Personal_Library.Books;