X

Celebrating the joy and power of Oracle SQL with the Oracle Developer Advocate team

  • August 27, 2019

Announcing a New Sample Schema: Customer Orders

Chris Saxon
Developer Advocate

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.

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:

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:

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:

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!

Be the first to comment

Comments ( 0 )
Please enter your name.Please provide a valid email address.Please enter a comment.CAPTCHA challenge response provided was incorrect. Please try again.