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;