Reference no: EM13859337

WHERE vs HAVING in SQL

Using WHERE or HAVING in your SQL statement, answer the following questions using the MOVIES Database available with this assignment.  Be sure to include your SQL code and a screen capture of your output for EACH question.  Do NOT decrease the size of your screen capture image!

Writing the SQL code yourself, useINNER JOIN  syntax on each of these exercises. You must decide whether to use WHERE or HAVING  or both to answer the question properly.  No guidance will be given in the question.

I suggest you save each query in Access for future reference.

1. Create a list of Countries and the Title of the movies that are NOT from the United States with the countries sorted in alphabetical order.  The two columns should be Country and Movie Title.

How many records/rows are there? _____

Be sure to include your SQL and a partial screen capture of your results.

2.  Create a list of Directors who have directed War movies. Include the Director Name and Title of the move (2 columns) in the output. Sort by the Director's name in alphabetical order. (Remember: You must use INNER JOIN on this query.)

3. Notice in the results of the previous question.  As you scrolled through you see some directors have more than 1 or 2 movies. Modify your query to display a list of Directors Names who have directed MORE THAN 2 War movies. There should only be 2 columns output.  Sort by alphabetical order.

4. Create a list of all of the STARS and the number of movies each has starred in. There should be two columns with this output. Sort by the highest to lowest total number of movies starred in.

Which actor has starred in the most movies? ________

Copy your SQL code and the several lines of the output below.

5.  Modify your query from number 4  above to list only those stars who have starred in 13 or more movies.

6. Create a list of stars and the number of movies they starred in who were born AFTER Jan. 1, 1980.  Be sure to surround the date with # characters.

7.  How many stars have starred in only one movie? _____   

 Create a list of Stars who have starred in exactly one movie.

8. Identify all of the stars who were born after 1970 and starred in AT LEAST 3 movies.  (Reminder:  Surround your date with #. ).

Attachment:- MOVIES_Database.accdb