dbscript Online Help - User contributions [en]

Page index

2012-10-27T13:48:37Z

Bot:

Proj-delete

2012-10-27T13:47:21Z

Bot: New page: {{Template:dbsweb}}

SH.SALES (table)

2011-08-27T09:36:13Z

Bot:

== wikibot ==

{| border="1" cellpadding="5" cellspacing="0" style="border-collapse:collapse"
|- valign="top"
| '''Table
| SH.SALES
|- valign="top"
| '''Description
| facts table, without a primary key; all rows are uniquely identified by the combination of all foreign keys
|}

=== Columns ===
{| border="1" cellpadding="5" cellspacing="0" style="border-collapse:collapse"
|- style="background:silver"
| '''Column
| '''Data Type
| '''Nullable
| '''Default
| '''Description

|- valign="top"
| PROD_ID
| NUMBER
| not null
|
| FK to the products dimension table
|- valign="top"
| CUST_ID
| NUMBER
| not null
|
| FK to the customers dimension table
|- valign="top"
| TIME_ID
| DATE(7)
| not null
|
| FK to the times dimension table
|- valign="top"
| CHANNEL_ID
| NUMBER
| not null
|
| FK to the channels dimension table
|- valign="top"
| PROMO_ID
| NUMBER
| not null
|
| promotion identifier, without FK constraint (intentionally) to show outer join optimization
|- valign="top"
| QUANTITY_SOLD
| NUMBER(10, 2)
| not null
|
| product quantity sold with the transaction
|- valign="top"
| AMOUNT_SOLD
| NUMBER(10, 2)
| not null
|
| invoiced amount to the customer
|}

=== Indexes ===
{| border="1" cellpadding="5" cellspacing="0" style="border-collapse:collapse"
|- style="background:silver"
| '''Index
| '''Type
| '''Columns

|- valign="top"
| SALES_CHANNEL_BIX
|
| CHANNEL_ID
|- valign="top"
| SALES_CUST_BIX
|
| CUST_ID
|- valign="top"
| SALES_PROD_BIX
|
| PROD_ID
|- valign="top"
| SALES_PROMO_BIX
|
| PROMO_ID
|- valign="top"
| SALES_TIME_BIX
|
| TIME_ID
|}

=== Check Constraints ===
{| border="1" cellpadding="5" cellspacing="0" style="border-collapse:collapse"
|- style="background:silver"
| '''Check Constraint
| '''Expression
| '''Description

|- valign="top"
| SYS_C005075
| "PROD_ID" IS NOT NULL
|
|- valign="top"
| SYS_C005076
| "CUST_ID" IS NOT NULL
|
|- valign="top"
| SYS_C005077
| "TIME_ID" IS NOT NULL
|
|- valign="top"
| SYS_C005078
| "CHANNEL_ID" IS NOT NULL
|
|- valign="top"
| SYS_C005079
| "PROMO_ID" IS NOT NULL
|
|- valign="top"
| SYS_C005080
| "QUANTITY_SOLD" IS NOT NULL
|
|- valign="top"
| SYS_C005081
| "AMOUNT_SOLD" IS NOT NULL
|
|}

=== Foreign Keys ===
{| border="1" cellpadding="5" cellspacing="0" style="border-collapse:collapse"
|- style="background:silver"
| '''Relation
| '''Column
| '''Referenced Column

|- valign="top"
| [[SH.CHANNELS_(table)|SH.CHANNELS]]
| CHANNEL_ID
| CHANNEL_ID
|- valign="top"
| [[SH.CUSTOMERS_(table)|SH.CUSTOMERS]]
| CUST_ID
| CUST_ID
|- valign="top"
| [[SH.PRODUCTS_(table)|SH.PRODUCTS]]
| PROD_ID
| PROD_ID
|- valign="top"
| [[SH.PROMOTIONS_(table)|SH.PROMOTIONS]]
| PROMO_ID
| PROMO_ID
|- valign="top"
| [[SH.TIMES_(table)|SH.TIMES]]
| TIME_ID
| TIME_ID
|}

SH.PROFITS (view)

2011-08-27T09:36:10Z

Bot:

== wikibot ==

{| border="1" cellpadding="5" cellspacing="0" style="border-collapse:collapse"
|- valign="top"
| '''View
| SH.PROFITS
|}

=== Source ===
<pre>
CREATE OR REPLACE VIEW SH.PROFITS AS
SELECT
s.channel_id,
s.cust_id,
s.prod_id,
s.promo_id,
s.time_id,
c.unit_cost,
c.unit_price,
s.amount_sold,
s.quantity_sold,
c.unit_cost * s.quantity_sold TOTAL_COST
FROM costs c, sales s
WHERE c.prod_id = s.prod_id
AND c.time_id = s.time_id
AND c.channel_id = s.channel_id
AND c.promo_id = s.promo_id
</pre>

SH.PRODUCTS (table)

2011-08-27T09:36:09Z

Bot:

== wikibot ==

{| border="1" cellpadding="5" cellspacing="0" style="border-collapse:collapse"
|- valign="top"
| '''Table
| SH.PRODUCTS
|- valign="top"
| '''Description
| dimension table
|}

=== Columns ===
{| border="1" cellpadding="5" cellspacing="0" style="border-collapse:collapse"
|- style="background:silver"
| '''Column
| '''Data Type
| '''Nullable
| '''Default
| '''Description

|- valign="top"
| PROD_ID
| NUMBER(6, 0)
| not null
|
| primary key
|- valign="top"
| PROD_NAME
| VARCHAR2(50)
| not null
|
| product name
|- valign="top"
| PROD_DESC
| VARCHAR2(4000)
| not null
|
| product description
|- valign="top"
| PROD_SUBCATEGORY
| VARCHAR2(50)
| not null
|
| product subcategory
|- valign="top"
| PROD_SUBCATEGORY_ID
| NUMBER
| not null
|
|
|- valign="top"
| PROD_SUBCATEGORY_DESC
| VARCHAR2(2000)
| not null
|
| product subcategory description
|- valign="top"
| PROD_CATEGORY
| VARCHAR2(50)
| not null
|
| product category
|- valign="top"
| PROD_CATEGORY_ID
| NUMBER
| not null
|
|
|- valign="top"
| PROD_CATEGORY_DESC
| VARCHAR2(2000)
| not null
|
| product category description
|- valign="top"
| PROD_WEIGHT_CLASS
| NUMBER(3, 0)
| not null
|
| product weight class
|- valign="top"
| PROD_UNIT_OF_MEASURE
| VARCHAR2(20)
| null
|
| product unit of measure
|- valign="top"
| PROD_PACK_SIZE
| VARCHAR2(30)
| not null
|
| product package size
|- valign="top"
| SUPPLIER_ID
| NUMBER(6, 0)
| not null
|
| this column
|- valign="top"
| PROD_STATUS
| VARCHAR2(20)
| not null
|
| product status
|- valign="top"
| PROD_LIST_PRICE
| NUMBER(8, 2)
| not null
|
| product list price
|- valign="top"
| PROD_MIN_PRICE
| NUMBER(8, 2)
| not null
|
| product minimum price
|- valign="top"
| PROD_TOTAL
| VARCHAR2(13)
| not null
|
|
|- valign="top"
| PROD_TOTAL_ID
| NUMBER
| not null
|
|
|- valign="top"
| PROD_SRC_ID
| NUMBER
| null
|
|
|- valign="top"
| PROD_EFF_FROM
| DATE(7)
| null
|
|
|- valign="top"
| PROD_EFF_TO
| DATE(7)
| null
|
|
|- valign="top"
| PROD_VALID
| VARCHAR2(1)
| null
|
|
|}

=== Primary Key ===
{| border="1" cellpadding="5" cellspacing="0" style="border-collapse:collapse"
|- style="background:silver"
| '''Primary Key
| '''Columns
|- valign="top"
| PRODUCTS_PK
| PROD_ID
|}

=== Indexes ===
{| border="1" cellpadding="5" cellspacing="0" style="border-collapse:collapse"
|- style="background:silver"
| '''Index
| '''Type
| '''Columns

|- valign="top"
| PRODUCTS_PROD_CAT_IX
|
| PROD_CATEGORY
|- valign="top"
| PRODUCTS_PROD_STATUS_BIX
|
| PROD_STATUS
|- valign="top"
| PRODUCTS_PROD_SUBCAT_IX
|
| PROD_SUBCATEGORY
|}

=== Check Constraints ===
{| border="1" cellpadding="5" cellspacing="0" style="border-collapse:collapse"
|- style="background:silver"
| '''Check Constraint
| '''Expression
| '''Description

|- valign="top"
| SYS_C005127
| "PROD_ID" IS NOT NULL
|
|- valign="top"
| SYS_C005128
| "PROD_NAME" IS NOT NULL
|
|- valign="top"
| SYS_C005129
| "PROD_DESC" IS NOT NULL
|
|- valign="top"
| SYS_C005130
| "PROD_SUBCATEGORY" IS NOT NULL
|
|- valign="top"
| SYS_C005131
| "PROD_SUBCATEGORY_ID" IS NOT NULL
|
|- valign="top"
| SYS_C005132
| "PROD_SUBCATEGORY_DESC" IS NOT NULL
|
|- valign="top"
| SYS_C005133
| "PROD_CATEGORY" IS NOT NULL
|
|- valign="top"
| SYS_C005134
| "PROD_CATEGORY_ID" IS NOT NULL
|
|- valign="top"
| SYS_C005135
| "PROD_CATEGORY_DESC" IS NOT NULL
|
|- valign="top"
| SYS_C005136
| "PROD_WEIGHT_CLASS" IS NOT NULL
|
|- valign="top"
| SYS_C005137
| "PROD_PACK_SIZE" IS NOT NULL
|
|- valign="top"
| SYS_C005138
| "SUPPLIER_ID" IS NOT NULL
|
|- valign="top"
| SYS_C005139
| "PROD_STATUS" IS NOT NULL
|
|- valign="top"
| SYS_C005140
| "PROD_LIST_PRICE" IS NOT NULL
|
|- valign="top"
| SYS_C005141
| "PROD_MIN_PRICE" IS NOT NULL
|
|- valign="top"
| SYS_C005142
| "PROD_TOTAL" IS NOT NULL
|
|- valign="top"
| SYS_C005143
| "PROD_TOTAL_ID" IS NOT NULL
|
|}

=== Detail Tables ===
{| border="1" cellpadding="5" cellspacing="0" style="border-collapse:collapse"
|- style="background:silver"
| '''Detail Table
| '''Column
| '''Referencing Column

|- valign="top"
| [[SH.COSTS_(table)|SH.COSTS]]
| PROD_ID
| PROD_ID
|- valign="top"
| [[SH.SALES_(table)|SH.SALES]]
| PROD_ID
| PROD_ID
|}

SH.FWEEK PSCAT SALES MV (table)

2011-08-27T09:36:06Z

Bot:

== wikibot ==

{| border="1" cellpadding="5" cellspacing="0" style="border-collapse:collapse"
|- valign="top"
| '''Table
| SH.FWEEK_PSCAT_SALES_MV
|}

=== Columns ===
{| border="1" cellpadding="5" cellspacing="0" style="border-collapse:collapse"
|- style="background:silver"
| '''Column
| '''Data Type
| '''Nullable
| '''Default
| '''Description

|- valign="top"
| WEEK_ENDING_DAY
| DATE(7)
| not null
|
|
|- valign="top"
| PROD_SUBCATEGORY
| VARCHAR2(50)
| not null
|
|
|- valign="top"
| DOLLARS
| NUMBER
| null
|
|
|- valign="top"
| CHANNEL_ID
| NUMBER
| not null
|
|
|- valign="top"
| PROMO_ID
| NUMBER
| not null
|
|
|}

=== Indexes ===
{| border="1" cellpadding="5" cellspacing="0" style="border-collapse:collapse"
|- style="background:silver"
| '''Index
| '''Type
| '''Columns

|- valign="top"
| FW_PSC_S_MV_CHAN_BIX
|
| CHANNEL_ID
|- valign="top"
| FW_PSC_S_MV_PROMO_BIX
|
| PROMO_ID
|- valign="top"
| FW_PSC_S_MV_SUBCAT_BIX
|
| PROD_SUBCATEGORY
|- valign="top"
| FW_PSC_S_MV_WD_BIX
|
| WEEK_ENDING_DAY
|}

=== Check Constraints ===
{| border="1" cellpadding="5" cellspacing="0" style="border-collapse:collapse"
|- style="background:silver"
| '''Check Constraint
| '''Expression
| '''Description

|- valign="top"
| SYS_C005198
| "WEEK_ENDING_DAY" IS NOT NULL
|
|- valign="top"
| SYS_C005199
| "PROD_SUBCATEGORY" IS NOT NULL
|
|- valign="top"
| SYS_C005200
| "CHANNEL_ID" IS NOT NULL
|
|- valign="top"
| SYS_C005201
| "PROMO_ID" IS NOT NULL
|
|}

SH.CUSTOMERS (table)

2011-08-27T09:36:00Z

Bot:

== wikibot ==

{| border="1" cellpadding="5" cellspacing="0" style="border-collapse:collapse"
|- valign="top"
| '''Table
| SH.CUSTOMERS
|- valign="top"
| '''Description
| dimension table
|}

=== Columns ===
{| border="1" cellpadding="5" cellspacing="0" style="border-collapse:collapse"
|- style="background:silver"
| '''Column
| '''Data Type
| '''Nullable
| '''Default
| '''Description

|- valign="top"
| CUST_ID
| NUMBER
| not null
|
| primary key
|- valign="top"
| CUST_FIRST_NAME
| VARCHAR2(20)
| not null
|
| first name of the customer
|- valign="top"
| CUST_LAST_NAME
| VARCHAR2(40)
| not null
|
| last name of the customer
|- valign="top"
| CUST_GENDER
| CHAR(1)
| not null
|
| gender; low cardinality attribute
|- valign="top"
| CUST_YEAR_OF_BIRTH
| NUMBER(4, 0)
| not null
|
| customer year of birth
|- valign="top"
| CUST_MARITAL_STATUS
| VARCHAR2(20)
| null
|
| customer marital status; low cardinality attribute
|- valign="top"
| CUST_STREET_ADDRESS
| VARCHAR2(40)
| not null
|
| customer street address
|- valign="top"
| CUST_POSTAL_CODE
| VARCHAR2(10)
| not null
|
| postal code of the customer
|- valign="top"
| CUST_CITY
| VARCHAR2(30)
| not null
|
| city where the customer lives
|- valign="top"
| CUST_CITY_ID
| NUMBER
| not null
|
|
|- valign="top"
| CUST_STATE_PROVINCE
| VARCHAR2(40)
| not null
|
| customer geography: state or province
|- valign="top"
| CUST_STATE_PROVINCE_ID
| NUMBER
| not null
|
|
|- valign="top"
| COUNTRY_ID
| NUMBER
| not null
|
| foreign key to the countries table (snowflake)
|- valign="top"
| CUST_MAIN_PHONE_NUMBER
| VARCHAR2(25)
| not null
|
| customer main phone number
|- valign="top"
| CUST_INCOME_LEVEL
| VARCHAR2(30)
| null
|
| customer income level
|- valign="top"
| CUST_CREDIT_LIMIT
| NUMBER
| null
|
| customer credit limit
|- valign="top"
| CUST_EMAIL
| VARCHAR2(30)
| null
|
| customer email id
|- valign="top"
| CUST_TOTAL
| VARCHAR2(14)
| not null
|
|
|- valign="top"
| CUST_TOTAL_ID
| NUMBER
| not null
|
|
|- valign="top"
| CUST_SRC_ID
| NUMBER
| null
|
|
|- valign="top"
| CUST_EFF_FROM
| DATE(7)
| null
|
|
|- valign="top"
| CUST_EFF_TO
| DATE(7)
| null
|
|
|- valign="top"
| CUST_VALID
| VARCHAR2(1)
| null
|
|
|}

=== Primary Key ===
{| border="1" cellpadding="5" cellspacing="0" style="border-collapse:collapse"
|- style="background:silver"
| '''Primary Key
| '''Columns
|- valign="top"
| CUSTOMERS_PK
| CUST_ID
|}

=== Indexes ===
{| border="1" cellpadding="5" cellspacing="0" style="border-collapse:collapse"
|- style="background:silver"
| '''Index
| '''Type
| '''Columns

|- valign="top"
| CUSTOMERS_GENDER_BIX
|
| CUST_GENDER
|- valign="top"
| CUSTOMERS_MARITAL_BIX
|
| CUST_MARITAL_STATUS
|- valign="top"
| CUSTOMERS_YOB_BIX
|
| CUST_YEAR_OF_BIRTH
|}

=== Check Constraints ===
{| border="1" cellpadding="5" cellspacing="0" style="border-collapse:collapse"
|- style="background:silver"
| '''Check Constraint
| '''Expression
| '''Description

|- valign="top"
| SYS_C005164
| "CUST_ID" IS NOT NULL
|
|- valign="top"
| SYS_C005165
| "CUST_FIRST_NAME" IS NOT NULL
|
|- valign="top"
| SYS_C005166
| "CUST_LAST_NAME" IS NOT NULL
|
|- valign="top"
| SYS_C005167
| "CUST_GENDER" IS NOT NULL
|
|- valign="top"
| SYS_C005168
| "CUST_YEAR_OF_BIRTH" IS NOT NULL
|
|- valign="top"
| SYS_C005169
| "CUST_STREET_ADDRESS" IS NOT NULL
|
|- valign="top"
| SYS_C005170
| "CUST_POSTAL_CODE" IS NOT NULL
|
|- valign="top"
| SYS_C005171
| "CUST_CITY" IS NOT NULL
|
|- valign="top"
| SYS_C005172
| "CUST_CITY_ID" IS NOT NULL
|
|- valign="top"
| SYS_C005173
| "CUST_STATE_PROVINCE" IS NOT NULL
|
|- valign="top"
| SYS_C005174
| "CUST_STATE_PROVINCE_ID" IS NOT NULL
|
|- valign="top"
| SYS_C005175
| "COUNTRY_ID" IS NOT NULL
|
|- valign="top"
| SYS_C005176
| "CUST_MAIN_PHONE_NUMBER" IS NOT NULL
|
|- valign="top"
| SYS_C005177
| "CUST_TOTAL" IS NOT NULL
|
|- valign="top"
| SYS_C005178
| "CUST_TOTAL_ID" IS NOT NULL
|
|}

=== Foreign Keys ===
{| border="1" cellpadding="5" cellspacing="0" style="border-collapse:collapse"
|- style="background:silver"
| '''Relation
| '''Column
| '''Referenced Column

|- valign="top"
| [[SH.COUNTRIES_(table)|SH.COUNTRIES]]
| COUNTRY_ID
| COUNTRY_ID
|}

=== Detail Tables ===
{| border="1" cellpadding="5" cellspacing="0" style="border-collapse:collapse"
|- style="background:silver"
| '''Detail Table
| '''Column
| '''Referencing Column

|- valign="top"
| [[SH.SALES_(table)|SH.SALES]]
| CUST_ID
| CUST_ID
|}

SH.COSTS (table)

2011-08-27T09:35:58Z

Bot:

== wikibot ==

{| border="1" cellpadding="5" cellspacing="0" style="border-collapse:collapse"
|- valign="top"
| '''Table
| SH.COSTS
|}

=== Columns ===
{| border="1" cellpadding="5" cellspacing="0" style="border-collapse:collapse"
|- style="background:silver"
| '''Column
| '''Data Type
| '''Nullable
| '''Default
| '''Description

|- valign="top"
| PROD_ID
| NUMBER
| not null
|
|
|- valign="top"
| TIME_ID
| DATE(7)
| not null
|
|
|- valign="top"
| PROMO_ID
| NUMBER
| not null
|
|
|- valign="top"
| CHANNEL_ID
| NUMBER
| not null
|
|
|- valign="top"
| UNIT_COST
| NUMBER(10, 2)
| not null
|
|
|- valign="top"
| UNIT_PRICE
| NUMBER(10, 2)
| not null
|
|
|}

=== Indexes ===
{| border="1" cellpadding="5" cellspacing="0" style="border-collapse:collapse"
|- style="background:silver"
| '''Index
| '''Type
| '''Columns

|- valign="top"
| COSTS_PROD_BIX
|
| PROD_ID
|- valign="top"
| COSTS_TIME_BIX
|
| TIME_ID
|}

=== Check Constraints ===
{| border="1" cellpadding="5" cellspacing="0" style="border-collapse:collapse"
|- style="background:silver"
| '''Check Constraint
| '''Expression
| '''Description

|- valign="top"
| SYS_C005082
| "PROD_ID" IS NOT NULL
|
|- valign="top"
| SYS_C005083
| "TIME_ID" IS NOT NULL
|
|- valign="top"
| SYS_C005084
| "PROMO_ID" IS NOT NULL
|
|- valign="top"
| SYS_C005085
| "CHANNEL_ID" IS NOT NULL
|
|- valign="top"
| SYS_C005086
| "UNIT_COST" IS NOT NULL
|
|- valign="top"
| SYS_C005087
| "UNIT_PRICE" IS NOT NULL
|
|}

=== Foreign Keys ===
{| border="1" cellpadding="5" cellspacing="0" style="border-collapse:collapse"
|- style="background:silver"
| '''Relation
| '''Column
| '''Referenced Column

|- valign="top"
| [[SH.CHANNELS_(table)|SH.CHANNELS]]
| CHANNEL_ID
| CHANNEL_ID
|- valign="top"
| [[SH.PRODUCTS_(table)|SH.PRODUCTS]]
| PROD_ID
| PROD_ID
|- valign="top"
| [[SH.PROMOTIONS_(table)|SH.PROMOTIONS]]
| PROMO_ID
| PROMO_ID
|- valign="top"
| [[SH.TIMES_(table)|SH.TIMES]]
| TIME_ID
| TIME_ID
|}

PM.TEXTDOC TAB (type)

2011-08-27T09:35:52Z

Bot:

== wikibot ==

{| border="1" cellpadding="5" cellspacing="0" style="border-collapse:collapse"
|- valign="top"
| '''Type
| PM.TEXTDOC_TAB
|}

=== Source ===
<pre>
CREATE OR REPLACE TYPE "TEXTDOC_TAB" AS TABLE OF textdoc_typ;
</pre>

PM.PRINT MEDIA (table)

2011-08-27T09:35:50Z

Bot:

== wikibot ==

{| border="1" cellpadding="5" cellspacing="0" style="border-collapse:collapse"
|- valign="top"
| '''Table
| PM.PRINT_MEDIA
|}

=== Columns ===
{| border="1" cellpadding="5" cellspacing="0" style="border-collapse:collapse"
|- style="background:silver"
| '''Column
| '''Data Type
| '''Nullable
| '''Default
| '''Description

|- valign="top"
| PRODUCT_ID
| NUMBER(6, 0)
| not null
|
|
|- valign="top"
| AD_ID
| NUMBER(6, 0)
| not null
|
|
|- valign="top"
| AD_COMPOSITE
| BLOB(4000)
| null
|
|
|- valign="top"
| AD_SOURCETEXT
| CLOB(4000)
| null
|
|
|- valign="top"
| AD_FINALTEXT
| CLOB(4000)
| null
|
|
|- valign="top"
| AD_FLTEXTN
| NCLOB(4000)
| null
|
|
|- valign="top"
| AD_TEXTDOCS_NTAB
| PM.TEXTDOC_TAB
| null
|
|
|- valign="top"
| AD_PHOTO
| BLOB(4000)
| null
|
|
|- valign="top"
| AD_GRAPHIC
| BFILE(530)
| null
|
|
|- valign="top"
| AD_HEADER
| PM.ADHEADER_TYP
| null
|
|
|}

=== Primary Key ===
{| border="1" cellpadding="5" cellspacing="0" style="border-collapse:collapse"
|- style="background:silver"
| '''Primary Key
| '''Columns
|- valign="top"
| PRINTMEDIA_PK
| PRODUCT_ID, AD_ID
|}

=== Indexes ===
{| border="1" cellpadding="5" cellspacing="0" style="border-collapse:collapse"
|- style="background:silver"
| '''Index
| '''Type
| '''Columns

|- valign="top"
| SYS_C005057
|
| AD_TEXTDOCS_NTAB
|}

=== Foreign Keys ===
{| border="1" cellpadding="5" cellspacing="0" style="border-collapse:collapse"
|- style="background:silver"
| '''Relation
| '''Column
| '''Referenced Column

|- valign="top"
| [[OE.PRODUCT_INFORMATION_(table)|OE.PRODUCT_INFORMATION]]
| PRODUCT_ID
| PRODUCT_ID
|}

PM.ADHEADER TYP (type)

2011-08-27T09:35:48Z

Bot:

== wikibot ==

{| border="1" cellpadding="5" cellspacing="0" style="border-collapse:collapse"
|- valign="top"
| '''Type
| PM.ADHEADER_TYP
|}

=== Source ===
<pre>
CREATE OR REPLACE TYPE "ADHEADER_TYP" AS OBJECT
( header_name VARCHAR2(256)
, creation_date DATE
, header_text VARCHAR2(1024)
, logo BLOB
);
</pre>

OE.TORONTO INVENTORY (view)

2011-08-27T09:35:33Z

Bot:

== wikibot ==

{| border="1" cellpadding="5" cellspacing="0" style="border-collapse:collapse"
|- valign="top"
| '''View
| OE.TORONTO_INVENTORY
|}

=== Source ===
<pre>
CREATE OR REPLACE VIEW OE.TORONTO_INVENTORY AS
SELECT p.product_id
, p.product_name
, i.quantity_on_hand
FROM inventories i
, warehouses w
, products p
WHERE p.product_id = i.product_id
AND i.warehouse_id = w.warehouse_id
AND w.warehouse_name = 'Toronto'
</pre>

OE.SYDNEY INVENTORY (view)

2011-08-27T09:35:26Z

Bot:

== wikibot ==

{| border="1" cellpadding="5" cellspacing="0" style="border-collapse:collapse"
|- valign="top"
| '''View
| OE.SYDNEY_INVENTORY
|}

=== Source ===
<pre>
CREATE OR REPLACE VIEW OE.SYDNEY_INVENTORY AS
SELECT p.product_id
, p.product_name
, i.quantity_on_hand
FROM inventories i
, warehouses w
, products p
WHERE p.product_id = i.product_id
AND i.warehouse_id = w.warehouse_id
AND w.warehouse_name = 'Sydney'
</pre>

OE.PURCHASEORDERS (table)

2011-08-27T09:35:21Z

Bot:

== wikibot ==

{| border="1" cellpadding="5" cellspacing="0" style="border-collapse:collapse"
|- valign="top"
| '''Table
| OE.PURCHASEORDERS
|}

=== Columns ===
{| border="1" cellpadding="5" cellspacing="0" style="border-collapse:collapse"
|- style="background:silver"
| '''Column
| '''Data Type
| '''Nullable
| '''Default
| '''Description

|- valign="top"
| SYS_NC_ROWINFO$
| SYS.XMLTYPE
| null
|
|
|}

=== Indexes ===
{| border="1" cellpadding="5" cellspacing="0" style="border-collapse:collapse"
|- style="background:silver"
| '''Index
| '''Type
| '''Columns

|- valign="top"
| REFERENCE_IS_UNIQUE
|
|
|- valign="top"
| SYS_C005248
|
|
|}

=== Foreign Keys ===
{| border="1" cellpadding="5" cellspacing="0" style="border-collapse:collapse"
|- style="background:silver"
| '''Relation
| '''Column
| '''Referenced Column

|- valign="top"
| [[_(table)|_(table)]]
|
|
|}

OE.PRODUCTS (view)

2011-08-27T09:35:19Z

Bot:

== wikibot ==

{| border="1" cellpadding="5" cellspacing="0" style="border-collapse:collapse"
|- valign="top"
| '''View
| OE.PRODUCTS
|}

=== Source ===
<pre>
CREATE OR REPLACE VIEW OE.PRODUCTS AS
SELECT i.product_id
, d.language_id
, CASE WHEN d.language_id IS NOT NULL
THEN d.translated_name
ELSE TRANSLATE(i.product_name USING NCHAR_CS)
END AS product_name
, i.category_id
, CASE WHEN d.language_id IS NOT NULL
THEN d.translated_description
ELSE TRANSLATE(i.product_description USING NCHAR_CS)
END AS product_description
, i.weight_class
, i.warranty_period
, i.supplier_id
, i.product_status
, i.list_price
, i.min_price
, i.catalog_url
FROM product_information i
, product_descriptions d
WHERE d.product_id (+) = i.product_id
AND d.language_id (+) = sys_context('USERENV','LANG')
</pre>

OE.PRODUCT PRICES (view)

2011-08-27T09:35:15Z

Bot:

== wikibot ==

{| border="1" cellpadding="5" cellspacing="0" style="border-collapse:collapse"
|- valign="top"
| '''View
| OE.PRODUCT_PRICES
|}

=== Source ===
<pre>
CREATE OR REPLACE VIEW OE.PRODUCT_PRICES AS
SELECT category_id
, COUNT(*) as "#_OF_PRODUCTS"
, MIN(list_price) as low_price
, MAX(list_price) as high_price
FROM product_information
GROUP BY category_id
</pre>

OE.PRODUCT INFORMATION (table)

2011-08-27T09:35:13Z

Bot:

== wikibot ==

{| border="1" cellpadding="5" cellspacing="0" style="border-collapse:collapse"
|- valign="top"
| '''Table
| OE.PRODUCT_INFORMATION
|- valign="top"
| '''Description
| Non-industry-specific data in various categories.
|}

=== Columns ===
{| border="1" cellpadding="5" cellspacing="0" style="border-collapse:collapse"
|- style="background:silver"
| '''Column
| '''Data Type
| '''Nullable
| '''Default
| '''Description

|- valign="top"
| PRODUCT_ID
| NUMBER(6, 0)
| not null
|
| Primary key column.
|- valign="top"
| PRODUCT_NAME
| VARCHAR2(50)
| null
|
|
|- valign="top"
| PRODUCT_DESCRIPTION
| VARCHAR2(2000)
| null
|
| Primary language description corresponding to translated_description in
oe.product_descriptions, added to provide non-NLS text columns for OC views
to accss.
|- valign="top"
| CATEGORY_ID
| NUMBER(2, 0)
| null
|
| Low cardinality column, can be used for bitmap index.
Schema SH uses it as foreign key
|- valign="top"
| WEIGHT_CLASS
| NUMBER(1, 0)
| null
|
| Low cardinality column, can be used for bitmap index.
|- valign="top"
| WARRANTY_PERIOD
| INTERVAL YEAR(2) TO MONTH
| null
|
| INTERVAL YEAER TO MONTH column, low cardinality, can be used for bitmap
index.
|- valign="top"
| SUPPLIER_ID
| NUMBER(6, 0)
| null
|
| Offers possibility of extensions outside Common Schema.
|- valign="top"
| PRODUCT_STATUS
| VARCHAR2(20)
| null
|
| Check constraint. Appropriate for complex rules, such as "All products in
status PRODUCTION must have at least one inventory entry." Also appropriate
for a trigger auditing status change.
|- valign="top"
| LIST_PRICE
| NUMBER(8, 2)
| null
|
|
|- valign="top"
| MIN_PRICE
| NUMBER(8, 2)
| null
|
|
|- valign="top"
| CATALOG_URL
| VARCHAR2(50)
| null
|
|
|}

=== Primary Key ===
{| border="1" cellpadding="5" cellspacing="0" style="border-collapse:collapse"
|- style="background:silver"
| '''Primary Key
| '''Columns
|- valign="top"
| PRODUCT_INFORMATION_PK
| PRODUCT_ID
|}

=== Indexes ===
{| border="1" cellpadding="5" cellspacing="0" style="border-collapse:collapse"
|- style="background:silver"
| '''Index
| '''Type
| '''Columns

|- valign="top"
| PROD_SUPPLIER_IX
|
| SUPPLIER_ID
|}

=== Check Constraints ===
{| border="1" cellpadding="5" cellspacing="0" style="border-collapse:collapse"
|- style="background:silver"
| '''Check Constraint
| '''Expression
| '''Description

|- valign="top"
| PRODUCT_STATUS_LOV
| product_status in ('orderable'
,'planned'
,'under development'
,'obsolete')

|
|}

=== Detail Tables ===
{| border="1" cellpadding="5" cellspacing="0" style="border-collapse:collapse"
|- style="background:silver"
| '''Detail Table
| '''Column
| '''Referencing Column

|- valign="top"
| [[OE.INVENTORIES_(table)|OE.INVENTORIES]]
| PRODUCT_ID
| PRODUCT_ID
|- valign="top"
| [[PM.ONLINE_MEDIA_(table)|PM.ONLINE_MEDIA]]
| PRODUCT_ID
| PRODUCT_ID
|- valign="top"
| [[OE.ORDER_ITEMS_(table)|OE.ORDER_ITEMS]]
| PRODUCT_ID
| PRODUCT_ID
|- valign="top"
| [[OE.PRODUCT_DESCRIPTIONS_(table)|OE.PRODUCT_DESCRIPTIONS]]
| PRODUCT_ID
| PRODUCT_ID
|- valign="top"
| [[PM.PRINT_MEDIA_(table)|PM.PRINT_MEDIA]]
| PRODUCT_ID
| PRODUCT_ID
|}

OE.PHONE LIST TYP (type)

2011-08-27T09:35:10Z

Bot:

== wikibot ==

{| border="1" cellpadding="5" cellspacing="0" style="border-collapse:collapse"
|- valign="top"
| '''Type
| OE.PHONE_LIST_TYP
|}

=== Source ===
<pre>
CREATE OR REPLACE TYPE "PHONE_LIST_TYP" AS VARRAY(5) OF VARCHAR2(25);
</pre>

OE.ORDERS VIEW (view)

2011-08-27T09:35:09Z

Bot:

== wikibot ==

{| border="1" cellpadding="5" cellspacing="0" style="border-collapse:collapse"
|- valign="top"
| '''View
| OE.ORDERS_VIEW
|}

=== Source ===
<pre>
CREATE OR REPLACE VIEW OE.ORDERS_VIEW AS
SELECT
order_id,
TO_DATE(TO_CHAR(order_date,'DD-MON-YY HH:MI:SS'),'DD-MON-YY HH:MI:SS') ORDER_DATE,
order_mode,
customer_id,
order_status,
order_total,
sales_rep_id,
promotion_id
FROM orders
</pre>

OE.ORDERS SEQ (sequence)

2011-08-27T09:35:08Z

Bot:

== wikibot ==

{| border="1" cellpadding="5" cellspacing="0" style="border-collapse:collapse"
|- valign="top"
| '''Sequence
| OE.ORDERS_SEQ
|}

=== Sequence ===
{| border="1" cellpadding="5" cellspacing="0" style="border-collapse:collapse"
|- valign="top"
| Start Value
|
|- valign="top"
| Max. Value
|
|- valign="top"
| Increment
|
|- valign="top"
| Cache Values
|
|- valign="top"
| Cycle
|
|}
<pre>
CREATE SEQUENCE OE.ORDERS_SEQ
INCREMENT BY
START WITH
MAXVALUE
CACHE CYCLE
</pre>

OE.ORDERS (table)

2011-08-27T09:35:06Z

Bot:

== wikibot ==

{| border="1" cellpadding="5" cellspacing="0" style="border-collapse:collapse"
|- valign="top"
| '''Table
| OE.ORDERS
|- valign="top"
| '''Description
| Contains orders entered by a salesperson as well as over the Web.
|}

=== Columns ===
{| border="1" cellpadding="5" cellspacing="0" style="border-collapse:collapse"
|- style="background:silver"
| '''Column
| '''Data Type
| '''Nullable
| '''Default
| '''Description

|- valign="top"
| ORDER_ID
| NUMBER(12, 0)
| not null
|
| PRIMARY KEY column.
|- valign="top"
| ORDER_DATE
| TIMESTAMP(6) WITH LOCAL TIME ZONE
| not null
|
| TIMESTAMP WITH LOCAL TIME ZONE column, NOT NULL constraint.
|- valign="top"
| ORDER_MODE
| VARCHAR2(8)
| null
|
| CHECK constraint.
|- valign="top"
| CUSTOMER_ID
| NUMBER(6, 0)
| not null
|
|
|- valign="top"
| ORDER_STATUS
| NUMBER(2, 0)
| null
|
| 0: Not fully entered, 1: Entered, 2: Canceled - bad credit, -
3: Canceled - by customer, 4: Shipped - whole order, -
5: Shipped - replacement items, 6: Shipped - backlog on items, -
7: Shipped - special delivery, 8: Shipped - billed, 9: Shipped - payment plan,-
10: Shipped - paid
|- valign="top"
| ORDER_TOTAL
| NUMBER(8, 2)
| null
|
| CHECK constraint.
|- valign="top"
| SALES_REP_ID
| NUMBER(6, 0)
| null
|
| References hr.employees.employee_id.
|- valign="top"
| PROMOTION_ID
| NUMBER(6, 0)
| null
|
| Sales promotion ID. Used in SH schema
|}

=== Primary Key ===
{| border="1" cellpadding="5" cellspacing="0" style="border-collapse:collapse"
|- style="background:silver"
| '''Primary Key
| '''Columns
|- valign="top"
| ORDER_PK
| ORDER_ID
|}

=== Indexes ===
{| border="1" cellpadding="5" cellspacing="0" style="border-collapse:collapse"
|- style="background:silver"
| '''Index
| '''Type
| '''Columns

|- valign="top"
| ORD_CUSTOMER_IX
|
| CUSTOMER_ID
|- valign="top"
| ORD_ORDER_DATE_IX
|
| ORDER_DATE
|- valign="top"
| ORD_SALES_REP_IX
|
| SALES_REP_ID
|}

=== Check Constraints ===
{| border="1" cellpadding="5" cellspacing="0" style="border-collapse:collapse"
|- style="background:silver"
| '''Check Constraint
| '''Expression
| '''Description

|- valign="top"
| ORDER_CUSTOMER_ID_NN
| "CUSTOMER_ID" IS NOT NULL
|
|- valign="top"
| ORDER_DATE_NN
| "ORDER_DATE" IS NOT NULL
|
|- valign="top"
| ORDER_MODE_LOV
| order_mode in ('direct','online')
|
|- valign="top"
| ORDER_TOTAL_MIN
| order_total >= 0
|
|}

=== Foreign Keys ===
{| border="1" cellpadding="5" cellspacing="0" style="border-collapse:collapse"
|- style="background:silver"
| '''Relation
| '''Column
| '''Referenced Column

|- valign="top"
| [[OE.CUSTOMERS_(table)|OE.CUSTOMERS]]
| CUSTOMER_ID
| CUSTOMER_ID
|- valign="top"
| [[HR.EMPLOYEES_(table)|HR.EMPLOYEES]]
| SALES_REP_ID
| EMPLOYEE_ID
|}

=== Detail Tables ===
{| border="1" cellpadding="5" cellspacing="0" style="border-collapse:collapse"
|- style="background:silver"
| '''Detail Table
| '''Column
| '''Referencing Column

|- valign="top"
| [[OE.ORDER_ITEMS_(table)|OE.ORDER_ITEMS]]
| ORDER_ID
| ORDER_ID
|}

OE.ORDER ITEMS (table)

2011-08-27T09:35:02Z

Bot:

== wikibot ==

{| border="1" cellpadding="5" cellspacing="0" style="border-collapse:collapse"
|- valign="top"
| '''Table
| OE.ORDER_ITEMS
|- valign="top"
| '''Description
| Example of many-to-many resolution.
|}

=== Columns ===
{| border="1" cellpadding="5" cellspacing="0" style="border-collapse:collapse"
|- style="background:silver"
| '''Column
| '''Data Type
| '''Nullable
| '''Default
| '''Description

|- valign="top"
| ORDER_ID
| NUMBER(12, 0)
| not null
|
| Part of concatenated primary key, references orders.order_id.
|- valign="top"
| LINE_ITEM_ID
| NUMBER(3, 0)
| not null
|
| Part of concatenated primary key.
|- valign="top"
| PRODUCT_ID
| NUMBER(6, 0)
| not null
|
| References product_information.product_id.
|- valign="top"
| UNIT_PRICE
| NUMBER(8, 2)
| null
|
|
|- valign="top"
| QUANTITY
| NUMBER(8, 0)
| null
|
|
|}

=== Primary Key ===
{| border="1" cellpadding="5" cellspacing="0" style="border-collapse:collapse"
|- style="background:silver"
| '''Primary Key
| '''Columns
|- valign="top"
| ORDER_ITEMS_PK
| ORDER_ID, LINE_ITEM_ID
|}

=== Indexes ===
{| border="1" cellpadding="5" cellspacing="0" style="border-collapse:collapse"
|- style="background:silver"
| '''Index
| '''Type
| '''Columns

|- valign="top"
| ITEM_ORDER_IX
|
| ORDER_ID
|- valign="top"
| ITEM_PRODUCT_IX
|
| PRODUCT_ID
|- valign="top"
| ORDER_ITEMS_UK
|
| ORDER_ID, PRODUCT_ID
|}

=== Check Constraints ===
{| border="1" cellpadding="5" cellspacing="0" style="border-collapse:collapse"
|- style="background:silver"
| '''Check Constraint
| '''Expression
| '''Description

|- valign="top"
| SYS_C005039
| "LINE_ITEM_ID" IS NOT NULL
|
|- valign="top"
| SYS_C005040
| "PRODUCT_ID" IS NOT NULL
|
|}

=== Foreign Keys ===
{| border="1" cellpadding="5" cellspacing="0" style="border-collapse:collapse"
|- style="background:silver"
| '''Relation
| '''Column
| '''Referenced Column

|- valign="top"
| [[OE.ORDERS_(table)|OE.ORDERS]]
| ORDER_ID
| ORDER_ID
|- valign="top"
| [[OE.PRODUCT_INFORMATION_(table)|OE.PRODUCT_INFORMATION]]
| PRODUCT_ID
| PRODUCT_ID
|}

OE.OC PRODUCT INFORMATION (view)

2011-08-27T09:34:58Z

Bot:

== wikibot ==

{| border="1" cellpadding="5" cellspacing="0" style="border-collapse:collapse"
|- valign="top"
| '''View
| OE.OC_PRODUCT_INFORMATION
|}

=== Source ===
<pre>
CREATE OR REPLACE VIEW OE.OC_PRODUCT_INFORMATION AS
SELECT p.product_id, p.product_name, p.product_description, p.category_id,
p.weight_class, p.warranty_period, p.supplier_id, p.product_status,
p.list_price, p.min_price, p.catalog_url,
CAST(MULTISET(SELECT i.product_id,i.warehouse,i.quantity_on_hand
FROM oc_inventories i
WHERE p.product_id=i.product_id)
AS inventory_list_typ)
FROM product_information p
</pre>

OE.OC ORDERS (view)

2011-08-27T09:34:57Z

Bot:

== wikibot ==

{| border="1" cellpadding="5" cellspacing="0" style="border-collapse:collapse"
|- valign="top"
| '''View
| OE.OC_ORDERS
|}

=== Source ===
<pre>
CREATE OR REPLACE VIEW OE.OC_ORDERS AS
SELECT o.order_id, o.order_mode,MAKE_REF(oc_customers,o.customer_id),
o.order_status,o.order_total,o.sales_rep_id,
CAST(MULTISET(SELECT l.order_id,l.line_item_id,l.unit_price,l.quantity,
make_ref(oc_product_information,l.product_id)
FROM order_items l
WHERE o.order_id = l.order_id)
AS order_item_list_typ)
FROM orders o
</pre>

OE.OC INVENTORIES (view)

2011-08-27T09:34:56Z

Bot:

== wikibot ==

{| border="1" cellpadding="5" cellspacing="0" style="border-collapse:collapse"
|- valign="top"
| '''View
| OE.OC_INVENTORIES
|}

=== Source ===
<pre>
CREATE OR REPLACE VIEW OE.OC_INVENTORIES AS
SELECT i.product_id,
warehouse_typ(w.warehouse_id, w.warehouse_name, w.location_id),
i.quantity_on_hand
FROM inventories i, warehouses w
WHERE i.warehouse_id=w.warehouse_id
</pre>

OE.OC CUSTOMERS (view)

2011-08-27T09:34:55Z

Bot:

== wikibot ==

{| border="1" cellpadding="5" cellspacing="0" style="border-collapse:collapse"
|- valign="top"
| '''View
| OE.OC_CUSTOMERS
|}

=== Source ===
<pre>
CREATE OR REPLACE VIEW OE.OC_CUSTOMERS AS
SELECT c.customer_id, c.cust_first_name, c.cust_last_name, c.cust_address,
c.phone_numbers,c.nls_language,c.nls_territory,c.credit_limit,
c.cust_email,
CAST(MULTISET(SELECT o.order_id, o.order_mode,
MAKE_REF(oc_customers,o.customer_id),
o.order_status,
o.order_total,o.sales_rep_id,
CAST(MULTISET(SELECT l.order_id,l.line_item_id,
l.unit_price,l.quantity,
MAKE_REF(oc_product_information,
l.product_id)
FROM order_items l
WHERE o.order_id = l.order_id)
AS order_item_list_typ)
FROM orders o
WHERE c.customer_id = o.customer_id)

</pre>

OE.OC CORPORATE CUSTOMERS (view)

2011-08-27T09:34:54Z

Bot:

== wikibot ==

{| border="1" cellpadding="5" cellspacing="0" style="border-collapse:collapse"
|- valign="top"
| '''View
| OE.OC_CORPORATE_CUSTOMERS
|}

=== Source ===
<pre>
CREATE OR REPLACE VIEW OE.OC_CORPORATE_CUSTOMERS AS
SELECT c.customer_id, c.cust_first_name, c.cust_last_name,
c.cust_address, c.phone_numbers,c.nls_language,c.nls_territory,
c.credit_limit, c.cust_email,
CAST(MULTISET(SELECT o.order_id, o.order_mode,
MAKE_REF(oc_customers,o.customer_id),
o.order_status,
o.order_total,o.sales_rep_id,
CAST(MULTISET(SELECT l.order_id,l.line_item_id,
l.unit_price,l.quantity,
make_ref(oc_product_information,
l.product_id)
FROM order_items l
WHERE o.order_id = l.order_id)
AS order_item_list_typ)
FROM orders o
WHERE c.customer_id = o.customer_id)

</pre>

OE.INVENTORIES (table)

2011-08-27T09:34:48Z

Bot:

== wikibot ==

{| border="1" cellpadding="5" cellspacing="0" style="border-collapse:collapse"
|- valign="top"
| '''Table
| OE.INVENTORIES
|- valign="top"
| '''Description
| Tracks availability of products by product_it and warehouse_id.
|}

=== Columns ===
{| border="1" cellpadding="5" cellspacing="0" style="border-collapse:collapse"
|- style="background:silver"
| '''Column
| '''Data Type
| '''Nullable
| '''Default
| '''Description

|- valign="top"
| PRODUCT_ID
| NUMBER(6, 0)
| not null
|
| Part of concatenated primary key, references product_information.product_id.
|- valign="top"
| WAREHOUSE_ID
| NUMBER(3, 0)
| not null
|
| Part of concatenated primary key, references warehouses.warehouse_id.
|- valign="top"
| QUANTITY_ON_HAND
| NUMBER(8, 0)
| not null
|
|
|}

=== Primary Key ===
{| border="1" cellpadding="5" cellspacing="0" style="border-collapse:collapse"
|- style="background:silver"
| '''Primary Key
| '''Columns
|- valign="top"
| INVENTORY_IX
| WAREHOUSE_ID, PRODUCT_ID
|}

=== Indexes ===
{| border="1" cellpadding="5" cellspacing="0" style="border-collapse:collapse"
|- style="background:silver"
| '''Index
| '''Type
| '''Columns

|- valign="top"
| INV_PRODUCT_IX
|
| PRODUCT_ID
|}

=== Check Constraints ===
{| border="1" cellpadding="5" cellspacing="0" style="border-collapse:collapse"
|- style="background:silver"
| '''Check Constraint
| '''Expression
| '''Description

|- valign="top"
| INVENTORY_QOH_NN
| "QUANTITY_ON_HAND" IS NOT NULL
|
|- valign="top"
| INVENTORY_WAREHOUSE_ID_NN
| "WAREHOUSE_ID" IS NOT NULL
|
|}

=== Foreign Keys ===
{| border="1" cellpadding="5" cellspacing="0" style="border-collapse:collapse"
|- style="background:silver"
| '''Relation
| '''Column
| '''Referenced Column

|- valign="top"
| [[OE.PRODUCT_INFORMATION_(table)|OE.PRODUCT_INFORMATION]]
| PRODUCT_ID
| PRODUCT_ID
|- valign="top"
| [[OE.WAREHOUSES_(table)|OE.WAREHOUSES]]
| WAREHOUSE_ID
| WAREHOUSE_ID
|}

OE.DEPTVIEW (view)

2011-08-27T09:34:46Z

Bot:

== wikibot ==

{| border="1" cellpadding="5" cellspacing="0" style="border-collapse:collapse"
|- valign="top"
| '''View
| OE.DEPTVIEW
|}

=== Source ===
<pre>
CREATE OR REPLACE VIEW OE.DEPTVIEW AS
SELECT xmlelement("Department", xmlForest(department_name, location_id),
(SELECT sys_xmlagg(xmlelement("Employee",xmlForest(last_name,
job_id,manager_id,
hire_date,
salary,
commission_pct)
), xmlformat('EmployeeList'))
FROM employees e WHERE e.department_id = d.department_id
)) xml
FROM departments d
</pre>

OE.CUSTOMERS VIEW (view)

2011-08-27T09:34:44Z

Bot:

== wikibot ==

{| border="1" cellpadding="5" cellspacing="0" style="border-collapse:collapse"
|- valign="top"
| '''View
| OE.CUSTOMERS_VIEW
|}

=== Source ===
<pre>
CREATE OR REPLACE VIEW OE.CUSTOMERS_VIEW AS
SELECT
c.customer_id,
c.cust_first_name,
c.cust_last_name,
c.cust_address.street_address street_address,
c.cust_address.postal_code postal_code,
c.cust_address.city city,
c.cust_address.state_province state_province,
co.country_id,
co.country_name,
co.region_id,
c.nls_language,
c.nls_territory,
c.credit_limit,
c.cust_email,
substr(get_phone_number_f(1,phone_numbers),1,25) Primary_Phone_number,
substr(get_phone_number_f(2,phone_numbers),1,25) Phone_number_2,
substr(get_phone_number_f(3,phone_numbers),1,25) Phone_number_3,
substr(get_phone_number_f(4,phone_numbers),1,25) Phone_number_4,
substr(get_phone_number_f(5,phone_numbers),1,25) Phone_number_5,
c.account_mgr_id,
c.cust_geo_location.sdo_gtype location_gtype,
c.cust_geo_location.sdo_srid location_srid,
c.cust_geo_location.sdo_point.x location_x,
c.cust_geo_location.sdo_point.y location_y,
c.cust_geo_location.sdo_point.z location_z
FROM
countries co,
customers c
WHERE
c.cust_address.country_id = co.coun
</pre>

OE.CUSTOMERS (table)

2011-08-27T09:34:43Z

Bot:

== wikibot ==

{| border="1" cellpadding="5" cellspacing="0" style="border-collapse:collapse"
|- valign="top"
| '''Table
| OE.CUSTOMERS
|- valign="top"
| '''Description
| Contains customers data either entered by an employee or by the customer
him/herself over the Web.
|}

=== Columns ===
{| border="1" cellpadding="5" cellspacing="0" style="border-collapse:collapse"
|- style="background:silver"
| '''Column
| '''Data Type
| '''Nullable
| '''Default
| '''Description

|- valign="top"
| CUSTOMER_ID
| NUMBER(6, 0)
| not null
|
| Primary key column.
|- valign="top"
| CUST_FIRST_NAME
| VARCHAR2(20)
| not null
|
| NOT NULL constraint.
|- valign="top"
| CUST_LAST_NAME
| VARCHAR2(20)
| not null
|
| NOT NULL constraint.
|- valign="top"
| CUST_ADDRESS
| OE.CUST_ADDRESS_TYP
| null
|
| Object column of type address_typ.
|- valign="top"
| PHONE_NUMBERS
| OE.PHONE_LIST_TYP
| null
|
| Varray column of type phone_list_typ
|- valign="top"
| NLS_LANGUAGE
| VARCHAR2(3)
| null
|
|
|- valign="top"
| NLS_TERRITORY
| VARCHAR2(30)
| null
|
|
|- valign="top"
| CREDIT_LIMIT
| NUMBER(9, 2)
| null
|
| Check constraint.
|- valign="top"
| CUST_EMAIL
| VARCHAR2(30)
| null
|
|
|- valign="top"
| ACCOUNT_MGR_ID
| NUMBER(6, 0)
| null
|
| References hr.employees.employee_id.
|- valign="top"
| CUST_GEO_LOCATION
| MDSYS.SDO_GEOMETRY
| null
|
| SDO (spatial) column.
|- valign="top"
| DATE_OF_BIRTH
| DATE(7)
| null
|
|
|- valign="top"
| MARITAL_STATUS
| VARCHAR2(20)
| null
|
|
|- valign="top"
| GENDER
| VARCHAR2(1)
| null
|
|
|- valign="top"
| INCOME_LEVEL
| VARCHAR2(20)
| null
|
|
|}

=== Primary Key ===
{| border="1" cellpadding="5" cellspacing="0" style="border-collapse:collapse"
|- style="background:silver"
| '''Primary Key
| '''Columns
|- valign="top"
| CUSTOMERS_PK
| CUSTOMER_ID
|}

=== Indexes ===
{| border="1" cellpadding="5" cellspacing="0" style="border-collapse:collapse"
|- style="background:silver"
| '''Index
| '''Type
| '''Columns

|- valign="top"
| CUST_ACCOUNT_MANAGER_IX
|
| ACCOUNT_MGR_ID
|- valign="top"
| CUST_EMAIL_IX
|
| CUST_EMAIL
|- valign="top"
| CUST_LNAME_IX
|
| CUST_LAST_NAME
|- valign="top"
| CUST_UPPER_NAME_IX
|
|
|}

=== Check Constraints ===
{| border="1" cellpadding="5" cellspacing="0" style="border-collapse:collapse"
|- style="background:silver"
| '''Check Constraint
| '''Expression
| '''Description

|- valign="top"
| CUST_FNAME_NN
| "CUST_FIRST_NAME" IS NOT NULL
|
|- valign="top"
| CUST_LNAME_NN
| "CUST_LAST_NAME" IS NOT NULL
|
|- valign="top"
| CUSTOMER_CREDIT_LIMIT_MAX
| credit_limit <= 5000
|
|- valign="top"
| CUSTOMER_ID_MIN
| customer_id > 0
|
|}

=== Foreign Keys ===
{| border="1" cellpadding="5" cellspacing="0" style="border-collapse:collapse"
|- style="background:silver"
| '''Relation
| '''Column
| '''Referenced Column

|- valign="top"
| [[HR.EMPLOYEES_(table)|HR.EMPLOYEES]]
| ACCOUNT_MGR_ID
| EMPLOYEE_ID
|}

=== Detail Tables ===
{| border="1" cellpadding="5" cellspacing="0" style="border-collapse:collapse"
|- style="background:silver"
| '''Detail Table
| '''Column
| '''Referencing Column

|- valign="top"
| [[OE.ORDERS_(table)|OE.ORDERS]]
| CUSTOMER_ID
| CUSTOMER_ID
|}

OE.CUST ADDRESS TYP (type)

2011-08-27T09:34:41Z

Bot:

== wikibot ==

{| border="1" cellpadding="5" cellspacing="0" style="border-collapse:collapse"
|- valign="top"
| '''Type
| OE.CUST_ADDRESS_TYP
|}

=== Source ===
<pre>
CREATE OR REPLACE TYPE "CUST_ADDRESS_TYP" AS OBJECT
( street_address VARCHAR2(40)
, postal_code VARCHAR2(10)
, city VARCHAR2(30)
, state_province VARCHAR2(10)
, country_id CHAR(2)
);
</pre>

OE.CATEGORIES TAB (table)

2011-08-27T09:34:36Z

Bot:

== wikibot ==

{| border="1" cellpadding="5" cellspacing="0" style="border-collapse:collapse"
|- valign="top"
| '''Table
| OE.CATEGORIES_TAB
|}

=== Columns ===
{| border="1" cellpadding="5" cellspacing="0" style="border-collapse:collapse"
|- style="background:silver"
| '''Column
| '''Data Type
| '''Nullable
| '''Default
| '''Description

|- valign="top"
| CATEGORY_NAME
| VARCHAR2(50)
| null
|
|
|- valign="top"
| CATEGORY_DESCRIPTION
| VARCHAR2(1000)
| null
|
|
|- valign="top"
| CATEGORY_ID
| NUMBER(2, 0)
| not null
|
|
|- valign="top"
| PARENT_CATEGORY_ID
| NUMBER(2, 0)
| null
|
|
|}

=== Primary Key ===
{| border="1" cellpadding="5" cellspacing="0" style="border-collapse:collapse"
|- style="background:silver"
| '''Primary Key
| '''Columns
|- valign="top"
| SYS_C005251
| CATEGORY_ID
|}

=== Indexes ===
{| border="1" cellpadding="5" cellspacing="0" style="border-collapse:collapse"
|- style="background:silver"
| '''Index
| '''Type
| '''Columns

|- valign="top"
| SYS_C005252
|
|
|- valign="top"
| SYS_C005253
|
|
|- valign="top"
| SYS_C005254
|
|
|}

OE.BOMBAY INVENTORY (view)

2011-08-27T09:34:34Z

Bot:

== wikibot ==

{| border="1" cellpadding="5" cellspacing="0" style="border-collapse:collapse"
|- valign="top"
| '''View
| OE.BOMBAY_INVENTORY
|}

=== Source ===
<pre>
CREATE OR REPLACE VIEW OE.BOMBAY_INVENTORY AS
SELECT p.product_id
, p.product_name
, i.quantity_on_hand
FROM inventories i
, warehouses w
, products p
WHERE p.product_id = i.product_id
AND i.warehouse_id = w.warehouse_id
AND w.warehouse_name = 'Bombay'
</pre>

OE.ACCOUNT MANAGERS (view)

2011-08-27T09:34:32Z

Bot:

== wikibot ==

{| border="1" cellpadding="5" cellspacing="0" style="border-collapse:collapse"
|- valign="top"
| '''View
| OE.ACCOUNT_MANAGERS
|}

=== Source ===
<pre>
CREATE OR REPLACE VIEW OE.ACCOUNT_MANAGERS AS
SELECT c.account_mgr_id ACCT_MGR,
cr.region_id REGION,
c.cust_address.country_id COUNTRY,
c.cust_address.state_province PROVINCE,
count(*) NUM_CUSTOMERS
FROM customers c, countries cr
WHERE c.cust_address.country_id = cr.country_id
GROUP BY ROLLUP (c.account_mgr_id,
cr.region_id,
c.cust_address.country_id,
c.cust_address.state_province)
</pre>

IX.ORDERS QUEUETABLE (table)

2011-08-27T09:34:28Z

Bot:

== wikibot ==

{| border="1" cellpadding="5" cellspacing="0" style="border-collapse:collapse"
|- valign="top"
| '''Table
| IX.ORDERS_QUEUETABLE
|}

=== Columns ===
{| border="1" cellpadding="5" cellspacing="0" style="border-collapse:collapse"
|- style="background:silver"
| '''Column
| '''Data Type
| '''Nullable
| '''Default
| '''Description

|- valign="top"
| Q_NAME
| VARCHAR2(30)
| null
|
|
|- valign="top"
| MSGID
| RAW(16)
| not null
|
|
|- valign="top"
| CORRID
| VARCHAR2(128)
| null
|
|
|- valign="top"
| PRIORITY
| NUMBER
| null
|
|
|- valign="top"
| STATE
| NUMBER
| null
|
|
|- valign="top"
| DELAY
| TIMESTAMP(6)
| null
|
|
|- valign="top"
| EXPIRATION
| NUMBER
| null
|
|
|- valign="top"
| TIME_MANAGER_INFO
| TIMESTAMP(6)
| null
|
|
|- valign="top"
| LOCAL_ORDER_NO
| NUMBER
| null
|
|
|- valign="top"
| CHAIN_NO
| NUMBER
| null
|
|
|- valign="top"
| CSCN
| NUMBER
| null
|
|
|- valign="top"
| DSCN
| NUMBER
| null
|
|
|- valign="top"
| ENQ_TIME
| TIMESTAMP(6)
| null
|
|
|- valign="top"
| ENQ_UID
| VARCHAR2(30)
| null
|
|
|- valign="top"
| ENQ_TID
| VARCHAR2(30)
| null
|
|
|- valign="top"
| DEQ_TIME
| TIMESTAMP(6)
| null
|
|
|- valign="top"
| DEQ_UID
| VARCHAR2(30)
| null
|
|
|- valign="top"
| DEQ_TID
| VARCHAR2(30)
| null
|
|
|- valign="top"
| RETRY_COUNT
| NUMBER
| null
|
|
|- valign="top"
| EXCEPTION_QSCHEMA
| VARCHAR2(30)
| null
|
|
|- valign="top"
| EXCEPTION_QUEUE
| VARCHAR2(30)
| null
|
|
|- valign="top"
| STEP_NO
| NUMBER
| null
|
|
|- valign="top"
| RECIPIENT_KEY
| NUMBER
| null
|
|
|- valign="top"
| DEQUEUE_MSGID
| RAW(16)
| null
|
|
|- valign="top"
| SENDER_NAME
| VARCHAR2(30)
| null
|
|
|- valign="top"
| SENDER_ADDRESS
| VARCHAR2(1024)
| null
|
|
|- valign="top"
| SENDER_PROTOCOL
| NUMBER
| null
|
|
|- valign="top"
| USER_DATA
| IX.ORDER_EVENT_TYP
| null
|
|
|- valign="top"
| USER_PROP
| SYS.ANYDATA
| null
|
|
|}

=== Primary Key ===
{| border="1" cellpadding="5" cellspacing="0" style="border-collapse:collapse"
|- style="background:silver"
| '''Primary Key
| '''Columns
|- valign="top"
| SYS_C005059
| MSGID
|}

IX.ORDER EVENT TYP (type)

2011-08-27T09:34:26Z

Bot:

== wikibot ==

{| border="1" cellpadding="5" cellspacing="0" style="border-collapse:collapse"
|- valign="top"
| '''Type
| IX.ORDER_EVENT_TYP
|}

=== Source ===
<pre>
CREATE OR REPLACE TYPE "ORDER_EVENT_TYP" AS OBJECT (
order_id NUMBER(12),
product_id NUMBER(6),
customer_id NUMBER(6),
cust_first_name VARCHAR2(20),
cust_last_name VARCHAR2(20),
order_status NUMBER(2),
delivery_date DATE
);
</pre>

IX.AQ$STREAMS QUEUE TABLE S (view)

2011-08-27T09:34:25Z

Bot:

== wikibot ==

{| border="1" cellpadding="5" cellspacing="0" style="border-collapse:collapse"
|- valign="top"
| '''View
| IX.AQ$STREAMS_QUEUE_TABLE_S
|}

=== Source ===
<pre>
CREATE OR REPLACE VIEW IX.AQ$STREAMS_QUEUE_TABLE_S AS
SELECT queue_name QUEUE, name NAME , address ADDRESS , protocol PROTOCOL, trans_name TRANSFORMATION FROM "AQ$_STREAMS_QUEUE_TABLE_S" s WHERE (bitand(s.subscriber_type, 1) = 1) WITH READ ONLY
</pre>

IX.AQ$STREAMS QUEUE TABLE R (view)

2011-08-27T09:34:24Z

Bot:

== wikibot ==

{| border="1" cellpadding="5" cellspacing="0" style="border-collapse:collapse"
|- valign="top"
| '''View
| IX.AQ$STREAMS_QUEUE_TABLE_R
|}

=== Source ===
<pre>
CREATE OR REPLACE VIEW IX.AQ$STREAMS_QUEUE_TABLE_R AS
SELECT queue_name QUEUE, s.name NAME , address ADDRESS , protocol PROTOCOL, rule_condition RULE, ruleset_name RULE_SET, trans_name TRANSFORMATION FROM "AQ$_STREAMS_QUEUE_TABLE_S" s , sys.all_rules r WHERE (bitand(s.subscriber_type, 1) = 1) AND s.rule_name = r.rule_name and r.rule_owner = 'IX' WITH READ ONLY
</pre>

IX.AQ$ORDERS QUEUETABLE S (view)

2011-08-27T09:34:21Z

Bot:

== wikibot ==

{| border="1" cellpadding="5" cellspacing="0" style="border-collapse:collapse"
|- valign="top"
| '''View
| IX.AQ$ORDERS_QUEUETABLE_S
|}

=== Source ===
<pre>
CREATE OR REPLACE VIEW IX.AQ$ORDERS_QUEUETABLE_S AS
SELECT queue_name QUEUE, name NAME , address ADDRESS , protocol PROTOCOL, trans_name TRANSFORMATION FROM "AQ$_ORDERS_QUEUETABLE_S" s WHERE (bitand(s.subscriber_type, 1) = 1) WITH READ ONLY
</pre>

IX.AQ$ORDERS QUEUETABLE R (view)

2011-08-27T09:34:19Z

Bot:

== wikibot ==

{| border="1" cellpadding="5" cellspacing="0" style="border-collapse:collapse"
|- valign="top"
| '''View
| IX.AQ$ORDERS_QUEUETABLE_R
|}

=== Source ===
<pre>
CREATE OR REPLACE VIEW IX.AQ$ORDERS_QUEUETABLE_R AS
SELECT queue_name QUEUE, s.name NAME , address ADDRESS , protocol PROTOCOL, rule_condition RULE, ruleset_name RULE_SET, trans_name TRANSFORMATION FROM "AQ$_ORDERS_QUEUETABLE_S" s , sys.all_rules r WHERE (bitand(s.subscriber_type, 1) = 1) AND s.rule_name = r.rule_name and r.rule_owner = 'IX' WITH READ ONLY
</pre>

IX.AQ$ STREAMS QUEUE TABLE S (table)

2011-08-27T09:34:15Z

Bot:

== wikibot ==

{| border="1" cellpadding="5" cellspacing="0" style="border-collapse:collapse"
|- valign="top"
| '''Table
| IX.AQ$_STREAMS_QUEUE_TABLE_S
|}

=== Columns ===
{| border="1" cellpadding="5" cellspacing="0" style="border-collapse:collapse"
|- style="background:silver"
| '''Column
| '''Data Type
| '''Nullable
| '''Default
| '''Description

|- valign="top"
| SUBSCRIBER_ID
| NUMBER
| not null
|
|
|- valign="top"
| QUEUE_NAME
| VARCHAR2(30)
| not null
|
|
|- valign="top"
| NAME
| VARCHAR2(30)
| null
|
|
|- valign="top"
| ADDRESS
| VARCHAR2(1024)
| null
|
|
|- valign="top"
| PROTOCOL
| NUMBER
| null
|
|
|- valign="top"
| SUBSCRIBER_TYPE
| NUMBER
| null
|
|
|- valign="top"
| RULE_NAME
| VARCHAR2(30)
| null
|
|
|- valign="top"
| TRANS_NAME
| VARCHAR2(61)
| null
|
|
|- valign="top"
| RULESET_NAME
| VARCHAR2(65)
| null
|
|
|- valign="top"
| NEGATIVE_RULESET_NAME
| VARCHAR2(65)
| null
|
|
|}

=== Primary Key ===
{| border="1" cellpadding="5" cellspacing="0" style="border-collapse:collapse"
|- style="background:silver"
| '''Primary Key
| '''Columns
|- valign="top"
| SYS_C005070
| SUBSCRIBER_ID
|}

=== Check Constraints ===
{| border="1" cellpadding="5" cellspacing="0" style="border-collapse:collapse"
|- style="background:silver"
| '''Check Constraint
| '''Expression
| '''Description

|- valign="top"
| SYS_C005068
| "SUBSCRIBER_ID" IS NOT NULL
|
|- valign="top"
| SYS_C005069
| "QUEUE_NAME" IS NOT NULL
|
|}

IX.AQ$ STREAMS QUEUE TABLE N (sequence)

2011-08-27T09:34:14Z

Bot:

== wikibot ==

{| border="1" cellpadding="5" cellspacing="0" style="border-collapse:collapse"
|- valign="top"
| '''Sequence
| IX.AQ$_STREAMS_QUEUE_TABLE_N
|}

=== Sequence ===
{| border="1" cellpadding="5" cellspacing="0" style="border-collapse:collapse"
|- valign="top"
| Start Value
|
|- valign="top"
| Max. Value
|
|- valign="top"
| Increment
|
|- valign="top"
| Cache Values
|
|- valign="top"
| Cycle
|
|}
<pre>
CREATE SEQUENCE IX.AQ$_STREAMS_QUEUE_TABLE_N
INCREMENT BY
START WITH
MAXVALUE
CACHE CYCLE
</pre>

IX.AQ$ ORDERS QUEUETABLE S (table)

2011-08-27T09:34:07Z

Bot:

== wikibot ==

{| border="1" cellpadding="5" cellspacing="0" style="border-collapse:collapse"
|- valign="top"
| '''Table
| IX.AQ$_ORDERS_QUEUETABLE_S
|}

=== Columns ===
{| border="1" cellpadding="5" cellspacing="0" style="border-collapse:collapse"
|- style="background:silver"
| '''Column
| '''Data Type
| '''Nullable
| '''Default
| '''Description

|- valign="top"
| SUBSCRIBER_ID
| NUMBER
| not null
|
|
|- valign="top"
| QUEUE_NAME
| VARCHAR2(30)
| not null
|
|
|- valign="top"
| NAME
| VARCHAR2(30)
| null
|
|
|- valign="top"
| ADDRESS
| VARCHAR2(1024)
| null
|
|
|- valign="top"
| PROTOCOL
| NUMBER
| null
|
|
|- valign="top"
| SUBSCRIBER_TYPE
| NUMBER
| null
|
|
|- valign="top"
| RULE_NAME
| VARCHAR2(30)
| null
|
|
|- valign="top"
| TRANS_NAME
| VARCHAR2(61)
| null
|
|
|- valign="top"
| RULESET_NAME
| VARCHAR2(65)
| null
|
|
|- valign="top"
| NEGATIVE_RULESET_NAME
| VARCHAR2(65)
| null
|
|
|}

=== Primary Key ===
{| border="1" cellpadding="5" cellspacing="0" style="border-collapse:collapse"
|- style="background:silver"
| '''Primary Key
| '''Columns
|- valign="top"
| SYS_C005062
| SUBSCRIBER_ID
|}

=== Check Constraints ===
{| border="1" cellpadding="5" cellspacing="0" style="border-collapse:collapse"
|- style="background:silver"
| '''Check Constraint
| '''Expression
| '''Description

|- valign="top"
| SYS_C005060
| "SUBSCRIBER_ID" IS NOT NULL
|
|- valign="top"
| SYS_C005061
| "QUEUE_NAME" IS NOT NULL
|
|}

IX.AQ$ ORDERS QUEUETABLE N (sequence)

2011-08-27T09:34:06Z

Bot:

== wikibot ==

{| border="1" cellpadding="5" cellspacing="0" style="border-collapse:collapse"
|- valign="top"
| '''Sequence
| IX.AQ$_ORDERS_QUEUETABLE_N
|}

=== Sequence ===
{| border="1" cellpadding="5" cellspacing="0" style="border-collapse:collapse"
|- valign="top"
| Start Value
|
|- valign="top"
| Max. Value
|
|- valign="top"
| Increment
|
|- valign="top"
| Cache Values
|
|- valign="top"
| Cycle
|
|}
<pre>
CREATE SEQUENCE IX.AQ$_ORDERS_QUEUETABLE_N
INCREMENT BY
START WITH
MAXVALUE
CACHE CYCLE
</pre>

HR.LOCATIONS SEQ (sequence)

2011-08-27T09:33:58Z

Bot:

== wikibot ==

{| border="1" cellpadding="5" cellspacing="0" style="border-collapse:collapse"
|- valign="top"
| '''Sequence
| HR.LOCATIONS_SEQ
|}

=== Sequence ===
{| border="1" cellpadding="5" cellspacing="0" style="border-collapse:collapse"
|- valign="top"
| Start Value
|
|- valign="top"
| Max. Value
|
|- valign="top"
| Increment
|
|- valign="top"
| Cache Values
|
|- valign="top"
| Cycle
|
|}
<pre>
CREATE SEQUENCE HR.LOCATIONS_SEQ
INCREMENT BY
START WITH
MAXVALUE
CACHE CYCLE
</pre>

HR.LOCATIONS (table)

2011-08-27T09:33:57Z

Bot:

== wikibot ==

{| border="1" cellpadding="5" cellspacing="0" style="border-collapse:collapse"
|- valign="top"
| '''Table
| HR.LOCATIONS
|- valign="top"
| '''Description
| Locations table that contains specific address of a specific office,
warehouse, and/or production site of a company. Does not store addresses /
locations of customers. Contains 23 rows; references with the
departments and countries tables.
|}

=== Columns ===
{| border="1" cellpadding="5" cellspacing="0" style="border-collapse:collapse"
|- style="background:silver"
| '''Column
| '''Data Type
| '''Nullable
| '''Default
| '''Description

|- valign="top"
| LOCATION_ID
| NUMBER(4, 0)
| not null
|
| Primary key of locations table
|- valign="top"
| STREET_ADDRESS
| VARCHAR2(40)
| null
|
| Street address of an office, warehouse, or production site of a company.
Contains building number and street name
|- valign="top"
| POSTAL_CODE
| VARCHAR2(12)
| null
|
| Postal code of the location of an office, warehouse, or production site
of a company.
|- valign="top"
| CITY
| VARCHAR2(30)
| not null
|
| A not null column that shows city where an office, warehouse, or
production site of a company is located.
|- valign="top"
| STATE_PROVINCE
| VARCHAR2(25)
| null
|
| State or Province where an office, warehouse, or production site of a
company is located.
|- valign="top"
| COUNTRY_ID
| CHAR(2)
| null
|
| Country where an office, warehouse, or production site of a company is
located. Foreign key to country_id column of the countries table.
|}

=== Primary Key ===
{| border="1" cellpadding="5" cellspacing="0" style="border-collapse:collapse"
|- style="background:silver"
| '''Primary Key
| '''Columns
|- valign="top"
| LOC_ID_PK
| LOCATION_ID
|}

=== Indexes ===
{| border="1" cellpadding="5" cellspacing="0" style="border-collapse:collapse"
|- style="background:silver"
| '''Index
| '''Type
| '''Columns

|- valign="top"
| LOC_CITY_IX
|
| CITY
|- valign="top"
| LOC_COUNTRY_IX
|
| COUNTRY_ID
|- valign="top"
| LOC_STATE_PROVINCE_IX
|
| STATE_PROVINCE
|}

=== Check Constraints ===
{| border="1" cellpadding="5" cellspacing="0" style="border-collapse:collapse"
|- style="background:silver"
| '''Check Constraint
| '''Expression
| '''Description

|- valign="top"
| LOC_CITY_NN
| "CITY" IS NOT NULL
|
|}

=== Foreign Keys ===
{| border="1" cellpadding="5" cellspacing="0" style="border-collapse:collapse"
|- style="background:silver"
| '''Relation
| '''Column
| '''Referenced Column

|- valign="top"
| [[HR.COUNTRIES_(table)|HR.COUNTRIES]]
| COUNTRY_ID
| COUNTRY_ID
|}

=== Detail Tables ===
{| border="1" cellpadding="5" cellspacing="0" style="border-collapse:collapse"
|- style="background:silver"
| '''Detail Table
| '''Column
| '''Referencing Column

|- valign="top"
| [[HR.DEPARTMENTS_(table)|HR.DEPARTMENTS]]
| LOCATION_ID
| LOCATION_ID
|- valign="top"
| [[OE.WAREHOUSES_(table)|OE.WAREHOUSES]]
| LOCATION_ID
| LOCATION_ID
|}

HR.JOB HISTORY (table)

2011-08-27T09:33:53Z

Bot:

== wikibot ==

{| border="1" cellpadding="5" cellspacing="0" style="border-collapse:collapse"
|- valign="top"
| '''Table
| HR.JOB_HISTORY
|- valign="top"
| '''Description
| Table that stores job history of the employees. If an employee
changes departments within the job or changes jobs within the department,
new rows get inserted into this table with old job information of the
employee. Contains a complex primary key: employee_id+start_date.
Contains 25 rows. References with jobs, employees, and departments tables.
|}

=== Columns ===
{| border="1" cellpadding="5" cellspacing="0" style="border-collapse:collapse"
|- style="background:silver"
| '''Column
| '''Data Type
| '''Nullable
| '''Default
| '''Description

|- valign="top"
| EMPLOYEE_ID
| NUMBER(6, 0)
| not null
|
| A not null column in the complex primary key employee_id+start_date.
Foreign key to employee_id column of the employee table
|- valign="top"
| START_DATE
| DATE(7)
| not null
|
| A not null column in the complex primary key employee_id+start_date.
Must be less than the end_date of the job_history table. (enforced by
constraint jhist_date_interval)
|- valign="top"
| END_DATE
| DATE(7)
| not null
|
| Last day of the employee in this job role. A not null column. Must be
greater than the start_date of the job_history table.
(enforced by constraint jhist_date_interval)
|- valign="top"
| JOB_ID
| VARCHAR2(10)
| not null
|
| Job role in which the employee worked in the past; foreign key to
job_id column in the jobs table. A not null column.
|- valign="top"
| DEPARTMENT_ID
| NUMBER(4, 0)
| null
|
| Department id in which the employee worked in the past; foreign key to deparment_id column in the departments table
|}

=== Primary Key ===
{| border="1" cellpadding="5" cellspacing="0" style="border-collapse:collapse"
|- style="background:silver"
| '''Primary Key
| '''Columns
|- valign="top"
| JHIST_EMP_ID_ST_DATE_PK
| EMPLOYEE_ID, START_DATE
|}

=== Indexes ===
{| border="1" cellpadding="5" cellspacing="0" style="border-collapse:collapse"
|- style="background:silver"
| '''Index
| '''Type
| '''Columns

|- valign="top"
| JHIST_DEPARTMENT_IX
|
| DEPARTMENT_ID
|- valign="top"
| JHIST_EMPLOYEE_IX
|
| EMPLOYEE_ID
|- valign="top"
| JHIST_JOB_IX
|
| JOB_ID
|}

=== Check Constraints ===
{| border="1" cellpadding="5" cellspacing="0" style="border-collapse:collapse"
|- style="background:silver"
| '''Check Constraint
| '''Expression
| '''Description

|- valign="top"
| JHIST_DATE_INTERVAL
| end_date > start_date
|
|- valign="top"
| JHIST_EMPLOYEE_NN
| "EMPLOYEE_ID" IS NOT NULL
|
|- valign="top"
| JHIST_END_DATE_NN
| "END_DATE" IS NOT NULL
|
|- valign="top"
| JHIST_JOB_NN
| "JOB_ID" IS NOT NULL
|
|- valign="top"
| JHIST_START_DATE_NN
| "START_DATE" IS NOT NULL
|
|}

=== Foreign Keys ===
{| border="1" cellpadding="5" cellspacing="0" style="border-collapse:collapse"
|- style="background:silver"
| '''Relation
| '''Column
| '''Referenced Column

|- valign="top"
| [[HR.DEPARTMENTS_(table)|HR.DEPARTMENTS]]
| DEPARTMENT_ID
| DEPARTMENT_ID
|- valign="top"
| [[HR.EMPLOYEES_(table)|HR.EMPLOYEES]]
| EMPLOYEE_ID
| EMPLOYEE_ID
|- valign="top"
| [[HR.JOBS_(table)|HR.JOBS]]
| JOB_ID
| JOB_ID
|}

HR.EMPLOYEES SEQ (sequence)

2011-08-27T09:33:52Z

Bot:

== wikibot ==

{| border="1" cellpadding="5" cellspacing="0" style="border-collapse:collapse"
|- valign="top"
| '''Sequence
| HR.EMPLOYEES_SEQ
|}

=== Sequence ===
{| border="1" cellpadding="5" cellspacing="0" style="border-collapse:collapse"
|- valign="top"
| Start Value
|
|- valign="top"
| Max. Value
|
|- valign="top"
| Increment
|
|- valign="top"
| Cache Values
|
|- valign="top"
| Cycle
|
|}
<pre>
CREATE SEQUENCE HR.EMPLOYEES_SEQ
INCREMENT BY
START WITH
MAXVALUE
CACHE CYCLE
</pre>

HR.EMPLOYEES (table)

2011-08-27T09:33:50Z

Bot:

== wikibot ==

{| border="1" cellpadding="5" cellspacing="0" style="border-collapse:collapse"
|- valign="top"
| '''Table
| HR.EMPLOYEES
|- valign="top"
| '''Description
| employees table. Contains 107 rows. References with departments,
jobs, job_history tables. Contains a self reference.
|}

=== Columns ===
{| border="1" cellpadding="5" cellspacing="0" style="border-collapse:collapse"
|- style="background:silver"
| '''Column
| '''Data Type
| '''Nullable
| '''Default
| '''Description

|- valign="top"
| EMPLOYEE_ID
| NUMBER(6, 0)
| not null
|
| Primary key of employees table.
|- valign="top"
| FIRST_NAME
| VARCHAR2(20)
| null
|
| First name of the employee. A not null column.
|- valign="top"
| LAST_NAME
| VARCHAR2(25)
| not null
|
| Last name of the employee. A not null column.
|- valign="top"
| EMAIL
| VARCHAR2(25)
| not null
|
| Email id of the employee
|- valign="top"
| PHONE_NUMBER
| VARCHAR2(20)
| null
|
| Phone number of the employee; includes country code and area code
|- valign="top"
| HIRE_DATE
| DATE(7)
| not null
|
| Date when the employee started on this job. A not null column.
|- valign="top"
| JOB_ID
| VARCHAR2(10)
| not null
|
| Current job of the employee; foreign key to job_id column of the
jobs table. A not null column.
|- valign="top"
| SALARY
| NUMBER(8, 2)
| null
|
| Monthly salary of the employee. Must be greater
than zero (enforced by constraint emp_salary_min)
|- valign="top"
| COMMISSION_PCT
| NUMBER(2, 2)
| null
|
| Commission percentage of the employee; Only employees in sales
department elgible for commission percentage
|- valign="top"
| MANAGER_ID
| NUMBER(6, 0)
| null
|
| Manager id of the employee; has same domain as manager_id in
departments table. Foreign key to employee_id column of employees table.
(useful for reflexive joins and CONNECT BY query)
|- valign="top"
| DEPARTMENT_ID
| NUMBER(4, 0)
| null
|
| Department id where employee works; foreign key to department_id
column of the departments table
|}

=== Primary Key ===
{| border="1" cellpadding="5" cellspacing="0" style="border-collapse:collapse"
|- style="background:silver"
| '''Primary Key
| '''Columns
|- valign="top"
| EMP_EMP_ID_PK
| EMPLOYEE_ID
|}

=== Indexes ===
{| border="1" cellpadding="5" cellspacing="0" style="border-collapse:collapse"
|- style="background:silver"
| '''Index
| '''Type
| '''Columns

|- valign="top"
| EMP_DEPARTMENT_IX
|
| DEPARTMENT_ID
|- valign="top"
| EMP_EMAIL_UK
|
| EMAIL
|- valign="top"
| EMP_JOB_IX
|
| JOB_ID
|- valign="top"
| EMP_MANAGER_IX
|
| MANAGER_ID
|- valign="top"
| EMP_NAME_IX
|
| LAST_NAME, FIRST_NAME
|}

=== Check Constraints ===
{| border="1" cellpadding="5" cellspacing="0" style="border-collapse:collapse"
|- style="background:silver"
| '''Check Constraint
| '''Expression
| '''Description

|- valign="top"
| EMP_EMAIL_NN
| "EMAIL" IS NOT NULL
|
|- valign="top"
| EMP_HIRE_DATE_NN
| "HIRE_DATE" IS NOT NULL
|
|- valign="top"
| EMP_JOB_NN
| "JOB_ID" IS NOT NULL
|
|- valign="top"
| EMP_LAST_NAME_NN
| "LAST_NAME" IS NOT NULL
|
|- valign="top"
| EMP_SALARY_MIN
| salary > 0
|
|}

=== Foreign Keys ===
{| border="1" cellpadding="5" cellspacing="0" style="border-collapse:collapse"
|- style="background:silver"
| '''Relation
| '''Column
| '''Referenced Column

|- valign="top"
| [[HR.DEPARTMENTS_(table)|HR.DEPARTMENTS]]
| DEPARTMENT_ID
| DEPARTMENT_ID
|- valign="top"
| [[HR.JOBS_(table)|HR.JOBS]]
| JOB_ID
| JOB_ID
|- valign="top"
| [[HR.EMPLOYEES_(table)|HR.EMPLOYEES]]
| MANAGER_ID
| EMPLOYEE_ID
|}

=== Detail Tables ===
{| border="1" cellpadding="5" cellspacing="0" style="border-collapse:collapse"
|- style="background:silver"
| '''Detail Table
| '''Column
| '''Referencing Column

|- valign="top"
| [[OE.CUSTOMERS_(table)|OE.CUSTOMERS]]
| EMPLOYEE_ID
| ACCOUNT_MGR_ID
|- valign="top"
| [[HR.DEPARTMENTS_(table)|HR.DEPARTMENTS]]
| EMPLOYEE_ID
| MANAGER_ID
|- valign="top"
| [[HR.EMPLOYEES_(table)|HR.EMPLOYEES]]
| EMPLOYEE_ID
| MANAGER_ID
|- valign="top"
| [[HR.JOB_HISTORY_(table)|HR.JOB_HISTORY]]
| EMPLOYEE_ID
| EMPLOYEE_ID
|- valign="top"
| [[OE.ORDERS_(table)|OE.ORDERS]]
| EMPLOYEE_ID
| SALES_REP_ID
|}