MIS Management Information System
Please answer by filling this table, Thanks!
| | Query | Question Answer |
| 1 | | |
| 2 | | |
| 3 | | |
| 4 | | |
| 5 | | |
| 6 | | |
| 7 | | |
H film film_id title description release_year language_id original_language film_actor filmactor_id actor id film_id last_update actor Hactor_id first_name last_name last_update id Inventory inventory id film_id store_id last_update rental_duration rental_rate length replacement_cost rating spcial_features last_update language language_id name last_update rental rental id rental_date inventory_id customer_id return_date staff_id last_update customer customer_id store_id first_name last_name email address_id active create_date last_update address address_id address district postal_code phone last_update In MySQL Workbench, open the connection to the dataanalytics.temple.edu server using your username and password. Click on the "moviedb" schema and then the tables tab to see the list of tables. Recall the connection configuration as follows: Connection Name: Hostname: Username: mis 2502 dataanalytics.temple.edu Your username is available on the MIS Community Gradebook USCIllalle. IUUI UScllalle 15 avalla UIC UL LIIVIIJ CULTTUTTILY UDUCUUUN Questions 1. How many times has each inventory been rented? Return the value for all inventories, even those inventory items that have never been rented at all. Sort your output by inventory id and limit your answer to the first 5 items. (Hint: You need a LEFT JOIN to answer this question correctly!) Display: inventory id and number of rentals 2. What is the street address of Patricia Johnson? Display: address Page 2 of 4 3. (You must use an inner join for this question) Who has starred in movies in the French language? Return only the first five distinct results in alphabetical order by last name, (Hints: (1) Create the query to only return the first five, in alphabetical order by last name; (2) Be sure to reference the language name "French" in your query. In the language table, there is a field "name" that contains the film language names.) Display: first name and last name 3. (You must use an inner join for this question) who has starred in movies in the French language? Return only the first five distinct results in alphabetical order by last name. (Hints: (1) Create the query to only return the first five, in alphabetical order by last name; (2) Be sure to reference the language name "French" in your query. In the language table, there is a field "name" that contains the film language names.) Display: first name and last name 4. Who has rented the fewest movies? How many movies did they rent? (Hint: Get a list of all customers and the number of movies they rented, arranged in ascending order and returning only the first row. Assume no ties - there's only one customer with the lowest value.) Display: first name, last name and number of movies 5. Which are the languages of the movies that Mabel Holland has rented and how many has she rented in each of those languages? Display: language and number of languages 6. What is (are) the shortest R-rated movie(s) in English? And how long is it are they)? (Hint #1. R-rated movies have rating value equal to 'R'; Hint #2: There might be more than one movie returned. Use a subselect with the MIN() function to return only the shortest R-rated movie(s) ) Display: movie title and movie length 7. What was the shortest movie starring Daryl Crawford, how long is it, and what was its rating? (Hint: There might be more than one movie returned. Use a subselect with the MIN() function to return only the movie with the shortest length starring Daryl Crawford.) Display: movie title, length and rating H film film_id title description release_year language_id original_language film_actor filmactor_id actor id film_id last_update actor Hactor_id first_name last_name last_update id Inventory inventory id film_id store_id last_update rental_duration rental_rate length replacement_cost rating spcial_features last_update language language_id name last_update rental rental id rental_date inventory_id customer_id return_date staff_id last_update customer customer_id store_id first_name last_name email address_id active create_date last_update address address_id address district postal_code phone last_update In MySQL Workbench, open the connection to the dataanalytics.temple.edu server using your username and password. Click on the "moviedb" schema and then the tables tab to see the list of tables. Recall the connection configuration as follows: Connection Name: Hostname: Username: mis 2502 dataanalytics.temple.edu Your username is available on the MIS Community Gradebook USCIllalle. IUUI UScllalle 15 avalla UIC UL LIIVIIJ CULTTUTTILY UDUCUUUN Questions 1. How many times has each inventory been rented? Return the value for all inventories, even those inventory items that have never been rented at all. Sort your output by inventory id and limit your answer to the first 5 items. (Hint: You need a LEFT JOIN to answer this question correctly!) Display: inventory id and number of rentals 2. What is the street address of Patricia Johnson? Display: address Page 2 of 4 3. (You must use an inner join for this question) Who has starred in movies in the French language? Return only the first five distinct results in alphabetical order by last name, (Hints: (1) Create the query to only return the first five, in alphabetical order by last name; (2) Be sure to reference the language name "French" in your query. In the language table, there is a field "name" that contains the film language names.) Display: first name and last name 3. (You must use an inner join for this question) who has starred in movies in the French language? Return only the first five distinct results in alphabetical order by last name. (Hints: (1) Create the query to only return the first five, in alphabetical order by last name; (2) Be sure to reference the language name "French" in your query. In the language table, there is a field "name" that contains the film language names.) Display: first name and last name 4. Who has rented the fewest movies? How many movies did they rent? (Hint: Get a list of all customers and the number of movies they rented, arranged in ascending order and returning only the first row. Assume no ties - there's only one customer with the lowest value.) Display: first name, last name and number of movies 5. Which are the languages of the movies that Mabel Holland has rented and how many has she rented in each of those languages? Display: language and number of languages 6. What is (are) the shortest R-rated movie(s) in English? And how long is it are they)? (Hint #1. R-rated movies have rating value equal to 'R'; Hint #2: There might be more than one movie returned. Use a subselect with the MIN() function to return only the shortest R-rated movie(s) ) Display: movie title and movie length 7. What was the shortest movie starring Daryl Crawford, how long is it, and what was its rating? (Hint: There might be more than one movie returned. Use a subselect with the MIN() function to return only the movie with the shortest length starring Daryl Crawford.) Display: movie title, length and rating
