V475/I519 Final Applied Practice

Fall 2019 V475/I519 Final Applied Practice

100 points total

Honor System: Do your own work. Collaboration is not permitted. You may ask me any clarifying questions up until the due date/time regarding this exercise.

Due Date/Time: Thursday, Dec 18th, by 12pm (10% penalty for every HOUR late—I must send grades to Registrar!) Accommodations made for special circumstances.

Submission: You will submit only the answer template file.

MySQL and Database Development

In this section, you will import data into MySQL and write SQL scripts that display answers to the questions provided. Partial credit will be given for unsuccessful “in the ball park” attempts at answering the questions below.

Use the Final_Applied_Practice_Answer_Template file to copy and paste your code for further review. Do NOT submit this file.

Obtain the files from Canvas located with this Assignment:

MOVIE.csv

DIRECTOR.csv

City.sql

Countrylanguage.sql

Nations.sql

Section 1: The Movies and Directors DB

IMPORTANT--Do this first!!!:

->Edit the Directors.csv file in Excel and enter your name and cntry for the last record #999. <-

Be sure to save your file. If you use a non-US country, be sure it matches other data!

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

Using the Data Table Import Wizard in MySQL Workbench, import the data in the MOVIE.CSV and DIRECTOR.csv files. MAKE SURE you have entered your name as a director first at record #999!

1) Properly select the data type for each field during the import process. Pay attention to that! 2) Set the primary key of each table after import.

Screen capture evidence that you have properly imported the data from both tables and save that to the answer template. If you are not sure about this, ask.

Task 2: Add Movies (4 pts.)

Add two movies that have been directed by you. Here is the required info:

Movie 1—Title: The Education of Yourname; Yr: 2019; Category: Drama; MPAA: G; Len: 122

Movie 2—You make up a Title, Category, MPAA. Set the Length to 90 and a USGROSS of 388668. The other field data (ie., awards) are optional.

Screen capture your SQL code that you used to add the data for both movies and display the results of the SELECT query to show you were successful.

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

You may either use the MySQL GUI or proper SQL commands to setup the PK-FK relationship between the two tables to enforce referential integrity. Screen capture the results and paste to the answer template. Your screen capture should show that you have done this successfully.

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

What movies have you directed? Include your name in the results. There should be two columns—the director’s name (You!) and the title of the movies. The results will be more than 2. Be sure to use the PK for to identify you precisely in the query criteria.

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

Which movie directors have directed War movies? List the director names and titles of the movies in your results.

Task 6: SELECT statement that answers the following question (4 pts.)

Using the query you created in Task 5, which directors have directed 2 OR MORE war movies? List their names and the number of movies directed.

Task 7: SELECT statement that answers the following question (5 pts.)

Which directors, sorted from highest to lowest, have had worldwide gross ticket sales greater than $200,000,000 (ie., 200 MILLION $$)? Important!--Notice how gross sales is saved in the data. What is going on here? You may screen capture the first 10 or 15 to show you have it.

Task 8: SELECT statement that answers the following question (5 pts.)

Which movies have some form of the word “ Love ” in the title? Format the output so that the title is followed by the text “—directed by”. It should look like this:

Task 9: SELECT statement that answers the following question (5 pts.)

Which categories of movie on average have the longest running length? Sort highest to lowest.

Task 10: SELECT statement that answers the following question (7 pts.)

Steven Spielberg has directed quite a few movies in various categories. In one SELECT query statement, calculate both the total number of minutes and the average number of minutes that Mr. Spielberg has directed for each category. There should be three columns of output: the category, the total number of minutes and the average number of minutes for all the movies he has directed. Use the PK value for Mr. Spielberg in the criteria, just like you did for yourself!

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

Be sure to state your question on the answer template and show your results. Your score will be based on applying what you have learned. A simple query that reflects minimal effort will not receive credit.

Section 2: The World DB

You should have downloaded and saved the three .SQL files (City.sql, Countrylanguage.sql,

Nations.sql).

Note: Be careful not to confuse the Countries table that we used with the Human Resources database some time ago with these queries!

This is going to be a bit trickier. You have to know how your data relates between the various tables. Study your tables carefully to determine how the data from one table matches the data in the other tables. PK – FK fields in relationships are not all named the same. You might want to write these down or sketch out a schema.

Task 1: Non-African nations winning independence after 1970. (6 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. Sort alphabetically A-Z by name.

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

Calculate the total metropolitan population for all of the cities in each nation. Your output should have three columns—The code and the name from the Nation table and the total city population from the City table. Be sure to save your VIEW. You will use it in the next question.

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.

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? You will need to JOIN the view you created in Task 2 with the Nations table. Two columns of output—the name of the nation and the population difference should be seen. Aruba should be 73966.

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

Which cities in Europe have French as their official language? Your output should include three columns: continent, name and language. You probably should use the “building blocks” approach that I have mentioned in class when constructing this query. Be careful! You will need more than just one field to identify the official language(s) of a country. Not all languages are “official!”

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

8