Oracle Decode - Exercise

Use DECODE built-in function with Oracle SQL

Objective: Write SQL with built-in value translation.

Exercise scoring

This exercise is worth a total of 15 points. 10 for writing a correct SQL statement and five for also using the GROUPING and ROLLUP BIFs.

Background

You have been asked to provide a summary of sales of cars from a vehicle database. The CAR table appears as follows:

CREATE TABLE CAR (
CAR_MAKE                char(20),
CAR_COLOR            number(2),
SALE_STATE_CODE   number(2),
SALE PRICE                 number)

You need to write an SQL statement that will provide the following:

SALE_STATE     COLOR         NUMBER_SOLD
------------ ----------     ----------------------
California     Blue          123,456
The problem is that the state_codes and colors are coded to save space.
The translation codes follow:
Colors:
Red = 1
Blue = 2
Green = 3
States:
California = 12
Nebraska = 33
North Carolina = 44

Exercise - Instructions

Your task is to modify the SQL below to add meaningful names for color and state. For extra credit, add the GROUPING and ROLLUP clauses to provide statewide totals.

Select
Sale_state_code,
Car_color,
Avg(sale_price)
From car
Group by
Sale_state_code,
Car_color;

Submitting your exercise

Once you have created your SQL statement in the text box, click Submit to submit the exercise.