Machine Learning with SQL on Oracle ATP — Iris Dataset 🌸

This project demonstrates how to build, train, and evaluate a machine learning classification model using pure SQL on an Oracle Autonomous Database (19c), following the workflow from the article:

Machine Learning with SQL — It’s Easier Than You Think by Dario Radečić.

We replicate the Iris dataset classification example using Oracle’s built‑in Machine Learning capabilities (DBMS_DATA_MINING) and SQL Developer.

---

📂 Files

• iris-ml.sql — SQL script to create table, load data, train model, and evaluate predictions.
• iris.csv — Iris dataset (150 rows, 5 features).
• wallet.zip — Oracle Cloud ATP wallet for secure DB connection.
• screenshots/ — example outputs.

---

🧰 Prerequisites

✅ Oracle Cloud Free Tier account
✅ Autonomous Transaction Processing (ATP) database created & running
✅ SQL Developer (standalone or VS Code extension)
✅ Wallet downloaded for ATP database

---

🚀 Steps to Run

☁️ Part 1: Set up Oracle Cloud ATP and upload the dataset

These steps walk you through setting up everything from scratch in Oracle Cloud.

1️⃣ Sign up for Oracle Cloud (if you don’t already have an account)

• 👉 https://www.oracle.com/cloud/free/
• Free Tier is sufficient for this project.

2️⃣ Create an Autonomous Database (ATP)

• Go to https://cloud.oracle.com/
• ☰ → Oracle Database → Autonomous Database → Create Autonomous Database
• Use:
  • Workload Type: Transaction Processing
  • Database Version: 19c
  • Always Free: ✅ enabled if available
• Click Create Autonomous Database

3️⃣ Download the Cloud Wallet

• When the ATP is ready (Status = Available), open it.
• Click DB Connection → Download Wallet
• Set a password, download wallet.zip, and save it to your project folder.

4️⃣ Upload iris.csv to the ATP database

• From the ATP details page, open SQL Developer Web under Tools
• Log in as ADMIN with your password
• Go to Data Load → Select schema (e.g., ADMIN) → choose table IRIS (after running the script below)
• Upload iris.csv
• 🔴 Important: The ID column must be present and unique in the uploaded data
  • If your CSV does not contain the ID column:
    • After uploading, run this query in SQL Developer or VS Code to generate it:

      ALTER TABLE IRIS ADD (ID NUMBER);
      UPDATE IRIS SET ID = ROWNUM;

• Map the columns to: ID, SEPAL_LENGTH, SEPAL_WIDTH, PETAL_LENGTH, PETAL_WIDTH, SPECIES
• Click Next → Load Data

---

💻 Part 2: Run the SQL script from VS Code

1️⃣ Install SQL Developer Extension

• In VS Code: go to Extensions → search Oracle Developer Tools for VS Code
• Install it

2️⃣ Create a new Oracle DB Connection

• Open the Oracle SQL Developer panel (database icon)
• Click + to add a new connection:
  • Connection Name: ATP19c
  • Username: ADMIN
  • Password: (what you set for ATP)
  • Wallet Location: path to wallet.zip
  • Service Name: choose from the wallet (e.g., yourdbname_high)

3️⃣ Connect and Run SQL

• Right‑click the saved connection → Connect
• Open iris-ml.sql
• Run the script:
  • Right-click → Run Query, or
  • Select all and hit the "Run" icon

4️⃣ View Outputs

• The script:
  • Creates the IRIS table
  • Creates a settings table
  • Trains a Decision Tree model
  • Predicts on the same data
  • Calculates accuracy
  • Displays a confusion matrix via SQL

✅ You’ll see predictions like setosa, virginica, etc., and model accuracy around 95–97%.

---

📊 Output Example

• Model: IRIS_MODEL
• Accuracy: ~96%
• Confusion Matrix: see script output.

---

📖 Acknowledgments

This project is a replication of the excellent article:

Machine Learning with SQL — It’s Easier Than You Think by Dario Radečić..

Implemented on Oracle ATP Free Tier with SQL Developer.

---

📝 License

See LICENSE for details.