Customer Orders is a new sample schema in Oracle Database launched in Aug’19, its a simple retail application database. You can use this for learning SQL, writing blog posts or any kind of application demo. In this schema you will also get to know the usage of JSON in Oracle Database.

Below is the relational diagram of CO schema created using Oracle Data Modeler.


Customer Orders schema requires Oracle Database 12c or higher version. Refer below blogs for installing Oracle Database 12c & setting up the pluggable database.

Installing CO schema

Download the schemas from the GitHub link: db-sample-schemas . After downloading the schemas you have to run below sql

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

I have created the table spaces in my pluggable database, which is required for running the co_main script for schema creation.

CREATE SMALLFILE TABLESPACE "USERS" DATAFILE 'C:\APP\SHOBHIT\VIRTUAL\ORADATA\ORCL\PDB\USERS01.dbf' SIZE 100M AUTOEXTEND ON NEXT 100M ;
CREATE TEMPORARY TABLESPACE TEMP TEMPFILE 'C:\APP\SHOBHIT\VIRTUAL\ORADATA\ORCL\PDB\TEMP1' SIZE 2G;

Now cloning the Oracle sample schemas from GitHub or you can download the Zip extract & unzip from GitHub.

Executing the co_main script under my pluggable database.

This command will first drop the CO schema & then create it, after that it will start the DDL script & DML script. You can check the script logs in co_install txt file in the same directory where your schema is present.

Connecting the CO schema using the SQL Developer.

Checking out the JSON data in Product table:

Sample JSON data for one of the product:

{
    "colour": "green",
    "gender": "Women's",
    "brand": "FLEETMIX",
    "description": "Excepteur anim adipisicing aliqua ad. Ex aliquip ad tempor cupidatat dolore ipsum ex anim Lorem aute amet.",
    "sizes": [0, 2, 4, 6, 8, 10, 12, 14, 16, 18, 20],
    "reviews": [{
        "rating": 8,
        "review": "Laborum ipsum adipisicing magna nulla tempor incididunt."
    }, {
        "rating": 10,
        "review": "Cupidatat dolore nulla pariatur quis quis."
    }, {
        "rating": 9,
        "review": "Pariatur mollit dolor in deserunt cillum consectetur."
    }, {
        "rating": 3,
        "review": "Dolore occaecat mollit id ad aliqua irure reprehenderit amet eiusmod pariatur."
    }, {
        "rating": 10,
        "review": "Est pariatur et qui minim velit non consectetur sint fugiat ad."
    }, {
        "rating": 6,
        "review": "Et pariatur ipsum eu qui."
    }, {
        "rating": 6,
        "review": "Voluptate labore irure cupidatat mollit irure quis fugiat enim laborum consectetur officia sunt."
    }, {
        "rating": 8,
        "review": "Irure elit do et elit aute veniam proident sunt."
    }, {
        "rating": 8,
        "review": "Aute mollit proident id veniam occaecat dolore mollit dolore nostrud."
    }]
}

Parsing the JSON data using JSON_TABLE:

select p.product_name, j.gender, j.colour,j.brand,j.sizes
from   products p,
       json_table (
         p.product_details, '$'
         columns ( 
             colour varchar2(4000) path '$.colour',
             gender varchar2(4000) path '$.gender',
             brand  varchar2(4000) path '$.brand',
             sizes varchar2(4000) FORMAT JSON path '$.sizes'
))j;
SELECT p.product_name,  j.value
FROM products p,
  json_table ( p.product_details, '$.sizes[*]' 
columns ( value PATH '$' ) 
)j;

For more details please visit: announcing-a-new-sample-schema-customer-orders


Thanks!

Happy Learning! Your feedback would be appreciated!

6 thoughts on “Customer-Orders Sample Schema | Oracle 12c

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out /  Change )

Google photo

You are commenting using your Google account. Log Out /  Change )

Twitter picture

You are commenting using your Twitter account. Log Out /  Change )

Facebook photo

You are commenting using your Facebook account. Log Out /  Change )

Connecting to %s