Script praktikum oracle

1)    create table trkodetransaksi (c_trans number(5) constraint trkodetransaksi_PK primary key, e_transaksi varchar(30));

create table trjob (c_job number(5) constraint trjob_PK primary key, n_job varchar(50))

create table trpetugas(i_petugas number(5) constraint trpetugas_PK primary key, n_petugas varchar(30))

create table tragama (c_agama number(5) constraint tragama_PK primary key, n_agama varchar(50))

create table nasabah (i_rek number(5) constraint nasabah_PK primary key, n_nasabah varchar(50), a_nasabah varchar(50), d_lahir date, c_job number(4) constraint nasabah_PK_c_job references trjob(c_job), c_agama number(4) constraint nasabah_PK_c_agama references tragama(c_agama));

create table tabungan (i_transaksi number(5) constraint tabungan_PK primary key, c_trans number(5) constraint tabungan_PK_c_trans references trkodetransaksi(c_trans), d_transaksi date, v_debet number(15), v_kredit number(15), v_saldo number(15), i_petugas number(5) constraint tabungan_PK_i_petugas references trpetugas(i_petugas), i_rek number(5) constraint tabungan_PK_i_rek references nasabah(i_rek))

2)    insert into trkodetransaksi values(1,'setor');

insert into trkodetransaksi values(2,'tarik');

insert into trkodetransaksi values(3,'atm');

insert into trkodetransaksi values(4,'pemindah_bukuan');

insert into trkodetransaksi values(5,'koreksi_plus');

insert into trkodetransaksi values(6,'koreksi_minus');

insert into trkodetransaksi values(7,'bunga');

insert into trkodetransaksi values(8,'administrasi_biaya');

insert into trkodetransaksi values(9,'pajak');

insert into trjob values(1,'programmer');

insert into trjob values(2,'teller');

insert into trjob values(3,'manager');

insert into trjob values(4,'system_analyst');

insert into trpetugas values(1,'ame');

insert into trpetugas values(2,'mei');

insert into trpetugas values(3,'dea');

insert into trpetugas values(4,'andi');

3)    insert into tragama values(1,'islam');

insert into tragama values(2,'protestan');

insert into tragama values(3,'katolik');

insert into tragama values(4,'hindu');

insert into tragama values(5,'budha');

insert into nasabah values(11,'bryan_mcfadden', 'westlife', '18-apr-80', 4, 1);

insert into nasabah values(12,’nicky_byrne’, ‘boyzone’, '25-jan-78', 1, 2);

insert into nasabah values(13,’shane_filan’, ‘backstreet_boys’, '26-sep-79', 3, 1);

insert into tabungan values(01, 1,  ’23-feb-12’ , 50000000, 0, 17000000, 1, 13);

insert into tabungan values(02, 2, ’24-jul-12’ , 0, 15000000, 2500000, 3, 11);

insert into tabungan values(03, 3, ’20-jun-12’ , 7000000, 0, 20000000, 2, 12);

4)    select tabungan .i_rek, nasabah.n_nasabah, tabungan.v_saldo from tabungan inner join nasabah on nasabah.i_rek=tabungan.i_rek where v_saldo=(select min(v_saldo) from tabungan);

5)    a) select trkodetransaksi.c_trans, trkodetransaksi.e_transaksi, sum(tabungan.v_debet), sum(tabungan.v_kredit) from tabungan inner join trkodetransaksi on trkodetransaksi.c_trans=tabungan.c_trans group by trkodetransaksi.c_trans, trkodetransaksi.e_transaksi;

 b) select trkodetransaksi.c_trans, trkodetransaksi.e_transaksi, sum(tabungan.v_debet), sum(tabungan.v_kredit) from tabungan inner join trkodetransaksi on trkodetransaksi.c_trans=tabungan.c_trans where d_transaksi between ‘31-jul-2012’ group by trkodetransaksi.c_trans, trkodetransaksi.e_transaksi;

 c) select count (*) as “jumlah”, c_trans from tm_tabungan group by c_trans;