database

For this assignment, you will use your SQL Server. As an alternative to SQL Server, you are allowed to use a different database, e.g. MySQL, Postgres, or SQL Server installed in your own machine. In that case you first need to import the movie database in your database system or you need to create same database with all tables: you can obtain the data from here. Note: If you use alternatives, e.g. MySQL, please ensure the queries you email me are executable on MS SQL Server (e.g. on IISQLSRV). Description In this Assignment you are asked to write 12SQL queries on a relational movie database. The data in this database is from the IMDB website. The database consists of seven tables; however you will be using only the following five tables - ACTOR (id, fname, lname, gender) - MOVIE (id, name, year, rank) - DIRECTOR (id, fname, Iname) - CAST (pid, mid, role) - MOVIE DIRECTOR (did, mid) id column in ACTOR, MOVIE \& DIRECTOR tables is a key for the respective table. CAST.pid refers to ACTOR. id, CAST.mid refers to MOVIE.id MOVIE DIRECTOR.did refers to DIRECTOR.id and MOVIE DIRECTOR.mid refers to MOVIE.id 1. a. List all the actors who acted in at least one film in 2 nd half of the 19 th century and in at least one film in the 1st half of the 20th century b. List all the directors who directed a film in a leap year 2. List all the movies that have the same year as the movie 'Shrek (2001)', but a better rank. (Note: bigger value of rank implies a better rank) 3. List first name and last name of all the actors who played in the movie 'Officer 444 (1926) 4. List all directors in descending order of the number of films they directed 5. a. Find the film(s) with the largest cast. b. Find the film(s) with the smallest cast. In both cases, also retum the size of the cast. 6. Find all the actors who acted in films by at least 10 distinct directors (i.e. actors who worked with at least 10 distinct directors). 7. Find all actors who acted only in films before 1960. 8. Find the films with more women actors than men. 9. For every pair of male and female actors that appear together in some film, find the total number of films in which they appear together. Sort the answers in decreasing order of the total number of films. 10. For every actor, list the films he/she appeared in their debut year. Sort the results by last name of the actor. 11. The Bacon number of an actor is the length of the shortest path between the actor and Kevin Bacon in the "co-acting" graph. That is, Kevin Bacon has Bacon number 0 ; all actors who acted in the same film as KB have Bacon number 1; all actors who acted in the same film as some actor with Bacon number 1 have Bacon number 2, etc. Return all actors whose Bacon number is 2. 12. Bonus: Suppose you write a single SELECT-FROM-WHERE SQL query that returns all actors whose Bacon number is infinity. How big is the query? Note: The above "Bonus" problem is ill-stated. The correct one should be as follows: Suppose you write a single SELECT-FROM-WHERE SQL query that returns all Algoma University actors who have finite Bacon numbers. How big is the query? 13. A decade is a sequence of 10 consecutive years. For example, 1965, 1966,... 1974 is a decade, and so is 1967,1968,,1976. Find the decade with the largest number of films. 14. Bonus: Rank the actors based on their popularity, and compute a list of all actors in descending order of their popularity ranks. You need to come up with your own metric for computing the populanity ranking. This may include information such as the number of movies that an actor has acted in; the 'popularity' of these movies directors (where the directors' popularity is the number of movies they have directed), etc. Be creative in how you choose your criteria of computing the actors' popularity. For this answer, in iddition to the query, also turn in the criteria you used to rank the actors