/*===============================================================
ORACLE 23AI COMPLETE AI + SQL + MACHINE LEARNING MASTER SCRIPT
Author : Vinayak Vishweshwara Dabgar
www.dabgarvinayakv.com
Oracle Apps DBA - Consultant | DBA - Developer | Database Architect | Performance Tuning Expert
PART 1 : ENVIRONMENT SETUP AND DATA CREATION
=================================================================
This script is designed as a COMPLETE learning reference.
Features Covered
----------------
1 Oracle 23ai SQL improvements
2 JSON data type
3 VECTOR data type for AI embeddings
4 Oracle Machine Learning SQL
5 Classification models
6 Regression models
7 Clustering
8 Time series forecasting
9 NLP text analytics
10 Anomaly detection
All examples include
- Table creation
- Data insertion
- SQL queries
- Model creation
- Model prediction
===============================================================*/
/*===============================================================
SECTION 1
BASIC SAMPLE TABLES
These tables will be reused across ML examples
===============================================================*/
DROP TABLE customer_data PURGE;
CREATE TABLE customer_data
(
customer_id NUMBER PRIMARY KEY,
age NUMBER,
income NUMBER,
subscribed VARCHAR2(10)
);
INSERT INTO customer_data VALUES (1,25,50000,'YES');
INSERT INTO customer_data VALUES (2,40,80000,'NO');
INSERT INTO customer_data VALUES (3,30,45000,'YES');
INSERT INTO customer_data VALUES (4,50,100000,'NO');
INSERT INTO customer_data VALUES (5,28,52000,'YES');
INSERT INTO customer_data VALUES (6,35,76000,'NO');
COMMIT;
/*===============================================================
TABLE FOR PREDICTION
===============================================================*/
DROP TABLE new_customer_data PURGE;
CREATE TABLE new_customer_data
(
customer_id NUMBER,
age NUMBER,
income NUMBER
);
INSERT INTO new_customer_data VALUES (101,27,51000);
INSERT INTO new_customer_data VALUES (102,48,92000);
INSERT INTO new_customer_data VALUES (103,33,60000);
COMMIT;
/*===============================================================
HOUSE PRICE DATA
Used for REGRESSION example
===============================================================*/
DROP TABLE house_data PURGE;
CREATE TABLE house_data
(
house_id NUMBER,
size_sqft NUMBER,
location VARCHAR2(10),
price NUMBER
);
INSERT INTO house_data VALUES (1,1000,'A',300000);
INSERT INTO house_data VALUES (2,1500,'B',450000);
INSERT INTO house_data VALUES (3,1200,'A',350000);
INSERT INTO house_data VALUES (4,1800,'C',600000);
INSERT INTO house_data VALUES (5,2000,'B',650000);
COMMIT;
/*===============================================================
NEW HOUSE DATA FOR PREDICTION
===============================================================*/
DROP TABLE new_house_data PURGE;
CREATE TABLE new_house_data
(
house_id NUMBER,
size_sqft NUMBER,
location VARCHAR2(10)
);
INSERT INTO new_house_data VALUES (101,1100,'A');
INSERT INTO new_house_data VALUES (102,1700,'B');
INSERT INTO new_house_data VALUES (103,2100,'C');
COMMIT;
/*===============================================================
SALES DATA
Used for TIME SERIES FORECASTING
===============================================================*/
DROP TABLE sales_data PURGE;
CREATE TABLE sales_data
(
month_id DATE,
sales NUMBER
);
INSERT INTO sales_data VALUES (DATE '2024-01-01',500);
INSERT INTO sales_data VALUES (DATE '2024-02-01',550);
INSERT INTO sales_data VALUES (DATE '2024-03-01',600);
INSERT INTO sales_data VALUES (DATE '2024-04-01',650);
INSERT INTO sales_data VALUES (DATE '2024-05-01',700);
INSERT INTO sales_data VALUES (DATE '2024-06-01',750);
COMMIT;
/*===============================================================
CUSTOMER REVIEWS TABLE
Used for NLP TEXT CLASSIFICATION
===============================================================*/
DROP TABLE customer_reviews PURGE;
CREATE TABLE customer_reviews
(
review_id NUMBER,
review_text VARCHAR2(1000),
sentiment VARCHAR2(20)
);
INSERT INTO customer_reviews VALUES
(1,'Great product I love it','Positive');
INSERT INTO customer_reviews VALUES
(2,'Its okay could be better','Neutral');
INSERT INTO customer_reviews VALUES
(3,'Worst purchase ever','Negative');
INSERT INTO customer_reviews VALUES
(4,'Excellent quality and service','Positive');
INSERT INTO customer_reviews VALUES
(5,'Not satisfied with the product','Negative');
COMMIT;
/*===============================================================
NEW REVIEWS FOR PREDICTION
===============================================================*/
DROP TABLE new_reviews_data PURGE;
CREATE TABLE new_reviews_data
(
review_id NUMBER,
review_text VARCHAR2(1000)
);
INSERT INTO new_reviews_data VALUES
(101,'Amazing product highly recommended');
INSERT INTO new_reviews_data VALUES
(102,'Very bad experience');
COMMIT;
/*===============================================================
TRANSACTION DATA
Used for FRAUD / ANOMALY DETECTION
===============================================================*/
DROP TABLE transaction_data PURGE;
CREATE TABLE transaction_data
(
transaction_id NUMBER,
amount NUMBER,
transaction_type VARCHAR2(30),
txn_date DATE
);
INSERT INTO transaction_data VALUES
(1,100,'Deposit',DATE '2024-01-01');
INSERT INTO transaction_data VALUES
(2,5000,'Withdrawal',DATE '2024-01-02');
INSERT INTO transaction_data VALUES
(3,200,'Deposit',DATE '2024-01-03');
INSERT INTO transaction_data VALUES
(4,10000,'Withdrawal',DATE '2024-01-04');
INSERT INTO transaction_data VALUES
(5,150,'Deposit',DATE '2024-01-05');
COMMIT;
/*===============================================================
FINANCIAL DATA
Used for PIVOT example
===============================================================*/
DROP TABLE financial_data PURGE;
CREATE TABLE financial_data
(
year NUMBER,
quarter VARCHAR2(2),
revenue NUMBER
);
INSERT INTO financial_data VALUES (2023,'Q1',20000);
INSERT INTO financial_data VALUES (2023,'Q2',25000);
INSERT INTO financial_data VALUES (2023,'Q3',27000);
INSERT INTO financial_data VALUES (2023,'Q4',30000);
INSERT INTO financial_data VALUES (2024,'Q1',32000);
INSERT INTO financial_data VALUES (2024,'Q2',35000);
INSERT INTO financial_data VALUES (2024,'Q3',38000);
INSERT INTO financial_data VALUES (2024,'Q4',42000);
COMMIT;
/*===============================================================
EMPLOYEE TABLE
Used for analytic SQL examples
===============================================================*/
DROP TABLE employees_ai PURGE;
CREATE TABLE employees_ai
(
employee_id NUMBER,
first_name VARCHAR2(50),
department_id NUMBER,
salary NUMBER,
hire_date DATE
);
INSERT INTO employees_ai VALUES (1,'John',10,5000,DATE '2020-01-01');
INSERT INTO employees_ai VALUES (2,'Mary',10,6000,DATE '2019-03-10');
INSERT INTO employees_ai VALUES (3,'David',20,7000,DATE '2021-07-20');
INSERT INTO employees_ai VALUES (4,'Susan',20,8000,DATE '2018-09-12');
INSERT INTO employees_ai VALUES (5,'James',30,9000,DATE '2017-02-01');
COMMIT;
/*===============================================================
END OF PART 1
===============================================================
Disclaimer and Limitation of Liability
The author provides all scripts, queries, and related materials βas isβ, without any express or implied
warranties, including but not limited to warranties of accuracy, completeness, reliability,
merchantability, or fitness for a particular purpose.
By using, executing, or implementing any part of this material, the user acknowledges and agrees
that they do so at their own risk. The author shall not be held liable for any direct, indirect,
incidental, consequential, special, or exemplary damages, including but not limited to loss of data,
loss of profits, system failures, security breaches, or any other damages or losses arising from the
use or misuse of these materials.
Users are strongly advised to thoroughly review, validate, and test all scripts, queries, and
configurations in a controlled, non-production (test) environment prior to deploying them in any
live or production systems.
It is the sole responsibility of the user to ensure that the use of these materials complies with
all applicable local, national, and international laws, regulations, and organizational policies.
By proceeding to use these materials, the user agrees to indemnify and hold harmless the author
from any claims, damages, liabilities, or expenses arising from their use.
===============================================================*/