Question

MYSQL:

Question: 1.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.

2. Display the number of publishers by city.   Display the city as ‘City’ and the number of publishers in that city as ‘Number of Publishers’. Insert your snip of the query and resultset 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

1. Technically MySql functions can not return result sets. Assuming that MySql Procedure is mentioned in the question.

 DELIMITER // DROP PROCEDURE IF EXISTS getLowPriceBooks // CREATE PROCEDURE getLowPriceBooks() BEGIN select b.book_code as 'Book Code', b.title as 'Title', concat(a.author_first,a.author_last) as 'Author Name', b.price as 'Price' from book b inner join wrote w on b.book_code = w.book_code inner join author a on w.author_num=a.author_num where b.paperback='Y' and b.price=(select min(price) from book); END // DELIMITER ; call getLowPriceBooks();

[mysql> DROP PROCEDURE IF EXISTS getLowPriceBooks // Query OK, Orows affected, 1 warning (0.02 sec )nputs; vector<int> numbers; [mysql> CREATE PROCEDURE getLowPriceBooks() int numbers2 (10); [ -> BEGIN vector<int> numbers3(10, 0); I -> select b.book_code as 'Book Code', b.title as 'Title', concat(a.author_first, a.author_last) as 'Author Name', b.price as Price' from bol ok b inner join wrote won b.book_code = w.book_code inner join author a on w.author_num=a.author_num where b.paperback='Y' and b.price=(select min(price) from book); num_of_inputs = get_num_of_inputs(); [ -> END build_vector (numbers, num_of_inputs); -> // Query OK, Orows affected (0.00 sec) pop front (numbers); print_vector(numbers); print_vector (numbers); [mysql> DELIMITER ; (mysql> call getLowPriceBooks(); pop_front (numbers); print vector numbe Hi-----+ | Book Code | Title PP Author Name Price | ---+------ --------+ | 3743 Nine Stories | J.D. Salinger | 5.99 | 6908 | Franny and Zooey J.D. Salinger | 5.99 9883 The Catcher in the Rye | J.D. Salinger | 5.99 ------- --------------+-------+ 3 rows in set (0.00 sec) +--- +--- + - - - Query OK, 0 rows affected (0.00 sec) forint = 0; numbers3.size( ***) mysql> | LF UTF-8 C++ GitHub - Git (0)

2. Number of Publishers in the city

 SELECT city AS 'City', count(publisher_code) AS 'Number of Publishers' FROM publisher GROUP BY city;