access f

Final Applied Practice – Spring 2019

Answer Template for Part 2 (110 pts.)

Your Name:_______________________

Submit this file on Canvas with your Access database at the same time!

Note: Screen captures that are not viewable/legible or too small to read will receive no credit. Delete all place holders from this document as you paste your results ( green font color ).

Sample Screen Captures {please delete these images from this template!}:

{Please delete these images! Thanks!}

Section 1: The Movies and Directors DB

Task 1: Data Import and Table Setup (10 pts.)

[paste screen capture of your code and results here; delete this place holder]

Task 2: Add Your Movies (5 pts.)

[paste screen capture of your code and results here; delete this place holder]

Task 3: Enforce Referential Integrity Between the Tables (5 pts.)

[paste screen capture of your code and results here; delete this place holder]

Task 4: Write a SELECT statement that answers the following question (movies you directed) (4 pts.)

[paste screen capture of your code and results here; delete this place holder]

Task 5: SELECT statement that answers the following question (drama movies) (4 pts.)

[paste screen capture of your code and results here; delete this place holder]

Task 6: SELECT statement that answers the following question (Dirs with more than 2 drama movies) (5 pts.)

[paste screen capture of your code and results here; delete this place holder]

Task 7: SELECT statement that answers the following question (WWgross tix sales gt $200M) (5 pts.)

[paste screen capture of your code and results here; delete this place holder]

Task 8: SELECT statement that answers the following question (concatenated output with “Love”) (7 pts.)

[paste screen capture of your code and results here; delete this place holder]

Task 9: SELECT statement that answers the Spielberg movies question about durations. (8 pts.)

[paste screen capture of your code and results here; delete this place holder]

Task 10: What can you do? Create your own select statement. Write your question on the answer template (7 pts.).

[paste screen capture of your code and results here; delete this place holder]

Section 2: The World DB

Task 1: Non-African nations winning independence after 1970. (10 pts.)

Which non-African nations have won their independence after the year of 1970. Include the name of the country and the year of independence in your results.

[paste screen capture of your code and results here; delete this place holder]

Task 2: Create a VIEW called totalmetropop (12 pts.)

Calculate the total metropolitan population for all of the cities in each nation.

Display the first 8 or 10 rows of results to show you successfully created the view after selecting all the rows in the table. Hint: use an alias for your total population. It will make the next task easier.

[paste screen capture of your code and results here; delete this place holder]

Task 3: Use the VIEW to calculate the difference in non-metropolitan populations for each country. (12 pts.)

What is the difference between the city populations and those living outside of the city? Eg., Aruba should be 73966.

[paste screen capture of your code and results here; delete this place holder]

Task 4: List the Official Language (12 pts.)

Which cities in Europe have French as their official language?

[paste screen capture of your code and results here; delete this place holder]

Task 5: What can you do? Create your own select statement. Write your question in the answer template. (8 pts.)

State your question here: { ___ }

[paste screen capture of your code and results here; delete this place holder]

4