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