Henry Books Database assignment_3 by pattrick987

VIEWS: 4,921 PAGES: 4

Henry Books Database assignment_3 My Sql CITD 120 1. (2 points) For each book, list he book code, book title, publisher code, and publisher name. Sort the results by publisher name. Within publisher name order the books by title. 2. (2 points) Find the book title for each book written by author number 18. Use the IN operator. 3. (2 points) Find the book title for each book written by author number 18. Use the EXISTS operator. 4. (2 points) Find the book code and book title for each book located in branch number 2 that is written by John Steinbeck. Do not use IN or EXISTS.C:\LCC\2010Fall\CITD120\Assignments\Fall-6\CITD120Fa-Assign4.doc 5. (2 points) List the book codes and title for each pair of books that have the same price. The first book code listed should be the major sort key. The second book code listed should be the minor sort key. Choose column names that make it clear which title is associated with which book code. 6. (2 points) List the book code and book title for each book whose price is more than $10 or that was published in Boston. Use a UNION. Sort by title. 7. (1 points) List the book code and book title for each book whose price is more than $10 or that was published in Boston. Do not use a UNION. Sort by title. 8. (2 points) Find the book code and book title for each book whose price is greater than the price of at least one book that has the type HOR. Use ANY or ALL. Note: You must choose ANY or ALL based upon the requirements of the query. Only one choice is correct. 9. (1 points) Find the book code and book title for each book whose price is greater than the price of at least one book that has the type HOR. Do not use ANY or ALL. 10. (4 points) List the book code, book title, and branch name for each book where there are fewer on hand than the book 'Beloved'. Use ANY or ALL. Note: You must choose ANY or ALL based upon the requirements of the query. Only one choice is correct.

More Info
									Assignment 3

Task 1
mysql> SELECT DISTINCT Type
    -> FROM Book;
+------+
| Type |
+------+
| SFI |
| HOR |
| FIC |
| ART |
| MYS |
| POE |
| PSY |
| SCI |
| TRA |
| HIS |
| CMP |
| PHI |
+------+
12 rows in set (0.00 sec)

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

Task 2

mysql> SELECT COUNT(Type)
    -> FROM Book
    -> WHERE Type = 'SFI';
+-------------+
| COUNT(Type) |
+-------------+
|           3 |
+-------------+
1 row in set (0.00 sec)

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

Task 3

mysql> SELECT AVG(Num_Employees)
    -> FROM Branch;
+--------------------+
| AVG(Num_Employees) |
+--------------------+
|            10.0000 |
+--------------------+
1 row in set (0.36 sec)

***********************************************
Task 4

mysql> SELECT Count(*)
    -> FROM Inventory
    -> WHERE On_Hand < 5;
+------------+---------+
| Branch_Num | On_Hand |
+------------+---------+
|          1 |        2 |
|          2 |        2 |
|          1 |        1 |
|          2 |        3 |
|          3 |        2 |
|          2 |        1 |
|          4 |        3 |
|          2 |        4 |
|          1 |        3 |
|          4 |        1 |
|          4 |        2 |
|          3 |        3 |
|          3 |        1 |
+------------+---------+
13 rows in set (0.00 sec)

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

Task 5

mysql> SELECT Count(Author_Last)
    -> FROM Author
    -> WHERE Author_Last
    -> LIKE 'S%';
+-------------+
| Author_Last |
+-------------+
| Solotaroff |
| Straub      |
| Salinger    |
| Steinbeck   |
| Schleining |
+-------------+
5 rows in set (0.00 sec)

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

Task 6

mysql> SELECT BRANCH_NUM
    -> FROM INVENTORY
    -> WHERE BOOK_CODE IN
             (SELECT Book_Code
               FROM Book
               WHERE Type = 'POE');
+------------+
| BRANCH_NUM |
+------------+
|          1 |
|          4 |
+------------+
2 rows in set, 5 warnings (0.00 sec)

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

Task 7

mysql> SELECT TITLE
    -> FROM BOOK
    -> WHERE PRICE > (SELECT AVG(PRICE)
    -> FROM BOOK);
+------------------------------------------+
| TITLE                                    |
+------------------------------------------+
| Venice                                   |
| Second Wind                              |
| Dreamcatcher: A Novel                    |
| Treasure Chests                          |
| Harry Potter and the Prisoner of Azkaban |
| Van Gogh and Gauguin                     |
| Electric Light                           |
| A Guide to SQL                           |
| Harry Potter and the Goblet of Fire      |
| Godel, Escher, Bach                      |
| Black House                              |
| Song of Solomon                          |
| To Kill a Mockingbird                    |
+------------------------------------------+
13 rows in set (0.00 sec)

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

Task 8

mysql> SELECT TITLE
    -> FROM BOOK
    -> WHERE PUBLISHER_CODE IS NULL;
+--------------------+
| TITLE              |
+--------------------+
| Mr. Roberts        |
| The Silver Chalice |
+--------------------+
2 rows in set (0.00 sec)

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

Task 9

mysql> SELECT TYPE, AVG(PRICE)
    -> FROM BOOK
    -> WHERE PAPERBACK = 'Y'
    -> GROUP BY TYPE;
+------+------------+
| TYPE | AVG(PRICE) |
+------+------------+
| CMP | 37.950000 |
| FIC |    9.897500 |
| HIS |    9.600000 |
| HOR |    7.990000 |
| MYS |    6.990000 |
| PHI | 14.000000 |
| PSY |    8.345000 |
| SCI | 11.160000 |
| SFI |    7.190000 |
| TRA |    7.950000 |
+------+------------+
10 rows in set (0.00 sec)

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

Task 10

mysql> SELECT BRANCH_NUM, SUM(ON_HAND)
    -> FROM INVENTORY
       GROUP BY Branch_Num
       HAVING SUM(On_Hand) < 25;

+------------+---------+
| BRANCH_NUM | ON_HAND |
+------------+---------+
|          1 |       2 |
|          2 |       2 |
|          1 |       1 |
|          2 |       3 |
|          3 |       2 |
|          2 |       1 |
|          4 |       3 |
|          2 |       4 |
|          1 |       3 |
|          4 |       1 |
|          4 |       2 |
|          3 |       3 |
|          3 |       1 |
|          3 |       5 |
+------------+---------+
14 rows in set (0.00 sec)

mysql> NOTEE

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

								
To top