/*===============================================================
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
ORACLE 23AI PRACTICAL LAB EXERCISES
SECTION 1 (LABS 1β15)
===============================================================*/
/*---------------------------------------------------------------
LAB 1
Create a table using the JSON data type.
---------------------------------------------------------------
Task:
Create a table called customer_json_store with a JSON column.
Solution:
*/
CREATE TABLE customer_json_store
(
id NUMBER,
customer_info JSON
);
/*---------------------------------------------------------------
LAB 2
Insert JSON data.
---------------------------------------------------------------
Insert customer information with name, city, and age.
*/
INSERT INTO customer_json_store VALUES
(
1,
'{
"name":"John",
"city":"New York",
"age":30
}'
);
/*---------------------------------------------------------------
LAB 3
Extract JSON values.
---------------------------------------------------------------
Retrieve customer names using JSON_VALUE.
*/
SELECT
JSON_VALUE(customer_info,'$.name')
FROM customer_json_store;
/*---------------------------------------------------------------
LAB 4
Use JSON_QUERY.
---------------------------------------------------------------
Retrieve the entire JSON document.
*/
SELECT
JSON_QUERY(customer_info,'$')
FROM customer_json_store;
/*---------------------------------------------------------------
LAB 5
Create VECTOR column.
---------------------------------------------------------------
Create table for document embeddings.
*/
CREATE TABLE document_vectors
(
doc_id NUMBER,
content VARCHAR2(1000),
embedding VECTOR(4)
);
/*---------------------------------------------------------------
LAB 6
Insert vector embeddings.
---------------------------------------------------------------
*/
INSERT INTO document_vectors VALUES
(
1,
'Oracle AI database tutorial',
VECTOR '[0.12,0.22,0.34,0.56]'
);
/*---------------------------------------------------------------
LAB 7
Perform vector similarity search.
---------------------------------------------------------------
*/
SELECT
doc_id,
content
FROM document_vectors
ORDER BY
VECTOR_DISTANCE(
embedding,
VECTOR '[0.11,0.20,0.33,0.55]',
COSINE
);
/*---------------------------------------------------------------
LAB 8
Create vector index.
---------------------------------------------------------------
*/
CREATE VECTOR INDEX doc_vec_index
ON document_vectors(embedding);
/*---------------------------------------------------------------
LAB 9
Limit similarity search results.
---------------------------------------------------------------
*/
SELECT
doc_id,
content
FROM document_vectors
ORDER BY
VECTOR_DISTANCE(
embedding,
VECTOR '[0.11,0.20,0.33,0.55]',
COSINE
)
FETCH FIRST 2 ROWS ONLY;
/*---------------------------------------------------------------
LAB 10
Create ML training table.
---------------------------------------------------------------
*/
CREATE TABLE lab_customer_data
(
customer_id NUMBER,
age NUMBER,
income NUMBER,
subscribed VARCHAR2(10)
);
/*---------------------------------------------------------------
LAB 11
Insert ML training data.
---------------------------------------------------------------
*/
INSERT INTO lab_customer_data VALUES (1,25,50000,'YES');
INSERT INTO lab_customer_data VALUES (2,40,70000,'NO');
INSERT INTO lab_customer_data VALUES (3,30,45000,'YES');
/*---------------------------------------------------------------
LAB 12
Create Decision Tree settings.
---------------------------------------------------------------
*/
CREATE TABLE lab_dt_settings
(
setting_name VARCHAR2(30),
setting_value VARCHAR2(4000)
);
INSERT INTO lab_dt_settings VALUES
('ALGO_NAME','ALGO_DECISION_TREE');
/*---------------------------------------------------------------
LAB 13
Create classification model.
---------------------------------------------------------------
*/
BEGIN
DBMS_DATA_MINING.CREATE_MODEL(
model_name => 'LAB_DT_MODEL',
mining_function => DBMS_DATA_MINING.CLASSIFICATION,
data_table_name => 'LAB_CUSTOMER_DATA',
case_id_column_name => 'CUSTOMER_ID',
target_column_name => 'SUBSCRIBED',
settings_table_name => 'LAB_DT_SETTINGS'
);
END;
/
/*---------------------------------------------------------------
LAB 14
Run prediction query.
---------------------------------------------------------------
*/
SELECT
customer_id,
PREDICTION(LAB_DT_MODEL USING age,income)
FROM lab_customer_data;
/*---------------------------------------------------------------
LAB 15
View model metadata.
---------------------------------------------------------------
*/
SELECT
model_name,
algorithm
FROM user_mining_models;
/*===============================================================
ORACLE 23AI PRACTICAL LAB EXERCISES
SECTION 2 (LABS 16β30)
===============================================================*/
/*----------------------------------------------------------------
LAB 16
Create dataset for regression model
----------------------------------------------------------------*/
CREATE TABLE lab_house_prices
(
house_id NUMBER,
size_sqft NUMBER,
location VARCHAR2(10),
price NUMBER
);
INSERT INTO lab_house_prices VALUES (1,1000,'A',300000);
INSERT INTO lab_house_prices VALUES (2,1500,'B',450000);
INSERT INTO lab_house_prices VALUES (3,1200,'A',350000);
INSERT INTO lab_house_prices VALUES (4,1800,'C',600000);
COMMIT;
/*----------------------------------------------------------------
LAB 17
Create regression settings table
----------------------------------------------------------------*/
CREATE TABLE lab_reg_settings
(
setting_name VARCHAR2(30),
setting_value VARCHAR2(4000)
);
INSERT INTO lab_reg_settings VALUES
('ALGO_NAME','ALGO_GENERALIZED_LINEAR_MODEL');
COMMIT;
/*----------------------------------------------------------------
LAB 18
Create regression model
----------------------------------------------------------------*/
BEGIN
DBMS_DATA_MINING.CREATE_MODEL(
model_name => 'LAB_PRICE_REG_MODEL',
mining_function => DBMS_DATA_MINING.REGRESSION,
data_table_name => 'LAB_HOUSE_PRICES',
case_id_column_name => 'HOUSE_ID',
target_column_name => 'PRICE',
settings_table_name => 'LAB_REG_SETTINGS'
);
END;
/
/*----------------------------------------------------------------
LAB 19
Predict house prices
----------------------------------------------------------------*/
SELECT
house_id,
size_sqft,
PREDICTION(
LAB_PRICE_REG_MODEL
USING size_sqft,location
) AS predicted_price
FROM lab_house_prices;
/*----------------------------------------------------------------
LAB 20
Create clustering dataset
----------------------------------------------------------------*/
CREATE TABLE lab_customer_cluster
(
customer_id NUMBER,
age NUMBER,
income NUMBER
);
INSERT INTO lab_customer_cluster VALUES (1,25,50000);
INSERT INTO lab_customer_cluster VALUES (2,40,80000);
INSERT INTO lab_customer_cluster VALUES (3,30,45000);
INSERT INTO lab_customer_cluster VALUES (4,50,100000);
COMMIT;
/*----------------------------------------------------------------
LAB 21
Create K-Means clustering settings
----------------------------------------------------------------*/
CREATE TABLE lab_kmeans_settings
(
setting_name VARCHAR2(30),
setting_value VARCHAR2(4000)
);
INSERT INTO lab_kmeans_settings VALUES
('ALGO_NAME','ALGO_KMEANS');
INSERT INTO lab_kmeans_settings VALUES
('KMNS_NUM_CLUSTERS','3');
COMMIT;
/*----------------------------------------------------------------
LAB 22
Create clustering model
----------------------------------------------------------------*/
BEGIN
DBMS_DATA_MINING.CREATE_MODEL(
model_name => 'LAB_CLUSTER_MODEL',
mining_function => DBMS_DATA_MINING.CLUSTERING,
data_table_name => 'LAB_CUSTOMER_CLUSTER',
case_id_column_name => 'CUSTOMER_ID',
settings_table_name => 'LAB_KMEANS_SETTINGS'
);
END;
/
/*----------------------------------------------------------------
LAB 23
Retrieve cluster IDs
----------------------------------------------------------------*/
SELECT
customer_id,
CLUSTER_ID(
LAB_CLUSTER_MODEL
USING age,income
) AS cluster_group
FROM lab_customer_cluster;
/*----------------------------------------------------------------
LAB 24
Create time series dataset
----------------------------------------------------------------*/
CREATE TABLE lab_sales
(
month_id DATE,
sales NUMBER
);
INSERT INTO lab_sales VALUES (DATE '2024-01-01',500);
INSERT INTO lab_sales VALUES (DATE '2024-02-01',550);
INSERT INTO lab_sales VALUES (DATE '2024-03-01',600);
INSERT INTO lab_sales VALUES (DATE '2024-04-01',650);
COMMIT;
/*----------------------------------------------------------------
LAB 25
Create ARIMA forecasting settings
----------------------------------------------------------------*/
CREATE TABLE lab_ts_settings
(
setting_name VARCHAR2(30),
setting_value VARCHAR2(4000)
);
INSERT INTO lab_ts_settings VALUES
('ALGO_NAME','ALGO_ARIMA');
COMMIT;
/*----------------------------------------------------------------
LAB 26
Create forecasting model
----------------------------------------------------------------*/
BEGIN
DBMS_DATA_MINING.CREATE_MODEL(
model_name => 'LAB_SALES_FORECAST',
mining_function => DBMS_DATA_MINING.TIME_SERIES,
data_table_name => 'LAB_SALES',
case_id_column_name => 'MONTH_ID',
target_column_name => 'SALES',
settings_table_name => 'LAB_TS_SETTINGS'
);
END;
/
/*----------------------------------------------------------------
LAB 27
Forecast future sales
----------------------------------------------------------------*/
SELECT
month_id,
PREDICTION(
LAB_SALES_FORECAST
USING month_id
)
FROM lab_sales;
/*----------------------------------------------------------------
LAB 28
Create JSON index
----------------------------------------------------------------*/
CREATE INDEX lab_json_idx
ON customer_json_store
(
JSON_VALUE(customer_info,'$.city')
);
/*----------------------------------------------------------------
LAB 29
Use analytic window function
----------------------------------------------------------------*/
SELECT
customer_id,
income,
RANK() OVER(ORDER BY income DESC) AS income_rank
FROM lab_customer_cluster;
/*----------------------------------------------------------------
LAB 30
Perform vector recommendation query
----------------------------------------------------------------*/
SELECT
doc_id,
content
FROM document_vectors
ORDER BY
VECTOR_DISTANCE(
embedding,
(SELECT embedding FROM document_vectors WHERE doc_id=1),
COSINE
)
FETCH FIRST 2 ROWS ONLY;
/*===============================================================
ORACLE 23AI PRACTICAL LAB EXERCISES
SECTION 3 (LABS 31β50)
===============================================================*/
/*----------------------------------------------------------------
LAB 31
Create enterprise knowledge base table for RAG systems
----------------------------------------------------------------*/
CREATE TABLE lab_ai_knowledge
(
doc_id NUMBER,
title VARCHAR2(200),
content VARCHAR2(2000),
embedding VECTOR(6)
);
/*----------------------------------------------------------------
LAB 32
Insert knowledge base documents
----------------------------------------------------------------*/
INSERT INTO lab_ai_knowledge VALUES
(1,'Oracle Database','Oracle database is an enterprise RDBMS.',
VECTOR '[0.11,0.14,0.18,0.21,0.25,0.29]');
INSERT INTO lab_ai_knowledge VALUES
(2,'Machine Learning','Machine learning learns patterns from data.',
VECTOR '[0.90,0.81,0.74,0.68,0.60,0.55]');
INSERT INTO lab_ai_knowledge VALUES
(3,'Artificial Intelligence','AI enables machines to perform intelligent tasks.',
VECTOR '[0.92,0.84,0.77,0.70,0.65,0.60]');
COMMIT;
/*----------------------------------------------------------------
LAB 33
Perform semantic search query
----------------------------------------------------------------*/
SELECT
title,
content
FROM lab_ai_knowledge
ORDER BY
VECTOR_DISTANCE(
embedding,
VECTOR '[0.91,0.80,0.73,0.69,0.61,0.57]',
COSINE
)
FETCH FIRST 2 ROWS ONLY;
/*----------------------------------------------------------------
LAB 34
Create vector index for knowledge base
----------------------------------------------------------------*/
CREATE VECTOR INDEX lab_knowledge_vec_idx
ON lab_ai_knowledge(embedding);
/*----------------------------------------------------------------
LAB 35
Simulate enterprise knowledge retrieval query
----------------------------------------------------------------*/
SELECT
doc_id,
title
FROM lab_ai_knowledge
ORDER BY
VECTOR_DISTANCE(
embedding,
VECTOR '[0.92,0.82,0.75,0.71,0.64,0.58]',
COSINE
)
FETCH FIRST 1 ROW ONLY;
/*----------------------------------------------------------------
LAB 36
Build recommendation query using embeddings
----------------------------------------------------------------*/
SELECT
doc_id,
title
FROM lab_ai_knowledge
WHERE doc_id <> 2
ORDER BY
VECTOR_DISTANCE(
embedding,
(SELECT embedding FROM lab_ai_knowledge WHERE doc_id=2),
COSINE
)
FETCH FIRST 2 ROWS ONLY;
/*----------------------------------------------------------------
LAB 37
Create prediction results table
----------------------------------------------------------------*/
CREATE TABLE lab_model_predictions AS
SELECT
customer_id,
subscribed AS actual_value,
PREDICTION(
LAB_DT_MODEL
USING age,income
) AS predicted_value
FROM lab_customer_data;
/*----------------------------------------------------------------
LAB 38
Generate confusion matrix
----------------------------------------------------------------*/
SELECT
actual_value,
predicted_value,
COUNT(*) AS count_records
FROM lab_model_predictions
GROUP BY actual_value,predicted_value;
/*----------------------------------------------------------------
LAB 39
Calculate model accuracy
----------------------------------------------------------------*/
SELECT
SUM(
CASE
WHEN actual_value = predicted_value
THEN 1 ELSE 0
END
) / COUNT(*) AS accuracy
FROM lab_model_predictions;
/*----------------------------------------------------------------
LAB 40
Calculate prediction probability
----------------------------------------------------------------*/
SELECT
customer_id,
PREDICTION_PROBABILITY(
LAB_DT_MODEL,
'YES'
USING age,income
) AS probability_yes
FROM lab_customer_data;
/*----------------------------------------------------------------
LAB 41
Simulate AI data ingestion pipeline
----------------------------------------------------------------*/
CREATE TABLE lab_ai_data_pipeline
(
record_id NUMBER,
text_data VARCHAR2(500)
);
INSERT INTO lab_ai_data_pipeline VALUES
(1,'Oracle AI database tutorial');
INSERT INTO lab_ai_data_pipeline VALUES
(2,'Machine learning training data');
COMMIT;
/*----------------------------------------------------------------
LAB 42
Create embeddings table for pipeline
----------------------------------------------------------------*/
CREATE TABLE lab_pipeline_embeddings
(
record_id NUMBER,
embedding VECTOR(4)
);
/*----------------------------------------------------------------
LAB 43
Insert simulated embeddings
----------------------------------------------------------------*/
INSERT INTO lab_pipeline_embeddings VALUES
(1,VECTOR '[0.12,0.20,0.34,0.56]');
INSERT INTO lab_pipeline_embeddings VALUES
(2,VECTOR '[0.90,0.75,0.68,0.60]');
COMMIT;
/*----------------------------------------------------------------
LAB 44
Perform similarity search on pipeline embeddings
----------------------------------------------------------------*/
SELECT
record_id
FROM lab_pipeline_embeddings
ORDER BY
VECTOR_DISTANCE(
embedding,
VECTOR '[0.89,0.74,0.67,0.59]',
COSINE
);
/*----------------------------------------------------------------
LAB 45
Create vector performance test table
----------------------------------------------------------------*/
CREATE TABLE lab_vector_perf
(
id NUMBER,
embedding VECTOR(4)
);
/*----------------------------------------------------------------
LAB 46
Insert vector performance sample data
----------------------------------------------------------------*/
INSERT INTO lab_vector_perf VALUES
(1,VECTOR '[0.11,0.22,0.33,0.44]');
INSERT INTO lab_vector_perf VALUES
(2,VECTOR '[0.91,0.80,0.75,0.70]');
COMMIT;
/*----------------------------------------------------------------
LAB 47
Create vector index for performance testing
----------------------------------------------------------------*/
CREATE VECTOR INDEX lab_vector_perf_idx
ON lab_vector_perf(embedding);
/*----------------------------------------------------------------
LAB 48
Explain plan for vector similarity query
----------------------------------------------------------------*/
EXPLAIN PLAN FOR
SELECT
id
FROM lab_vector_perf
ORDER BY
VECTOR_DISTANCE(
embedding,
VECTOR '[0.90,0.80,0.70,0.60]',
COSINE
);
SELECT * FROM TABLE(DBMS_XPLAN.DISPLAY);
/*----------------------------------------------------------------
LAB 49
Use analytic function for AI trend analysis
----------------------------------------------------------------*/
SELECT
month_id,
sales,
LAG(sales) OVER(ORDER BY month_id) AS previous_sales,
sales - LAG(sales) OVER(ORDER BY month_id) AS sales_growth
FROM lab_sales;
/*----------------------------------------------------------------
LAB 50
Create enterprise AI metadata query
----------------------------------------------------------------*/
SELECT
model_name,
algorithm,
mining_function
FROM user_mining_models;
/*===============================================================
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.
===============================================================*/