practice_order

Tables (8)

categories

  • category_id — NUMBER — Primary key
  • category_name — VARCHAR2(50) — NOT NULL
  • parent_category_id — NUMBER
  • description — VARCHAR2(200)

customers

  • customer_id — NUMBER — Primary key
  • customer_name — VARCHAR2(100) — NOT NULL
  • email — VARCHAR2(100) — NOT NULL
  • phone — VARCHAR2(20)
  • address — VARCHAR2(200)
  • city — VARCHAR2(50)
  • postal_code — VARCHAR2(10)
  • registration_date — DATE
  • customer_grade — VARCHAR2(10)
  • is_active — CHAR(1)

employees

  • employee_id — NUMBER — Primary key
  • customer_id — NUMBER — Foreign key → customers.customer_id
  • employee_name — VARCHAR2(50) — NOT NULL
  • department — VARCHAR2(30)
  • position — VARCHAR2(30)
  • email — VARCHAR2(100)
  • hire_date — DATE
  • salary — NUMBER(10,2)
  • manager_id — NUMBER
  • is_active — CHAR(1)

inventory_transactions

  • transaction_id — NUMBER — Primary key
  • product_id (product_id2) — NUMBER — Foreign key → products.product_id
  • category_id (category_id2) — NUMBER — Foreign key → products.category_id
  • product_id — NUMBER — NOT NULL
  • transaction_type — VARCHAR2(10) — NOT NULL
  • quantity — NUMBER — NOT NULL
  • transaction_date — DATE
  • reference_type — VARCHAR2(20)
  • reference_id — NUMBER
  • notes — VARCHAR2(200)

order_details

  • order_detail_id — NUMBER — Primary key
  • order_id (order_id2) — NUMBER — Foreign key → orders.order_id
  • employee_id (employee_id2) — NUMBER — Foreign key → orders.employee_id
  • customer_id (customer_id2) — NUMBER — Foreign key → orders.customer_id
  • product_id (product_id2) — NUMBER — Foreign key → orders.product_id
  • category_id (category_id2) — NUMBER — Foreign key → orders.category_id
  • order_id — NUMBER — NOT NULL
  • product_id — NUMBER — NOT NULL
  • quantity — NUMBER — NOT NULL
  • unit_price — NUMBER(10,2) — NOT NULL
  • discount_rate — NUMBER(3,2)
  • line_total — NUMBER(12,2)

orders

  • order_id — NUMBER — Primary key
  • employee_id (employee_id2) — NUMBER — Foreign key → employees.employee_id
  • customer_id (customer_id2) — NUMBER — Foreign key → employees.customer_id
  • product_id — NUMBER — Foreign key → products.product_id
  • category_id (category_id2) — NUMBER — Foreign key → products.category_id
  • customer_id — NUMBER — NOT NULL
  • employee_id — NUMBER
  • order_date — DATE
  • required_date — DATE
  • shipped_date — DATE
  • ship_address — VARCHAR2(200)
  • ship_city — VARCHAR2(50)
  • ship_postal_code — VARCHAR2(10)
  • total_amount — NUMBER(12,2)
  • order_status — VARCHAR2(20)
  • payment_method — VARCHAR2(20)

products

  • product_id — NUMBER — Primary key
  • category_id (category_id2) — NUMBER — Foreign key → categories.category_id
  • product_name — VARCHAR2(100) — NOT NULL
  • category_id — NUMBER — NOT NULL
  • price — NUMBER(10,2) — NOT NULL
  • stock_quantity — NUMBER
  • description — CLOB
  • manufacturer — VARCHAR2(50)
  • created_date — DATE
  • is_active — CHAR(1)

reviews

  • review_id — NUMBER — Primary key
  • customer_id (customer_id2) — NUMBER — Foreign key → customers.customer_id
  • product_id (product_id2) — NUMBER — Foreign key → products.product_id
  • category_id (category_id2) — NUMBER — Foreign key → products.category_id
  • product_id — NUMBER — NOT NULL
  • customer_id — NUMBER — NOT NULL
  • rating — NUMBER(2,1)
  • review_text — CLOB
  • review_date — DATE
  • is_verified_purchase — CHAR(1)