1. 1. Buat tabel di samping :
·
create table TR_Kodetransaksi
(c_trans number(10) primary key,
e_transaksi varchar(10));
·
insert into TR_Kodetransaksi values
(1,‘setor’);
insert into TR_Kodetransaksi values
(2, ‘tagih’);
insert into TR_Kodetransaksi values
(3, ‘atm’);
insert into TR_Kodetransaksi values
(4, ‘pemindah bukuan’);
insert into TR_Kodetransaksi values
(5, ‘koreksi plus);
insert into TR_Kodetransaksi values
(6, ‘koreksi minus’);
insert into TR_Kodetransaksi values
(7, bunga’);
insert into TR_Kodetransaksi values
(8, ‘administrasi biaya’);
insert into TR_Kodetransaksi values
(9, ‘pajak’);
·
create table TR_Job
(c_job varchar(10) primary key,
n_job varchar(20));
·
insert into TR_Job values (‘PEM’,
‘pemrograman’);
insert into TR_Job values (‘TEL’,
‘teller’);
insert into TR_Job values (‘MGR’,
‘manager’);
insert into TR_Job values (‘SYS’,
‘system analis’);
·
create table TR_Petugas
(I_petugas varchar(10) primary key,
n_petugas varchar(20));
·
insert into TR_Petugas values
(‘PT6-01’,’nina’);
insert into TR_Petugas values
(‘PT6-02’,’dea’);
·
create table TR_Agama
(c_agama varchar(10) primary key,
n_agama varchar(20));
insert into TR_Agama values (001,
‘islam’);
insert into TR_Agama values (002,
‘kristen’);
insert into TR_Agama values (003,
‘hindu’);
·
create table nasabah
(
i_rek number(10) primary key,
n_nasabah varchar(20),
a_nasabah varchar(20),
d_lahir date,
c_jobvarchar(10) constraint
nasabah_PK_c_job references TR_job (c_job),
c_agama int(10) constraint nasabah_PK_c_agama
references TR_agama (c_agama)
)
·
insert into nasabah values
(‘1234’,’paris’,’sari asih 4’,’20-10-1992’,’PEM’,’001’);
insert into nasabah values
(‘4321’,’abdul’,’sari asih 4’,’2-05-1994’,’SYS’,’002’);
insert into nasabah values
(‘2121’,’riko’,’cijerokaso’,’8-01-1993’,’SYS’,’003’);
·
create table TM_tabungan
(
i_transaksi varchar(10) primary key
c_transaksi number(10) constraint
tabungan_PK_c_transaksi references TR_codetransaksi(c_transaksi),
d_transaksi date,
v_debit int(50),
v_kredit number(20),
v_saldo number(20),
i_petugas varchar(10) constraint
tabungan_PK_i_petugas references TR_petugas (i_petugas),
i_rekening number(10) constraint
tabungan_PK_i_rekening references TR_nasabah (i_rekening)
insert into TM_tabungan values (‘trans01’,’1’,’4-04-2012’,’4.000.000’,’’,’4.000.000’,’PT6-01’,’1234’);
insert into TM_tabungan values
(‘trans08’,’3’,’5-052012’,’12.000.000’,’’,’12.000.000’,’PT6-2’,’4321’);
1. 4. Keluarkan
data nasabah yang mempunyai saldo terkecil (i_rek, n_nasabah, v_saldo) :
insert into tm_tabungan
(i_transaksi, c_trans, d_trans, v_debet, v_kredit, v_saldo, i_rek, i_petugas)
values (09071992, 1,
’25-october-2012′, 10000000,’0′, 10000000, 123456789, ‘ptg-01′);
select tr_nasabah.i_rek,
tr_nasabah.n_nasabah, tm_tabungan.v_saldo
from tr_nasabah inner join
tm_tabungan on tr_nasabah.i_rek=tm_tabungan.i_rek
where v_saldo=(select min (v_saldo)
from tm_tabungan group by
tr_nasabah.i_rek, tr_nasabah.n_nasabah);
1. 5. keluarkan data bebagaiberikut:
a. c_trans, e_trans, sum(v_debet),
sum(v_kredit).
selecttr_kodetransaksi.c_trans,
tr_kodetransaksi.e_trans, sum (tm_tabungan.v_debet), sum (tm_tabungan.v_kredit)
from tm_tabungan inner join tr_kodetransaksi on
tr_kodetransaksi.c_trans=tm_tabungan.c_trans group by tr_kodetransaksi.c_trans,tr_kodetransaksi.e_trans;
b. 5. Keluarkan data bebagai
berikut:
a.
c_trans, e_trans, sum(v_debet), sum(v_kredit).
select tr_kodetransaksi.c_trans,
tr_kodetransaksi.e_trans, sum (tm_tabungan.v_debet), sum (tm_tabungan.v_kredit)
from tm_tabungan inner join tr_kodetransaksi on
tr_kodetransaksi.c_trans=tm_tabungan.c_trans group by
tr_kodetransaksi.c_trans,tr_kodetransaksi.e_trans;
b.
c_trans, e_trans, sum(v_debet), sum(v_kredit) antara tgl 2-0ct-2012 sampai
29-oct-2012
select tr_kodetransaksi.c_trans,
tr_kodetransaksi.e_trans, sum (tm_tabungan.v_debet), sum (tm_tabungan.v_kredit)
from tm_tabungan inner join tr_kodetransaksi on
tr_kodetransaksi.c_trans=tm_tabungan.c_trans where d_trans between ‘2-0ct-2012
and ’29-oct-2012’ group by tr_kodetransaksi.c_trans,tr_kodetransaksi.e_trans;
c.
Menampilkan jumlah data transaksi
select count (*) as “jumlah”,
c_trans from tm_tabungan group by c_trans;
