You will work with the following database schema for a small bookstore. Note that in many situations you will need to write queries based on a schema. Also note that in this database, the bookstore doesn't keep track of inventory A book will be identified by a unique book number (INT). The name of the book is a VARCHAR type, its publication year is stored as an integer in format, and the number of pages in the book is stored as an INT. A customer will be assigned a unique customer number (INT). The customer's name and address are both a VARCHAR type. Phone is stored as a ten-digit integer. Sale information includes the book sold, the customer who bought the book, the date of the sale (stored an integer in MMDD format), and the price of the book (REAL). Finally, the bookstore wants to keep track of the publishers of the books it stocks. The publisher information includes a unique publisher name (VARCHAR), and the city (VARCHAR) and phone (INT, as above) of the publisher's main office. Additional information is that the store normally would stock many books by a given publisher but would also like to track publishers even if the store does not currently stock any of its books. To be considered a customer, a person must have purchased at least one book from the bookstore. It is possible that a particular book (e. g. a new book) has not yet been sold to any customer. BOOK BOOKNUM BOOKNAME PUBYEAR PAGES PUBNAME CUSTOMER CUSTNUM CUSTNAME ADDRESS PHONE SALES BOOKNUM CUSTNUM DATE PRICE DUBLISHISE PUBNAME ADDRESS PHONE Write SQL queries to retrieve the information requested. These can be captured in a Word document or .pdf file. You will not be able to directly test these queries on a database. If you want, you can create a sample database on your own to test your queries, but this is not necessary. 1) Display the names of the books which have been purchased by customer number 26751.
2) Display the names of the books which have been purchased by customer "Fred Flintstone".
3) Display the book number of the books sold by the bookstore which were published by Lions Publishing. List each book number only once.
4) Display the number of titles carried in the bookstore. Also display the average number of pages in the books in the bookstore as well as the fewest number of pages in any of the books as well as the most number of pages in any of the books.
5) Display the year the newest book in the store was published.
6) List the number (count) of customers who purchased each book.
7) List both the total and average price of books sold for each publisher.
8) List both the total and average price of books sold for each publisher, but only for those publishers for which more than 5000 books have been sold.
9) List the name, address, and phone for each publisher who published a book in 2001 which was sold by the bookstore. The information for each publisher should be listed only once.