KUIS
BASISDATA
1. Latihan
1 :
select ename from emp where ename like '__I%'
2. Tabel
trpetugas :
create table trpetugas
(
I_petugas varchar(10) primary key,
n_petugas varchar(30)
);
3. Tabel
tr kode_transaksi :
create table trkode_transaksi
(
c_transaksi varchar(10) primary
key,
e_transaksi varchar(30)
);
4. Tabel
trjob :
create table trjob
(
c_job varchar(10) primary key,
n_job varchar(30)
);
5. Tabel
tragama :
create table tragama
(
c_agama varchar(10) primary key,
nama_agama varchar(30)
);
6. Tabel
tmtabungan :
create table tmtabungan
(
I_transaksi varchar(20) constraint
tmtabungan_pk primary key,
c_transaksi varchar(20) constraint
tmtabungan_pk_c_transaksi references trkode_transaksi(c_transaksi),
D_transaksi Date,
v_debit varchar(30),
v_kredit varchar(30),
v_saldo varchar(30),
I_petugas varchar(10) constraint
tmtabungan_pk_I_petugas references trpetugas(I_petugas),
I_rek varchar(10) constraint
tmtabungan_pk_I_rek references nasabah(I_rek)
);
7. Tabel
nasabah :
create table nasabah
(
i_rek varchar(10) constraint
nasabah_pk primary key,
n_nasabah varchar(30),
A_nasabah varchar(30),
D_lahir Date,
c_job varchar(10) constraint
nasabah_pk_c_job references trjob(c_job),
c_agama varchar(10) constraint
nasabah_pk_c_agama references tragama(c_agama)
);
8. Insert
trpetugas :
insert into trpetugas
values('PTG-01','Fuady');
insert into trpetugas
values('PTG-02','Jefri');
9. Insert
trkode_transaksi :
insert into trkode_transaksi
values(1,'setor');
insert into trkode_transaksi
values(2,'tarik');
insert into trkode_transaksi
values(3,'atm');
insert into trkode_transaksi
values(4,'pemindah_bukuan');
insert into trkode_transaksi
values(5,'koreksi_plus');
insert into trkode_transaksi
values(6,'koreksi_minus');
insert into trkode_transaksi
values(7,'bunga');
insert into trkode_transaksi
values(8,'administrasi_biaya');
insert into trkode_transaksi
values(9,'pajak');
10. Insert
trjob :
insert into trjob
values('PGM','Programer');
insert into trjob
values('Tel','Teller');
insert into trjob
values('MGR','Manager');
insert into trjob
values('SYS','System Analis);
11. Insert
tragama :
insert into tragama
values('IS','Islam');
insert into tragama
values('K','Kristen');
insert into tragama
values('H','Hindu');
insert into tragama
values('B','Budha');
12. Insert
tmtabungan :
insert into tmtabungan
values('transaksi-01','1','20-Jan-12','600000','300000','300000','PTG-01','1');
insert into tmtabungan
values('transaksi-02','1','25-Jan-12','1000000','500000','500000','PTG-01','1');
13. Insert
nasabah :
insert into nasabah values
(001,'Joko','Magelang','25-Jan-93','PGM','IS');
14. Innerjoin
nasabah dan tmt tabungan, memunculkan saldo paling kecil :
select nasabah.I_rek,
nasabah.n_nasabah, tmtabungan.v_saldo
from tmtabungan
inner join nasabah
on tmtabungan.I_rek=nasabah.I_rek
where v_saldo=(select min (v_saldo) from
tmtabungan);
15. Jawaban
soal 5a :
select
trkode_transaksi.c_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;
16. Jawaban
soal 5b :
select
trkode_transaksi.c_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 where
d_transaksi between ‘20-Jan-2012’ group by trkode_transaksi.c_transaksi,
trkode_transaksi.e_transaksi;
17. Jawaban
soal 5c :
select count (*) as “jumlah”, c_transaksi
from tmtabungan group by c_transaksi;
