Announcing a New Sample Schema: Customer Orders

Calling all Oracle Database developers! There's a new sample schema in town!

Customer Orders

Customer Orders is a simple new schema for you to use in your presentations, demos, and blogs.

Why Have We Created a New Schema?

The current sample schemas (HR, SH, CO, PM, IX, & BI) were created in the early 2000s, back when 9i was still hip and new.

Nearly two decades and five major releases later, Oracle Database has improved significantly. And the development community has evolved too. JSON has replaced XML as the de facto data transfer format. And some are taking to storing JSON as-in their tables.

So we wanted a data set showing you how to use JSON in Oracle Database. While also highlighting other enhancements added over the years, such as identity columns.

Customer Orders requires Oracle Database 12c or higher.

While we could have added new features to one of the existing schemas, one of the key reasons for using them is they've become a standard you can rely upon. Everyone works from the same definition. Which means you can use the tables in your blog posts, scripts, and presentations without further explanation. Changing the existing schemas breaks this common reference point.

So we decided to have a fresh start. And Customer Orders was born!

What Is the New Schema?

Customer Orders models a simple retail application using these tables:

The sample data represents a basic clothing line. PRODUCTS.PRODUCT_DETAILS stores a JSON document describing each clothing item and its customer reviews. For example:

{
  "colour" : "red",
  "gender" : "Girl's",
  "brand" : "BRANDNAME",
  "description" : "description",
  "sizes" : [ 
    "1 Yr", "2 Yr", "3-4 Yr", "5-6 Yr", "7-8 Yr", "9-10 Yr" 
  ],
  "reviews" :  [
    {
      "rating" : 9,
      "review" : "Review text"
    }
  ]
}  

This allows you the flexibility to use this schema to store a wide range of products. All you need to do is add the relevant attributes to your JSON!

Where Can I Find Customer Orders?

Download the schema from the Oracle sample schemas GitHub repository.

How Do I Install Customer Orders?

To install customer orders, you must use Oracle Database 12.1.0.2 or higher.

Once you've downloaded the scripts, run co_main with the following parameters:

This will drop and recreate the user CO.

@co_main <CO_password> <connect string> <tablespace> <temp tablespace>

For example:

@co_main copassword localhost:1521/pdb USERS TEMP

If you want to install the tables in an existing schema, run the following scripts:

@co_ddl
@co_dml

For further details, see the readme on GitHub or the Sample Schemas Documentation.

What Can I Do with This Schema?

Whatever you like! Please use this to build demos, write scripts, and use in presentations.

Looking for inspiration?

The installation includes several views and sample queries to get your SQL juices flowing.

Here are a few examples:

Extract Product Reviews from PRODUCT_DETAILS JSON

The sample data include an array of reviews for each product in its JSON data.

Using 12c's JSON_table, you can extract these out to traditional rows-and-columns, like so:

1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19

select p.product_name, r.rating,        round (          avg ( r.rating ) over (            partition by product_name          ),          2        ) avg_rating,        r.review from   products p,        json_table (          p.product_details, '$'          columns (            nested path '$.reviews[*]'            columns (              rating integer path '$.rating',              review varchar2(4000) path '$.review'            )          )        ) r;

Find High-Value Customers

Knowing who your best customers are and keeping them happy is a great way to keep your company going.

The following uses 12c's row pattern matching to find all the people who placed at least £100 worth of orders for three consecutive months:

1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26

with rws as (   select          o.customer_id, trunc ( o.order_datetime, 'mm' ) order_month,          sum ( oi.quantity * oi.unit_price ) month_total   from   products p   join   order_items oi   on     p.product_id = oi.product_id   join   orders o   on     oi.order_id = o.order_id   group  by o.customer_id, trunc ( o.order_datetime, 'mm' ) )   select * from rws   match_recognize (     partition by customer_id     order by order_month     measures       count(*) as num_months,       sum ( month_total ) as total_value     pattern ( high_value consecutive{2,} )     define       high_value as         month_total >= 100,       consecutive as         order_month = prev ( add_months ( order_month, 1 ) )         and month_total >= 100 );

Product a Matrix of Sales Value by Month and Year

Finance teams often want sales broken down into a table with months across the top and years down the side.

The following shows you how to do this with the PIVOT clause:

1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19

with order_totals as (   select extract ( year from o.order_datetime ) order_year,          to_char ( o.order_datetime, 'MON', 'NLS_DATE_LANGUAGE = english' ) order_month,          sum ( oi.quantity * oi.unit_price ) value_of_orders   from   orders o   join   order_items oi   on     o.order_id = oi.order_id   group  by extract ( year from o.order_datetime ),          to_char ( o.order_datetime, 'MON', 'NLS_DATE_LANGUAGE = english' ) )   select * from order_totals   pivot (     sum ( value_of_orders ) value     for order_month in (       'JAN' JAN, 'FEB' FEB, 'MAR' MAR, 'APR' APR, 'MAY' MAY, 'JUN' JUN,       'JUL' JUL, 'AUG' AUG, 'SEP' SEP, 'OCT' OCT, 'NOV' NOV, 'DEC' DEC     )   ) order by order_year;

So what are you waiting for? Head over to GitHub and download it today!

UPDATE 15 Nov 2019: Added link to documentation