Tables are provided here. Using Oracle Application Express SQL Commands.

1. In SQL, Netflix has partnered up with Visa and Mastercard in order to give a promotional deal to its most active stream plan customers. Netflix is willing to lower its monthly payment for stream plan customers, with Visa or Mastercard credit cards, who rate Netflix movies often. Produce a query that only shows all customers who rated movies at least 1,000 times, are subscribed to a streaming plan, and have a Visa or Mastercard credit card. The query (presented in this exact order) must show: customer ID, customer first and last name (in the same column with a space between first name and last name), the total number of ratings they have made shown with commas (ex. 123,456), the id of the streaming plan they subscribe to, the customers current price per month from their streaming plan, and the new price per month. Both prices should be outputted with a dollar sign, commas, and two decimal places (ex. $123,456.78). Stream plan customers who rated at least 1,000 times will receive 5% off their monthly fee, stream plan customers who rated at least 5,000 times will receive 10% off their monthly fee, and stream plan customers who rated at least 10,000 times will receive 25% off their monthly fee. The query must be ordered with the most active stream plan customer at the top. (40%)

2. The Netflix Marketing department wants to know if Halloween has any significant effect on viewers movie choices. A new study has surfaced claiming that viewers are more likely to rate Horror or Thriller films higher in the month of October than any other month. The Marketing department would like you to produce a query that investigates this claim. Your query should output each month of the year (Name of the month, not the number) in a column called Month, and the average rating (rounded to 1 decimal place, ex. 3.2) for all R-rated Horror or Thriller movies given in that month (of any year) in a column called Average Rating. The month you are focusing on is the month that the user rated the movie. You may hardcode the genre codes (Hint: Looking at the netflix.genres table will help you find the correct genre codes). Order your results by month, starting with January. (25%)

3. Netflix would like to add a webpage to spotlight famous female actors who have starred in recent popular movies. Because Netflix has found that viewers of Drama films would be most interested in these background stories, Netflix has asked you to compile a list of actresses that starred in a drama within the past 12 years (do not hardcode the year) that had an average rating of at least 4, when rounded to 2 decimal places. (First, round the average rating and then compare it to see if it is at least 4). Create a query that displays the movie title, its average rating (rounded to two decimal places), and the actors first and last name formatted with a space between them (ex. Jane Smith). You may hardcode the genre code (Hint: Looking at the netflix.genres table will help you find the correct genre code). A movie may appear twice if multiple actors in the movie meet the criteria for this query. Order the results by descending average rating, and title.

MOMES_FORMAT MOVIEID NUMBER0) PF MOVIEID NUMBERC ACTORID NUMBER(O) ACTORID NUMBER (B) MOVIES ACTORS PK(MOMEID, ACTORID) MOVIES_ACTORS_CON (MOVIEID) MOVIES_ACTORS_CON1 (ACTORID) VARCHAR2 (25) VARCHAR2 (35) FORMATID CHAR (1) MOMES_FORMAT PK(MOVIEID, FORMATID) FK FORMAT (FORMATID) DIRECTORID CHAR (8) MOVIES DIRECTORS PK (MOMEID, DIRECTORID) MOVIES_DIRECTORS_FK (MOVIEID) MOVIES_DIRECTORS_FK2 (DIRECTORID) ALNAME BIRTHDATE DATE FK MOVIES FORMAT (MOVIEID) ACTOR PK(ACTORID) Netflix ERD FORMAT DIRECTORS MOVIEID FORMATID FORMATDESCRIPT VARCHAR2 (25) NUMBER C.O) VARCHAR2 (108) NUMBER0) MOMEID NUMBER MOVIES_AWARDS PK (MOVIEID, AWARDID) MOVIES AWARDS FK2 (AWARDID) RYEAR MPAA RATING VARCHAR2 (4) DIRECTORID DIRECTOR-FIRST DIRECTOR-LAST CHAR (O) VARCHAR2 (25) VARCHAR2 150) FORMAT_PK (FORMATID) PK MOVIES (MOVIEID) DIRECTORS_PK (DIRECTORID) MOVIES GENRES NUMBER C0) VARCHAR2 (3) PF" GENRECODE RATINGS100 MOVIES_GENRES PK (MOVIEID, GENRECODE) CUSTOMERS AWARDS MOVIEID NUMBER C.0) NUMBERC.D) CUSTID CHAR (1) FK GENRES_MOVIES_GENRES (GENRECODE) FK MOVME MOVIEGENRES (MOVIEID) VARCHAR2 (25) VARCHAR2 (50) VARCHAR2 (50) AWARDTITLE VARCHAR2 (100) AWARDS PK (AVARDID) CHAR (15) RATINOID NUMBER (10) RATINGS100_PK (RATINGID) FK RATINGS_CUSTOMER (CUSTID) FK RATINGS MOVIES (MOVMEID) CPASSWORD CBIRTHMONTH CBIRTHDAY CREDITTYPE CREDITNUMB CREDITNAME CREDITEXPMTH NUMBER (2 CREDITEXPYEAR NUMBER (4) BANKROUTING BANKACCOUNT VARCHAR2 (25) BANKHOLDER ORIVERLIC ORIVERICSTATE CHAR (2) NUMBER (2) NUMBER (2) VARCHAR2 (15) VARCHAR2 (10) VARCHAR2 (75) GENRES GENRECODE VARCHAR2 (3) GENRENAME VARCHAR2 (25) STRMPLANID DESCRIPTION PRICEPERMONTH NUMBER (5,2) GENRES_PK (GENRECODE) ADDRESSES CHAR (15) CHAR (1) PF* CUSTID VARCHAR2 (17) ADDRTYPE STREETADDR1 VARCHAR2 (5D) STREETADDR2 VARCHAR2 (3D) VARCHAR2 (75) VARCHAR2 (25) VARCHAR2 (35) CHAR (2) DVDPLAN STATE OP PK ADDRESSES (CUSTID, ADDRTYPE FK ADDRESS_CUSTOMER (CUSTID) UMBER (2) CHAR PHONENUMB VARCHAR2 (100) DESCRIPTION PRICEPERMONTH NUMBER (5.2) DVDPLANID PK CUSTOMER (CUSTID) CUSTOMERS_CON (STRMP LANID) CUSTOMERS_CON2 (DVDPLANID) CHAR (5) OVDPLAN PK (DVDPLANID)