Management Information Systems (33:136:370) 
Spring 2016, Professor Eckstein
Assignment 2

Due: Friday, Feburary 5

This assignment involves the history books database you built for assignment 1.  If you have lost this database, or are not sure you built it correctly, you can download a copy from the Sakai or through a link on the class website (it will be posted after assignment 1 is collected).

Q1.   Using the history books database from the last assignment, perform the following queries.  Make sure the fields are displayed in the same order they are requested.  For each query, paste both the query results and a screen shot of the query grid into a Word document, and hand in this document.  A sample of how each solution should look is available online.  Note that you can paste query results into word by clicking in the upper left corner of the result of the query to select the entire table of data, typing Ctrl-C to copy, then switching to word, and typing Ctrl-V to paste.  Note that "title" should be considered synonymous with "book name", and that the PublicationYear field is just an integer holding the year of publication, rather than using the Date/Time datatype.  

  1. Show the author, title, year, and pages of all books with 15 or fewer pages.
  2. Show the author and title and year of all "Ancient" books published in 1970 or after.  Do not display the category.
  3. Show the author, title, pages, and year for all "United States" books published in the 1920's.  Do not display the category.
  4. Show the title, pages, year, and category of all "Ancient" books published in the 1940's, along with all "England & Empire" books published in the 1930's.  Sort the results alphabetically by title.
  5. Show the title, author, pages, and category for all books with at least 800 pages.  Sort alphabetically by category; within each category, sort by number of pages, with the largest number of pages first.  Note that the category should be displayed after the number of pages.

Q2.  Produce a report showing all the fields except ID number.  It should be grouped by author; the author need not appear in the detail section, but only in the header for each group.  Within each author, books should be sorted chronologically by publication year (earliest books first).  At the end of the section for each author, show the number of books written by the author and the average number of pages for books by that author, rounded to the nearest whole number of pages (note: the Access function for averaging is called "Avg", and rounding to the nearest whole number pages involves adopting the "fixed" format and selecting zero digits after the decimal point).  Make sure no box or border is visible around the book count or the average number of pages.  Make sure the entirety of each column label is visible, and insert spaces into column names so they are readable, for example "Publication Year" instead of "PublicationYear".   Print and hand in all pages of the report containing authors starting with "A" (do not print or hand in any other pages -- depending on how the report is formatted, there may be over 100 pages!).