{"id":99,"date":"2017-07-26T14:24:30","date_gmt":"2017-07-26T07:24:30","guid":{"rendered":"https:\/\/oracletutorial.com\/?page_id=99"},"modified":"2025-05-31T05:31:24","modified_gmt":"2025-05-31T12:31:24","slug":"oracle-sample-database","status":"publish","type":"page","link":"https:\/\/www.oracletutorial.com\/getting-started\/oracle-sample-database\/","title":{"rendered":"Oracle Sample Database"},"content":{"rendered":"\n

Summary<\/strong>: This tutorial introduces you to an Oracle sample database and shows you how to load it into the Oracle Database Server.<\/span><\/p>\n\n\n\n

Introduction to the OT Oracle sample database #<\/a><\/h2>\n\n\n\n

We provide you with an Oracle sample database named OT<\/strong>, which is based on a global fictitious company<\/strong> that sells computer hardware, including storage, motherboards, RAM, video cards, and CPUs.<\/p>\n\n\n\n

The company maintains product information, including name, description, standard cost, list price, and product line.<\/p>\n\n\n\n

It also tracks the inventory information for all products, including warehouses where products are available.<\/p>\n\n\n\n

Since the company operates globally, it maintains warehouses in various locations worldwide.<\/p>\n\n\n\n

The company maintains a comprehensive record of all customer information, including name, address, and website. Each customer has at least one dedicated contact person with detailed information, including their name, email address, and phone number.<\/p>\n\n\n\n

The company also places a credit limit on each customer to limit the amount that the customer can owe.<\/p>\n\n\n\n

Whenever a customer issues a purchase order, a sales order is created in the database with the pending status. When the company ships the order, the order status updates to ‘shipped’. If the customer cancels an order, the order status is updated to canceled.<\/p>\n\n\n\n

In addition to sales information, the database includes employee data with detailed information, such as name, email, phone, job title, manager, and hire date.<\/p>\n\n\n\n

Oracle sample database diagram #<\/a><\/h2>\n\n\n\n

The following picture shows the database diagram:<\/p>\n\n\n\n

\"Oracle<\/figure>\n\n\n\n
Table Names<\/th>Description<\/th>Records<\/th><\/tr><\/thead>
 CONTACTS<\/code><\/td>Contact person information of customers<\/td>319 records<\/td><\/tr>
 COUNTRIES<\/code><\/td>Country information<\/td>25 records<\/td><\/tr>
 CUSTOMERS<\/code><\/td>Customer master<\/td>319 records<\/td><\/tr>
 EMPLOYEES<\/code><\/td>Employee master<\/td>107 records<\/td><\/tr>
 INVENTORIES<\/code><\/td>Inventory information of products<\/td>1112 records<\/td><\/tr>
 LOCATIONS<\/code><\/td>Locations of warehouses<\/td>23 records<\/td><\/tr>
 ORDERS<\/code><\/td>Order header information<\/td>105 records<\/td><\/tr>
 ORDER_ITEMS<\/code><\/td>Order line items<\/td>665 records<\/td><\/tr>
 PRODUCT_CATEGORIES<\/code><\/td>Product categories<\/td>5 records<\/td><\/tr>
 PRODUCTS<\/code><\/td>Product information<\/td>288 records<\/td><\/tr>
 REGIONS<\/code><\/td>Regions where the company operates<\/td>4 records<\/td><\/tr>
 WAREHOUSES<\/code><\/td>Warehouse master<\/td>9 records<\/td><\/tr><\/tbody><\/table><\/figure>\n\n\n\n

Download Oracle Sample Database #<\/a><\/h2>\n\n\n\n

You can download the Oracle Sample Database.<\/a><\/p>\n\n\n\n

After downloading the file, you need to extract it to a directory. In the zip file, you’ll find the oracle_sample_database.sql<\/code> file.<\/p>\n\n\n\n

Loading the sample database using SQL Developer #<\/a><\/h2>\n\n\n\n

Step 1. Create a User #<\/a><\/h3>\n\n\n\n

First, launch SQL Developer from your computer.<\/p>\n\n\n\n

Second, connect to the Oracle Database server using the system<\/code> account:<\/p>\n\n\n\n

\"oracle<\/figure>\n\n\n\n

Third, run the following script to create a new user called ot<\/code> and grant privileges:<\/p>\n\n\n

-- create new user<\/span>\nCREATE<\/span> USER<\/span> ot IDENTIFIED<\/span> BY<\/span> oracle<\/span>;\n\n-- grant privileges<\/span>\nGRANT<\/span> CONNECT<\/span>, RESOURCE<\/span>, DBA TO<\/span> ot;<\/code><\/span>Code language:<\/span> SQL (Structured Query Language)<\/span> (<\/span>sql<\/span>)<\/span><\/small><\/pre>\n\n\n
\"run<\/figure>\n\n\n\n
Step 2. Load the sample database #<\/a><\/h3>\n\n\n\n
First, create a new connection and use the new user (ot<\/code>) and password (oracle<\/code>) to connect to the Oracle Database Server:<\/p>\n\n\n\n
\"oracle<\/figure>\n\n\n\n
Second, open the oracle_sample_database.sql<\/code> file and click the Run Script<\/strong> to execute the statements to create tables and populating data:<\/p>\n\n\n\n
\"oracle<\/figure>\n\n\n\n
Third, wait for the script to complete.<\/p>\n\n\n\n
Oracle Sample Database Schema Script #<\/a><\/h2>\n\n\n\n
The following statements create the database objects in the sample database.<\/p>\n\n\n
CREATE<\/span> TABLE<\/span> regions\n  (\n    region_id NUMBER<\/span> GENERATED<\/span> BY<\/span> DEFAULT<\/span> AS<\/span> IDENTITY<\/span>\n    START<\/span> WITH<\/span> 5<\/span> PRIMARY KEY<\/span>,\n    region_name VARCHAR2<\/span>( 50<\/span> ) NOT<\/span> NULL<\/span>\n  );\n-- countries table<\/span>\nCREATE<\/span> TABLE<\/span> countries\n  (\n    country_id   CHAR<\/span>( 2<\/span> ) PRIMARY KEY<\/span>  ,\n    country_name VARCHAR2<\/span>( 40<\/span> ) NOT<\/span> NULL<\/span>,\n    region_id    NUMBER<\/span>                 , -- fk<\/span>\n    CONSTRAINT<\/span> fk_countries_regions FOREIGN<\/span> KEY<\/span>( region_id )\n      REFERENCES<\/span> regions( region_id ) \n      ON<\/span> DELETE<\/span> CASCADE<\/span>\n  );\n\n-- location<\/span>\nCREATE<\/span> TABLE<\/span> locations\n  (\n    location_id NUMBER<\/span> GENERATED<\/span> BY<\/span> DEFAULT<\/span> AS<\/span> IDENTITY<\/span> START<\/span> WITH<\/span> 24<\/span> \n                PRIMARY KEY<\/span>       ,\n    address     VARCHAR2<\/span>( 255<\/span> ) NOT<\/span> NULL<\/span>,\n    postal_code VARCHAR2<\/span>( 20<\/span> )          ,\n    city        VARCHAR2<\/span>( 50<\/span> )          ,\n    state       VARCHAR2<\/span>( 50<\/span> )          ,\n    country_id  CHAR<\/span>( 2<\/span> )               , -- fk<\/span>\n    CONSTRAINT<\/span> fk_locations_countries \n      FOREIGN<\/span> KEY<\/span>( country_id )\n      REFERENCES<\/span> countries( country_id ) \n      ON<\/span> DELETE<\/span> CASCADE<\/span>\n  );\n-- warehouses<\/span>\nCREATE<\/span> TABLE<\/span> warehouses\n  (\n    warehouse_id NUMBER<\/span> \n                 GENERATED<\/span> BY<\/span> DEFAULT<\/span> AS<\/span> IDENTITY<\/span> START<\/span> WITH<\/span> 10<\/span> \n                 PRIMARY KEY<\/span>,\n    warehouse_name VARCHAR<\/span>( 255<\/span> ) ,\n    location_id    NUMBER<\/span>( 12<\/span>, 0<\/span> ), -- fk<\/span>\n    CONSTRAINT<\/span> fk_warehouses_locations \n      FOREIGN<\/span> KEY<\/span>( location_id )\n      REFERENCES<\/span> locations( location_id ) \n      ON<\/span> DELETE<\/span> CASCADE<\/span>\n  );\n-- employees<\/span>\nCREATE<\/span> TABLE<\/span> employees\n  (\n    employee_id NUMBER<\/span> \n                GENERATED<\/span> BY<\/span> DEFAULT<\/span> AS<\/span> IDENTITY<\/span> START<\/span> WITH<\/span> 108<\/span> \n                PRIMARY KEY<\/span>,\n    first_name VARCHAR<\/span>( 255<\/span> ) NOT<\/span> NULL<\/span>,\n    last_name  VARCHAR<\/span>( 255<\/span> ) NOT<\/span> NULL<\/span>,\n    email      VARCHAR<\/span>( 255<\/span> ) NOT<\/span> NULL<\/span>,\n    phone      VARCHAR<\/span>( 50<\/span> ) NOT<\/span> NULL<\/span> ,\n    hire_date  DATE<\/span> NOT<\/span> NULL<\/span>          ,\n    manager_id NUMBER<\/span>( 12<\/span>, 0<\/span> )        , -- fk<\/span>\n    job_title  VARCHAR<\/span>( 255<\/span> ) NOT<\/span> NULL<\/span>,\n    CONSTRAINT<\/span> fk_employees_manager \n        FOREIGN<\/span> KEY<\/span>( manager_id )\n        REFERENCES<\/span> employees( employee_id )\n        ON<\/span> DELETE<\/span> CASCADE<\/span>\n  );\n-- product category<\/span>\nCREATE<\/span> TABLE<\/span> product_categories\n  (\n    category_id NUMBER<\/span> \n                GENERATED<\/span> BY<\/span> DEFAULT<\/span> AS<\/span> IDENTITY<\/span> START<\/span> WITH<\/span> 6<\/span> \n                PRIMARY KEY<\/span>,\n    category_name VARCHAR2<\/span>( 255<\/span> ) NOT<\/span> NULL<\/span>\n  );\n\n-- products table<\/span>\nCREATE<\/span> TABLE<\/span> products\n  (\n    product_id NUMBER<\/span> \n               GENERATED<\/span> BY<\/span> DEFAULT<\/span> AS<\/span> IDENTITY<\/span> START<\/span> WITH<\/span> 289<\/span> \n               PRIMARY KEY<\/span>,\n    product_name  VARCHAR2<\/span>( 255<\/span> ) NOT<\/span> NULL<\/span>,\n    description   VARCHAR2<\/span>( 2000<\/span> )        ,\n    standard_cost NUMBER<\/span>( 9<\/span>, 2<\/span> )          ,\n    list_price    NUMBER<\/span>( 9<\/span>, 2<\/span> )          ,\n    category_id   NUMBER<\/span> NOT<\/span> NULL<\/span>         ,\n    CONSTRAINT<\/span> fk_products_categories \n      FOREIGN<\/span> KEY<\/span>( category_id )\n      REFERENCES<\/span> product_categories( category_id ) \n      ON<\/span> DELETE<\/span> CASCADE<\/span>\n  );\n-- customers<\/span>\nCREATE<\/span> TABLE<\/span> customers\n  (\n    customer_id NUMBER<\/span> \n                GENERATED<\/span> BY<\/span> DEFAULT<\/span> AS<\/span> IDENTITY<\/span> START<\/span> WITH<\/span> 320<\/span> \n                PRIMARY KEY<\/span>,\n    name<\/span>         VARCHAR2<\/span>( 255<\/span> ) NOT<\/span> NULL<\/span>,\n    address      VARCHAR2<\/span>( 255<\/span> )         ,\n    website      VARCHAR2<\/span>( 255<\/span> )         ,\n    credit_limit NUMBER<\/span>( 8<\/span>, 2<\/span> )\n  );\n-- contacts<\/span>\nCREATE<\/span> TABLE<\/span> contacts\n  (\n    contact_id NUMBER<\/span> \n               GENERATED<\/span> BY<\/span> DEFAULT<\/span> AS<\/span> IDENTITY<\/span> START<\/span> WITH<\/span> 320<\/span> \n               PRIMARY KEY<\/span>,\n    first_name  VARCHAR2<\/span>( 255<\/span> ) NOT<\/span> NULL<\/span>,\n    last_name   VARCHAR2<\/span>( 255<\/span> ) NOT<\/span> NULL<\/span>,\n    email       VARCHAR2<\/span>( 255<\/span> ) NOT<\/span> NULL<\/span>,\n    phone       VARCHAR2<\/span>( 20<\/span> )          ,\n    customer_id NUMBER<\/span>                  ,\n    CONSTRAINT<\/span> fk_contacts_customers \n      FOREIGN<\/span> KEY<\/span>( customer_id )\n      REFERENCES<\/span> customers( customer_id ) \n      ON<\/span> DELETE<\/span> CASCADE<\/span>\n  );\n-- orders table<\/span>\nCREATE<\/span> TABLE<\/span> orders\n  (\n    order_id NUMBER<\/span> \n             GENERATED<\/span> BY<\/span> DEFAULT<\/span> AS<\/span> IDENTITY<\/span> START<\/span> WITH<\/span> 106<\/span> \n             PRIMARY KEY<\/span>,\n    customer_id NUMBER<\/span>( 6<\/span>, 0<\/span> ) NOT<\/span> NULL<\/span>, -- fk<\/span>\n    status<\/span>      VARCHAR<\/span>( 20<\/span> ) NOT<\/span> NULL<\/span> ,\n    salesman_id NUMBER<\/span>( 6<\/span>, 0<\/span> )         , -- fk<\/span>\n    order_date  DATE<\/span> NOT<\/span> NULL<\/span>          ,\n    CONSTRAINT<\/span> fk_orders_customers \n      FOREIGN<\/span> KEY<\/span>( customer_id )\n      REFERENCES<\/span> customers( customer_id )\n      ON<\/span> DELETE<\/span> CASCADE<\/span>,\n    CONSTRAINT<\/span> fk_orders_employees \n      FOREIGN<\/span> KEY<\/span>( salesman_id )\n      REFERENCES<\/span> employees( employee_id ) \n      ON<\/span> DELETE<\/span> SET<\/span> NULL<\/span>\n  );\n-- order items<\/span>\nCREATE<\/span> TABLE<\/span> order_items\n  (\n    order_id   NUMBER<\/span>( 12<\/span>, 0<\/span> )                                , -- fk<\/span>\n    item_id    NUMBER<\/span>( 12<\/span>, 0<\/span> )                                ,\n    product_id NUMBER<\/span>( 12<\/span>, 0<\/span> ) NOT<\/span> NULL<\/span>                       , -- fk<\/span>\n    quantity   NUMBER<\/span>( 8<\/span>, 2<\/span> ) NOT<\/span> NULL<\/span>                        ,\n    unit_price NUMBER<\/span>( 8<\/span>, 2<\/span> ) NOT<\/span> NULL<\/span>                        ,\n    CONSTRAINT<\/span> pk_order_items \n      PRIMARY KEY<\/span>( order_id, item_id ),\n    CONSTRAINT<\/span> fk_order_items_products \n      FOREIGN<\/span> KEY<\/span>( product_id )\n      REFERENCES<\/span> products( product_id ) \n      ON<\/span> DELETE<\/span> CASCADE<\/span>,\n    CONSTRAINT<\/span> fk_order_items_orders \n      FOREIGN<\/span> KEY<\/span>( order_id )\n      REFERENCES<\/span> orders( order_id ) \n      ON<\/span> DELETE<\/span> CASCADE<\/span>\n  );\n-- inventories<\/span>\nCREATE<\/span> TABLE<\/span> inventories\n  (\n    product_id   NUMBER<\/span>( 12<\/span>, 0<\/span> )        , -- fk<\/span>\n    warehouse_id NUMBER<\/span>( 12<\/span>, 0<\/span> )        , -- fk<\/span>\n    quantity     NUMBER<\/span>( 8<\/span>, 0<\/span> ) NOT<\/span> NULL<\/span>,\n    CONSTRAINT<\/span> pk_inventories \n      PRIMARY KEY<\/span>( product_id, warehouse_id ),\n    CONSTRAINT<\/span> fk_inventories_products \n      FOREIGN<\/span> KEY<\/span>( product_id )\n      REFERENCES<\/span> products( product_id ) \n      ON<\/span> DELETE<\/span> CASCADE<\/span>,\n    CONSTRAINT<\/span> fk_inventories_warehouses \n      FOREIGN<\/span> KEY<\/span>( warehouse_id )\n      REFERENCES<\/span> warehouses( warehouse_id ) \n      ON<\/span> DELETE<\/span> CASCADE<\/span>\n  );<\/code><\/span>Code language:<\/span> SQL (Structured Query Language)<\/span> (<\/span>sql<\/span>)<\/span><\/small><\/pre>
\n\t
\n\t\t
Was this tutorial helpful?<\/div>\n\t\t
\n\t\t\t\n\t\t\t\t\n\t\t\t\t\t<\/path>\n\t\t\t\t<\/svg>\n\t\t\t\t Yes <\/span>\n\t\t\t<\/button>\n\n\t\t\t\n\t\t\t\t\n\t\t\t\t\t<\/path>\n\t\t\t\t<\/svg>\n\t\t\t\t No <\/span>\n\t\t\t<\/button>\n\t\t<\/div>\n\t<\/header>\n\n\t
\n\t\t
\n\t\t\t
<\/div>\n\t\t\t\n\t\t\t