Homework answers / question archive / 1) Create dataset countries

1) Create dataset countries

MS Access

Share With

---

1) Create dataset countries.accdb that contains a single table COUNTRY. Export the

“countrydata.txt.” Then, set the fields in this table as follows:

• ID number (number/integer; primary key)
• Country name (text/50 characters)
• Total area in square kilometers (Number/Double)
• Land area in square kilometers (Number/Double)
• Population (Number/Double)
• GDP (Number/Double)
• Oil Consumption (Number/Double)

Note that total area and land area are different because land area does not include lakes, rivers, and coastal waters claimed as part of the country’s territory. GDP is measured in US dollars per year and oil consumption in barrels per day. 

 

1. Create a form using all available data, with appropriate layout. In the form, add population density in people/square kilometers (Number/double/decimal places: 2), where the density = population / total land. 

 

2. Create a report using country name, population, GDP, and oil consumption, which sorts the data by ascending order of the country name.

 

3. Create queries displaying following different criteria:

• show the name and total area of all countries having at least 2 million square km of total area 
• For each country that has at least 1 million square km land area and at least 20 million people, show the country name, land area, population, and population density, which is the population divided by the land area. Display the population density column in the “Fixed” format with two decimal places. Sort the results from the least densely populated to the most densely populated.
• Show the name, land area, total area, and population of each country with a population of at least 1 million, along with the fraction of the total area that is made up of land. Display this last calculated field in the “Percent” format with two digits after the decimal point. Sort from the smallest to largest fraction of land, and only show countries that are less than 95% land.
• A country’s “oil per capita” is its amount of oil consumed per year, divided by its population (to convert barrels per day to barrels per year, assume that a year is exactly 365 days). For each country with at least 40 million people or at least 2 million barrels per day consumption, show the country name, oil consumption, population, and oil per capita. Sort the results so that the highest oil per capita appears at the top of the output and display this field in “Fixed” format with two digits after the decimal place.

 

             

2. Create dataset "counties_population.accdb" that contains a single table COUNTRY. Export the “counties-a-through-d.txt.” Then, set the fields in this table as follows:

• An ID number (number; primary key) 
• County name (30 characters text) 
• State code (2 characters text) 
• Population (Number/Long Integer) 
• Percent of population under 5 (Number/Double) 
• Percent of population under 18 (Number/Double) 
• Percent of population over 65 (Number/Double) 
• Percent of population female (Number/Double) 
• Percent of population with high?school education (Number/Double) 
• Percent of population with college education (Number/Double) 
• Per capita income (Currency $) 
• Land area in square miles (Number/Double)

 

1. Create a form using all available data, with appropriate layout. In the form, add population with college education (Number/integer) and population without high-school education (Number/integer). 

 

2. Create a report using county name, state code, population, per capita income, and land area, which is grouped by the state code and sorted by ascending order of the county name.

 

3. Create queries displaying following different criteria:

• Show the name, state, population, and land area for all counties with at least 1 million people.
• Show the name, population, and per capita income of all counties in Delaware (“DE”). Do not display the state.
• Show the name, population, and per capita income for all Arizona (“AZ”) counties whose per capita income is at least $20,000. Do not display the state.
• Show the name, state, percentage of the population under five, and percentage of females for all counties that have more than 10% of the population under five, or less than 40% of the population female. Note that you should enter 10% as “0.1” and 40% as “0.4.”
• For all counties that have a land area of at least 5000 square miles and are not in Alaska (“AK”), show the county name, state, population, land area, and percentage of the population with a college degree. Sort the results by population, with the largest populations coming first.

 

 

             

3. Create dataset "employees.accdb" that contains a single table COUNTRY. Export the “employee-data.txt.” Then, set the fields in this table as follows:

• A five?digit employee ID (use five characters of text, input mask “00000”; primary key) 
• Last name (50 characters text)
• First name (30 characters text)
• Gender (1 character of text)
• Title (25 characters text)
• Annual salary (Currency datatype $)
• Location (30 characters text)
• Performance rating (20 characters text)

 

(a) Create a form using all available data, with appropriate layout. In the form, add monthly salary (Number/double/decimal places:1). 

 

(b-1) Create a report using all available data, which is grouped by the title, and then performance rating, and sorted by ascending order of the annual salary.

 

(b-2) Create a report using all available data, which is grouped by the location, and then performance rating, and sorted by ascending order of the last name and then first name.

 

(c) Create queries displaying following different criteria:

• First name, last name, title, and salary of all employees making at least $50,000.
• First name, last name, gender, and salary for all Boston employees who make less than $30,000. The location field should not appear in the output, and the output rows should be sorted alphabetically by last name.
• First name, last name, title, and salary for all senior account reps who make at least $60,000 and all account reps who make at least $45,000. The rows should be sorted from highest salary to lowest. Employees with the same salary should be sorted alphabetically by last name (but the columns should appear in the order specified). For both account reps and senior account reps, only include employees with “Excellent” performance ratings.