BIT231 MPBookstore

The MPBookstore database gathers and organizes information about branches, publishers, authors, and books. There are six tables in the database.

Publisher: This table stores the publisher of the book, and the publisher's city and state of origin. (publisher_code is the Primary Key)

Book: This table contains Information about books, including the title for the book, the publisher of the book, the publishing date, the price. (book_code is the Primary Key and publisher_code is the Foreign Key)

Author: This table accommodates the information about authors. The table only contains the author's first name and last name, but other information, such as the author's age and nationality can be added. (author_no is the Primary Key)

Branch: This table stores the information about the each branch of the bookstore and their details about what is the branch name and where it is located. (branch_no is the Primary Key)

Wrote: This table contains the association between books and authors as one book can be written by more than one author. It is used to relate books and authors. The Sequence field indicates the order in which the authors of a particular book are listed on the cover

Author_no in Author and Author_number in Wrote , both store author_no values (book_code,Author_number is the Composite Primary Key. book_code and Author_number are the Foriegn Keys)

Invent(inventory) : This table contains the association between books and branches. It is used to indicate the number of copies of a particular book that are currently on hand at a particular branch. Which book is available at which branch and how many copies of the book are at each branch of the bookstore.

Here is the ER Diagram of Bookstore database: see image.

Question 1

Consider the ER Diagram given above; build the application using SAS PROC SQL

PART A

Import all data from import document (Download it from Moodle) into SAS Studio to run the queries in Question 2.

PART B

Write SQL statements to for following

a) Use an update query to change the price of any book in the Fiction (FIC) with a current price of 3.95 to 5.50.
b) Use a delete query to delete all books in the SFI type table that have the publisher code BB.

Question 2

Manipulate the data with the database you have created in Question 1 and perform the following queries:

PART A

1. Retrieve all the data in the Author table
2. Retrieve the book price for the Book "Stranger"
3. List all the books in alphabetical order (ASC) and price greater than $5.50
4. List the name of every publisher not located in New York.
5. List all the different book types. Avoid duplicates
6. List the book code and title of every book that has the type FIC, MYS or ART.

PART B

1. List all the branches with No of employees in range of 10 and 15
2. Find all publishers with "'t" in their name
3. Find all books with "Databases" in their title
4. List the authors who published "Higher Creativity"
5. List the books of the publishers who located at "NY"
6. List all books with their authors (full name)
7. What is the max and min "units on hand" out of all books
8. List the publications of author "Christie Agatha"
9. List the book code and title of every book that has the type "FIC" or that has the publisher code "BB".
10. Calculate the average price for each type of book.
11. For every book published by "Addison Wesley", list the book title and book price.
12. Members get a 10% discount off regular book prices. To determine the discounted prices, calculate
the dis-counted price of every book. (It can be calculate 90% of the current price, or calculate the
difference of the current price and 10% of current price.)

PART C

1. How many books do each author wrote?
2. List the book titles of all books that are held at the "Henrys Downtown" branch. Use nested queries only ie. no table joins
3. What is the title of the book with the second lowest price? List the book title and price
4. Which Book has the second highest availability (units on hands) in the table?
5. Which Book is not available at "Henrys Brentwood"
6. List the publishers (and the number of books they published) who have published 10 or more books.
7. Identify the authors who are wrote more than 3 books and which locations their books are available?
8. For all book types contains more than 5 published books, display the earliest publish dates for each book type.