To practice writing complex queries

MySQL Requirements: moviesFull database

zyBooks: $3\mathrm{.}6$

Required Screenshots

$1.$ Every Question #$1-7$

Complete the problems, take a screenshot of every problem, add it to a word document, and

submit the document containing all screenshots.

$1.$ Write a query that finds the years that have had a Japanese movie released. Show only

the years.

$2.$ Write a query that shows all of the movies that were released in the same year as a

Japanese movie. Use the IN operator and your query from #$1$ as the subquery. $($Hint:

Your query from #$1$ returns a list of valid years$)$

$3.$ Write a query that shows the country with the most movie releases.

$4.$ Show the ratings for movies that were released in the country with the highest number

of releases $($mov$\_$rel$\_$country$).$ Show only the reviewer$$s name, movie title, movie year,

release country, and rating. $($Hint: Use the query from #$3$ as your subquery$)$

$5.$ Show all movies whose duration $($mov$\_$time$)$ is greater than the average. Show only the

movie title, year, duration, language, and genre.

Subqueries can be used in a variety of different ways. One of the most powerful ways to use

subqueries is by turning a query$$s output into its own table. Let$$s walk through an example:

The above query shows the number of movies each director has directed. Each column in the

SELECT clause has been given an alias so it looks better in the output.

For example, instead of displaying $$dir$\_$firstName$$ as $$dir$\_$firstName$$ it is shown as $$First

Name$$ and instead of showing $$COUNT$(*)$ as $$COUNT$(*),$ is it shown as $$Amount$.$ This gives

the following output:

SELECT dir$\_$firstName As 'FirstName', dir$\_$lastName AS 'LastName', COUNT$(*)$ As 'Amount'

FROM movie

JOIN director

ON director.id $=$ movie.dir$\_$id

GROUP BY dir$\_$id

ORDER BY COUNT$(*)$ DESC;

Now take the following question $$What is the average number of movies directed by each

director?$$ In other words, this question wants to know the average of the $$Amount$$ column in

the above query.

How do we take the AVG$()$ of the $$Amount$$ column? The only way to do this is by treating the

result of our query as its own table. We will call this new table directorCount. Run the following

query.

You should receive the same output as running our previous query. Our new query turns our

old query into a subquery placed in the FROM clause. This allows us to select specific columns

and perform functions on the result of the subquery as if it were a table!

You can see in the FROM clause, instead of specifying one of the tables in your database $($such

as movie, or director$),$ we have given it our query from earlier.

MySQL will treat the result of this query as if it were a table. Note that you must always specify

a name for this table $$ in this case we$$ve named the table $$directorCount$.$ The contents of the

table are the same as the result of the query:

SELECT directorCount.FirstName, directorCount.LastName, directorCount.Amount

FROM $($SELECT dir$\_$firstName As 'FirstName', dir$\_$lastName AS 'LastName', COUNT$(*)$ As 'Amount'

FROM movie

JOIN director

ON director.id $=$ movie.dir$\_$id

GROUP BY dir$\_$id

ORDER BY COUNT$(*)$ DESC$)$ directorCount;

Now we can take the AVG$()$ of the $$Amount$$ column as we would any normal column in a table.

Run the following query:

The average number of movies directed by each director is $1\mathrm{.}1667.$ We were able to determine

this by using a subquery in the FROM clause. Try to answer the following questions on your

own.

$6.$ Write a query that counts the number of movies in each genre. Your query should show

the genre title and the number of movies in that genre. Use column aliases to format

your column names to match the below output. $($Hint: No subquery required$)$

SELECT AVG$($directorCount$.$Amount$)$

FROM $($SELECT dir$\_$firstName As 'FirstName', dir$\_$lastName AS 'LastName', COUNT$(*)$ As 'Amount'

FROM movie

JOIN director

ON director.id $=$ movie.dir$\_$id

GROUP BY dir$\_$id

ORDER BY COUNT$(*)$ DESC$)$ directorCount;

$7.$ Write a query that shows the average number of movies in each genre. $($Hint: This query