1. Get rid of all advertisements and get unlimited access to documents by upgrading to Premium Membership. Upgrade to Premium Now and also get a Premium Badge!

BMI counting using oracle

Discussion in 'SQL PL/SQL' started by monkey, Jun 6, 2014.

  1. monkey

    monkey Long Term Member

    Messages:
    156
    Likes Received:
    0
    Trophy Points:
    230
    Hello!
    Please, if we have different tables in our db and want to get for certain names BMIs (from their mass and height values form other tables), and if we want to insert to a certain table values mass*height whichare in other tables , how can we make the formula at inserting to do it authomattically?

    Hope U understood me.
    Sth like that is not a problem to do in excell, where we have cells with values AND names.

    Is sth like that possible in sql developer?
    many thanks!!!
     
  2. monkey

    monkey Long Term Member

    Messages:
    156
    Likes Received:
    0
    Trophy Points:
    230
    f.e.

    create table student(
    id_student int ...
    name...
    surname...
    mass...
    height...
    bmi...
    );

    insert into student values(1, 'a', 'A', 20,2, ?);

    How to write an expression instead of replacing ? with mass/height^2 by manual counting?

    MANY THANKS!!!

    many thanks!!!
     
  3. rajenb

    rajenb Forum Expert

    Messages:
    361
    Likes Received:
    114
    Trophy Points:
    655
    Location:
    Mauritius
    Hi,

    You can achieve this using the virtual column definition for column BMI (Oracle 11g ?).

    Try the following SQL:

    Code (SQL):
    SQL> CREATE TABLE student(
    id_student INTEGER,
    name       varchar2(10),
    surname    varchar2(10),
    mass       NUMBER,
    height     NUMBER,
    bmi        NUMBER generated always AS (mass/ (height*height))
    );  

    TABLE created.


    SQL> INSERT INTO student(id_student, name, surname, mass, height) VALUES(1, 'a', 'A', 20,2);

    1 ROW created.


    SQL> INSERT INTO student(id_student, name, surname, mass, height) VALUES(1, 'a', 'A', 20,2);

    1 ROW created.

    SQL> SELECT * FROM student;

    ID_STUDENT NAME       SURNAME          MASS HEIGHT        BMI
    ---------- ---------- ---------- ---------- ---------- ----------
         1 a          A          20      2      5

     
    Note: You can also use a database trigger to achieve same results or even more complex calculations or logic which cannot be handled by the virtual column definition expression.
     
    monkey likes this.
  4. monkey

    monkey Long Term Member

    Messages:
    156
    Likes Received:
    0
    Trophy Points:
    230
    Oo, people, u ar emore then ingenious!!!
    please, just one question now: if I have mass and height in some other tables, and want BMI in third one, how do I get the values?

    sth like:
    ...
    bmi number generated always AS (first.mass/(second.height*second.height)) ???

    many thansk!!
     
  5. rajenb

    rajenb Forum Expert

    Messages:
    361
    Likes Received:
    114
    Trophy Points:
    655
    Location:
    Mauritius
    Oh no ... not that's asking too much from virtual columns ;)

    But, as I mentioned earlier you can still achieve it using database triggers.

    P.S: You could also use views to get BMI calculated using data from one or multiple tables depending on your requirement.
     
    monkey likes this.
  6. monkey

    monkey Long Term Member

    Messages:
    156
    Likes Received:
    0
    Trophy Points:
    230
    Hello!
    Would this be the right syntsax? (I appolo9gise, have no oracle here)

    Code (SQL):

    CREATE VIEW experiment AS
    SELECT FIRST.mass AS fm, SECOND.height AS sh
    FROM FIRST, SECOND
    WHERE id_first=id_second;

    SELECT bmi ...?...
    FROM experiment
    WHERE bmi=fm/(sh*sh);
     
     
  7. rajenb

    rajenb Forum Expert

    Messages:
    361
    Likes Received:
    114
    Trophy Points:
    655
    Location:
    Mauritius
    There is a simpler way of doing it (if I ignore your syntax errors...):

    Code (SQL):
    SQL> CREATE TABLE student(
    id         INTEGER,
    name       varchar2(10),
    surname    varchar2(10)
    );

    TABLE created.

    SQL> CREATE TABLE mass (
    student_id  INTEGER,
    mass        NUMBER
    );

    TABLE created.

    SQL> CREATE TABLE height(
    student_id  INTEGER,
    height      NUMBER
    );

    TABLE created.

    --
    SQL> INSERT INTO student VALUES
      (10, 'Alain', 'Driver');

    1 ROW created.

    SQL> INSERT INTO mass VALUES
      (10, 60);

    1 ROW created.

    SQL> INSERT INTO height VALUES
      (10, 1.7);

    1 ROW created.

    SQL> COMMIT;

    Commit complete.

    SQL> CREATE VIEW student_v AS
    SELECT s.id,
      s.name,
      s.surname,
      m.mass,
      h.height,
      m.mass/(h.height*h.height) bmi
    FROM mass m,
      height h,
      student s
    WHERE h.student_id = s.id
    AND m.student_id   = s.id ;

    VIEW created.

    SQL> SELECT * FROM student_v;

        ID NAME       SURNAME          MASS HEIGHT        BMI
    ---------- ---------- ---------- ---------- ---------- ----------
        10 Alain      Driver         60    1.7 20.7612457
     
     
    monkey likes this.
  8. monkey

    monkey Long Term Member

    Messages:
    156
    Likes Received:
    0
    Trophy Points:
    230
    People, what would I do without you!!!