11.4

Consider the data warehouse schema depicted in Figure 11.2. Give an SQL query to summarize sales numbers and price by store and date, along with the hierarchies on store and date.

---

SELECT store_id, city, state, country, 
    date, month, quarter, year, 
    sum(number), sum(price)
FROM sales, store, date
WHERE sales.store_id = store.store_id AND 
    sales.date = date.date
GROUP BY ROLLUP(country, state, city, store_id), 
        ROLLUP(year, quarter, month, date)