Consider the schema Grades(id,grade), where grades are in between 0 and 100. Write a query that computes the histogram of the form

This problem is not as easy as it sounds.



CREATE VIEW Score (score) AS

SELECT floor(grade/10)

FROM Grades

SELECT score, COUNT(*)

FROM Score

GROUP BY score

ORDER BY DESC score

Note: This is only a partial solution, since, say, if 0 people got 20-29, it misses the pair
h2 0i. For a complete solution, we can create a constant table, DummyScores, with
the attribute score and the following tuples: 0, 1, 2, 3, 4, 5, 6, 7, 8, 9, 10
(each number is a tuple by itself). Then we can proceed as follows:



CREATE VIEW Score1 (score) AS

(SELECT floor(grade/10)

FROM Grades)

UNION

(SELECT * FROM DummyScores)

SELECT score, COUNT(*)-1

FROM Score1

GROUP BY score

ORDER BY DESC score

This solution is correct because DummyScores adds 1 to the count of every score in the
histogram, which we correct by subtracting 1 in the second query.