Advanced SQL Exercises

The Exercises for this Assignment will explore, using the MySQL Database Management system, some Advanced SQL concepts, which may be used in the Implementation Phase of the Systems Development Lifecycle.

For this Assignment, complete the following Exercises $($Include SQL and Output$)$:

$1.$ Using the MySQL SAKILA example Database, write a SQL statement in two ways to List the Film Titles and Category Names of all the films starring Renee Ball. Write the Query as a $1)$ JOIN and as a $2)$ SUBQUERY

$2.$ List the email ID and country name of all the inactive customers in the MySQL SAKILA example database.

$3.$ Using the MySQL WORLD example Database, write SQL Query to list the name and the population of all the cities in Norway ordered by population from highest to lowest.

$4.$ Create a View using MySQL WORLD Example Database Tables that lists the Country Name and Language of all the countries where $\backslash (100\backslash \%\backslash )$ of the population in that country speak that language

$-$ Then write a SQL Query using the View to find the language spoken by $\backslash (100\backslash \%\backslash )$ of the population in Cuba.

$5.$ Create a Stored Procedure getregion$(...)$ which will take as an input parameter the name of a country and will have an output parameter that will store the region

$-$ So for example, after creating the stored procedure in the WORLD database, and executing call getregion$(\text{'}$Ukraine$\text{'},$ @r$)$; then select @r; will return Eastern Europe

$-$ NOTE: You may need to use the MySQL command "delimiter" to temporarily use some other symbol besides the semicolon $("$;$")$ to separate statements so that you would be able to store the semicolon $("$;$")$ into the procedure when creating it$.$