Question

MYSQL

Questions:

1.For every author, display the number of books written by that author that is carried by Henry Books. Display the author number as ‘Author Number’, author name concatenated (first last) as ‘Author Name’, and the total number of books written by the author as ‘Number of Titles by Author’. List in descending order by author number. Limit the output to 10 rows. Insert your snip of the query and resultset together here.

2.Using a function, what are the lowest price books found in paperback? Only return the lowest price books. Display the book code as ‘Book Code’, the book title as ‘Title’, author name concatenated (first last) as ‘Author Name’ and price as ‘Price’. Insert your snip of the query and resultset together here.

----

DROP DATABASE IF EXISTS Henry_Books;

CREATE DATABASE Henry_Books;

#-- Creating Tables

USE Henry_Books;

#-- Branch Table

CREATE TABLE branch
(
branch_num        int    NOT NULL   auto_increment,
branch_name       varchar(50),
branch_location       varchar(50),
num_employees       int(2),

PRIMARY KEY (branch_num),
INDEX (branch_name),
INDEX (branch_location)
);

#-- Publisher table

CREATE TABLE publisher
(
publisher_code    varchar(3)        NOT NULL,
publisher_name    VARCHAR(25),
city      VARCHAR(20),

PRIMARY KEY (publisher_code),
INDEX (publisher_name)
);

#-- Author table

CREATE TABLE author
(
author_num        int    NOT NULL AUTO_INCREMENT,
author_last      varchar(12),
author_first        varchar(10),

PRIMARY KEY (author_num),
INDEX (author_last)
);

#-- Book table

CREATE TABLE book
(
book_code   varchar(4)   NOT NULL,
title       varchar(40),
publisher_code   char(3),
type       varchar(3),
price       decimal(4,2),
paperback   char(1),

PRIMARY KEY (book_code),
FOREIGN KEY (publisher_code) REFERENCES publisher (publisher_code),
INDEX (title),
INDEX (publisher_code)
);

#-- Wrote table   

CREATE TABLE wrote
(
book_code    char(4)    NOT NULL,
author_num    int   NOT NULL,
sequence   int,
PRIMARY KEY (book_code, author_num),
FOREIGN KEY (book_code) REFERENCES book (book_code),
FOREIGN KEY (author_num) REFERENCES author (author_num)
);

#-- Inventory table

CREATE TABLE inventory
(
book_code   char(4)       NOT NULL,
branch_num   int   NOT NULL,
on_hand       int,

PRIMARY KEY (book_code, branch_num),
FOREIGN KEY (book_code) REFERENCES book (book_code),
FOREIGN KEY (branch_num) REFERENCES branch (branch_num)
);

#-- Populate the branch table

INSERT INTO BRANCH VALUES (null, 'Henry Downtown', '16 Riverview', 10);
INSERT INTO BRANCH VALUES (null, 'Henry on the Hill', '1289 Bedford', 6);
INSERT INTO BRANCH VALUES (null, 'Henry Brentwood', 'Brentwood Mall', 15);
INSERT INTO BRANCH VALUES (null, 'Henry Eastshore', 'Eastshore Mall', 9);

#-- Populate the publisher table

INSERT INTO PUBLISHER VALUES ('AH', 'Arkham House', 'Sauk City WI');
INSERT INTO PUBLISHER VALUES ('AP', 'Arcade Publishing', 'New York');
INSERT INTO PUBLISHER VALUES ('BA', 'Basic Books', 'Boulder CO');
INSERT INTO PUBLISHER VALUES ('BP', 'Berkley Publishing', 'Boston');
INSERT INTO PUBLISHER VALUES ('BY', 'Back Bay Books', 'New York');
INSERT INTO PUBLISHER VALUES ('CT', 'Course Technology', 'Boston');
INSERT INTO PUBLISHER VALUES ('FA', 'Fawcett Books', 'New York');
INSERT INTO PUBLISHER VALUES ('FS', 'Farrar Straus and Giroux', 'New York');
INSERT INTO PUBLISHER VALUES ('HC', 'HarperCollins Publisher', 'New York');
INSERT INTO PUBLISHER VALUES ('JP', 'Jove Publications', 'New York');
INSERT INTO PUBLISHER VALUES ('JT', 'Jeremy P. Tarcher', 'Los Angeles');
INSERT INTO PUBLISHER VALUES ('LB', 'Lb Books', 'New York');
INSERT INTO PUBLISHER VALUES ('MP', 'McPherson and Co.', 'Kingston');
INSERT INTO PUBLISHER VALUES ('PE', 'Penguin USA', 'New York');
INSERT INTO PUBLISHER VALUES ('PL', 'Plume', 'New York');
INSERT INTO PUBLISHER VALUES ('PU', 'Putnam Publishing Group', 'New York');
INSERT INTO PUBLISHER VALUES ('RH', 'Random House', 'New York');
INSERT INTO PUBLISHER VALUES ('SB', 'Schoken Books', 'New York');
INSERT INTO PUBLISHER VALUES ('SC', 'Scribner', 'New York');
INSERT INTO PUBLISHER VALUES ('SS', 'Simon and Schuster', 'New York');
INSERT INTO PUBLISHER VALUES ('ST', 'Scholastic Trade', 'New York');
INSERT INTO PUBLISHER VALUES ('TA', 'Taunton Press', 'Newtown CT');
INSERT INTO PUBLISHER VALUES ('TB', 'Tor Books', 'New York');
INSERT INTO PUBLISHER VALUES ('TH', 'Thames and Hudson', 'New York');
INSERT INTO PUBLISHER VALUES ('TO', 'Touchstone Books', 'Westport CT');
INSERT INTO PUBLISHER VALUES ('VB', 'Vintage Books', 'New York');
INSERT INTO PUBLISHER VALUES ('WN', 'W.W. Norton', 'New York');
INSERT INTO PUBLISHER VALUES ('WP', 'Westview Press', 'Boulder CO');

#-- Populate the author table

INSERT INTO AUTHOR VALUES (Null, 'Morrison', 'Toni');
INSERT INTO AUTHOR VALUES (Null, 'Solotaroff', 'Paul');
INSERT INTO AUTHOR VALUES (Null, 'Vintage', 'Vernor');
INSERT INTO AUTHOR VALUES (Null, 'Francis', 'Dick');
INSERT INTO AUTHOR VALUES (Null, 'Straub', 'Peter');
INSERT INTO AUTHOR VALUES (Null, 'King', 'Stephen');
INSERT INTO AUTHOR VALUES (Null, 'Pratt', 'Philip');
INSERT INTO AUTHOR VALUES (Null, 'Chase', 'Truddi');
INSERT INTO AUTHOR VALUES (Null, 'Collins', 'Bradley');
INSERT INTO AUTHOR VALUES (Null, 'Heller', 'Joseph');
INSERT INTO AUTHOR VALUES (Null, 'Wills', 'Gary');
INSERT INTO AUTHOR VALUES (Null, 'Hofstadter', 'Douglas R.');
INSERT INTO AUTHOR VALUES (Null, 'Lee', 'Harper');
INSERT INTO AUTHOR VALUES (Null, 'Ambrose', 'Stephen E.');
INSERT INTO AUTHOR VALUES (Null, 'Rowling', 'J.K.');
INSERT INTO AUTHOR VALUES (Null, 'Salinger', 'J.D.');
INSERT INTO AUTHOR VALUES (Null, 'Heaney', 'Seamus');
INSERT INTO AUTHOR VALUES (Null, 'Camus', 'Albert');
INSERT INTO AUTHOR VALUES (Null, 'Collins, Jr.', 'Bradley');
INSERT INTO AUTHOR VALUES (Null, 'Steinbeck', 'John');
INSERT INTO AUTHOR VALUES (Null, 'Castelman', 'Riva');
INSERT INTO AUTHOR VALUES (Null, 'Owen', 'Barbara');
INSERT INTO AUTHOR VALUES (Null, 'O''Rourke', 'Randy');
INSERT INTO AUTHOR VALUES (Null, 'Kidder', 'Tracy');
INSERT INTO AUTHOR VALUES (Null, 'Schleining', 'Lon');

#-- Populate the Book table

INSERT INTO BOOK VALUES ('0180', 'A Deepness in the Sky', 'TB', 'SFI', 7.19, 'Y');
INSERT INTO BOOK VALUES ('0189', 'Magic Terror', 'FA', 'HOR', 7.99, 'Y');
INSERT INTO BOOK VALUES ('0200', 'The Stranger', 'VB', 'FIC', 8.00, 'Y');
INSERT INTO BOOK VALUES ('0378', 'Venice', 'SS', 'ART', 24.50, 'N');
INSERT INTO BOOK VALUES ('079X', 'Second Wind', 'PU', 'MYS', 24.95, 'N');
INSERT INTO BOOK VALUES ('0808', 'The Edge', 'JP', 'MYS', 6.99, 'Y');
INSERT INTO BOOK VALUES ('1351', 'Dreamcatcher: A Novel', 'SC', 'HOR', 19.60, 'N');
INSERT INTO BOOK VALUES ('1382', 'Treasure Chests', 'TA', 'ART', 24.46, 'N');
INSERT INTO BOOK VALUES ('138X', 'Beloved', 'PL', 'FIC', 12.95, 'Y');
INSERT INTO BOOK VALUES ('2226', 'Harry Potter and the Goblet of Fire', 'ST', 'SFI', 13.96, 'N');
INSERT INTO BOOK VALUES ('2281', 'Van Gogh and Gauguin', 'WP', 'ART', 21.00, 'N');
INSERT INTO BOOK VALUES ('2766', 'Of Mice and Men', 'PE', 'FIC', 6.95, 'Y');
INSERT INTO BOOK VALUES ('2908', 'Electric Light', 'FS', 'POE', 14.00, 'N');
INSERT INTO BOOK VALUES ('3350', 'Group: Six People in Search of a Life', 'BP', 'PSY', 10.40, 'Y');
INSERT INTO BOOK VALUES ('3743', 'Nine Stories', 'LB', 'FIC', 5.99, 'Y');
INSERT INTO BOOK VALUES ('3906', 'The Soul of a New Machine', 'BY', 'SCI', 11.16, 'Y');
INSERT INTO BOOK VALUES ('5163', 'Travels with Charley', 'PE', 'TRA', 7.95, 'Y');
INSERT INTO BOOK VALUES ('5790', 'Catch-22', 'SC', 'FIC', 12.00, 'Y');
INSERT INTO BOOK VALUES ('6128', 'Jazz', 'PL', 'FIC', 12.95, 'Y');
INSERT INTO BOOK VALUES ('6328', 'Band of Brothers', 'TO', 'HIS', 9.60, 'Y');
INSERT INTO BOOK VALUES ('669X', 'A Guide to SQL', 'CT', 'CMP', 37.95, 'Y');
INSERT INTO BOOK VALUES ('6908', 'Franny and Zooey', 'LB', 'FIC', 5.99, 'Y');
INSERT INTO BOOK VALUES ('7405', 'East of Eden', 'PE', 'FIC', 12.95, 'Y');
INSERT INTO BOOK VALUES ('7443', 'Harry Potter Order of the Phoenix', 'ST', 'SFI', 18.16, 'N');
INSERT INTO BOOK VALUES ('7559', 'The Fall', 'VB', 'FIC', 8.00, 'Y');
INSERT INTO BOOK VALUES ('8092', 'Godel, Escher, Bach', 'BA', 'PHI', 14.00, 'Y');
INSERT INTO BOOK VALUES ('8720', 'When Rabbit Howls', 'JP', 'PSY', 6.29, 'Y');
INSERT INTO BOOK VALUES ('9611', 'Black House', 'RH', 'HOR', 18.81, 'N');
INSERT INTO BOOK VALUES ('9627', 'Song of Solomon', 'PL', 'FIC', 14.00, 'Y');
INSERT INTO BOOK VALUES ('9701', 'The Grapes of Wrath', 'PE', 'FIC', 13.00, 'Y');
INSERT INTO BOOK VALUES ('9882', 'Slay Ride', 'JP', 'MYS', 6.99, 'Y');
INSERT INTO BOOK VALUES ('9883', 'The Catcher in the Rye', 'LB', 'FIC', 5.99, 'Y');
INSERT INTO BOOK VALUES ('9931', 'To Kill a Mockingbird', 'HC', 'FIC', 18.00, 'N');

#-- Populate the Wrote table

INSERT INTO WROTE VALUES ('0180', 3, 1);
INSERT INTO WROTE VALUES ('0189', 5, 1);
INSERT INTO WROTE VALUES ('0200', 18, 1);
INSERT INTO WROTE VALUES ('0378', 11, 1);
INSERT INTO WROTE VALUES ('079X', 4, 1);
INSERT INTO WROTE VALUES ('0808', 4, 1);
INSERT INTO WROTE VALUES ('1351', 6, 1);
INSERT INTO WROTE VALUES ('1382', 23, 2);
INSERT INTO WROTE VALUES ('1382', 25, 1);
INSERT INTO WROTE VALUES ('138X', 1, 1);
INSERT INTO WROTE VALUES ('2226', 15, 1);
INSERT INTO WROTE VALUES ('2281', 9, 2);
INSERT INTO WROTE VALUES ('2281', 19, 1);
INSERT INTO WROTE VALUES ('2766', 20, 1);
INSERT INTO WROTE VALUES ('2908', 17, 1);
INSERT INTO WROTE VALUES ('3350', 2, 1);
INSERT INTO WROTE VALUES ('3743', 16, 1);
INSERT INTO WROTE VALUES ('3906', 24, 1);
INSERT INTO WROTE VALUES ('5163', 20, 1);
INSERT INTO WROTE VALUES ('5790', 10, 1);
INSERT INTO WROTE VALUES ('6128', 1, 1);
INSERT INTO WROTE VALUES ('6328', 14, 1);
INSERT INTO WROTE VALUES ('669X', 7, 1);
INSERT INTO WROTE VALUES ('6908', 16, 1);
INSERT INTO WROTE VALUES ('7405', 20, 1);
INSERT INTO WROTE VALUES ('7443', 15, 1);
INSERT INTO WROTE VALUES ('7559', 18, 1);
INSERT INTO WROTE VALUES ('8092', 12, 1);
INSERT INTO WROTE VALUES ('8720', 8, 1);
INSERT INTO WROTE VALUES ('9611', 5, 2);
INSERT INTO WROTE VALUES ('9611', 6, 1);
INSERT INTO WROTE VALUES ('9627', 1, 1);
INSERT INTO WROTE VALUES ('9701', 20, 1);
INSERT INTO WROTE VALUES ('9882', 4, 1);
INSERT INTO WROTE VALUES ('9883', 16, 1);
INSERT INTO WROTE VALUES ('9931', 13, 1);

#-- Populate the inventory table

INSERT INTO INVENTORY VALUES ('0180', 1, 2);
INSERT INTO INVENTORY VALUES ('0189', 2, 2);
INSERT INTO INVENTORY VALUES ('0200', 1, 1);
INSERT INTO INVENTORY VALUES ('0200', 2, 3);
INSERT INTO INVENTORY VALUES ('0378', 3, 2);
INSERT INTO INVENTORY VALUES ('079X', 2, 1);
INSERT INTO INVENTORY VALUES ('079X', 3, 2);
INSERT INTO INVENTORY VALUES ('079X', 4, 3);
INSERT INTO INVENTORY VALUES ('0808', 2, 1);
INSERT INTO INVENTORY VALUES ('1351', 2, 4);
INSERT INTO INVENTORY VALUES ('1351', 3, 2);
INSERT INTO INVENTORY VALUES ('1382', 2, 1);
INSERT INTO INVENTORY VALUES ('138X', 2, 3);
INSERT INTO INVENTORY VALUES ('2226', 1, 3);
INSERT INTO INVENTORY VALUES ('2226', 3, 2);
INSERT INTO INVENTORY VALUES ('2226', 4, 1);
INSERT INTO INVENTORY VALUES ('2281', 4, 3);
INSERT INTO INVENTORY VALUES ('2766', 3, 2);
INSERT INTO INVENTORY VALUES ('2908', 1, 3);
INSERT INTO INVENTORY VALUES ('2908', 4, 1);
INSERT INTO INVENTORY VALUES ('3350', 1, 2);
INSERT INTO INVENTORY VALUES ('3743', 2, 1);
INSERT INTO INVENTORY VALUES ('3906', 2, 1);
INSERT INTO INVENTORY VALUES ('3906', 3, 2);
INSERT INTO INVENTORY VALUES ('5163', 1, 1);
INSERT INTO INVENTORY VALUES ('5790', 4, 2);
INSERT INTO INVENTORY VALUES ('6128', 2, 4);
INSERT INTO INVENTORY VALUES ('6128', 3, 3);
INSERT INTO INVENTORY VALUES ('6328', 2, 2);
INSERT INTO INVENTORY VALUES ('669X', 1, 1);
INSERT INTO INVENTORY VALUES ('6908', 2, 2);
INSERT INTO INVENTORY VALUES ('7405', 3, 2);
INSERT INTO INVENTORY VALUES ('7443', 4, 1);
INSERT INTO INVENTORY VALUES ('7559', 2, 2);
INSERT INTO INVENTORY VALUES ('8092', 3, 1);
INSERT INTO INVENTORY VALUES ('8720', 1, 3);
INSERT INTO INVENTORY VALUES ('9611', 1, 2);
INSERT INTO INVENTORY VALUES ('9627', 3, 5);
INSERT INTO INVENTORY VALUES ('9627', 4, 2);
INSERT INTO INVENTORY VALUES ('9701', 1, 2);
INSERT INTO INVENTORY VALUES ('9701', 2, 1);
INSERT INTO INVENTORY VALUES ('9701', 3, 3);
INSERT INTO INVENTORY VALUES ('9701', 4, 2);
INSERT INTO INVENTORY VALUES ('9882', 3, 3);
INSERT INTO INVENTORY VALUES ('9883', 2, 3);
INSERT INTO INVENTORY VALUES ('9931', 1, 2);

--

Answer 1

Question 1:

SQL query :
select author.author_num as 'Author Number',concat(author_first,' ',author_last) as 'Author Name',
count(book_code) as 'Number of Titles by Author' from author , wrote where
author.author_num=wrote.author_num
group by author.author_num,author_first,author_last
order by author.author_num desc limit 10;

Screen in MySQL Workbench :

*************************************

Question 2:

Function :

DELIMITER //
CREATE FUNCTION min_Book() RETURNS decimal(4,2)
BEGIN
DECLARE minPrice decimal(4,2);
Select min(price) into minPrice from book where paperback='Y';
RETURN minPrice;
END
//
DELIMITER ;

SQL query :
select book.book_code as 'Book Code' , title as 'Title',
concat(author_first,' ',author_last) as 'Author Name',min_Book() as 'Price'
from book ,wrote,author where
book.book_code=wrote.book_code and
wrote.author_num=author.author_num and price=min_Book();

Screen in MySQL Workbench :

Price Book Code 3743 6908 9883 Title Nine Stories Franny and Zooey The Catcher in the Rye Author Name J.D. Salinger J.D. Salinger J.D. Salinger 5.99 5.99 5.99

*************************************