史上最详细的Oracle 所有版本(本文实例为19c)安装sample schemas ( HR, OE, PM, SH, IX, BI)(12C+)CO, QS(9i+)

本文主要是介绍史上最详细的Oracle 所有版本(本文实例为19c)安装sample schemas ( HR, OE, PM, SH, IX, BI)(12C+)CO, QS(9i+),希望对大家解决编程问题提供一定的参考价值,需要的开发者们随着小编来一起学习吧!

 官方文档(请参考不同的版本对脚本进行下载):

Installing Sample Schemas
https://github.com/oracle-samples/db-sample-schemas/releasesReleases · oracle-samples/db-sample-schemas · GitHub

The schemas are:

  • HR: Human Resources
  • OE: Order Entry
  • PM: Product Media
  • IX: Information Exchange
  • SH: Sales History
  • BI: Business Intelligence
  • CO: Customer Orders
  • QS : Queued Shipping

Step 1: 将 db_sample_schemas-19.2的tar包放入环境中.

https://github.com/oracle-samples/db-sample-schemas/releases/tag/v19c

 Step2 : 进行解压缩

[oracle@oracle-db-19c admin]$ tar -zxvf db-sample-schemas-19.2.tar.gz

Step3: 进入被解压缩的文件夹中.

[oracle@oracle-db-19c admin]$ cd db-sample-schemas-19.2/
[oracle@oracle-db-19c db-sample-schemas-19.2]$ pwd
/u01/app/oracle/product/19.3.0/dbhome_1/rdbms/admin/db-sample-schemas-19.2
[oracle@oracle-db-19c db-sample-schemas-19.2]$ 

Step4: 验证是否安装了perl.(备注可以手动操作Step5,本说明使用perl命令进行路径替换)

[oracle@oracle-db-19c db-sample-schemas-19.2]$ perl -vThis is perl 5, version 26, subversion 3 (v5.26.3) built for x86_64-linux-thread-multi
(with 57 registered patches, see perl -V for more detail)Copyright 1987-2018, Larry WallPerl may be copied only under the terms of either the Artistic License or the
GNU General Public License, which may be found in the Perl 5 source kit.Complete documentation for Perl, including FAQ lists, should be found on
this system using "man perl" or "perldoc perl".  If you have access to the
Internet, point your browser at http://www.perl.org/, the Perl Home Page.[oracle@oracle-db-19c db-sample-schemas-19.2]$ 

Step5: Change all embedded paths to match your working directory

perl -p -i.bak -e 's#__SUB__CWD__#'$(pwd)'#g' *.sql */*.sql */*.dat 
[oracle@oracle-db-19c db-sample-schemas-19.2]$ perl -p -i.bak -e 's#__SUB__CWD__#'$(pwd)'#g' *.sql */*.sql */*.dat 
[oracle@oracle-db-19c db-sample-schemas-19.2]$ 

Step6: Set the Oracle environment(本文忽略Step6)

Skip this step when running on a client machine (one without an Oracle Database installed).

(在客户端机器(没有安装Oracle数据库的机器)上运行时,请跳过此步骤。)

source /usr/local/bin/oraenv

Note: Oracle's sqlldr utility needs to be in $PATH for correct loading of the Product Media (PM) and Sales History (SH) schemas.

Step7 :检查是否Step5的修正结果是否正确
 

[oracle@oracle-db-19c human_resources]$ pwd
/u01/app/oracle/product/19.3.0/dbhome_1/rdbms/admin/db-sample-schemas-19.2/human_resources
[oracle@oracle-db-19c human_resources]$ cat hr_main.sql

HR Human Resources

Step 8: 开始执行HR: Human Resources   sample 脚本.如下所示:

[oracle@oracle-db-19c db-sample-schemas-19.2]$
[oracle@oracle-db-19c db-sample-schemas-19.2]$ sqlplus / as sysdba

SQL*Plus: Release 19.0.0.0.0 - Production on Sat Nov 26 14:22:22 2022
Version 19.3.0.0.0

Copyright (c) 1982, 2019, Oracle.  All rights reserved.


Connected to:
Oracle Database 19c Enterprise Edition Release 19.0.0.0.0 - Production
Version 19.3.0.0.0

SQL> show con_name;

CON_NAME
------------------------------
CDB$ROOT
SQL> alter session set container=PDB1;

Session altered.

SQL> show con_name;

CON_NAME
------------------------------
PDB1
SQL> @/u01/app/oracle/product/19.3.0/dbhome_1/rdbms/admin/db-sample-schemas-19.2/human_resources/hr_main.sql

specify password for HR as parameter 1:
Enter value for 1: hr

specify default tablespeace for HR as parameter 2:
Enter value for 2: users

specify temporary tablespace for HR as parameter 3:
Enter value for 3: temp

specify password for SYS as parameter 4:
Enter value for 4: sys

specify log path as parameter 5:
Enter value for 5: /u01/app/oracle/product/19.3.0/dbhome_1/demo/schema/log/

specify connect string as parameter 6:
Enter value for 6: PDB1

DROP USER hr CASCADE
          *
ERROR at line 1:
ORA-01918: user 'HR' does not exist

User created.


User altered.


User altered.


Grant succeeded.


Grant succeeded.

Step 9: 确认HR sample schema是否创建成功.

SQL> SELECT table_name FROM user_tables;TABLE_NAME
--------------------------------------------------------------------------------
REGIONS
COUNTRIES
LOCATIONS
DEPARTMENTS
JOBS
EMPLOYEES
JOB_HISTORY7 rows selected.SQL> show user;
USER is "HR"
SQL> 

 

OE Order Entry

Step10 开始执行OE: Order Entry sample schema  sample 脚本.如下所示:

[oracle@oracle-db-19c db-sample-schemas-19.2]$ sqlplus / as sysdba

SQL*Plus: Release 19.0.0.0.0 - Production on Sat Nov 26 14:48:03 2022
Version 19.3.0.0.0

Copyright (c) 1982, 2019, Oracle.  All rights reserved.


Connected to:
Oracle Database 19c Enterprise Edition Release 19.0.0.0.0 - Production
Version 19.3.0.0.0

SQL> alter session set container=PDB1;

Session altered.

SQL> show con_name;

CON_NAME
------------------------------
PDB1
SQL> conn hr/hr@PDB1
Connected.
SQL> show user;
USER is "HR"
SQL> conn / as sysdba
Connected.
SQL> show user;
USER is "SYS"
SQL> alter session set container=PDB1;

Session altered.

SQL> show con_name;

CON_NAME
------------------------------
PDB1
SQL> @/u01/app/oracle/product/19.3.0/dbhome_1/rdbms/admin/db-sample-schemas-19.2/order_entry/oe_main.sql

specify password for OE as parameter 1:
Enter value for 1: OE

specify default tablespeace for OE as parameter 2:
Enter value for 2: users

specify temporary tablespace for OE as parameter 3:
Enter value for 3: temp

specify password for HR as parameter 4:
Enter value for 4: HR

specify password for SYS as parameter 5:
Enter value for 5: sys

specify directory path for the data files as parameter 6:
Enter value for 6: /u01/app/oracle/product/19.3.0/dbhome_1/rdbms/admin/db-sample-schemas-19.2/order_entry/

writeable directory path for the log files as parameter 7:
Enter value for 7: /u01/app/oracle/product/19.3.0/dbhome_1/demo/schema/log/

specify version as parameter 8:
Enter value for 8: v3

specify connect string as parameter 9:
Enter value for 9: PDB1


User dropped.

old   1: CREATE USER oe IDENTIFIED BY &pass
new   1: CREATE USER oe IDENTIFIED BY OE

User created.

old   1: ALTER USER oe DEFAULT TABLESPACE &tbs QUOTA UNLIMITED ON &tbs
new   1: ALTER USER oe DEFAULT TABLESPACE users QUOTA UNLIMITED ON users

User altered.

old   1: ALTER USER oe TEMPORARY TABLESPACE &ttbs
new   1: ALTER USER oe TEMPORARY TABLESPACE temp

User altered.


Grant succeeded.


Grant succeeded.


Grant succeeded.


Grant succeeded.


Grant succeeded.

Step 11: 确认OE Order Entry sample schema是否创建成功.

SQL> conn OE/OE@PDB1
Connected.
SQL> show user;
USER is "OE"
SQL> SELECT table_name FROM user_tables;TABLE_NAME
--------------------------------------------------------------------------------
CUSTOMERS
WAREHOUSES
ORDER_ITEMS
ORDERS
INVENTORIES
PRODUCT_INFORMATION
PRODUCT_DESCRIPTIONS
PROMOTIONS
PRODUCT_REF_LIST_NESTEDTAB
SUBCATEGORY_REF_LIST_NESTEDTAB10 rows selected.SQL> 

PM: Product Media

Step12: 开始执行PM: Product Media sample schema  sample 脚本.如下所示:

[oracle@oracle-db-19c db-sample-schemas-19.2]$ sqlplus / as sysdba

SQL*Plus: Release 19.0.0.0.0 - Production on Sat Nov 26 15:22:36 2022
Version 19.3.0.0.0

Copyright (c) 1982, 2019, Oracle.  All rights reserved.


Connected to:
Oracle Database 19c Enterprise Edition Release 19.0.0.0.0 - Production
Version 19.3.0.0.0

SQL> alter session set container=PDB1;

Session altered.

SQL> show con_name

CON_NAME
------------------------------
PDB1
SQL> @/u01/app/oracle/product/19.3.0/dbhome_1/rdbms/admin/db-sample-schemas-19.2/product_media/pm_main.sql

specify password for PM as parameter 1:
Enter value for 1: PM

specify default tablespeace for PM as parameter 2:
Enter value for 2: users

specify temporary tablespace for PM as parameter 3:
Enter value for 3: temp

specify password for OE as parameter 4:
Enter value for 4: OE

specify password for SYS as parameter 5:
Enter value for 5: sys

specify directory path for the PM data files as parameter 6:
Enter value for 6: /u01/app/oracle/product/19.3.0/dbhome_1/rdbms/admin/db-sample-schemas-19.2/product_media/

specify directory path for the PM load log files as parameter 7:
Enter value for 7: /u01/app/oracle/product/19.3.0/dbhome_1/demo/schema/log/

specify work directory path as parameter 8:
Enter value for 8: /u01/app/oracle/product/19.3.0/dbhome_1/rdbms/admin/db-sample-schemas-19.2/product_media/

specify connect string as parameter 9:
Enter value for 9: PDB1


User dropped.

old   1: CREATE USER pm IDENTIFIED BY &pass
new   1: CREATE USER pm IDENTIFIED BY PM

User created.

old   1: ALTER USER pm DEFAULT TABLESPACE &tbs QUOTA UNLIMITED ON &tbs
new   1: ALTER USER pm DEFAULT TABLESPACE users QUOTA UNLIMITED ON users

User altered.

old   1: ALTER USER pm TEMPORARY TABLESPACE &ttbs
new   1: ALTER USER pm TEMPORARY TABLESPACE temp

User altered.


Grant succeeded.


Grant succeeded.

Connected.

Step 13: 确认PM: Product Media sample schema是否创建成功.

SQL> show user;
USER is "PM"
SQL> SELECT table_name FROM user_tables;TABLE_NAME
--------------------------------------------------------------------------------
PRINT_MEDIA1 row selected.SQL> 

IX: Information Exchange

Step14: 开始执行IX: Information Exchange sample schema  sample 脚本.如下所示:

[oracle@oracle-db-19c db-sample-schemas-19.2]$
[oracle@oracle-db-19c db-sample-schemas-19.2]$ sqlplus / as sysdba

SQL*Plus: Release 19.0.0.0.0 - Production on Sat Nov 26 15:28:32 2022
Version 19.3.0.0.0

Copyright (c) 1982, 2019, Oracle.  All rights reserved.


Connected to:
Oracle Database 19c Enterprise Edition Release 19.0.0.0.0 - Production
Version 19.3.0.0.0

SQL> alter session set container=PDB1;

Session altered.

SQL> @/u01/app/oracle/product/19.3.0/dbhome_1/rdbms/admin/db-sample-schemas-19.2/info_exchange/ix_main.sql

specify password for IX as parameter 1:
Enter value for 1: IX

specify default tablespeace for IX as parameter 2:
Enter value for 2: users

specify temporary tablespace for IX as parameter 3:
Enter value for 3: temp

specify password for SYS as parameter 4:
Enter value for 4: SYS

specify path for log files as parameter 5:
Enter value for 5: /u01/app/oracle/product/19.3.0/dbhome_1/demo/schema/log/

specify version as parameter 6:
Enter value for 6: v3

specify connect string as parameter 7:
Enter value for 7: PDB1

dropping user ...
DROP USER ix CASCADE
          *
ERROR at line 1:
ORA-01918: user 'IX' does not exist


creating user ...

old   1: CREATE USER ix IDENTIFIED BY &pass
new   1: CREATE USER ix IDENTIFIED BY IX

User created.

old   1: ALTER USER ix DEFAULT TABLESPACE &tbs QUOTA UNLIMITED ON &tbs
new   1: ALTER USER ix DEFAULT TABLESPACE users QUOTA UNLIMITED ON users

User altered.

old   1: ALTER USER ix TEMPORARY TABLESPACE &ttbs
new   1: ALTER USER ix TEMPORARY TABLESPACE temp

User altered.


Grant succeeded.

Step 15: 确认IX: Information Exchange sample schema是否创建成功.

SQL> conn IX/IX@PDB1
Connected.
SQL> show user;
USER is "IX"
SQL> SELECT table_name FROM user_tables;TABLE_NAME
--------------------------------------------------------------------------------------------------------------------------------
ORDERS_QUEUETABLE
AQ$_ORDERS_QUEUETABLE_S
AQ$_ORDERS_QUEUETABLE_T
AQ$_ORDERS_QUEUETABLE_H
AQ$_ORDERS_QUEUETABLE_L
SYS_IOT_OVER_73345
AQ$_ORDERS_QUEUETABLE_G
AQ$_ORDERS_QUEUETABLE_I8 rows selected.SQL> 

SH: Sales History

Step16 开始执行SH: Sales History sample schema  sample 脚本.如下所示:

[oracle@oracle-db-19c db-sample-schemas-19.2]$ sqlplus / as sysdba

SQL*Plus: Release 19.0.0.0.0 - Production on Sat Nov 26 15:34:59 2022
Version 19.3.0.0.0

Copyright (c) 1982, 2019, Oracle.  All rights reserved.


Connected to:
Oracle Database 19c Enterprise Edition Release 19.0.0.0.0 - Production
Version 19.3.0.0.0

SQL> alter session set container=PDB1;

Session altered.

SQL> @/u01/app/oracle/product/19.3.0/dbhome_1/rdbms/admin/db-sample-schemas-19.2/sales_history/sh_main.sql

specify password for SH as parameter 1:
Enter value for 1: SH

specify default tablespace for SH as parameter 2:
Enter value for 2: users

specify temporary tablespace for SH as parameter 3:
Enter value for 3: temp

specify password for SYS as parameter 4:
Enter value for 4: SYS

specify directory path for the data files as parameter 5:
Enter value for 5: /u01/app/oracle/product/19.3.0/dbhome_1/rdbms/admin/db-sample-schemas-19.2/sales_history/

writeable directory path for the log files as parameter 6:
Enter value for 6: /u01/app/oracle/product/19.3.0/dbhome_1/demo/schema/log/

specify version as parameter 7:
Enter value for 7: v3

specify connect string as parameter 8:
Enter value for 8: PDB1


Session altered.

DROP USER sh CASCADE
          *
ERROR at line 1:
ORA-01918: user 'SH' does not exist


old   1: CREATE USER sh IDENTIFIED BY &pass
new   1: CREATE USER sh IDENTIFIED BY SH

User created.

old   1: ALTER USER sh DEFAULT TABLESPACE &tbs
new   1: ALTER USER sh DEFAULT TABLESPACE users
old   2:  QUOTA UNLIMITED ON &tbs
new   2:  QUOTA UNLIMITED ON users

User altered.

old   1: ALTER USER sh TEMPORARY TABLESPACE &ttbs
new   1: ALTER USER sh TEMPORARY TABLESPACE temp

User altered.


Grant succeeded.


Grant succeeded.


Grant succeeded.

Step 17: 确认SH: Sales History sample schema是否创建成功.

SQL> show user;
USER is "SH"
SQL> SELECT table_name FROM user_tables;TABLE_NAME
--------------------------------------------------------------------------------
SALES
COSTS
TIMES
PRODUCTS
CHANNELS
PROMOTIONS
CUSTOMERS
COUNTRIES
SUPPLEMENTARY_DEMOGRAPHICS
DR$SUP_TEXT_IDX$I
DR$SUP_TEXT_IDX$K
DR$SUP_TEXT_IDX$N
DR$SUP_TEXT_IDX$U
CAL_MONTH_SALES_MV
FWEEK_PSCAT_SALES_MV15 rows selected.SQL> 

BI: Business Intelligence

Step18: 开始执行BI: Business Intelligence sample schema  sample 脚本.如下所示(若Step18无法正常执行,请参照下方手动执行操作):

[oracle@oracle-db-19c db-sample-schemas-19.2]$ sqlplus / as sysdba

SQL*Plus: Release 19.0.0.0.0 - Production on Sat Nov 26 15:57:55 2022
Version 19.3.0.0.0

Copyright (c) 1982, 2019, Oracle.  All rights reserved.


Connected to:
Oracle Database 19c Enterprise Edition Release 19.0.0.0.0 - Production
Version 19.3.0.0.0

SQL> alter session set container=PDB1;

Session altered.

SQL> @/u01/app/oracle/product/19.3.0/dbhome_1/rdbms/admin/db-sample-schemas-19.2/bus_intelligence/bi_main.sql

specify password for BI as parameter 1:
Enter value for 1: bi

specify default tablespeace for BI as parameter 2:
Enter value for 2: users

specify temporary tablespace for BI as parameter 3:
Enter value for 3: temp

specify password for SYS as parameter 4:
Enter value for 4: sys

specify password for OE as parameter 5:
Enter value for 5: oe

specify password for SH as parameter 6:
Enter value for 6: sh

specify log path as parameter 7:
Enter value for 7: /u01/app/oracle/product/19.3.0/dbhome_1/demo/schema/log/

specify version as parameter 8:
Enter value for 8: v3

specify connect string as parameter 9:
Enter value for 9: PDB1

 若手动执行,请打开脚本bi_main.sql,参照如下执行:

[oracle@oracle-db-19c db-sample-schemas-19.2]$ sqlplus / as sysdbaSQL*Plus: Release 19.0.0.0.0 - Production on Sat Nov 26 16:08:36 2022
Version 19.3.0.0.0Copyright (c) 1982, 2019, Oracle.  All rights reserved.Connected to:
Oracle Database 19c Enterprise Edition Release 19.0.0.0.0 - Production
Version 19.3.0.0.0SQL> alter session set container=PDB1;Session altered.SQL> CREATE USER bi IDENTIFIED BY bi;User created.SQL> show user
USER is "SYS"
SQL> DROP USER bi CASCADE;User dropped.SQL> CREATE USER bi IDENTIFIED BY bi;User created.SQL> ALTER USER bi DEFAULT TABLESPACE users QUOTA UNLIMITED ON users;User altered.SQL> ALTER USER bi TEMPORARY TABLESPACE temp;User altered.SQL> GRANT CREATE SESSION       TO bi;Grant succeeded.SQL> GRANT CREATE SYNONYM       TO bi;Grant succeeded.SQL> GRANT CREATE TABLE         TO bi;Grant succeeded.SQL> GRANT CREATE VIEW          TO bi;Grant succeeded.SQL> GRANT CREATE SEQUENCE      TO bi;Grant succeeded.SQL> GRANT CREATE CLUSTER       TO bi;Grant succeeded.SQL> GRANT CREATE DATABASE LINK TO bi;Grant succeeded.SQL> GRANT ALTER SESSION        TO bi;Grant succeeded.SQL> GRANT RESOURCE , UNLIMITED TABLESPACE             TO bi;Grant succeeded.SQL> @/u01/app/oracle/product/19.3.0/dbhome_1/rdbms/admin/db-sample-schemas-19.2/bus_intelligence/bi_oe_pr.sql oe PDB1specify password for oe as parameter 1:specify connect string as parameter 2:Connected.Grant succeeded.Grant succeeded.Grant succeeded.Grant succeeded.Grant succeeded.Grant succeeded.Grant succeeded.Grant succeeded.Grant succeeded.Grant succeeded.Grant succeeded.Grant succeeded.Grant succeeded.Commit complete.SQL> @/u01/app/oracle/product/19.3.0/dbhome_1/rdbms/admin/db-sample-schemas-19.2/bus_intelligence/bi_sh_pr.sql sh PDB1specify password for SH as parameter 1:specify connect string as parameter 2:Connected.Grant succeeded.Grant succeeded.Grant succeeded.Grant succeeded.Grant succeeded.Grant succeeded.Grant succeeded.Grant succeeded.Grant succeeded.Grant succeeded.Grant succeeded.Commit complete.SQL> @/u01/app/oracle/product/19.3.0/dbhome_1/rdbms/admin/db-sample-schemas-19.2/bus_intelligence/bi_views.sql bi PDB1specify password for BI as parameter 1:specify connect string as parameter 2:Connected.Synonym created.Synonym created.Synonym created.Synonym created.Synonym created.Synonym created.Synonym created.Synonym created.Commit complete.SQL>

Step 19: 确认BI: Business Intelligence sample schema是否创建成功.

SQL> SET ECHO ON
SQL> COLUMN TABLE_NAME FORMAT A25
SQL> COLUMN COLUMN_NAME FORMAT A30
SQL> conn bi/bi@PDB1
Connected.
SQL> SELECT COUNT(*) FROM customers;COUNT(*)
----------555001 row selected.SQL> SELECT COUNT(*) FROM products;
SELECT COUNT(*) FROM promotions;
SELECT COUNT(*) FROM sales;COUNT(*)
----------721 row selected.SQL> COUNT(*)
----------5031 row selected.SQL> COUNT(*)
----------9188431 row selected.SQL> SELECT COUNT(*) FROM costs;COUNT(*)
----------01 row selected.SQL> SELECT COUNT(*) FROM sh.cal_month_sales_mv;COUNT(*)
----------481 row selected.SQL> SELECT COUNT(*) FROM sh.fweek_pscat_sales_mv;COUNT(*)
----------112661 row selected.SQL> SELECT COUNT(*) FROM channels;COUNT(*)
----------51 row selected.SQL> SELECT COUNT(*) FROM countries;COUNT(*)
----------231 row selected.SQL> SELECT COUNT(*) FROM times;COUNT(*)
----------18261 row selected.SQL> SET ECHO OFF
SQL> 
SQL> set pagesize 200
SQL> set linesize 200
SQL> 
SQL> 
SQL> select * from tab;TNAME                                                                                                                            TABTYPE        CLUSTERID
-------------------------------------------------------------------------------------------------------------------------------- ------------- ----------
CHANNELS                                                                                                                         SYNONYM
COUNTRIES                                                                                                                        SYNONYM
TIMES                                                                                                                            SYNONYM
COSTS                                                                                                                            SYNONYM
CUSTOMERS                                                                                                                        SYNONYM
PRODUCTS                                                                                                                         SYNONYM
PROMOTIONS                                                                                                                       SYNONYM
SALES                                                                                                                            SYNONYM8 rows selected.SQL> show user;
USER is "BI"
SQL> 

 

CO: Customer Orders (ORACLE 9i sample schema)

Step20: 开始执行CO: Customer Orders sample schema  sample 脚本.如下所示:

[oracle@oracle-db-19c db-sample-schemas-19.2]$ sqlplus / as sysdba

SQL*Plus: Release 19.0.0.0.0 - Production on Sat Nov 26 16:33:34 2022
Version 19.3.0.0.0

Copyright (c) 1982, 2019, Oracle.  All rights reserved.


Connected to:
Oracle Database 19c Enterprise Edition Release 19.0.0.0.0 - Production
Version 19.3.0.0.0

SQL> alter session set container=PDB1;

Session altered.

SQL> @/u01/app/oracle/product/19.3.0/dbhome_1/rdbms/admin/db-sample-schemas-19.2/customer_orders/co_main.sql
Enter value for 1: co
Enter value for 2: PDB1
Enter value for 3: users
Enter value for 4: temp
Dropping user
drop user co
          *
ERROR at line 1:
ORA-01918: user 'CO' does not exist


Creating user

Grant succeeded.


User altered.


User altered.

Connected.
Running DDL
creating tables

Table created.


Comment created.


Comment created.


Comment created.


Comment created.


Table created.


Comment created.


Comment created.


Comment created.


Comment created.


Comment created.


Comment created.


Comment created.


Comment created.


Comment created.


Comment created.


Comment created.


Comment created.


Table created.


Comment created.


Comment created.


Comment created.


Comment created.


Comment created.


Comment created.


Comment created.


Comment created.


Comment created.


Comment created.


Table created.


Comment created.


Comment created.


Comment created.


Comment created.


Comment created.


Comment created.


Table created.


Comment created.


Comment created.


Comment created.


Comment created.


Comment created.


Comment created.

Creating indexes

Index created.


Index created.


Index created.

Creating views

View created.


Comment created.


Comment created.


Comment created.


Comment created.


Comment created.


Comment created.


Comment created.


Comment created.


Comment created.


View created.


Comment created.


Comment created.


Comment created.


Comment created.


Comment created.


Comment created.


Comment created.


Comment created.


Comment created.


View created.


Comment created.


Comment created.


Comment created.


Comment created.


Comment created.


View created.


Comment created.


Comment created.


Comment created.


Comment created.


Comment created.

Running DML
Removing existing data

Table truncated.


0 rows deleted.


0 rows deleted.


0 rows deleted.


0 rows deleted.

Inserting data
INSERTING into CUSTOMERS

PL/SQL procedure successfully completed.

INSERTING into PRODUCTS

PL/SQL procedure successfully completed.

INSERTING into STORES

PL/SQL procedure successfully completed.

INSERTING into ORDERS

PL/SQL procedure successfully completed.

INSERTING into ORDER_ITEMS

PL/SQL procedure successfully completed.


Commit complete.

Resetting identity columns

Table altered.


Table altered.


Table altered.


Table altered.
 

Example queries
Store sales analysisSTORE_NAME                     CONSECUTIVE_DAYS   STORE_MX
------------------------------ ---------------- ----------
Online                                       95         95
Tokyo                                         8          8
Tel Aviv                                      5          5
Bejing                                        4          4
Buenos Aires                                  4          4
Bengaluru                                     3          3
Perth                                         3          3
San Francisco                                 3          3
S??o Paulo                                    3          3
Utrecht                                       3          3
Berlin                                        2          2
Chicago                                       2          2
Johannesburg                                  2          2
Lagos                                         2          2
London                                        2          2
Madrid                                        2          2
Mexico City                                   2          2
Mumbai                                        2          2
New Dehli                                     2          2
New York City                                 2          2
Seattle                                       2          2
Sydney                                        2          2
Bucharest                                     1          123 rows selected.High value customersCUSTOMER_ID NUM_MONTHS TOTAL_VALUE
----------- ---------- -----------3          3      720.927          3      478.639          3      687.7319          3      430.9024          3      579.1749          3      631.5152          3      665.1262          4      882.9963          4      646.2387          3      515.1592          3    1,078.31104          3      757.34111          3      648.26119          3      584.34123          3      515.07131          3      817.67133          4    1,152.00134          3      703.64146          4    1,054.09152          4    1,088.09154          3      793.01160          4      632.68166          4    1,537.74189          3      746.02190          4      948.04194          3      760.11207          3      730.21213          3      734.65214          3      561.19219          3      773.65220          3      470.08239          3      573.00242          3      813.07245          3      492.64253          3      460.20278          3    1,198.37291          3      665.48299          3      541.97306          3      847.44317          3      576.98318          3      711.30330          4      709.53336          3      547.17364          3      656.75374          3      385.08386          3      778.70388          3      746.4847 rows selected.Product rating analysisPRODUCT_NAME                   NUMBER_OF_REVIEWS PRODUCT_MEAN_RATING PRODUCT_MEDIAN_RATING PRODUCT_MODE_RATING PRODUCT_LOWEST_RATING PRODUCT_HIGHEST_RATING
------------------------------ ----------------- ------------------- --------------------- ------------------- --------------------- ----------------------
Women's Trousers (Blue)                        1                  10                    10                  10                    10                     10
Men's Jeans (Grey)                             4                 8.5                   8.5                   7                     7                     10
Girl's Pyjamas (White)                         6                8.33                     8                   7                     7                     10
Women's Coat (Black)                           9                7.67                     8                   8                     5                     10
Boy's Trousers (Blue)                          3                7.67                     8                   6                     6                      9
Women's Shirt (Green)                          9                7.56                     8                   8                     3                     10
Men's Coat (Red)                               1                   7                     7                   7                     7                      7
Boy's Jeans (Black)                            6                   7                   6.5                   5                     5                     10
Girl's Dress (Red)                             7                6.86                     8                   9                     2                     10
Boy's Shorts (Blue)                            6                 6.5                     6                   6                     4                     10
Men's Hoodie (Red)                             5                 6.4                     5                  10                     3                     10
Girl's Coat (Blue)                             8                6.25                     8                   8                     1                     10
Boy's Socks (Grey)                             8                6.25                   6.5                   6                     1                      9
Girl's Trousers (Red)                          5                 6.2                     6                   4                     4                      9
Boy's Coat (Blue)                              5                 6.2                     6                   8                     4                      8
Boy's Pyjamas (Grey)                           2                   6                     6                   5                     5                      7
Girl's Shorts (Green)                          6                   6                     6                   4                     4                      8
Girl's Pyjamas (Black)                         7                5.86                     6                   4                     1                     10
Women's Skirt (Red)                            8                5.75                   6.5                   7                     2                      8
Women's Jacket (Black)                         2                 5.5                   5.5                   5                     5                      6
Women's Sweater (Brown)                        9                5.44                     5                   5                     2                     10
Boy's Shirt (Black)                            9                5.33                     5                   1                     1                     10
Boy's Trousers (White)                        30                 5.3                     5                   2                     1                     10
Boy's Trousers (Black)                         6                5.17                     5                   3                     3                      8
Boy's Sweater (Red)                            7                5.14                     5                   7                     2                      8
Women's Jacket (Blue)                          7                5.14                     6                   1                     1                      9
Boy's Socks (White)                            9                   5                     3                   8                     1                     10
Women's Socks (Grey)                           1                   5                     5                   5                     5                      5
Girl's Hoodie (White)                          8                4.88                     4                   3                     2                     10
Girl's Pyjamas (Red)                           8                4.88                     4                   2                     2                      9
Boy's Hoodie (Grey)                            9                4.67                     4                   4                     1                     10
Women's Jeans (Brown)                          6                 4.5                   3.5                   2                     1                      9
Women's Dress (Black)                          4                4.25                     4                   4                     4                      5
Men's Shorts (Black)                           4                4.25                   4.5                   1                     1                      7
Men's Pyjamas (Blue)                           6                4.17                     4                   1                     1                      8
Women's Jeans (Red)                            3                   4                     1                   1                     1                     10
Girl's Jeans (Grey)                            6                   4                   3.5                   1                     1                      9
Boy's Sweater (Green)                          2                 3.5                   3.5                   2                     2                      5
Women's Skirt (Brown)                          5                 3.4                     3                   3                     1                      8
Boy's Socks (Black)                            8                3.13                     2                   2                     2                      8
Women's Pyjamas (Grey)                         6                2.67                     3                   1                     1                      4
TOTAL                                        261                5.54                     6                   8                     1                     1042 rows selected.Most popular products by volumePRODUCT_NAME                   NUMBER_OF_ORDERS TOTAL_VALUE REVENUE_RANK
------------------------------ ---------------- ----------- ------------
Girl's Trousers (Red)                       148   15,794.76            1
Boy's Hoodie (Grey)                         100    3,754.08           35
Men's Pyjamas (Blue)                        100    3,274.61           36
Men's Coat (Red)                             98    4,230.30           31
Boy's Socks (White)                          98    3,081.12           38Most popular products by valuePRODUCT_NAME                   NUMBER_OF_ORDERS TOTAL_VALUE ORDER_COUNT_RANK
------------------------------ ---------------- ----------- ----------------
Girl's Trousers (Red)                       148   15,794.76                1
Girl's Hoodie (White)                        94   14,283.75                7
Women's Trousers (Blue)                      82   12,181.76               28
Boy's Coat (Blue)                            94   11,214.71                7
Girl's Pyjamas (White)                       89   11,118.60               17Daily order count and valueSALE_DATE            NUMBER_OF_ORDERS VALUE_OF_ORDERS
-------------------- ---------------- ---------------
04-FEB-2018                         1          209.38
05-FEB-2018                         0            0.00
06-FEB-2018                         0            0.00
07-FEB-2018                         0            0.00
08-FEB-2018                         1           54.23
09-FEB-2018                         1           43.30
10-FEB-2018                         1          304.98
11-FEB-2018                         1          153.19
12-FEB-2018                         0            0.00
13-FEB-2018                         1          199.48
14-FEB-2018                         0            0.00
15-FEB-2018                         0            0.00
16-FEB-2018                         0            0.00
17-FEB-2018                         0            0.00
18-FEB-2018                         0            0.00
19-FEB-2018                         0            0.00
20-FEB-2018                         0            0.00
21-FEB-2018                         0            0.00
22-FEB-2018                         2          537.23
23-FEB-2018                         2          448.61
24-FEB-2018                         4          413.10
25-FEB-2018                         0            0.00
26-FEB-2018                         2          221.88
27-FEB-2018                         1          351.64
28-FEB-2018                         1          319.20
01-MAR-2018                         4          875.32
02-MAR-2018                         2          247.40
03-MAR-2018                         3          422.83
04-MAR-2018                         0            0.00
05-MAR-2018                         1          239.28
06-MAR-2018                         2          355.04
07-MAR-2018                         2          263.79
08-MAR-2018                         1           96.68
09-MAR-2018                         2          135.39
10-MAR-2018                         2          247.21
11-MAR-2018                         1          132.05
12-MAR-2018                         5          562.18
13-MAR-2018                         2          522.16
14-MAR-2018                         4          601.28
15-MAR-2018                         3          236.50
16-MAR-2018                         2          388.88
17-MAR-2018                         2          285.21
18-MAR-2018                         2          218.53
19-MAR-2018                         1           52.16
20-MAR-2018                         1          130.95
21-MAR-2018                         1          138.20
22-MAR-2018                         5        1,003.52
23-MAR-2018                         2          305.96
24-MAR-2018                         2          240.35
25-MAR-2018                         1           38.28
26-MAR-2018                         1          359.11
27-MAR-2018                         4          912.41
28-MAR-2018                         2          256.71
29-MAR-2018                         3          505.10
30-MAR-2018                         7        1,329.04
31-MAR-2018                         3          303.90
01-APR-2018                         2          275.65
02-APR-2018                         3          544.43
03-APR-2018                         5          755.80
04-APR-2018                         3          466.20
05-APR-2018                         2          140.26
06-APR-2018                         1           78.14
07-APR-2018                         5          910.14
08-APR-2018                         0            0.00
09-APR-2018                         7        1,252.24
10-APR-2018                         7        1,212.61
11-APR-2018                         5        1,111.21
12-APR-2018                         3          815.66
13-APR-2018                         5          777.46
14-APR-2018                         1           57.10
15-APR-2018                         2          460.98
16-APR-2018                         3          375.71
17-APR-2018                         4          497.83
18-APR-2018                         3          527.73
19-APR-2018                         2          104.65
20-APR-2018                         5          700.68
21-APR-2018                         4          769.02
22-APR-2018                         4          838.40
23-APR-2018                         5          850.45
24-APR-2018                         3          470.73
25-APR-2018                         5          543.06
26-APR-2018                         7          962.23
27-APR-2018                         5          681.62
28-APR-2018                         7        1,082.03
29-APR-2018                         4          611.63
30-APR-2018                         2          443.74
01-MAY-2018                         4          433.75
02-MAY-2018                         6          810.51
03-MAY-2018                         5          631.16
04-MAY-2018                         4          703.06
05-MAY-2018                         5        1,304.49
06-MAY-2018                         7        1,568.44
07-MAY-2018                         3          366.23
08-MAY-2018                         2          295.09
09-MAY-2018                         3          357.54
10-MAY-2018                         3          687.55
11-MAY-2018                         3          764.82SALE_DATE            NUMBER_OF_ORDERS VALUE_OF_ORDERS
-------------------- ---------------- ---------------
12-MAY-2018                         3          427.95
13-MAY-2018                         5          810.37
14-MAY-2018                         2          415.91
15-MAY-2018                         5          807.96
16-MAY-2018                         1          150.00
17-MAY-2018                         3          402.88
18-MAY-2018                         4          871.67
19-MAY-2018                         1          104.37
20-MAY-2018                         2          376.42
21-MAY-2018                         6        1,194.02
22-MAY-2018                         5          626.35
23-MAY-2018                         4          710.44
24-MAY-2018                         7          955.61
25-MAY-2018                         3          410.90
26-MAY-2018                         3          445.38
27-MAY-2018                         5          436.81
28-MAY-2018                         3          552.76
29-MAY-2018                         1          101.68
30-MAY-2018                         0            0.00
31-MAY-2018                         4          530.82
01-JUN-2018                         6          683.14
02-JUN-2018                         5          598.83
03-JUN-2018                         4          542.80
04-JUN-2018                         4          774.33
05-JUN-2018                         3          400.90
06-JUN-2018                         5          977.01
07-JUN-2018                         1           26.14
08-JUN-2018                         3          648.46
09-JUN-2018                         3          415.18
10-JUN-2018                         3          520.15
11-JUN-2018                         2          379.92
12-JUN-2018                         4          417.65
13-JUN-2018                         5          903.63
14-JUN-2018                         5        1,112.48
15-JUN-2018                         4          462.06
16-JUN-2018                         5          817.75
17-JUN-2018                         3          532.90
18-JUN-2018                         4          818.02
19-JUN-2018                         6        1,116.91
20-JUN-2018                         6        1,109.11
21-JUN-2018                         6        1,313.93
22-JUN-2018                         7          792.58
23-JUN-2018                         3          488.63
24-JUN-2018                         4          660.64
25-JUN-2018                         5          758.19
26-JUN-2018                         7        1,077.50
27-JUN-2018                         6        1,192.44
28-JUN-2018                         2          443.51
29-JUN-2018                         4          359.01
30-JUN-2018                         1          156.64
01-JUL-2018                         3          544.36
02-JUL-2018                         7        1,076.29
03-JUL-2018                         2          308.14
04-JUL-2018                         5          725.44
05-JUL-2018                         6        1,056.17
06-JUL-2018                         0            0.00
07-JUL-2018                         2          261.43
08-JUL-2018                        11        1,688.50
09-JUL-2018                         5          642.62
10-JUL-2018                         1          155.88
11-JUL-2018                         5          419.96
12-JUL-2018                         3          640.59
13-JUL-2018                         5          980.06
14-JUL-2018                         4          652.81
15-JUL-2018                         1           19.16
16-JUL-2018                         0            0.00
17-JUL-2018                         3          722.53
18-JUL-2018                         4          813.25
19-JUL-2018                         4          715.92
20-JUL-2018                         4          559.31
21-JUL-2018                         4          563.57
22-JUL-2018                         6          690.61
23-JUL-2018                         3          544.45
24-JUL-2018                         4          484.77
25-JUL-2018                         3          402.02
26-JUL-2018                         5          476.93
27-JUL-2018                         4        1,213.76
28-JUL-2018                         5          744.00
29-JUL-2018                         7        1,447.36
30-JUL-2018                         4          594.54
31-JUL-2018                         3          740.03
01-AUG-2018                         5        1,099.93
02-AUG-2018                         3          374.01
03-AUG-2018                         2          223.42
04-AUG-2018                         5        1,079.57
05-AUG-2018                         5        1,077.18
06-AUG-2018                         2          293.20
07-AUG-2018                         4          466.25
08-AUG-2018                         3          490.18
09-AUG-2018                         5          640.42
10-AUG-2018                         3          364.84
11-AUG-2018                         5          713.17
12-AUG-2018                         7        1,125.01
13-AUG-2018                         9        1,545.54
14-AUG-2018                         6          929.81
15-AUG-2018                         3          665.71
16-AUG-2018                         4          643.10SALE_DATE            NUMBER_OF_ORDERS VALUE_OF_ORDERS
-------------------- ---------------- ---------------
17-AUG-2018                         5          709.08
18-AUG-2018                         8        1,303.89
19-AUG-2018                         5          871.74
20-AUG-2018                         4          854.74
21-AUG-2018                         2          166.50
22-AUG-2018                         5          484.46
23-AUG-2018                        11        1,949.52
24-AUG-2018                         4          704.29
25-AUG-2018                         0            0.00
26-AUG-2018                         3          454.58
27-AUG-2018                         6        1,025.92
28-AUG-2018                         5          455.32
29-AUG-2018                         4          668.26
30-AUG-2018                         5          972.71
31-AUG-2018                         8        1,000.66
01-SEP-2018                         4          417.83
02-SEP-2018                         7        1,431.90
03-SEP-2018                        10        1,144.54
04-SEP-2018                         4          678.74
05-SEP-2018                         9        1,509.30
06-SEP-2018                         7        1,282.86
07-SEP-2018                         1          204.46
08-SEP-2018                         9        1,235.66
09-SEP-2018                         8        1,128.13
10-SEP-2018                         7        1,198.13
11-SEP-2018                         3          637.48
12-SEP-2018                         8        1,409.66
13-SEP-2018                         6        1,265.07
14-SEP-2018                         3          749.05
15-SEP-2018                         4          471.35
16-SEP-2018                         5        1,145.17
17-SEP-2018                         2          409.50
18-SEP-2018                         3          696.43
19-SEP-2018                         8          988.78
20-SEP-2018                         4          605.09
21-SEP-2018                         7          816.78
22-SEP-2018                         4          647.82
23-SEP-2018                         3          620.31
24-SEP-2018                         6        1,094.24
25-SEP-2018                         4          917.77
26-SEP-2018                         6          713.95
27-SEP-2018                         4          614.63
28-SEP-2018                         3          823.48
29-SEP-2018                         6          613.57
30-SEP-2018                         6          890.78
01-OCT-2018                         6        1,154.87
02-OCT-2018                         3          226.69
03-OCT-2018                         7        1,155.18
04-OCT-2018                         1          228.76
05-OCT-2018                         3          445.35
06-OCT-2018                         6          846.67
07-OCT-2018                         8        1,082.29
08-OCT-2018                         3          615.53
09-OCT-2018                         8        1,437.97
10-OCT-2018                         9        1,788.72
11-OCT-2018                         7          747.83
12-OCT-2018                         2          305.48
13-OCT-2018                         5          816.43
14-OCT-2018                         5          528.84
15-OCT-2018                         6          725.76
16-OCT-2018                         3          422.58
17-OCT-2018                         9        1,134.96
18-OCT-2018                         6          764.75
19-OCT-2018                         5          760.67
20-OCT-2018                         5          797.36
21-OCT-2018                         2          198.62
22-OCT-2018                         4          723.16
23-OCT-2018                         7        1,412.60
24-OCT-2018                         7          931.38
25-OCT-2018                         8        1,010.50
26-OCT-2018                         6        1,233.25
27-OCT-2018                         6          759.75
28-OCT-2018                         9        1,167.47
29-OCT-2018                         6          771.18
30-OCT-2018                         3          424.11
31-OCT-2018                         7        1,304.17
01-NOV-2018                         5          660.67
02-NOV-2018                         6        1,487.50
03-NOV-2018                         4          580.93
04-NOV-2018                         4        1,014.95
05-NOV-2018                         4          792.70
06-NOV-2018                         7          979.82
07-NOV-2018                         8          839.06
08-NOV-2018                         9        1,360.67
09-NOV-2018                         4          650.25
10-NOV-2018                         4          590.20
11-NOV-2018                         6          865.12
12-NOV-2018                         5          730.06
13-NOV-2018                         3          481.38
14-NOV-2018                         2          320.39
15-NOV-2018                         8        1,336.67
16-NOV-2018                         4          839.44
17-NOV-2018                         5          644.00
18-NOV-2018                         8        1,237.14
19-NOV-2018                         7        1,028.19
20-NOV-2018                         5          828.49
21-NOV-2018                         6        1,384.62SALE_DATE            NUMBER_OF_ORDERS VALUE_OF_ORDERS
-------------------- ---------------- ---------------
22-NOV-2018                         3          236.50
23-NOV-2018                         4          891.24
24-NOV-2018                         3          699.45
25-NOV-2018                         2          318.94
26-NOV-2018                         4          738.78
27-NOV-2018                         4          702.97
28-NOV-2018                         6          870.00
29-NOV-2018                         5          635.92
30-NOV-2018                        10        1,666.31
01-DEC-2018                         2          348.97
02-DEC-2018                         9        1,566.81
03-DEC-2018                        10        1,597.85
04-DEC-2018                         9          919.85
05-DEC-2018                         2          456.58
06-DEC-2018                         8        1,477.13
07-DEC-2018                         5          977.65
08-DEC-2018                         5          890.85
09-DEC-2018                         9        1,450.03
10-DEC-2018                         9        1,541.38
11-DEC-2018                         4          519.10
12-DEC-2018                         8        1,433.59
13-DEC-2018                        10        1,241.31
14-DEC-2018                         4          833.18
15-DEC-2018                         5          839.04
16-DEC-2018                         8        1,236.49
17-DEC-2018                         3          226.37
18-DEC-2018                         9        1,527.34
19-DEC-2018                         3          493.39
20-DEC-2018                         7        1,065.27
21-DEC-2018                         6          920.23
22-DEC-2018                         4          638.16
23-DEC-2018                         6          400.79
24-DEC-2018                         5          854.51
25-DEC-2018                         7        1,222.27
26-DEC-2018                         3          453.00
27-DEC-2018                         4          513.24
28-DEC-2018                         8        1,270.83
29-DEC-2018                         5          795.87
30-DEC-2018                         5          609.17
31-DEC-2018                         9        1,448.28
01-JAN-2019                         7        1,028.36
02-JAN-2019                         6          972.54
03-JAN-2019                         8        1,080.85
04-JAN-2019                         4          753.68
05-JAN-2019                         9        1,613.11
06-JAN-2019                         4          701.82
07-JAN-2019                         7        1,030.74
08-JAN-2019                         6        1,204.56
09-JAN-2019                         1          221.38
10-JAN-2019                         9        1,203.94
11-JAN-2019                         4          402.55
12-JAN-2019                        11        1,884.84
13-JAN-2019                         9        1,612.92
14-JAN-2019                         7        1,293.03
15-JAN-2019                         7        1,103.19
16-JAN-2019                         6          941.68
17-JAN-2019                         4          823.33
18-JAN-2019                         8        1,217.60
19-JAN-2019                         3          929.88
20-JAN-2019                         9        1,353.34
21-JAN-2019                        10        1,357.98
22-JAN-2019                         7          844.70
23-JAN-2019                        10        1,562.43
24-JAN-2019                         8        1,071.97
25-JAN-2019                        11        2,038.57
26-JAN-2019                         7        1,088.72
27-JAN-2019                         4          500.10
28-JAN-2019                         8        1,013.35
29-JAN-2019                         4          346.98
30-JAN-2019                         8          922.87
31-JAN-2019                         9        1,202.63
01-FEB-2019                         6        1,007.87
02-FEB-2019                         6        1,193.55
03-FEB-2019                         6          895.15
04-FEB-2019                         7          713.00
05-FEB-2019                         5          869.79
06-FEB-2019                         3          168.49
07-FEB-2019                         7        1,402.48
08-FEB-2019                         2          277.42
09-FEB-2019                        10        1,196.25
10-FEB-2019                         4          676.61
11-FEB-2019                         4          353.43
12-FEB-2019                         8        1,433.28
13-FEB-2019                         6        1,160.04
14-FEB-2019                         7        1,263.98
15-FEB-2019                         4          767.94
16-FEB-2019                         5          927.73
17-FEB-2019                         3          243.90
18-FEB-2019                         5          826.58
19-FEB-2019                         8        1,076.35
20-FEB-2019                         6          922.16
21-FEB-2019                         4          576.21
22-FEB-2019                         6        1,124.39
23-FEB-2019                         3          369.60
24-FEB-2019                         4          473.60
25-FEB-2019                         7        1,155.70
26-FEB-2019                         5          748.25SALE_DATE            NUMBER_OF_ORDERS VALUE_OF_ORDERS
-------------------- ---------------- ---------------
27-FEB-2019                         7        1,055.80
28-FEB-2019                         7        1,202.08
01-MAR-2019                         6          925.22
02-MAR-2019                         4          751.81
03-MAR-2019                         5          760.03
04-MAR-2019                         8        1,361.05
05-MAR-2019                         4          396.97
06-MAR-2019                         5          667.02
07-MAR-2019                         6        1,291.81
08-MAR-2019                         4          593.47
09-MAR-2019                         9        1,551.99
10-MAR-2019                         5          494.58
11-MAR-2019                         2          390.24
12-MAR-2019                         5          918.08
13-MAR-2019                         4          596.63
14-MAR-2019                         6          914.78
15-MAR-2019                         7          920.92
16-MAR-2019                         5          685.33
17-MAR-2019                         7          952.78
18-MAR-2019                         4          511.17
19-MAR-2019                         6          979.97
20-MAR-2019                         4          602.58
21-MAR-2019                         5          661.32
22-MAR-2019                         3          655.92
23-MAR-2019                         3          277.65
24-MAR-2019                         5          918.06
25-MAR-2019                         7        1,124.52
26-MAR-2019                         2          311.84
27-MAR-2019                         5          531.44
28-MAR-2019                         6          616.94
29-MAR-2019                         6          740.97
30-MAR-2019                         3          654.42
31-MAR-2019                         3          509.66
01-APR-2019                         5        1,059.71
02-APR-2019                         2          234.04
03-APR-2019                         4          726.53
04-APR-2019                         4          374.67
05-APR-2019                         3          668.16
06-APR-2019                         3          420.33
07-APR-2019                         2          401.84
08-APR-2019                         3          468.68
09-APR-2019                         0            0.00
10-APR-2019                         5          673.99
11-APR-2019                         3          148.67
12-APR-2019                         1          309.36433 rows selected.Month and year sales matrixORDER_YEAR   JAN_VALUE   FEB_VALUE   MAR_VALUE   APR_VALUE   MAY_VALUE   JUN_VALUE   JUL_VALUE   AUG_VALUE   SEP_VALUE   OCT_VALUE   NOV_VALUE   DEC_VALUE
---------- ----------- ----------- ----------- ----------- ----------- ----------- ----------- ----------- ----------- ----------- ----------- -----------2018                3,256.22   11,405.42   18,317.39   18,254.94   20,500.44   19,884.46   23,353.01   26,362.46   25,922.88   25,412.36   29,768.532019   33,323.64   24,081.63   23,269.17    5,485.98SQL> 

Step 21: 确认CO: Customer Orders sample schema是否创建成功.

SQL> 
SQL> show user;
USER is "CO"
SQL> select * from tab;TNAME                                                                                                                            TABTYPE        CLUSTERID
-------------------------------------------------------------------------------------------------------------------------------- ------------- ----------
CUSTOMERS                                                                                                                        TABLE
STORES                                                                                                                           TABLE
PRODUCTS                                                                                                                         TABLE
ORDERS                                                                                                                           TABLE
ORDER_ITEMS                                                                                                                      TABLE
CUSTOMER_ORDER_PRODUCTS                                                                                                          VIEW
STORE_ORDERS                                                                                                                     VIEW
PRODUCT_REVIEWS                                                                                                                  VIEW
PRODUCT_ORDERS                                                                                                                   VIEW9 rows selected.SQL> 

QS : Queued Shipping (ORACLE 9i sample schema)

Step22: 开始执行QS : Queued Shipping sample schema  sample 脚本.如下所示(若Step22无法正常执行,请参考下方的手动执行操作):

[oracle@oracle-db-19c db-sample-schemas-19.2]$ sqlplus / as sysdba

SQL*Plus: Release 19.0.0.0.0 - Production on Sat Nov 26 16:45:36 2022
Version 19.3.0.0.0

Copyright (c) 1982, 2019, Oracle.  All rights reserved.


Connected to:
Oracle Database 19c Enterprise Edition Release 19.0.0.0.0 - Production
Version 19.3.0.0.0

SQL> alter session set container=PDB1;

Session altered.

SQL> @/u01/app/oracle/product/19.3.0/dbhome_1/rdbms/admin/db-sample-schemas-19.2/shipping/qs_main.sql

Session altered.


specify one password for the users QS,QS_ADM,QS_CBADM,
QS_WS,QS_ES,QS_OS,QS_CS and QS_CB as parameter 1:
Enter value for 1: qs

specify default tablespeace for QS as parameter 2:
Enter value for 2: users

specify temporary tablespace for QS as parameter 3:
Enter value for 3: temp

specify password for SYSTEM as parameter 4:
Enter value for 4: system

specify password for OE as parameter 5:
Enter value for 5: oe

specify password for SYS as parameter 6:
Enter value for 6: sys

specify log directory path as parameter 7:
Enter value for 7: /u01/app/oracle/product/19.3.0/dbhome_1/demo/schema/log/

specify connect string as parameter 8:
Enter value for 8: PDB1

DROP USER qs_adm CASCADE
          *
ERROR at line 1:
ORA-01918: user 'QS_ADM' does not exist


DROP USER qs CASCADE
          *
ERROR at line 1:
ORA-01918: user 'QS' does not exist


DROP USER qs_ws CASCADE
          *
ERROR at line 1:
ORA-01918: user 'QS_WS' does not exist


DROP USER qs_es CASCADE
          *
ERROR at line 1:
ORA-01918: user 'QS_ES' does not exist


DROP USER qs_os CASCADE
          *
ERROR at line 1:
ORA-01918: user 'QS_OS' does not exist


DROP USER qs_cbadm CASCADE
          *
ERROR at line 1:
ORA-01918: user 'QS_CBADM' does not exist


DROP USER qs_cb CASCADE
          *
ERROR at line 1:
ORA-01918: user 'QS_CB' does not exist


DROP USER qs_cs CASCADE
          *
ERROR at line 1:
ORA-01918: user 'QS_CS' does not exist

System altered.

old   1: CREATE USER qs_adm IDENTIFIED BY &pass
new   1: CREATE USER qs_adm IDENTIFIED BY qs

User created.

old   1: ALTER USER qs_adm DEFAULT TABLESPACE &tbs QUOTA UNLIMITED ON &tbs
new   1: ALTER USER qs_adm DEFAULT TABLESPACE users QUOTA UNLIMITED ON users

User altered.

old   1: ALTER USER qs_adm TEMPORARY TABLESPACE &ttbs
new   1: ALTER USER qs_adm TEMPORARY TABLESPACE temp

User altered.


Grant succeeded.


Grant succeeded.


Grant succeeded.


Grant succeeded.

以下为手动执行的脚本:

[oracle@oracle-db-19c db-sample-schemas-19.2]$ sqlplus / as sysdbaSQL*Plus: Release 19.0.0.0.0 - Production on Sat Nov 26 16:52:46 2022
Version 19.3.0.0.0Copyright (c) 1982, 2019, Oracle.  All rights reserved.Connected to:
Oracle Database 19c Enterprise Edition Release 19.0.0.0.0 - Production
Version 19.3.0.0.0SQL> alter session set container=PDB1;Session altered.SQL> ALTER SESSION SET NLS_LANGUAGE=American;Session altered.SQL> DROP USER qs_adm CASCADE;
DROP USER qs CASCADE;
DROP USER qs_ws CASCADE;
DROP USER qs_es CASCADE;
DROP USER qs_os CASCADE;
DROP USER qs_cbadm CASCADE;
DROP USER qs_cb CASCADE;User dropped.SQL> DROP USER qs CASCADE*
ERROR at line 1:
ORA-01918: user 'QS' does not existSQL> DROP USER qs_ws CASCADE*
ERROR at line 1:
ORA-01918: user 'QS_WS' does not existSQL> DROP USER qs_es CASCADE*
ERROR at line 1:
ORA-01918: user 'QS_ES' does not existSQL> DROP USER qs_os CASCADE*
ERROR at line 1:
ORA-01918: user 'QS_OS' does not existSQL> DROP USER qs_cbadm CASCADE*
ERROR at line 1:
ORA-01918: user 'QS_CBADM' does not existSQL> DROP USER qs_cb CASCADE*
ERROR at line 1:
ORA-01918: user 'QS_CB' does not existSQL> DROP USER qs_cs CASCADE;
DROP USER qs_cs CASCADE*
ERROR at line 1:
ORA-01918: user 'QS_CS' does not existSQL> alter system set job_queue_processes=4;System altered.SQL> CREATE USER qs_adm IDENTIFIED BY qs;User created.SQL> ALTER USER qs_adm DEFAULT TABLESPACE users QUOTA UNLIMITED ON users;User altered.SQL> ALTER USER qs_adm TEMPORARY TABLESPACE temp;User altered.SQL> GRANT CONNECT, RESOURCE , UNLIMITED TABLESPACE TO qs_adm;Grant succeeded.SQL> GRANT aq_administrator_role TO qs_adm;Grant succeeded.SQL> GRANT EXECUTE ON dbms_aq TO qs_adm;Grant succeeded.SQL> GRANT EXECUTE ON dbms_aqadm TO qs_adm;Grant succeeded.SQL> show user;
USER is "SYS"
SQL> GRANT execute ON sys.dbms_stats TO qs_adm;Grant succeeded.SQL> GRANT execute ON dbms_lock to qs_adm;Grant succeeded.SQL> conn system/system@PDB1
ERROR:
ORA-01017: invalid username/password; logon deniedWarning: You are no longer connected to ORACLE.
SQL> conn system/system as sysdba
Connected.
SQL> alter session set container=PDB1;Session altered.SQL> execute dbms_aqadm.grant_system_privilege('ENQUEUE_ANY','qs_adm',FALSE);PL/SQL procedure successfully completed.SQL> execute dbms_aqadm.grant_system_privilege('DEQUEUE_ANY','qs_adm',FALSE);PL/SQL procedure successfully completed.SQL> CREATE USER qs IDENTIFIED BY qs;User created.SQL> ALTER USER qs DEFAULT TABLESPACE users QUOTA UNLIMITED ON users;User altered.SQL> ALTER USER qs TEMPORARY TABLESPACE temp;User altered.SQL> GRANT CONNECT, RESOURCE , UNLIMITED TABLESPACE TO qs;Grant succeeded.SQL> GRANT EXECUTE ON dbms_aq to qs;Grant succeeded.SQL> GRANT EXECUTE ON dbms_aqadm to qs;Grant succeeded.SQL> CREATE USER qs_ws IDENTIFIED BY qs;User created.SQL> ALTER USER qs_ws DEFAULT TABLESPACE users QUOTA UNLIMITED ON users;User altered.SQL> ALTER USER qs_ws TEMPORARY TABLESPACE temp;User altered.SQL> GRANT CONNECT, RESOURCE , UNLIMITED TABLESPACE TO qs_ws;Grant succeeded.SQL> GRANT EXECUTE ON dbms_aq to qs_ws;Grant succeeded.SQL> GRANT EXECUTE ON dbms_aqadm to qs_ws;Grant succeeded.SQL> CREATE USER qs_es IDENTIFIED BY qs;User created.SQL> ALTER USER qs_es DEFAULT TABLESPACE users QUOTA UNLIMITED ON users;User altered.SQL> ALTER USER qs_es TEMPORARY TABLESPACE temp;User altered.SQL> GRANT CONNECT, RESOURCE , UNLIMITED TABLESPACE TO qs_es;Grant succeeded.SQL> GRANT EXECUTE ON dbms_aq TO qs_es;Grant succeeded.SQL> GRANT EXECUTE ON dbms_aqadm TO qs_es;Grant succeeded.SQL> CREATE USER qs_os IDENTIFIED BY qs;User created.SQL> ALTER USER qs_os DEFAULT TABLESPACE users QUOTA UNLIMITED ON users;User altered.SQL> ALTER USER qs_os TEMPORARY TABLESPACE temp;User altered.SQL> GRANT CONNECT, RESOURCE , UNLIMITED TABLESPACE TO qs_os;Grant succeeded.SQL> GRANT EXECUTE ON dbms_aq TO qs_os;Grant succeeded.SQL> GRANT EXECUTE ON dbms_aqadm TO qs_os;Grant succeeded.SQL> CREATE USER qs_cbadm IDENTIFIED BY qs;User created.SQL> ALTER USER qs_cbadm DEFAULT TABLESPACE users QUOTA UNLIMITED ON users;User altered.SQL> ALTER USER qs_cbadm TEMPORARY TABLESPACE temp;User altered.SQL> GRANT CONNECT, RESOURCE , UNLIMITED TABLESPACE TO qs_cbadm;Grant succeeded.SQL> GRANT EXECUTE ON dbms_aq to qs_cbadm;Grant succeeded.SQL> GRANT EXECUTE ON dbms_aqadm to qs_cbadm;Grant succeeded.SQL> CREATE USER qs_cb IDENTIFIED BY qs;User created.SQL> ALTER USER qs_cb DEFAULT TABLESPACE users QUOTA UNLIMITED ON users;User altered.SQL> ALTER USER qs_cb TEMPORARY TABLESPACE temp;User altered.SQL> GRANT CONNECT, RESOURCE , UNLIMITED TABLESPACE TO qs_cb;Grant succeeded.SQL> GRANT EXECUTE ON dbms_aq TO qs_cb;Grant succeeded.SQL> GRANT EXECUTE ON dbms_aqadm TO qs_cb;Grant succeeded.SQL> CREATE USER qs_cs IDENTIFIED BY qs;
ALTER USER qs_cs DEFAULT TABLESPACE users QUOTA UNLIMITED ON users;User created.SQL> 
User altered.SQL> 
SQL> ALTER USER qs_cs TEMPORARY TABLESPACE temp;User altered.SQL> 
SQL> GRANT CONNECT, RESOURCE , UNLIMITED TABLESPACE TO qs_cs;Grant succeeded.SQL> GRANT EXECUTE ON dbms_aq TO qs_cs;Grant succeeded.SQL> GRANT EXECUTE ON dbms_aqadm TO qs_cs;Grant succeeded.SQL> conn oe/oe@PDB1
Connected.
SQL> GRANT REFERENCES, SELECT ON customers TO qs_adm;Grant succeeded.SQL> GRANT REFERENCES, SELECT ON product_information TO qs_adm;Grant succeeded.SQL> conn qs_adm/qs@PDB1
Connected.
SQL> @/u01/app/oracle/product/19.3.0/dbhome_1/rdbms/admin/db-sample-schemas-19.2/shipping/qs_adm.sqlType created.Type created.Type created.Type created.Grant succeeded.Grant succeeded.Grant succeeded.Grant succeeded.PL/SQL procedure successfully completed.Grant succeeded.Grant succeeded.Grant succeeded.Grant succeeded.PL/SQL procedure successfully completed.Grant succeeded.Grant succeeded.Grant succeeded.Grant succeeded.PL/SQL procedure successfully completed.Grant succeeded.Grant succeeded.Grant succeeded.Grant succeeded.PL/SQL procedure successfully completed.Grant succeeded.Grant succeeded.Grant succeeded.Grant succeeded.Grant succeeded.Grant succeeded.Grant succeeded.Grant succeeded.Grant succeeded.Grant succeeded.Grant succeeded.Grant succeeded.Commit complete.SQL> CONNECT qs/qs@PDB1;
Connected.
SQL> @/u01/app/oracle/product/19.3.0/dbhome_1/rdbms/admin/db-sample-schemas-19.2/shipping/qs_cre.sqlPL/SQL procedure successfully completed.PL/SQL procedure successfully completed.PL/SQL procedure successfully completed.PL/SQL procedure successfully completed.PL/SQL procedure successfully completed.SQL> CONNECT qs_es/qs@PDB1
Connected.
SQL> @/u01/app/oracle/product/19.3.0/dbhome_1/rdbms/admin/db-sample-schemas-19.2/shipping/qs_es.sqlPL/SQL procedure successfully completed.PL/SQL procedure successfully completed.PL/SQL procedure successfully completed.PL/SQL procedure successfully completed.PL/SQL procedure successfully completed.Commit complete.SQL> CONNECT qs_ws/qs@PDB1
Connected.
SQL> @/u01/app/oracle/product/19.3.0/dbhome_1/rdbms/admin/db-sample-schemas-19.2/shipping/qs_ws.sqlPL/SQL procedure successfully completed.PL/SQL procedure successfully completed.PL/SQL procedure successfully completed.PL/SQL procedure successfully completed.PL/SQL procedure successfully completed.PL/SQL procedure successfully completed.PL/SQL procedure successfully completed.PL/SQL procedure successfully completed.PL/SQL procedure successfully completed.PL/SQL procedure successfully completed.Commit complete.SQL> CONNECT qs_os/qs@PDB1
Connected.
SQL> @/u01/app/oracle/product/19.3.0/dbhome_1/rdbms/admin/db-sample-schemas-19.2/shipping/qs_os.sqlPL/SQL procedure successfully completed.PL/SQL procedure successfully completed.PL/SQL procedure successfully completed.PL/SQL procedure successfully completed.PL/SQL procedure successfully completed.Commit complete.SQL> CONNECT qs_cbadm/qs@PDB1
Connected.
SQL> @/u01/app/oracle/product/19.3.0/dbhome_1/rdbms/admin/db-sample-schemas-19.2/shipping/qs_cbadm.sqlPL/SQL procedure successfully completed.PL/SQL procedure successfully completed.PL/SQL procedure successfully completed.PL/SQL procedure successfully completed.PL/SQL procedure successfully completed.Commit complete.SQL> CONNECT qs_cs/qs@PDB1
Connected.
SQL> @/u01/app/oracle/product/19.3.0/dbhome_1/rdbms/admin/db-sample-schemas-19.2/shipping/qs_cs.sqlTable created.PL/SQL procedure successfully completed.SQL> CONNECT qs_adm/qs@PDB1
Connected.
SQL> @/u01/app/oracle/product/19.3.0/dbhome_1/rdbms/admin/db-sample-schemas-19.2/shipping/qs_run.sqlType created.Package created.No errors.Package body created.No errors.Grant succeeded.Grant succeeded.Grant succeeded.Grant succeeded.Grant succeeded.Grant succeeded.SQL> show user;
USER is "QS_ADM"
SQL>

Step 23: 确认QS : Queued Shipping sample schema是否创建成功.

SQL> set pagesize 200
SQL> set linesize 200
SQL> conn qs/qs@PDB1
Connected.
SQL> select * from tab;TNAME                                                                                                                            TABTYPE        CLUSTERID
-------------------------------------------------------------------------------------------------------------------------------- ------------- ----------
QS_ORDERS_SQTAB                                                                                                                  TABLE
AQ$_QS_ORDERS_SQTAB_F                                                                                                            VIEW
AQ$QS_ORDERS_SQTAB                                                                                                               VIEW
QS_ORDERS_PR_MQTAB                                                                                                               TABLE
AQ$_QS_ORDERS_PR_MQTAB_S                                                                                                         TABLE
AQ$_QS_ORDERS_PR_MQTAB_T                                                                                                         TABLE
AQ$QS_ORDERS_PR_MQTAB_S                                                                                                          VIEW
AQ$_QS_ORDERS_PR_MQTAB_H                                                                                                         TABLE
AQ$_QS_ORDERS_PR_MQTAB_L                                                                                                         TABLE
SYS_IOT_OVER_73738                                                                                                               TABLE
AQ$_QS_ORDERS_PR_MQTAB_G                                                                                                         TABLE
AQ$_QS_ORDERS_PR_MQTAB_I                                                                                                         TABLE
AQ$_QS_ORDERS_PR_MQTAB_F                                                                                                         VIEW
AQ$QS_ORDERS_PR_MQTAB                                                                                                            VIEW
AQ$QS_ORDERS_PR_MQTAB_R                                                                                                          VIEW
AQ$_MEM_MC                                                                                                                       TABLE
AQ$_AQ$_MEM_MC_S                                                                                                                 TABLE
AQ$_AQ$_MEM_MC_T                                                                                                                 TABLE
AQ$AQ$_MEM_MC_S                                                                                                                  VIEW
AQ$_AQ$_MEM_MC_H                                                                                                                 TABLE
AQ$_AQ$_MEM_MC_L                                                                                                                 TABLE
SYS_IOT_OVER_73768                                                                                                               TABLE
AQ$_AQ$_MEM_MC_G                                                                                                                 TABLE
AQ$_AQ$_MEM_MC_I                                                                                                                 TABLE
AQ$_AQ$_MEM_MC_F                                                                                                                 VIEW
AQ$AQ$_MEM_MC                                                                                                                    VIEW26 rows selected.SQL> 

这篇关于史上最详细的Oracle 所有版本(本文实例为19c)安装sample schemas ( HR, OE, PM, SH, IX, BI)(12C+)CO, QS(9i+)的文章就介绍到这儿,希望我们推荐的文章对编程师们有所帮助!



http://www.chinasem.cn/article/278196

相关文章

C++快速排序超详细讲解

《C++快速排序超详细讲解》快速排序是一种高效的排序算法,通过分治法将数组划分为两部分,递归排序,直到整个数组有序,通过代码解析和示例,详细解释了快速排序的工作原理和实现过程,需要的朋友可以参考下... 目录一、快速排序原理二、快速排序标准代码三、代码解析四、使用while循环的快速排序1.代码代码1.由快

Mybatis从3.4.0版本到3.5.7版本的迭代方法实现

《Mybatis从3.4.0版本到3.5.7版本的迭代方法实现》本文主要介绍了Mybatis从3.4.0版本到3.5.7版本的迭代方法实现,包括主要的功能增强、不兼容的更改和修复的错误,具有一定的参考... 目录一、3.4.01、主要的功能增强2、selectCursor example3、不兼容的更改二、

Spring Boot拦截器Interceptor与过滤器Filter详细教程(示例详解)

《SpringBoot拦截器Interceptor与过滤器Filter详细教程(示例详解)》本文详细介绍了SpringBoot中的拦截器(Interceptor)和过滤器(Filter),包括它们的... 目录Spring Boot拦截器(Interceptor)与过滤器(Filter)详细教程1. 概述1

使用Dify访问mysql数据库详细代码示例

《使用Dify访问mysql数据库详细代码示例》:本文主要介绍使用Dify访问mysql数据库的相关资料,并详细讲解了如何在本地搭建数据库访问服务,使用ngrok暴露到公网,并创建知识库、数据库访... 1、在本地搭建数据库访问的服务,并使用ngrok暴露到公网。#sql_tools.pyfrom

Pycharm安装报错:Cannot detect a launch configuration解决办法

《Pycharm安装报错:Cannotdetectalaunchconfiguration解决办法》本文主要介绍了Pycharm安装报错:Cannotdetectalaunchconfigur... 本文主要介绍了Pycharm安装报错:Cannot detect a launch configuratio

pytorch+torchvision+python版本对应及环境安装

《pytorch+torchvision+python版本对应及环境安装》本文主要介绍了pytorch+torchvision+python版本对应及环境安装,安装过程中需要注意Numpy版本的降级,... 目录一、版本对应二、安装命令(pip)1. 版本2. 安装全过程3. 命令相关解释参考文章一、版本对

Windows系统下如何查找JDK的安装路径

《Windows系统下如何查找JDK的安装路径》:本文主要介绍Windows系统下如何查找JDK的安装路径,文中介绍了三种方法,分别是通过命令行检查、使用verbose选项查找jre目录、以及查看... 目录一、确认是否安装了JDK二、查找路径三、另外一种方式如果很久之前安装了JDK,或者在别人的电脑上,想

java导出pdf文件的详细实现方法

《java导出pdf文件的详细实现方法》:本文主要介绍java导出pdf文件的详细实现方法,包括制作模板、获取中文字体文件、实现后端服务以及前端发起请求并生成下载链接,需要的朋友可以参考下... 目录使用注意点包含内容1、制作pdf模板2、获取pdf导出中文需要的文件3、实现4、前端发起请求并生成下载链接使

大数据spark3.5安装部署之local模式详解

《大数据spark3.5安装部署之local模式详解》本文介绍了如何在本地模式下安装和配置Spark,并展示了如何使用SparkShell进行基本的数据处理操作,同时,还介绍了如何通过Spark-su... 目录下载上传解压配置jdk解压配置环境变量启动查看交互操作命令行提交应用spark,一个数据处理框架

MyBatis-Plus中Service接口的lambdaUpdate用法及实例分析

《MyBatis-Plus中Service接口的lambdaUpdate用法及实例分析》本文将详细讲解MyBatis-Plus中的lambdaUpdate用法,并提供丰富的案例来帮助读者更好地理解和应... 目录深入探索MyBATis-Plus中Service接口的lambdaUpdate用法及示例案例背景