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.
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.
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?