SQLize Online / PHPize Online  /  SQLtest Online

CREATE TABLE author ( id INT, author_first_name VARCHAR(300), author_last_name VARCHAR(300), CONSTRAINT pk_author PRIMARY KEY (id) ); insert into author (id, author_first_name, author_last_name) values (00001, 'Leesa', 'Linden'), (00002, 'Jeff', 'Norwood'), (00003, 'Allana', 'Codie'), (00004, 'Kenzie', 'Ford'), (00005, 'Abbie', 'Mandy'), (00006, 'Topaz', 'Flint'), (00007,'Matilda', 'Preston'), (00008, 'Lesleigh', 'Kaolin'), (00009, 'Janis', 'Lorin'), (00010, 'Rexanne', 'Irvin'); CREATE TABLE book ( id INT, title VARCHAR(500), category_id INT, publication_date DATE, copies_owned INT, CONSTRAINT pk_book PRIMARY KEY (id), CONSTRAINT fk_book_category FOREIGN KEY (category_id) REFERENCES category(id) ); insert into book (id, title, category_id, publication_date, copies_owned) values (001, 'Dressed for Slaughter', 1,'1970-11-02',2), (002, 'The Mannequin in the West',1,'1975-10-08',3), (003, 'Spell and the Shadow',2,'1976-03-30', 16), (004, 'The Crystal in the Wind',2,'1987-02-13', 2), (005, 'Sectret of the Silent Mermaid',3,'2007-09-27', 5), (006, 'Sign of the Artificial Amulet',3,'1975-12-15', 8), (007,'Have Number, Will Call',4,'1981-03-11',9), (008, 'Beautiful, Beloved',4,'2010-08-05', 3), (009, 'Conjured Station',5,'2017-08-30', 9), (010, 'Abyss Crying',5,'2017-12-14', 5); CREATE TABLE book_author ( book_id INT, author_id INT, CONSTRAINT fk_bookauthor_book FOREIGN KEY (book_id) REFERENCES book(id), CONSTRAINT fk_bookauthor_author FOREIGN KEY (author_id) REFERENCES author(id) ); insert into book_author (book_id, author_id) values (00111, 00001), (00112, 00002), (00113, 00003), (00114, 00004), (00115, 00005), (00116, 00006), (00117, 00007), (00118, 00008), (00119, 00009), (00120, 00010); CREATE TABLE category ( id INT, category_name VARCHAR(100), CONSTRAINT pk_category PRIMARY KEY (id) ); insert into category (id, category_name) values ('crime', 1), ('fantasy', 2), ('mystery', 3), ('romance', 4), ('sci-fi', 5); CREATE TABLE fine ( id INT, book_id INT, loan_id INT, fine_date DATE, fine_amount INT, CONSTRAINT pk_fine PRIMARY KEY (id), CONSTRAINT fk_fine_book FOREIGN KEY (book_id) REFERENCES book(id), CONSTRAINT fk_fine_loan FOREIGN KEY (loan_id) REFERENCES loan(id) ); insert into fine ( id, book_id, loan_id, fine_date, fine_amount) values (501, 00116, 005, '2022-03-03', 5), (502, 00120, 008, '2022-03-20', 7), (503, 00117, 006, '2022-03-19', 10), (504, 00113, 003, '2021-06-01', 3), (505, 00113, 007, '2022-04-25', 25); CREATE TABLE fine_payment ( id INT, member_id INT, payment_date DATE, payment_amount INT, CONSTRAINT pk_fine_payment PRIMARY KEY (id), CONSTRAINT fk_finepay_member FOREIGN KEY (member_id) REFERENCES member(id) ); insert into fine_payment (id, member_id, payment_date, payment_amount) values (1, 201, '2020-01-26', 6), (2, 203, '2021-05-14', 4), (3, 208, '2019-11-21', 2), (4, 205, '2022-02-15', 5); CREATE TABLE loan ( id INT, book_id INT, member_id INT, loan_date DATE, returned_date DATE, CONSTRAINT pk_loan PRIMARY KEY (id), CONSTRAINT fk_loan_book FOREIGN KEY (book_id) REFERENCES book(id), CONSTRAINT fk_loan_member FOREIGN KEY (member_id) REFERENCES member(id) ); insert into loan (id, book_id, member_id, loan_date, returned_date) values (005, 00113, 201, '2020-01-19', '2020-01-26'), (006, 00115, 203, '2021-05-07', '2021-05-14'), (007, 00118, 208, '2019-11-14', '2019-11-21'), (008, 00120, 205, '2022-02-05', '2022-02-15'); CREATE TABLE member ( id INT, first_name VARCHAR(300), last_name VARCHAR(300), joined_date DATE, active_status_id INT, CONSTRAINT pk_member PRIMARY KEY (id), CONSTRAINT fk_member_status FOREIGN KEY (active_status_id) REFERENCES member_status(id) ); insert into member (id, first_name, last_name, joined_date, active_status_id) values (201,'Chaz', 'Rich', '2008-07-11', 1), (202,'Pauleen', 'Lina', '2010-04-27', 1), (203,'Earline', 'Jannine', '2012-10-11', 1), (204,'Katelynn', 'Boone', '2013-02-12', 0), (205,'Reuben', 'Tyrrell', '2013-03-28', 0), (206,'Bryon', 'Ilean', '2000-10-31', 0), (207,'Mat', 'Duana', '2003-07-30', 1), (208,'Dan', 'Bonita', '2011-05-30', 1), (209,'Stacee', 'Laurelle', '2013-06-10', 1), (210,'Morton', 'Jem', '2018-05-29', 1); CREATE TABLE member_status ( id INT, status_value VARCHAR(50), CONSTRAINT pk_member_status PRIMARY KEY (id) ); insert into member_status (id, status_value) values (1, 'active'), (0, 'inactive'); CREATE TABLE reservation ( id INT, book_id INT, member_id INT null, reservation_date DATE null, reservation_status_id INT null, CONSTRAINT pk_reservation PRIMARY KEY (id), CONSTRAINT fk_res_book FOREIGN KEY (book_id) REFERENCES book(id), CONSTRAINT fk_res_member FOREIGN KEY (member_id) REFERENCES member(id) ); insert into reservation (id, book_id, member_id, reservation_date, reservation_status_id) values (301, 00111, 203, '2022-04-28',1), (302, 00112, null, null, 0 ), (303, 00113, 207, '2022-03-25', 1), (304, 00114, null, null, 0), (305, 00115, null, null, 0), (306, 00116, 208, '2022-04-25', 1), (307, 00117, 208, '2022-04-25',1), (308, 00118, null, null, 0), (309, 00119, 206, '2022-04-29', 1), (310, 00120, null, null, 0); CREATE TABLE reservation_status ( id INT, status_value VARCHAR(50), CONSTRAINT pk_res_status PRIMARY KEY (id) ); insert into reservation_status (id, status_value) values (1,'reserved'), (0,'not reserved'); insert into book (id, title, category_id, publication_date, copies_owned) values (00111, 'Dressed for Slaughter', 1,'1970-11-02',2), (00112, 'The Mannequin in the West',1,'1975-10-08',3), (00113, 'Spell and the Shadow',2,'1976-03-30', 16), (00114, 'The Crystal in the Wind',2,'1987-02-13', 2), (00115, 'Sectret of the Silent Mermaid',3,'2007-09-27', 5), (00116, 'Sign of the Artificial Amulet',3,'1975-12-15', 8), (00117,'Have Number, Will Call',4,'1981-03-11',9), (00118, 'Beautiful, Beloved',4,'2010-08-05', 3), (00119, 'Conjured Station',5,'2017-08-30', 9), (00120, 'Abyss Crying',5,'2017-12-14', 5); select * from author; select * from book; select * from book_author; select * from category; select * from fine; select * from fine_payment; select * from loan; select * from member; select * from member_status; select * from reservation; select * from reservation_status; select book.id, book.title, book.category_id, book.publication_date, book.copies_owned, author.id, author.author_first_name, author.author_last_name from book inner join author on book.id=author.id; select * from book; select * from book where copies_owned>=8; select * from book where copies_owned>=8 group by title; select * from author where author_last_name LIKE 'L%' order by author_first_name; select id, title, copies_owned from book where copies_owned=(select MAX (copies_owned) from book);