Q1.  Use the countries.mdb database you created for homework 1 to answer the following questions.  For each question, create an Access query and print out and hand in its results.  Make sure that the fields appear in the same order requested. 

1. Show the name and total area of all countries having at least 1 million square km total area.  List the countries from largest to smallest total area.
2. For each country that has at least 1 million square km land area and at least 10 million people, show the country name, land area, population, and population density, which is the number of people divided by the land area.  Sort the results from the least densely populated to the most densely populated.
3. A country's "oil per capita" is its amount of oil consumed per person per day, which you may calculate (in barrels per person per day) by dividing OilConsumption by Population.  For each country with at least 5 million people and at least .02 barrels per person per day consumption, show the country name, oil consumption, population, land area, and oil per capita.  Sort the results so that the highest oil per capita appears at the top of the output.
4. "GDP per barrel" is a country's GDP divided by its oil consumption per year, that is GDP/(OilConsumption ´ 365); the units work out to $/barrel.  For each country with a GDP of at least 100 billion dollars per year or at least 10 million people, show its name, GDP, population, oil consumption, and GDP per barrel.  Sort the results from the most GDP per barrel to least.
5. Modify the query from part (d) as follows: do not show the population column, and only show countries with at least $1500 GDP per barrel.  The result should look identical to the top part of the answer to (d), but with the population column missing.
    

Q2.  Download the file employees1.mdb from the class website.  Make a query that shows the first name, last name, gender, title, and salary for all Boston employees whose performance is either "excellent" or "good".  Use this query to make a report that shows these fields in the same order, in groups by title.  Neither location for performance rating should appear in the report.  Within each title, employees should be in alphabetical order by last name (but first name should be listed before last name on the report).  At the end of each group, there should be a footer showing the average salary for that job title.  Make sure the heading at the top of  the report reads "High-Rated Boston Employees".  Format the report so it fits on a single sheet of paper.  Print and hand in the report. 
 

Q3.    Compute the amount of storage needed for the following.  Show your work.

1. The text of a 5-page memo with and average of 80 characters per line and 50 lines per page.  Express the result in binary-style KB.
2. The COUNTRY table from Q1, which contains 160 countries.  Express the result in binary-style KB.
3. A 4 ´ 6 inch photo scanned at 1200 dots per inch (dpi) with 24-bit color.  Express you answer in binary-style MB.  Suppose that your compression algorithm attains a compression factor of 300.  How many binary-style KB would the picture consume after compression?
4. One minute of "720p24" high definition video: 720 ´ 1280 pixels, 24 bits per pixel, and 24 frames per second (disregard any accompanying audio).  Express your answer in binary-style GB.
5. A single-layer "Blu-Ray" disc holds 25 binary GB of data.  Without compression, how many minutes of 720p24 video (without sound) could you put on such a disk?  (Aside: this should give you some idea how much high definition video has to be compressed.)
6. An audio file of 90 minutes of music, with 2 channels, 44,100 samples per second, and 24 bits per sample.  Express your result in binary-style MB.  If MP3 compresses this file to 15 binary style MB, what is the compression factor?