--Jawaban No. 7
create database BANK
use BANK
--Jawaban No. 8
a. create table cabang(kode_cabang char(10)primary key,cabang varchar(50),alamat_cabang varchar(100))
b. create table jenis_tabungan(kode_tabungan char(10)primary key,jenis_tabungan varchar(50))
c. create table nasabah(kode_tabungan char(10)foreign key references jenis_tabungan(kode_tabungan),
no_rekening char(10)primary key,nama varchar(50),alamat_nasabah varchar(25),saldo int)
d. create table header_transaksi(no_transaksi char(10)primary key,tgl_transaksi date,
kode_cabang char(10)foreign key references cabang(kode_cabang))
e. create table detail_transaksi(no_transaksi char(10)foreign key references header_transaksi(no_transaksi),
no_rekening char(10)foreign key references nasabah(no_rekening),
jenis_transaksi varchar(50),jml_transaksi int)
--Jawaban No. 9
a. insert into cabang values('CBNI-0001','BNI Abdul Muis','Jl.Abdul Muis No.7A, Jakpus,Gedung Departemen Perhubungan')
insert into cabang values('CBNI-0002','BNI Cempaka Mas','Jl.Letjen Suprapto Jakarta Pusat,Rukan Graha Cempaka Mas Blok A No.30A')
insert into cabang values('CBNI-0003','BNI Cikini','Jl.Cikini Raya Kav.62-64 Jakarta Pusat 10330')
b. insert into jenis_tabungan values('TBNI-001','BNI Taplus')
insert into jenis_tabungan values('TBNI-002','BNI Taplus Bisnis')
insert into jenis_tabungan values('TBNI-003','BNI Haji')
c. insert into nasabah values ('TBNI-001','000000-01','Ali Mukmin','Jakarta Selatan',500000)
insert into nasabah values ('TBNI-002','000000-02','Fika Firdasari','Jakarta Pusat',100000)
insert into nasabah values ('TBNI-003','000000-03','Rosita','Jakarta Barat',5000000)
d. insert into header_transaksi values('0000000001','10/02/2019','CBNI-0001')
insert into header_transaksi values('0000000002','10/02/2019','CBNI-0002')
insert into header_transaksi values('0000000003','10/02/2019','CBNI-0003')
e. insert into detail_transaksi values('0000000001','000000-01','S',500000)
insert into detail_transaksi values('0000000002','000000-02','S',1000000)
insert into detail_transaksi values('0000000003','000000-03','S',5000000)
--Jawaban No. 10
select * from cabang
select * from detail_transaksi
select * from header_transaksi
select * from jenis_tabungan
select * from nasabah
--Jawaban No. 11
select nasabah.kode_tabungan, nasabah.no_rekening, nasabah.nama,
nasabah.alamat_nasabah, nasabah.Saldo from nasabah inner join jenis_tabungan on
nasabah.kode_tabungan = jenis_tabungan.kode_tabungan
--Jawaban No. 12
create view View_Transaksi as
Select ns.kode_tabungan, ns.no_rekening, ns.nama,ns.alamat_nasabah,
ns.saldo, cb.kode_cabang, cb.cabang, cb.alamat_cabang, ht.no_transaksi, ht.tgl_transaksi,
dt.jenis_transaksi, dt.jml_transaksi
From nasabah ns
inner join detail_transaksi dt on dt.no_rekening = ns.no_rekening
inner join header_transaksi as ht on ht.no_transaksi = dt.no_transaksi
inner join cabang as cb on cb.kode_cabang = ht.kode_cabang
select * from View_Transaksi
--Jawaban No. 13
select * from View_Transaksi where saldo < 500000
--Jawaban No. 14
select * from View_Transaksi where saldo > 500000
--Jawaban No. 15
select * from View_Transaksi where saldo > 500000 and nama like '%f'
--Jawaban No. 16
select No_Transaksi, COUNT(No_Transaksi) as Jumlah from View_Transaksi group by
No_Transaksi
--Jawaban No. 17
delete from detail_transaksi where no_transaksi = '0000000001'
delete from header_transaksi where no_transaksi = '0000000001'
--Jawaban No. 18
create Trigger tambah_detail_transaksibank
on detail_transaksi after insert
as begin
IF exists(select * from insertted where jenis_transaksi='s')
begin
update nasabah set saldo = saldo + jml_transaksi
from nasabah join inserted
on nasabah.no_rekening = inserted.no_rekening
end
else
begin
update nasabah set saldo = saldo - jml_transaksi
from nasabah join inserted
on nasabah.no_rekening = inserted.no_rekening
end
end
go
--Jawaban No. 19
create Procedure tambah_header_transaksibank
@No_Transaksi char (10),
@Tgl_Transaksi date,
@kode_Cabang char (10)
as begin
insert into header_transaksi values
(@No_Transaksi,@Tgl_Transaksi,@kode_Cabang)
select *from header_transaksi
end
go
--Jawaban No. 20
create Procedure tambah_detail_transaksibank
@No_Transaksi char (10),
@no_rekening char (10),
@Jenis_transaksi varchar (50),
@Jml_Transaksi int
as begin
insert into detail_transaksi values
(@No_Transaksi,@no_rekening,@Jenis_Transaksi,@Jml_Transaksi)
select * from detail_transaksi
end
go
--Jawaban 21
exec tambah_header_transaksibank '0000000005','10/02/2019','CBNI-0001'
exec tambah_header_transaksibank '0000000006','10/02/2019','CBNI-0002'
exec tambah_detail_transaksibank '0000000005','000000-01','S','5000000'
exec Tambah_Detail_transaksibank '0000000006','000000-02','T','1000000'