**Learning Goals**

By the end of this assignment, you should have:

- Cleared up any major gaps in your understanding of the relational model and SQL.
- Acquired a good knowledge of how to write some common query patterns.

**Schema**

For this assignment, please download `PS2-db.sql` from Canvas. It contains a simple movie schema and some sample data. Recall that in Postgres you can use the command `\i 'PS2-db.sql';` to load a set of SQL commands from a file called `PS2-db.sql`. Be sure that you either use the full path name or place the file in the same directory from which you invoke the `psql` command. Your queries should work on any instance of the given schema. In particular, your queries may give empty results on the given instance, so be sure to create some test databases with non-empty results. Also, note the constraints, consider what is a key and what can/cannot be null.

**Queries**

Write the queries below in SQL. Your queries must run on Postgres v13. Do not make any assumptions about the data that are not enforced by the constraints in the schema. Your queries should work for any database that satisfies the declared constraints. Be sure your queries work even if one or more tables are empty. When asked for a maximum or minimum, if there are ties, report all of them. The SQL queries must be correct even if there are nulls (where permitted in the schema) and should follow the ordering instructions for the query result (using the `ORDER BY` clause). You should also return in the `SELECT` clause the attributes in the order stated in the question.

1. Find all movies where the set of genres associated with the movie is exactly the same as the set of genres associated with all of its directors. A movie with no genres whose directors (if any) have no genres would satisfy this condition. Return the movie id and title, ordered by movie id then title.

2. Return the directors who have cast at least 10 different actors but have never cast an actor who identifies as female (gender = 'F'). Each director should be returned only once with their id, lname, fname (and order the result by lname, fname).

3. If a director hires an actor with the same last name but a different first name, this is a potential case of conflict of interest. For each director, report the number of different actors the director has hired on any movie that the director directs with the same last name but a different first name. Include directors who are not guilty of any conflict with a count of zero. For each director, return one tuple with the director’s id along with the last and first names of the director and the number of such (potentially related) actors. If an actor is hired for multiple movies or roles, just count that actor once. Order the output by director id, lname (of director), fname (of director).

4. For each director, return the genre that has the highest probability (prob). Directors without genres should be returned with null as the genre and null prob. Treat null probabilities as zero. So a null probability is returned only if there are no genres with a non-zero probability. Return the result ordered by lname, fname, genre of the director. If there are ties (e.g., Ava has genre Drama with prob 50 and genre Suspense with prob 50 and no other genres), then a director may be returned more than once.

5. The era of a director is the years from their first movie to their last. For every director, return the id, lname, and the length (last - first + 1) of his/her/their active period (which is 0 if the director has never directed a movie). If a director directs any movie with an unknown (null) year, the active period is also unknown (null). Order result by the era length, lname, id.