Create Materialized Views - Exercise

Create Materialized Views for Summary Data and Joins

Course Project: Creating a Materialized View

Objective: Create a materialized view for aggregate data.

Exercise Scoring

This exercise is worth 10 points.

Background | Overview

In this exercise, you will create a materialized view for aggregate data that contains the year and month.

Fact File

Review the fact file to find out more information about the House-O-Pets and its Oracle database.

Data Tables
Click the View Table link above to see the contents of the tables in the House-O-Pets database.

Download Files

You can download a correct version of the script for this exercise, cmn.sql, from the Resources page.

Scenario

Although there is a view called MONTHLY_SALES in the House-O-Pets database, it has to be explicitly referenced in a query to be used. You would like to create a materialized view that provides the same information, because the Oracle query optimizer will automatically substitute the materialized view when appropriate.

Instructions

Create a SQL DDL command to create a materialized view that contains the year and month extracted from the SALES_DATE column with the TO_CHAR(SALES_DATE, 'YY-MONTH') function, the sum of the SALE_TOTAL column, and the COUNT(*) function, which is required for materialized views that contain aggregate data based on a single table.

Hints

You will want to use the specifications discussed in the previous lesson.
You will not need to have a BUILD IMMEDIATE clause in your DDL, because that is the default.
You will need to use the GROUP BY clause with the same TO_CHAR() function that you applied to SALES_DATE, because all non-aggregate values have to be included in the GROUP BY clause.
You will probably want to specify a name for the first two columns in the view by listing a name after the function but before the ending comma.

Submitting your exercise

Type (or cut and paste) your plan into the box below to submit your answers. Click the Submit button when you are finished to submit the exercise. Remember to submit all your answers at once.