Oracle示例数据库
精华
小牛编辑
153浏览
2023-03-14
本教程向您介绍Oracle示例数据库,并提供链接供下载。
Oracle示例数据库简介
我们为您提供一个名为 OT 的Oracle示例数据库,它基于全球虚拟公司,销售计算机硬件,包括存储,主板,RAM,视频卡和CPU。
公司保存产品信息,如:名称,描述标准成本,标价,产品线。它还跟踪所有产品的库存信息,包括产品可用的仓库。由于该公司在全球运营,因此在世界各地拥有仓库。
公司记录所有客户信息,包括姓名,地址和网站。 每个客户至少有一个联系人,包括姓名,电子邮件和电话等详细信息。公司还对每位客户设置了信用限额,以限制客户可能欠的金额。
只要客户发出采购订单,就会在数据库中创建具有待处理状态的销售订单。当公司运送订单时,订单状态变成 - 运送。如果客户取消订单,则订单状态将被 - 取消。
除销售信息外,员工数据还记录了一些基本信息,如姓名,电子邮件,电话,职位,经理和雇用日期。
Oracle示例数据库图
以下举例说明示例数据库图表:
表名称 | 描述 | 记录 |
---|---|---|
contact |
存储客户的联系人信息 | 319 条记录 |
countries |
存储国家信息 | 25 条记录 |
customers |
存储客户的信息 | 319 条记录 |
employees |
存储员工的信息 | 107 条记录 |
inventories |
存储产品的库存信息 | 1112 条记录 |
locations |
仓库的地点 | 23 条记录 |
orders |
存储订单主要信息 | 105 条记录 |
order_items |
存储订单行项目 | 665 条记录 |
product_categories |
存储产品类别 | 5 条记录 |
products |
存储产品信息 | 288 条记录 |
regions |
存储公司经营的地区 | 4 条记录 |
warehouses |
存储仓库信息 | 9 条记录 |
下载Oracle示例数据库
以zip文件格式下载以下示例数据库:
下载文件后,然后提取它。该zip文件包含以下的SQL文件:
- 文件:ot_create_user.sql - 用于创建OT用户和授予权限。
- 文件:ot_schema.sql - 用于创建数据库对象,如表,约束等
- 文件:ot_data.sql - 用于将数据加载到表中。
- 文件:ot_drop.sql - 用于删除示例数据库中的所有对象。
以上文件可通过加入 Oracle数据库技术QQ群(175248146),从群文件里找到示例数据库(oraok.com)_11g.v1.zip文件并下载。
以下是用于创建数据库对象的语句。
-- regions
CREATE TABLE regions
(
region_id NUMBER GENERATED BY DEFAULT AS IDENTITY
START WITH 5 PRIMARY KEY,
region_name VARCHAR2( 50 ) NOT NULL
);
-- countries table
CREATE TABLE countries
(
country_id CHAR( 2 ) PRIMARY KEY ,
country_name VARCHAR2( 40 ) NOT NULL,
region_id NUMBER ,
CONSTRAINT fk_countries_regions FOREIGN KEY( region_id ) REFERENCES regions( region_id ) ON DELETE CASCADE
);
-- location
CREATE TABLE locations
(
location_id NUMBER GENERATED BY DEFAULT AS IDENTITY START WITH 24
PRIMARY KEY ,
address VARCHAR2( 255 ) NOT NULL,
postal_code VARCHAR2( 20 ) ,
city VARCHAR2( 50 ) ,
state VARCHAR2( 50 ) ,
country_id CHAR( 2 ) ,
CONSTRAINT fk_locations_countries
FOREIGN KEY( country_id )
REFERENCES countries( country_id )
ON DELETE CASCADE
);
-- warehouses
CREATE TABLE warehouses
(
warehouse_id NUMBER
GENERATED BY DEFAULT AS IDENTITY START WITH 10
PRIMARY KEY,
warehouse_name VARCHAR( 255 ) ,
location_id NUMBER( 12, 0 ),
CONSTRAINT fk_warehouses_locations FOREIGN KEY( location_id ) REFERENCES locations( location_id ) ON DELETE CASCADE
);
-- employees
CREATE TABLE employees
(
employee_id NUMBER
GENERATED BY DEFAULT AS IDENTITY START WITH 108
PRIMARY KEY,
first_name VARCHAR( 255 ) NOT NULL,
last_name VARCHAR( 255 ) NOT NULL,
email VARCHAR( 255 ) NOT NULL,
phone VARCHAR( 50 ) NOT NULL ,
hire_date DATE NOT NULL ,
manager_id NUMBER( 12, 0 ) ,
job_title VARCHAR( 255 ) NOT NULL,
CONSTRAINT fk_employees_manager FOREIGN KEY( manager_id ) REFERENCES employees( employee_id ) ON DELETE CASCADE
);
-- product category
CREATE TABLE product_categories
(
category_id NUMBER
GENERATED BY DEFAULT AS IDENTITY START WITH 6
PRIMARY KEY,
category_name VARCHAR2( 255 ) NOT NULL
);
-- products table
CREATE TABLE products
(
product_id NUMBER
GENERATED BY DEFAULT AS IDENTITY START WITH 289
PRIMARY KEY,
product_name VARCHAR2( 255 ) NOT NULL,
description VARCHAR2( 2000 ) ,
standard_cost NUMBER( 9, 2 ) ,
list_price NUMBER( 9, 2 ) ,
category_id NUMBER NOT NULL ,
CONSTRAINT fk_products_categories FOREIGN KEY( category_id ) REFERENCES product_categories( category_id ) ON DELETE CASCADE
);
-- customers
CREATE TABLE customers
(
customer_id NUMBER
GENERATED BY DEFAULT AS IDENTITY START WITH 320
PRIMARY KEY,
name VARCHAR2( 255 ) NOT NULL,
address VARCHAR2( 255 ) ,
website VARCHAR2( 255 ) ,
credit_limit NUMBER( 8, 2 )
);
-- contacts
CREATE TABLE contacts
(
contact_id NUMBER
GENERATED BY DEFAULT AS IDENTITY START WITH 320
PRIMARY KEY,
first_name VARCHAR2( 255 ) NOT NULL,
last_name VARCHAR2( 255 ) NOT NULL,
email VARCHAR2( 255 ) NOT NULL,
phone VARCHAR2( 20 ) ,
customer_id NUMBER ,
CONSTRAINT fk_contacts_customers FOREIGN KEY( customer_id ) REFERENCES customers( customer_id ) ON DELETE CASCADE
);
-- orders table
CREATE TABLE orders
(
order_id NUMBER
GENERATED BY DEFAULT AS IDENTITY START WITH 106
PRIMARY KEY,
customer_id NUMBER( 6, 0 ) NOT NULL,
status VARCHAR( 20 ) NOT NULL ,
salesman_id NUMBER( 6, 0 ) ,
order_date DATE NOT NULL ,
CONSTRAINT fk_orders_customers FOREIGN KEY( customer_id ) REFERENCES customers( customer_id ) ON DELETE CASCADE,
CONSTRAINT fk_orders_employees FOREIGN KEY( salesman_id ) REFERENCES employees( employee_id ) ON DELETE SET NULL
);
-- order items
CREATE TABLE order_items
(
order_id NUMBER( 12, 0 ) ,
item_id NUMBER( 12, 0 ) ,
product_id NUMBER( 12, 0 ) NOT NULL ,
quantity NUMBER( 8, 2 ) NOT NULL ,
unit_price NUMBER( 8, 2 ) NOT NULL ,
CONSTRAINT pk_order_items PRIMARY KEY( order_id, item_id ),
CONSTRAINT fk_order_items_products FOREIGN KEY( product_id ) REFERENCES products( product_id ) ON DELETE CASCADE,
CONSTRAINT fk_order_items_orders FOREIGN KEY( order_id ) REFERENCES orders( order_id ) ON DELETE CASCADE
);
-- inventories
CREATE TABLE inventories
(
product_id NUMBER( 12, 0 ) ,
warehouse_id NUMBER( 12, 0 ) ,
quantity NUMBER( 8, 0 ) NOT NULL,
CONSTRAINT pk_inventories PRIMARY KEY( product_id, warehouse_id ),
CONSTRAINT fk_inventories_products FOREIGN KEY( product_id ) REFERENCES products( product_id ) ON DELETE CASCADE,
CONSTRAINT fk_inventories_warehouses FOREIGN KEY( warehouse_id ) REFERENCES warehouses( warehouse_id ) ON DELETE CASCADE
);
在本教程中,我们介绍了Oracle示例数据库并展示了如何下载它。现在,您应该准备好在Oracle数据库服务器中创建示例数据库以供接下来的章节中练习。