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:

You have installed SQL Server 2014 on your machine. Apple users can use Access for this assignment

You have downloaded the three SQL scripts I put on Moodle (viewer, movie and rating), copied their contents onto SQL Server Management Studio, and successfully executed them.

Some tips on creating the movie review database:

Make sure that you run viewer and movie before you run rating, since rating has foreign keys referencing the other two tables

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

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 YourName-HW7.sql. This is the file that you will submit through Moodle.

QUERIES:

1. Get the reviewers ID and their occupations

2. Get the movie title for all movies that are comedy or horror, rename the output attribute name to be Happily Scared

3. Get the reviewer ID for male students or students older than 30.

4. What are the occupations that are NOT student, lawyer or educator? List them in descending alphabetical order

5. List all movies released in 1982[1], sorted by alphabetical order

6. List all mean reviewers and their average rating scores mean reviewers are reviewers whose average rating score is less than 2

7. List the oldest age of reviewers for each profession

8. List number of reviewers for each profession, not including students or educators

9. List the average student reviewers age by gender

10. List all occupations in which the female reviewers have an average age above 40

[1] I know we do NOT have a release date field! However, work smart, analyze your data, and think of a solution! Hint: review the first 1000 rows of data (this can be done by right clicking on a table name and choosing edit table) would give you some inspiration! BTW: This represents a very real scenario where database designers / developers observe and learn from actual data instead of logical schema to come up with an effective solution.