Jawaban

 --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'