Henry solutions page 22, problems 6-13 use the standard search #6 to find the question and sql query solution
MariaDB [Fruit]> show tables;
+-----------------+
| Tables_in_Fruit |
+-----------------+
| CUSTOMER |
| H_AUTHOR |
| H_BOOK |
| H_BRANCH |
| H_INVENTORY |
| H_PUBLISHER |
| H_WROTE |
| ORDERS |
| ORDER_LINE |
| PART |
| REP |
+-----------------+
11 rows in set (0.01 sec)
MariaDB [Fruit]> #6. List the book code and title of each book that has the type MYS and a price of less than $20.00.
MariaDB [Fruit]> describe H_BOOK;
+----------------+--------------+------+-----+---------+-------+
| Field | Type | Null | Key | Default | Extra |
+----------------+--------------+------+-----+---------+-------+
| BOOK_CODE | char(4) | NO | PRI | NULL | |
| TITLE | char(40) | YES | | NULL | |
| PUBLISHER_CODE | char(3) | YES | MUL | NULL | |
| TYPE | char(3) | YES | | NULL | |
| PRICE | decimal(4,2) | YES | | NULL | |
| PAPERBACK | char(1) | YES | | NULL | |
+----------------+--------------+------+-----+---------+-------+
6 rows in set (0.00 sec)
MariaDB [Fruit]> select BOOK_CODE, TITLE from H_BOOK where TYPE = "MYS" and PRICE < 20;
+-----------+-----------+
| BOOK_CODE | TITLE |
+-----------+-----------+
| 0808 | The Edge |
| 9882 | Slay Ride |
+-----------+-----------+
2 rows in set (0.00 sec)
MariaDB [Fruit]> #7. Customers who are part of a special program get a 10 percent discount off regular book prices. For the first five books in the BOOK table, list the book code, title, and discounted price. (Use the PRICE column to calculate the discounted price.)
MariaDB [Fruit]> select BOOK_CODE, TITLE, PRICE*.9 as "Discounted Price" from H_BOOK limit 5;
+-----------+-----------------------+------------------+
| BOOK_CODE | TITLE | Discounted Price |
+-----------+-----------------------+------------------+
| 0180 | A Deepness in the Sky | 6.471 |
| 0189 | Magic Terror | 7.191 |
| 0200 | The Stranger | 7.200 |
| 0378 | Venice | 22.050 |
| 079X | Second Wind | 22.455 |
+-----------+-----------------------+------------------+
5 rows in set (0.00 sec)
MariaDB [Fruit]> #8. Find the name of each publisher containing the word and. Can use REGEXP or LIKE
MariaDB [Fruit]> describe H_PUBLISHER;
+----------------+----------+------+-----+---------+-------+
| Field | Type | Null | Key | Default | Extra |
+----------------+----------+------+-----+---------+-------+
| PUBLISHER_CODE | char(3) | NO | PRI | NULL | |
| PUBLISHER_NAME | char(25) | YES | | NULL | |
| CITY | char(20) | YES | | NULL | |
+----------------+----------+------+-----+---------+-------+
3 rows in set (0.00 sec)
/* MySQL allows standard regexp, but you need to know regexp, covered in cimw160 JavaScript */
MariaDB [Fruit]> select PUBLISHER_NAME from H_PUBLISHER where PUBLISHER_NAME regexp ".*and.*";
+--------------------------+
| PUBLISHER_NAME |
+--------------------------+
| Farrar Straus and Giroux |
| McPherson and Co. |
| Random House |
| Simon and Schuster |
| Thames and Hudson |
+--------------------------+
5 rows in set (0.00 sec)
MariaDB [Fruit]> select PUBLISHER_NAME from H_PUBLISHER where PUBLISHER_NAME like "%and%";
+--------------------------+
| PUBLISHER_NAME |
+--------------------------+
| Farrar Straus and Giroux |
| McPherson and Co. |
| Random House |
| Simon and Schuster |
| Thames and Hudson |
+--------------------------+
5 rows in set (0.00 sec)
MariaDB [Fruit]> #9. List the book code and title of each book that has the type FIC, MYS, or ART
MariaDB [Fruit]>
/* in is easier but not a standard programming language operator */
MariaDB [Fruit]> select BOOK_CODE, TITLE from H_BOOK where type in ("FIC", "MYS", "ART");
+-----------+------------------------+
| BOOK_CODE | TITLE |
+-----------+------------------------+
| 0200 | The Stranger |
| 0378 | Venice |
| 079X | Second Wind |
| 0808 | The Edge |
| 1382 | Treasure Chests |
| 138X | Beloved |
| 2281 | Van Gogh and Gauguin |
| 2766 | Of Mice and Men |
| 3743 | Nine Stories |
| 5790 | Catch-22 |
| 6128 | Jazz |
| 6908 | Franny and Zooey |
| 7405 | East of Eden |
| 7559 | The Fall |
| 9627 | Song of Solomon |
| 9701 | The Grapes of Wrath |
| 9882 | Slay Ride |
| 9883 | The Catcher in the Rye |
| 9931 | To Kill a Mockingbird |
+-----------+------------------------+
19 rows in set (0.00 sec)
MariaDB [Fruit]> select BOOK_CODE, TITLE from H_BOOK where type = "FIC" or type = "MYS" or type = "ART";
+-----------+------------------------+
| BOOK_CODE | TITLE |
+-----------+------------------------+
| 0200 | The Stranger |
| 0378 | Venice |
| 079X | Second Wind |
| 0808 | The Edge |
| 1382 | Treasure Chests |
| 138X | Beloved |
| 2281 | Van Gogh and Gauguin |
| 2766 | Of Mice and Men |
| 3743 | Nine Stories |
| 5790 | Catch-22 |
| 6128 | Jazz |
| 6908 | Franny and Zooey |
| 7405 | East of Eden |
| 7559 | The Fall |
| 9627 | Song of Solomon |
| 9701 | The Grapes of Wrath |
| 9882 | Slay Ride |
| 9883 | The Catcher in the Rye |
| 9931 | To Kill a Mockingbird |
+-----------+------------------------+
19 rows in set (0.00 sec)
MariaDB [Fruit]> #10. How many books have the type SFI?
MariaDB [Fruit]> select count( * ) from H_BOOK where type = "SFI";
+------------+
| count( * ) |
+------------+
| 3 |
+------------+
1 row in set (0.00 sec)
MariaDB [Fruit]> #11. Calculate the average price for books that have the type ART.
MariaDB [Fruit]> select avg( PRICE ) from H_BOOK where type = "ART";
+--------------+
| avg( PRICE ) |
+--------------+
| 23.320000 |
+--------------+
1 row in set (0.00 sec)
MariaDB [Fruit]> # 12. For each book published by Penguin USA, list the book code and title.
MariaDB [Fruit]> describe H_PUBLISHER
-> ;
+----------------+----------+------+-----+---------+-------+
| Field | Type | Null | Key | Default | Extra |
+----------------+----------+------+-----+---------+-------+
| PUBLISHER_CODE | char(3) | NO | PRI | NULL | |
| PUBLISHER_NAME | char(25) | YES | | NULL | |
| CITY | char(20) | YES | | NULL | |
+----------------+----------+------+-----+---------+-------+
3 rows in set (0.00 sec)
MariaDB [Fruit]> describe H_BOOK;
+----------------+--------------+------+-----+---------+-------+
| Field | Type | Null | Key | Default | Extra |
+----------------+--------------+------+-----+---------+-------+
| BOOK_CODE | char(4) | NO | PRI | NULL | |
| TITLE | char(40) | YES | | NULL | |
| PUBLISHER_CODE | char(3) | YES | MUL | NULL | |
| TYPE | char(3) | YES | | NULL | |
| PRICE | decimal(4,2) | YES | | NULL | |
| PAPERBACK | char(1) | YES | | NULL | |
+----------------+--------------+------+-----+---------+-------+
6 rows in set (0.00 sec)
MariaDB [Fruit]> select H_BOOK.BOOK_CODE, TITLE from H_BOOK, H_PUBLISHER
where H_BOOK.PUBLISHER_CODE = H_PUBLISHER.PUBLISHER_CODE and PUBLISHER_NAME = "Penguin USA";
+-----------+----------------------+
| BOOK_CODE | TITLE |
+-----------+----------------------+
| 2766 | Of Mice and Men |
| 5163 | Travels with Charley |
| 7405 | East of Eden |
| 9701 | The Grapes of Wrath |
+-----------+----------------------+
4 rows in set (0.00 sec)
MariaDB [Fruit]> select H_BOOK.BOOK_CODE, TITLE from H_BOOK left join H_PUBLISHER on H_BOOK.PUBLISHER_CODE = H_PUBLISHER.PUBLISHER_CODE where PUBLISHER_NAME = "Penguin USA";
+-----------+----------------------+
| BOOK_CODE | TITLE |
+-----------+----------------------+
| 2766 | Of Mice and Men |
| 5163 | Travels with Charley |
| 7405 | East of Eden |
| 9701 | The Grapes of Wrath |
+-----------+----------------------+
4 rows in set (0.00 sec)
MariaDB [Fruit]> #13. List the book code, book title, and units on hand for each book in branch number 3
MariaDB [Fruit]> describe H_INVENTORY;
+------------+--------------+------+-----+---------+-------+
| Field | Type | Null | Key | Default | Extra |
+------------+--------------+------+-----+---------+-------+
| BOOK_CODE | char(4) | NO | PRI | NULL | |
| BRANCH_NUM | decimal(2,0) | NO | PRI | NULL | |
| ON_HAND | decimal(2,0) | YES | | NULL | |
+------------+--------------+------+-----+---------+-------+
3 rows in set (0.00 sec)
MariaDB [Fruit]> select H_BOOK.BOOK_CODE, TITLE, ON_HAND from H_BOOK left join H_INVENTORY on H_BOOK.BOOK_CODE = H_INVENTORY.BOOK_CODE where BRANCH_NUM = 3;
+-----------+------------------------------------------+---------+
| BOOK_CODE | TITLE | ON_HAND |
+-----------+------------------------------------------+---------+
| 0378 | Venice | 2 |
| 079X | Second Wind | 2 |
| 1351 | Dreamcatcher: A Novel | 2 |
| 2226 | Harry Potter and the Prisoner of Azkaban | 2 |
| 2766 | Of Mice and Men | 2 |
| 3906 | The Soul of a New Machine | 2 |
| 6128 | Jazz | 3 |
| 7405 | East of Eden | 2 |
| 8092 | Godel, Escher, Bach | 1 |
| 9627 | Song of Solomon | 5 |
| 9701 | The Grapes of Wrath | 3 |
| 9882 | Slay Ride | 3 |
+-----------+------------------------------------------+---------+
12 rows in set (0.00 sec)
MariaDB [Fruit]> notee