KUIS DATA BASE II
1.
Buat
tabel di samping
Query
buat table
create
table TR_kodetransaksi
(
c_transaksi
number(10) primary key,
e_transaksi
varchar(10)
)
insert
into TR_kodetransaksi values (‘1’,‘sektor’);
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
tabel 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
int(10) primary key,
n_agama
varchar(24)
)
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’,’anggi’,’sarijadi’,’1-01-1991’,’PEM’,’001’);
insert
into nasabah values (‘4321’,’putra’,’sarijadi’,’2-02-1992’,’SYS’,’002’);
insert
into nasabah values (‘2121’,’wibowo’,’jln tikus’,’3-03-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,
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’);
4. Keluarkan data nasabah (i_rek, n_nasabah, v_saldo)
select
TM_tabungan.i_rek, nasabah.n_nasabah, TM_tabungan.v_saldo from TM_tabungan
inner join nasabah on nasabah.i_rek, TM_tabungan.i_rek where v_saldo = (select
min (v_saldo) fromTM_tabungan);
5. Jawaban soal 5a :
select
trkode_transaksi.c_transaksi,
trkode_transaksi.e_transaksi,
trkode_transaksi.e_transaksi,
sum(tmtabungan.v_debet),
sum(tmtabungan.v_kredit)
from tmtabungan inner join
trkode_transaksi on
trkode_transaksi.c_transaksi=tmtabungan.c_transaksi
group by
trkode_transaksi.c_transaksi,
trkode_transaksi.e_transaksi;
Jawaban soal 5b :
select
trkode_transaksi.c_transaksi,
trkode_transaksi.e_transaksi,
trkode_transaksi.e_transaksi,
sum(tmtabungan.v_debet),
sum(tmtabungan.v_kredit) from
tmtabungan inner join trkode_transaksi
tmtabungan inner join trkode_transaksi
on
trkode_transaksi.c_transaksi=tmtabungan.c_transaksi
whered_transaksi between
whered_transaksi between
‘20-Jan-2012’ group by trkode_transaksi.c_transaksi,
trkode_transaksi.e_transaksi;
trkode_transaksi.e_transaksi;
Jawaban soal 5c :
select count (*) as “jumlah”,
c_transaksi from tmtabungan
group by c_transaksi;
group by c_transaksi;
