Oracle SQL - Write SQL to fill the new SALGRADES2 table with a single INSERT statement, using the existing SALGRADES table.

  1. HOME
  2. Oracle SQL
  3. Data Change Command
  4. INSERT Command

Requirements

Here is the table

Demo

SQL>
SQL> create table salgrades(
  2  grade      NUMBER(2)   primary key,
  3  lowerlimit NUMBER(6,2) not null check (lowerlimit >= 0),
  4  upperlimit NUMBER(6,2) not null,
  5  bonus      NUMBER(6,2) not null);

Table created.-- from w ww .  j  a  va2 s.com

SQL>
SQL> insert into salgrades values (1,  700,1200,   0);
SQL> insert into salgrades values (2, 1201,1400,  50);
SQL> insert into salgrades values (3, 1401,2000, 100);
SQL> insert into salgrades values (4, 2001,3000, 200);
SQL> insert into salgrades values (5, 3001,9999, 500);
SQL>

create or replace type SALRANGE_TYPE
as varray(2) of number(6,2);
/

create table salgrades2
( grade      number(2)   constraint S2_PK
                         primary key
, salrange   SALRANGE_TYPE  constraint S2_RANGE_NN
                         not null
, bonus      NUMBER(6,2) constraint S2_BONUS_NN
                         not null
) ;

Fill the new SALGRADES2 table with a single INSERT statement, using the existing SALGRADES table.

Demo

SQL>
SQL>-- from   w w  w .  ja  v  a 2  s.  co  m
SQL> insert into salgrades2
  2  select grade
  3  ,      SALRANGE_TYPE(lowerlimit,upperlimit)
  4  ,      bonus
  5  from   salgrades;
SQL>
SQL> col salrange format a25
SQL>
SQL> select * from salgrades2;

     GRADE SALRANGE                       BONUS
---------- ------------------------- ----------
         1 SALRANGE_TYPE(700, 1200)           0
         2 SALRANGE_TYPE(1201, 1400)         50
         3 SALRANGE_TYPE(1401, 2000)        100
         4 SALRANGE_TYPE(2001, 3000)        200
         5 SALRANGE_TYPE(3001, 9999)        500
SQL>

Related Quiz