Assignment 7

In this homework, you will develop a SQL script that contains all of the following queries. In order to

work with this homework, I assume that:

1. You have installed SQL Server 2017 or SQL Server 2019 on your machine. Apple users may

use Access for this assignment. If you use Access, you must copy the queries into a separate

document and submit that document. DO NOT SUBMIT AN ACCESS MDF or ACCDB.

2. You have downloaded the three SQL scripts I put on Canvas ("viewer", "movie" and "rating"),

copied their contents onto SQL Server Management Studio, and successfully executed them.

Some tips on creating the movie review database:

1. Make sure that you run "viewer" and "movie" before you run "rating", since "rating" has foreign

keys referencing the other two tables

2. After running the script to create each table, it is a good idea to quickly check whether you

have successfully populated the table by running the command:

SELECT COUNT(*)

FROM

table_name

;

If you did everything right, the number of rows for "viewer", "movie" and "rating" tables are 943,

1682 and 100,000

3. Be warned - the "rating" script could run for 10 - 20 minutes (it is a big table) depending on the

speed of your computer

To work on this homework, please use SQL Server Management Studio. I recommend that you test-

run a query, save it when you succeed, then

work on the next query on the same page.

Remember Server Management Studio has this neat function that allows you to choose just part of

codes you want to compile and run. This means that you just need to highlight the query you are

working on and run it, and do not have to re-run the whole script every time.

Once you are done with all queries, save your SQL file as YourLastName_YourFirstName_A07.sql.

This is the file that you will submit through Canvas.

QUERIES:

1. Get the reviewers' IDs that are not over 21 years old.

2. Get the movie title for all movies that are both action and adventures. Rename the output

attribute to be "Action Adventures."

3. Get the reviewer ID for female lawyers or lawyers older than 32.

4. How many reviewers have occupations in the STEM fields? List those with an occupation of

Engineer, Scientist, or Programmer. List them in descending alphabetical order. Give the count

column the title "Jobs in STEM."

5. List all movies released in the 1970's, sorted by alphabetical order

6. List all "hard" reviewers and their average rating scores - hard reviewers are reviewers whose

average rating score is less than or equal to 2. Give the average rating column an alias.

7. List the youngest age, oldest age, and average age of reviewers for each profession that have

given the lowest rating to a musical film. Use a left join. Add appropriate column titles. Sort the

occupations in ascending alphabetical order.

8. List the number of reviewers for each profession, not including engineers or technicians. Sort

the list in descending numerical order by the count column. Give the count column the title

"Occupation Count"

9. By Gender, list the average student reviewers' age, Average Rating, and The number of

ratings given to movies made in 1976. Use the column titles "Average Age", "Average Rating"

and "Number of Ratings" for movies made in 1976. HINT: Use multiple left joins.

10. a) List all occupations in which the male reviewers have an average age above 43.00. Show

the results for average age as a decimal number.

10 b) Submit a second query using only integer values. Do you notice a difference?