Write a SELECT statement that answers the question
-
NotFlix Database
Your SQL test will use the NotFlix database. This database has two tables for an imaginary and very simplistic video rental business.
The tables are:
MovieInventory
This table contains one row per movie. Each row contains the name of the movie (movie_title), the price a subscriber needs to pay if he loses or damages the movie (replacement_price), and the number of movies currently in inventory (onhand_qty), a value of zero means we are out of stock. The primary key is movie_title.
NotFlix
This table describes the queues of movies that need to be sent to each subscriber. It contains one row per movie in a queue to be sent to a subscriber. Each row has the name of the subscriber (subscriber_name), the position of the movie in the queue (queue_nbr), and the name of the movie (movie_title). Position one is the first movie to be sent to the subscriber, position two is the next movie, etc. The primary key is made up of subscriber_name and queue_nbr. The foreign key to the MovieInventory table is movie_title.
Question 1
Write a query that produces the names of all subscribers that have the most expensive queue, based on the replacement costs of his or her movies. If a subscriber has the same movie more than once in his queue, then sum the cost of that movie as many times as it appears in the queue. If two or more subscribers tie, the output should include the names of all subscribers that tied.
Question 2
Write a query that produces the names of all subscribers who ordered the same movie more than once. Have the query output only the names, nothing else. Do not repeat names even if a subscriber ordered several movies more than once.
-
we do not do homework.
if you have a specific question, let us know, but the literal copy and paste, without any input on what you tried, what you think, and what you plan makes most of us turn away from even thinking of helping you. You will learn nothing if we spoon feed you the answer, and it'll just hurt you later when the skills that would be built off of this exercise are missing that you will need for the next chapter.
how would YOU approach the problem? what have you tried so far?
a google search for "Notflix database" pointed me towards a university in Washington; is that where you are taking this course?
Lowell
--help us help you! If you post a question, make sure you include a CREATE TABLE... statement and INSERT INTO... statement into that table to give the volunteers here representative data. with your description of the problem, we can provide a tested, verifiable solution to your question! asking the question the right way gets you a tested answer the fastest way possible! -
But but Lowell, If Iz answerz I kin haz internets cookie! Right? Prize iz internetz cookie?
- Craig FarrellNever stop learning, even if it hurts. Ego bruises are practically mandatory as you learn unless you've never risked enough to make a mistake.
For better assistance in answering your questions[/url] | Forum Netiquette
For index/tuning help, follow these directions.[/url] |Tally Tables[/url]
Twitter: @AnyWayDBA -
Craig Farrell (9/14/2010)
But but Lowell, If Iz answerz I kin haz internets cookie! Right? Prize iz internetz cookie?ok Craig, YOU get an internet cookie:
for varunkum:
No cookie for you!
Lowell
--help us help you! If you post a question, make sure you include a CREATE TABLE... statement and INSERT INTO... statement into that table to give the volunteers here representative data. with your description of the problem, we can provide a tested, verifiable solution to your question! asking the question the right way gets you a tested answer the fastest way possible! -
ROFL :hehe:
-
GIMME COOKIE !!!
__________________________________________________
Against stupidity the gods themselves contend in vain. -- Friedrich Schiller
Stop, children, what's that sound? Everybody look what's going down. -- Stephen Stills
Viewing 6 posts - 1 through 5 (of 5 total)
You must be logged in to reply to this topic. Login to reply