Machine Learning for SQL: train, deploy, score your models using SQL...😍
Tags: database, sql, machinelearning, datascience
Published: 2022-04-14 21:36:19


![Machine Learning for SQL - Training](https://dev-to-uploads.s3.amazonaws.com/uploads/articles/2awdruko496r0ls5ah7a.png)

👉MachineLearning for SQL: train, deploy and score models right inside the database, no need to move data over the network, stronger security, scalable platform, python and R APIs as well✅

![Machine Learning for SQL - Scoring](https://dev-to-uploads.s3.amazonaws.com/uploads/articles/fik8w4n6rmek0qi05jsw.png)

```
CREATE TABLE customers (
    id                NUMBER  PRIMARY KEY,
    children          NUMBER,
    age               NUMBER,
    gender            CHAR(1),
    salary            NUMBER,
    married           CHAR(1),
    will_buy_an_house NUMBER
);

-- Inserting a very small data set to train 
-- the machine learning model...
INSERT INTO customers VALUES (1,0,20,'F',1200,'N',0);
INSERT INTO customers VALUES (2,3,48,'M',2800,'N',0);
INSERT INTO customers VALUES (3,1,33,'F',2500,'Y',1);
INSERT INTO customers VALUES (4,2,37,'F',1800,'Y',1);
INSERT INTO customers VALUES (5,0,52,'M',2000,'Y',0);
INSERT INTO customers VALUES (6,1,61,'M',1700,'Y',0);
COMMIT;

SELECT * FROM customers;

 ID  CHILDREN  AGE  GENDER  SALARY  MARRIED  WILL_BUY_AN_HOUSE
--- --------- ---- ------- ------- -------- ------------------
  1         0   20       F    1200        N                  0
  2         3   48       M    2800        N                  0
  3         1   33       F    2500        Y                  1
  4         2   37       F    1800        Y                  1
  5         0   52       M    2000        Y                  0
  6         1   61       M    1700        Y                  0

-- Configuration...
CREATE TABLE config (
    setting_name  VARCHAR2(30),
    setting_value VARCHAR2(4000)
);

-- Neural Network algorithm for classification
INSERT INTO config VALUES ('ALGO_NAME', 'ALGO_NEURAL_NETWORK');
-- Automatic data preparation: enabled!
INSERT INTO config VALUES ('PREP_AUTO','ON');
COMMIT;

-- Now training...
BEGIN
    DBMS_DATA_MINING.CREATE_MODEL(
        model_name          => 'NN_Classification',
        mining_function     => DBMS_DATA_MINING.classification,
        data_table_name     => 'customers',
        case_id_column_name => 'id',
        target_column_name  => 'will_buy_an_house',
        settings_table_name => 'config' 
    );
END;
/

PL/SQL procedure successfully completed.
Elapsed: 00:00:00.742

-- Likely to buy an house?
SELECT 100 * 
       PREDICTION_PROBABILITY(NN_Classification, 
              1 USING 
             43 AS age, 
              2 AS children, 
            'M' AS gender, 
           2300 AS salary, 
            'Y' AS married) AS probability_to_buy_an_house
  FROM dual;


              PROBABILITY_TO_BUY_AN_HOUSE
             ----------------------------
                        79.05203574218477

```

📖 [Documentation](https://docs.oracle.com/en/database/oracle/machine-learning/)
💻 [Gist](https://gist.github.com/loiclefevre/0e925987f6bc7e4fb1dfcbc5927a1eda) with code above to reproduce
😺[OML GitHub repository](https://github.com/marancibia/oracle-db-examples/tree/main/machine-learning)
🤯[Oracle LiveLabs](https://apexapps.oracle.com/pls/apex/dbpm/r/livelabs/home)
😎[Oracle Machine Learning blog](https://blogs.oracle.com/machinelearning/)