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 2019 on your machine either into the Windows 10 file system or a Virtual Machine. 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. Further Directions: 1. Your database name should be A8. Point the queries to this database with the USE directive. 2. Do all of your work in this file. 3. Fill in your Name, Course (IS331), Section and date in the top of this file.

*/ /* QUERIES: */ /* **** 1. Get the reviewers IDs that are 32 years of age or older. */ /* **** 2. Get the movie title for all movies that are action and western. Rename the output attribute to be Action Westerns. */ /* **** 3. Get the reviewer ID, zip, age, gender, and occupation for reviewers in Illinois who are under 30 years old or who are educators. Zip codes for Illinois begin with 60 */ /* **** 4. Count the number of reviewers in each occupation. List the occupation, the number of reviews in that occupation, and the category of the occupation. The Category will be done using a CASE statement. The occupations of Engineer, Scientist, and Technician will be assigned a category of STEM. All others will be assigned a category of Non-STEM. Give the count column the title "Jobs in STEM." Give the Category column the title Job Category. List the Occupations in descending alphabetical order. */ /* **** 5. Create a temporary table using the WITH statement. Call the temporary table MovieTitle27. In MovieTitle27, create a field that contains the year of release only for titles that are 27 characters long. For titles with 27 characters, the Year is four characters long and begins at character 23. From the MovieTitle27 table, count the number of movies released in that year. See explanation of the WITH statement beginning on page 208 of the Murach book. FUNCTIONS NEEDED: Substring, Len, Count */ /* ****

6. List the most active reviewers and the number of reviews that they have done. Active reviewers are reviewers who have reviewed more than 270 movies. Give the count column an alias. Display the output by descending number of reviews. */ /* **** 7. List the youngest age, oldest age, and average age of reviewers for each profession that have given the Highest rating to an Action Western 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 Retired, None, or Other. Sort the list in descending numerical order by the count column. Give the count column the title "Occupation Count". */ /* **** 9. By occupation, list the Average age, Average Rating, and The number of ratings given to movies made in 1972. Use the column titles "Average Age", "Average Rating" and "Number of Ratings" for movies made in 1972. HINT: Use multiple right joins. List the results in ascending alphabetical order of occupation. */ /* **** 10. List all occupations in zip codes beginning with 60 and have are full length. Full length is five characters. Show the Average Age which must be above 40.00. Show the results for average age as a decimal number. Output your results FOR XML with each result on a single line with the table name on each result line. Only one of the XML parameters, RAW, AUTO and PATH AUTOmatically puts each value on its own line with the table name on the left. That's the one you should use. Use your full name as the ROOT. Separate the parts of your name and field names with underscores (_). Spaces are not a valid character for XML export. See Murach Chapter 18 for more on XML.