Spring 2024 BIS 324 PS3 Problems
docx
keyboard_arrow_up
School
Lehigh University *
*We aren’t endorsed by this school
Course
324
Subject
Information Systems
Date
Apr 3, 2024
Type
docx
Pages
3
Uploaded by MasterMusicHamster4
BIS 324 - Business Data Management
Spring 2024
Problem Set 3
General Requirements:
1.
While writing your SQL code, make sure you are displaying enough result lines to show all results. Execute the code by pressing RUN to make sure all result lines are visible. 2.
Make sure to submit your code under its respective assignment link in Course Site
. This must be submitted no later than the specified due date located in the assignment’s link on Course Site, otherwise you will receive reduced or zero credit. Grading Rubric:
Functionality of Query (90% of points/problem)
Query must correctly produce output asked for in problem, including all columns, ordering of data, etc. Readability/SQL Conventions (5% of points/problem):
A. All column headings must be user friendly, in mixed case, and include spaces if appropriate. “MOVIEID” is not user friendly. “Movie ID” is user friendly. B. All column headings and output (other than given data that is in the table) should be proofread for spelling/grammar errors. C. Your SQL code should be readable and on multiple lines with indentation. Note that the select
, from
and where clauses need to be on separate lines
. Efficiency/Scalability (5% of points/problem):
SQL must be efficient and straightforward and scalable to all situations. Even if your query produces the correct output, if it does so with extraneous steps and code, it is not efficient. Some examples are given below, but these are NOT all inclusive: Example 1: (eliminate unnecessary math)
Less efficient: SELECT merfnbr FROM merchant WHERE merfnbr + 3000 < 5000; More
efficient: SELECT merfnbr FROM merchant WHERE merfnbr < 2000; Example 2: including unnecessary WHERE clauses
Example 3: including unnecessary subqueries
Example 4: unless stated otherwise, it must work for all data possible, not just data in our database currently - your query should work for any type of data that MAY be in the
database, not only the data that is present now. For example, suppose that there are
only 2
movies in the database with the word “love” in them: “I Love Lucy” and “The
Love Letter”; You are asked to select all movies that contain the word “love” in the title
regardless of case, and you submit the query:
Not Scalable: SELECT title FROM movies WHERE title LIKE '% Love %'
Although that may pull all the movies CURRENTLY that have the word love in their
titles, it will not work for possible future
data (for example if we add the move “Love
Reinvented”, the above query would not work).
Scalable: SELECT title FROM movies WHERE title LIKE '% Love %' OR title LIKE '% love %' OR title LIKE '%Love %' OR title LIKE '%love %' OR title LIKE '% Love' OR title LIKE '% love%’ Scenario: Netflix
Netflix is an online movie and TV episode streaming and rental service that delivers DVDs at a flat rate by mail or via online streaming to customers in the United States. A customer can choose from thousands of titles and stream instantly on multiple devices by enlisting in the Streaming plan. When the feature is over, the customer may choose to rate it, view a different title, or exit the software. In addition to the Streaming service, customers may have DVDs, one at a time, delivered to their home addresses via the DVD plan. When the customer is done with the DVD, they send it back to Netflix via the mail and then Netflix sends the next available movie from the queue to the customer. You only need to access the tables customers, movies, ratings100, strmplan, actors
, movies_genres, and movies_actors in Oracle for this assignment: Note that there will be additional tables that will be forthcoming for future assignments that also deal with this same scenario.
Problems
1.
Netflix has partnered up with Visa and Mastercard 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 customer’s 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 viewer’s 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 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 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.
(35%)
Your preview ends here
Eager to read complete document? Join bartleby learn and gain access to the full version
- Access to all documents
- Unlimited textbook solutions
- 24/7 expert homework help