SQLize Online / PHPize Online  /  SQLtest Online

create table Publisher (publisher_name varchar(40) not null, address varchar(40) not null, phone numeric(12,0) not null, primary key (publisher_name)); create table Book (book_id int, title varchar(60) not null, publisher_name varchar(40) not null, year numeric(4,0) not null, price numeric(5,2) not null, primary key (book_id), foreign key (publisher_name) references Publisher(publisher_name)); create table Book_author (book_id int not null, author_name varchar(40) not null, primary key (author_name, book_id), foreign key (book_id) references Book(book_id)); create table Library_branch (branch_id int not null, branch_name varchar(40) not null, address varchar(40) not null, primary key (branch_id)); create table Book_copies (book_id int not null, branch_id int not null, no_of_copies int not null, primary key (book_id, branch_id), foreign key (book_id) references Book(book_id), foreign key (branch_id) references Library_branch(branch_id)); create table Borrower (card_no int not null, name varchar(40) not null, address varchar(40) not null, phone varchar(12) not null, primary key (card_no)); create table Book_loan (book_id int not null, branch_id int not null, card_no int not null, dueDate date, dateOut date, primary key (book_id, branch_id, card_no), foreign key (book_id) references Book(book_id), foreign key (branch_id) references Library_branch(branch_id), foreign key (card_no) references Borrower(card_no)); insert into Publisher values('Entangled Publishing', 'Denver, US','0001'); insert into Publisher values('Schocken','NYC, US','0002'); insert into Publisher values('Simon & Schuster', 'NYC, US','0003'); insert into Publisher values('Harvest House Publishers', 'Portland, US' ,'0004'); insert into Publisher values('Aria Publishing', 'London, GB','0005'); insert into Publisher values('Mysterious Press', 'NYC, US','0006'); insert into Publisher values('Quirk Books', 'Philadelphia, US','0007'); insert into Publisher values('Two Dollar Radio', 'Columbus, US','0008'); insert into Publisher values('Taylor & Francis Publishing', 'London, GB','0009'); insert into Publisher values('Harper Voyager', 'NYC, US','0010'); insert into Book values('0001', 'Crave', 'Entangled Publishing', '2020', '7.99'); insert into Book values('0002', 'The Castle', 'Schocken', '1926', '10.69'); insert into Book values('0003', 'It Ends With Us', 'Simon & Schuster', '2016', '16.99'); insert into Book values('0004', 'Risen Motherhood', 'Harvest House Publishers', '2020', '19.99'); insert into Book values('0005', 'Forbidden Love with my Neighbors Lawn Gnome', 'Aria Publishing', '2020', '69.42'); insert into Book values('0006', 'The Little Men', 'Mysterious Press', '2015', '5.95'); insert into Book values('0007', 'This is Not the Jess Show', 'Quirk Books', '2021', '10.83'); insert into Book values('0008', '8o8s & OTHERWORLDS', 'Two Dollar Radio', '2021', '15.43'); insert into Book values('0009', 'Learning Through Touch', 'Taylor & Francis Publishing', '2019', '34.95'); insert into Book values('0010', 'Please Kill Me', 'Harper Voyager', '2019', '69'); insert into Book values('0011', 'Crave 2', 'Entangled Publishing', '2020', '7.99'); insert into Book values('0012', 'Crave 3', 'Entangled Publishing', '2021', '7.99'); insert into Book values('0013', 'Crave 4', 'Entangled Publishing', '2022', '7.99'); insert into Book_author values('0001', 'Bob Dole'); insert into Book_author values('0002', 'Edwidge Danticat'); insert into Book_author values('0003', 'Ernest Hemingway'); insert into Book_author values('0004', 'J.R.R. Tolkien'); insert into Book_author values('0005', 'Herodotus'); insert into Book_author values('0006', 'Saul Bellow'); insert into Book_author values('0007', 'Sidney Sheldon'); insert into Book_author values('0008', 'Franz Kafka'); insert into Book_author values('0009', 'Marguerite Duras'); insert into Book_author values('0010', 'Kingsley Amis'); insert into Library_branch values('0001', 'Houston', 'Houston, TX'); insert into Library_branch values('0002', 'Katie', 'Katie, TX'); insert into Library_branch values('0003', 'New York', 'NYC, NY'); insert into Library_branch values('0004', 'Clear Lake', 'Houston, TX'); insert into Library_branch values('0005', 'Austin', 'Austin, TX'); insert into Library_branch values('0006', 'San Antonio', 'San Antonio, TX'); insert into Library_branch values('0007', 'San Diego', 'San Diego, CA'); insert into Library_branch values('0008', 'Baton Rouge', 'Baton Rouge, LA'); insert into Library_branch values('0009', 'Dallas', 'Dallas, TX'); insert into Library_branch values('0010', 'Baytown', 'Baytown TX'); insert into Book_copies values('0001', '0001', '3'); insert into Book_copies values('0002', '0002', '7'); insert into Book_copies values('0003', '0003', '9'); insert into Book_copies values('0004', '0004', '2'); insert into Book_copies values('0005', '0005', '11'); insert into Book_copies values('0006', '0006', '14'); insert into Book_copies values('0007', '0007', '23'); insert into Book_copies values('0008', '0008', '25'); insert into Book_copies values('0009', '0009', '11'); insert into Book_copies values('0010', '0010', '10'); insert into Book_copies values('0001', '0010', '11'); insert into Book_copies values('0011', '0010', '11'); insert into Book_copies values('0012', '0010', '11'); insert into Book_copies values('0013', '0010', '11'); insert into Borrower values('0001', 'Bruce Willis', '1234 Fake St.', '555-555-5555'); insert into Borrower values('0002', 'Johnny Depp', '1235 Fake St.', '555-555-5556'); insert into Borrower values('0003', 'Barack Obama', '1236 Fake St.', '555-555-5557'); insert into Borrower values('0004', 'Joe Biden', '1237 Fake St.', '555-555-5558'); insert into Borrower values('0005', 'Britney Spears', '1238 Fake St.', '555-555-5559'); insert into Borrower values('0006', 'Brett Langford', '1239 Fake St.', '555-555-5560'); insert into Borrower values('0007', 'Veronica Moreno', '1240 Fake St.', '555-555-5561'); insert into Borrower values('0008', 'Cassidy OBrien', '1241 Fake St.', '555-555-5562'); insert into Borrower values('0009', 'Tym Jones', '1242 Fake St.', '555-555-5563'); insert into Borrower values('0010', 'Stephanie Simpson', '1243 Fake St.', '555-555-5564'); insert into Book_loan values('0001', '0001', '0001', '2022-03-13', '2022-01-01'); insert into Book_loan values('0002', '0002', '0002', '2022-03-13', '2022-01-01'); insert into Book_loan values('0003', '0003', '0003', '2022-03-13', '2022-01-01'); insert into Book_loan values('0004', '0004', '0004', '2022-03-13', '2022-01-01'); insert into Book_loan values('0005', '0005', '0005', '2022-03-13', '2022-01-01'); insert into Book_loan values('0006', '0006', '0006', '2022-03-13', '2022-01-01'); insert into Book_loan values('0007', '0007', '0007', '2022-03-13', '2022-01-01'); insert into Book_loan values('0008', '0008', '0008', '2022-03-13', '2022-01-01'); insert into Book_loan values('0009', '0009', '0009', '2022-03-13', '2022-01-01'); insert into Book_loan values('0010', '0010', '0010', '2022-03-13', '2022-01-01'); insert into Book_loan values('0002', '0009', '0009', '2022-06-13', '2022-04-01'); insert into Book_loan values('0003', '0009', '0009', '2022-06-13', '2022-04-01'); SELECT branch_name, count(*) FROM (Book LEFT JOIN Book_copies ON Book.book_id = Book_copies.book_id) LEFT JOIN Library_branch ON Book_copies.branch_id = Library_branch.branch_id WHERE publisher_name = 'Entangled Publishing' GROUP BY branch_name; SELECT count(publisher_name) FROM Book WHERE publisher_name = 'Entangled Publishing' GROUP BY publisher_name;