Question
7. Write a SQL statement with a correlated scalar subquery to calculate the count of DVDs (DVDcount) in the price range using numbers table and
7. Write a SQL statement with a correlated scalar subquery to calculate the count of DVDs (DVDcount) in the price range using numbers table and DVD table. Example:
Example Data: DVD count less than price $1 = 1,
DVD count between price $1.01 and $2.00 = 204
DVD count between price $2.01 and $3.00 = 361
DVD count between $99.01 and $100 = 2153
Example Results: DVDprice DVDcount
1 1
2 204
3 361
100 2153
Hint: 1. Use column n (as DVDprice) in numbers table;
2. Use a correlated scalar subquery to calculate count of DVDs
in price range between (n 1) and n (as DVDcount);
3. In the correlated scalar subquery, the count of DVDs in
the price range can be calculated in a WHERE clause as:
WHERE DVD.price > (numbers.n 1) and DVD.price <= numbers.n
Paste the SQL below.
Need some clarification..this is my attempt
select n, dvdCount from numbers left join ( select dvdId, sum(dvd.price * count(dvdId)) as dvdCount from dvd join numbers where dvd.price > (numbers.n -1) and dvd.price <= (numbers.n) group by price order by price ) as Total on total.price = numbers.n
|
Step by Step Solution
There are 3 Steps involved in it
Step: 1
Get Instant Access to Expert-Tailored Solutions
See step-by-step solutions with expert insights and AI powered tools for academic success
Step: 2
Step: 3
Ace Your Homework with AI
Get the answers you need in no time with our AI-driven, step-by-step assistance
Get Started