Soal SQL

 

--Nama            : M. Arsyad Yunus
--Nim            : 09.01.2646
--Kls            : D3TI-4C
--Mata Kuliah    : Pemrograman Client Server

CREATE DATABASE transaksitabungan_2646
ON
( NAME = transaksitabungan_2646_dat,
FILENAME = 'D:\KULIAH\TUGAS KULIAH\Client&Server\transaksitabungan_2646.mdf',
SIZE = 10MB,
MAXSIZE = 50MB,
FILEGROWTH = 5MB)
LOG ON
( NAME = transaksitabungan_2646_log,
FILENAME = 'D:\KULIAH\TUGAS KULIAH\Client&Server\transaksitabungan_2646.ldf',
SIZE = 10MB,
MAXSIZE = 50MB,
FILEGROWTH = 5MB)
GO
use transaksitabungan_2646
GO
create table cabang
(
    kd_cabang char (3)  primary key not null,
    nm_cabang varchar (30) not null,
    alamat varchar (50) not null,
    )
GO
create table rekening
(
    no_rekening char (12) primary key not null,
    cab char (3) foreign key references cabang (kd_cabang) not null,
    nm_nasabah varchar (30) not null,
    tgl_lahir date,
    jk char (1) check (jk in ('P','L')),
    alamat varchar (50) not null,
    saldo int not null,
    )
GO
create table jenisTransaksi
(
    kd_jenis char (2) primary key not null,
    nm_jenis varchar (20) not null
    )
GO
create table transaksi
(
    kd_transaksi char (12) primary key not null,
    tgl_transaksi date,
    no_rekening char (12) foreign key references rekening (no_rekening),
    jenis char (2) foreign key references jenisTransaksi (kd_jenis),
    debit int default 0,
    kredit int default 0
    )
GO
insert into cabang values('001','Bank BCA Cab Kaliurang','Jl. Kaliurang Yogyakarta')
insert into cabang values('002','Bank BCA Cab Colombo','Jl. Colombo Yogyakarta')
GO
insert into rekening values('001-10-00001','001','M. Arsyad Yunus','1990/10/10','L',
                            'Jl. Cik Ditiro no 24/30 DIY',4500000)
insert into rekening values('001-10-00002','001','Fajar Muis','1995/08/11','L',
                            'Jl. Kamboja no 14 DIY',650000)
insert into rekening values('002-11-00001','002','Surianti','1991/11/05','P',
                            'Jl. Sriwijaya no 9 DIY',2800000)
GO
insert into jenisTransaksi values('01','Penyetoran')
insert into jenisTransaksi values('02','Penarikan')
insert into jenisTransaksi values('03','Pindah Saldo')
insert into jenisTransaksi values('04','Bunga')   
GO
insert into transaksi values('100712.00001','2010/07/12','001-10-00001','02',150000,0)
insert into transaksi values('101116.00038','2010/11/16','001-10-00002','01',0,1500000)
insert into transaksi values('101129.00055','2010/11/29','002-11-00001','01',0,1200000)
insert into transaksi values('110104.00121','2011/01/04','002-11-00001','02',140000,0)
insert into transaksi values('110217.00456','2011/02/17','001-10-00001','04',0,3700)
GO
select * from cabang
select * from rekening
select * from jenisTransaksi
select * from transaksi
--View untuk menampilkan NoRek,Nama Cabang, NamaNasabah,tgllahir,
--jk(jika L maka laki‐laki, jika P maka perempuan), alamat,saldo
create view ViewData1 as
select a.no_rekening as Nomor_Rekening,b.nm_cabang as Nama_Cabang,a.nm_nasabah
       as Nama_Nasabah,a.tgl_lahir as Tanggal_Lahir,case a.jk when 'L' then 'Laki-Laki'
       else 'Perempuan' end as Jenis_Kelamin,a.alamat as Alamat,a.saldo as Saldo
from rekening a
join cabang b ON
a.cab = b.kd_cabang
select * from ViewData1

--View untuk menampilkan TglTransaksi, NoRek,NamaNasabah, NamaJenisTransaksi, debit, kredit, saldo
create view ViewData2 as
select a.tgl_transaksi as Tanggal_Transaksi,a.no_rekening as Nomor_Rekening,c.nm_nasabah as
       Nama_Nasabah, b.nm_jenis as Nama_Jenis_Transaksi,a.debit as Debit,a.kredit as Kredit,
       c.saldo as Saldo
from transaksi a
join jenisTransaksi b ON
a.jenis=b.kd_jenis
join rekening c ON
a.no_rekening=c.no_rekening
select * from ViewData2

--menampilkan No rekening, Nama Nasabah, Nama Cabang, Alamat Cabang, Nama Jenis Transaksi, debit, kredit, saldo
create view ViewData3 as
select a.no_rekening as Nomor_Rekening,a.nm_nasabah as Nama_Nasabah,b.nm_cabang as
       Nama_Cabang,b.alamat as Alamat_Cabang,c.nm_jenis as Nama_Jenis,d.debit as Debit,
       d.kredit as Kredit,a.saldo as Saldo
from rekening a
join cabang b ON
a.cab = b.kd_cabang
join transaksi d ON
a.no_rekening = d.no_rekening
join jenisTransaksi c ON
d.jenis = c.kd_jenis
select * from ViewData3

--Menampilkan kode cabang, nama cabang,tanggal transaksi, kode transaksi, nama jenis transaksi, nomor rekening, nama nasabah, alamat nasabah, debit, kredit, saldo
create view ViewData4 as
select a.kd_cabang,a.nm_cabang,b.tgl_transaksi,b.kd_transaksi,c.nm_jenis,d.no_rekening,d.nm_nasabah,d.alamat,b.debit,b.kredit,d.saldo
from cabang a
join rekening d ON
a.kd_cabang = d.cab
join transaksi b ON
d.no_rekening = b.no_rekening
join jenisTransaksi c ON
b.jenis = c.kd_jenis
select * from ViewData4

----------------------------------------------------------------------------------------------------------

--Fungsi untuk membuat No Rekening baru
create function FcNoRekeningBaru(@tgl datetime,@cab char(3))
returns char(12)
as
begin declare
@baru char(12),@max int, @th char(2)
set @th=right (year (@tgl),2)
select @max = max (right(no_rekening,4)) from rekening
where left (no_rekening,7)=@cab+@th
if @max is null
set @baru = @cab+'-'+@th+'-'+'00001'
else
set @baru =@cab + @th+ right ('0000'+cast (@max +1 as varchar(4) ),4)
return @baru
end
go
--memanggil fungsi
select * from rekening
select dbo.FcNoRekeningBaru ('2011-06-09','001')as No_Rekening_Baru

--Fungsi untuk membuat Kode Transaksi baru
create function FcKdTransaksiBaru(@tgl datetime)
returns char(12)
as
begin declare
@baru char(12),@max int, @th char(2),@bln char (2),@hr char (2)
set @th=right (year (@tgl),2)
set @bln=right ('0'+cast(month(@tgl) as varchar (2)),2)
set @hr=right('0'+cast(day(@tgl) as varchar (2)),2)
select @max = max (right(kd_transaksi,4)) from transaksi
where left (kd_transaksi,7)=@th+@bln+@hr
if @max is null
set @baru = @th+@bln+@hr+'.'+'00001'
else
set @baru = @th+@bln+@hr+right ('0000'+cast (@max +1 as varchar(4) ),4)
return @baru
end
go
--Memanggil Fungsi
select dbo.FcKdTransaksiBaru ('2011-06-09') as kd_transaksi_baru
select * from transaksi

--Fungsi untuk menghitung jumlah debit dan kredit pada tanggal tertentu

create function FcTotDebitDanKredit(@tgl datetime)
returns int
as
begin
declare @jml int, @dbt int, @kret int
select @dbt=(debit) from Transaksi
select @kret=(kredit)from transaksi
if @dbt != 0
set @jml = (select sum(debit) from Transaksi where tgl_transaksi=@tgl)
else
set @jml = (select sum(kredit) from Transaksi where tgl_transaksi=@tgl)
return @jml
end
--Memanggil Fungsi
select sum (Debit) Debit, sum(Kredit) Kredit from Transaksi where tgl_transaksi = '2011-05-08'
select * from Transaksi

------------------------------------------------------------------------------------------------------------

--Procedure untuk menambahkan data rekening
create procedure SpInsertRekening(@cb char(3),@nm varchar(30),@tgl date,@jk char(1),@al varchar(50),@sal int)
as
begin transaction
  declare @no char (12);
set @no = dbo.FcNoRekeningBaru(getdate(),@cb)
insert into rekening values(@no,@cb,@nm,@tgl,@jk,@al,@sal)
if @@Error <> 0
Rollback Transaction
else
commit Transaction
--memanggil fungsi
insert into rekening values(dbo.FcNoRekeningBaru(getdate(),'001'),'001','Nunung Eka Safitri','1991-02-01','P','Jl. Kaliurang no 2',3400000)
select * from rekening

--Procedure khusus untuk transaksi transfer antar rekening
create proc sptransfer(@norektuj char(12),@nom numeric(18,2),@norekasal char(12))
as
begin tran
declare @tgl datetime,@no char(12)
set @no = dbo.FcKdTransaksiBaru(getdate())
insert into transaksi values(@no,GETDATE(),@norektuj,'03',0,@nom)
set @no = dbo.FcKdTransaksiBaru(getdate())
insert into transaksi values(@no,GETDATE(),@norekasal,'03',@nom,0)
if @@Error <> 0
Rollback Transaction
else
commit Transaction
--Memnaggil Fungsi
insert into transaksi values('110508.00002',GETDATE(),'001-10-00002','03',0,135000)
insert into transaksi values('110508.00003',GETDATE(),'001-11-00001','03',135000,0)
select * from transaksi
select * from jenisTransaksi


--Buat min. 3 procedure lagi (bebas)
--1. Procedure untuk menambahkan data transaksi
create procedure SpInsertTransaksi(@tgl date,@nr char(12),@jns char(2),@db int,@kr int)
as begin transaction
declare @no char (12)
set @no = dbo.FcKdTransaksiBaru(getdate())
insert into transaksi values(@no,@tgl,@nr,@jns,@db,@kr)
if @@Error <> 0
Rollback Transaction
else
commit Transaction
--memanggil fungsi
SpInsertTransaksi '2011-05-08','001-10-00001','01','0','6700000'
select* from transaksi
select * from rekening
delete from transaksi where kd_transaksi='110508.00001'

--2. Buat stored procedure dengan parameter input kode transaksi untuk menampilkan data(no rekening,
--   cabang, nama nasabah,tgl transaksi, no rekening, jenis, debit, kredit, saldo)
create procedure SpTampilDataTransaksi(@kd char(12))
as
select b.kd_transaksi, a.no_rekening, a.cab, b.tgl_transaksi, b.no_rekening, b.jenis, b.debit, b.kredit, a.saldo
from Transaksi b
join rekening a on
b.no_rekening=a.no_rekening where b.kd_transaksi=@kd

--memanggil fungsi
SpTampilDataTransaksi '100712.00001'
select * from transaksi

--3. Procedure untuk menambahkan data cabang
create procedure SpInsertCabang(@kd char(3),@nm char(30),@al char(50))
as begin transaction
insert into cabang values(@kd,@nm,@al)
if @@Error <> 0
Rollback Transaction
else
commit Transaction

--memanggil fungsi
SpInsertCabang '003','Bank BNI Cab Kaliurang','Jl.Kaliurang Yogyakarta'
select * from cabang

--------------------------------------------------------------------------------------------------------------------

-- 1. Trigger untuk mengubah saldo ketika terjadi transaksi (kredit menambah saldo sedangkan debit mengurangi saldo)
CREATE TRIGGER TgTranUbhSaldo ON Transaksi
FOR INSERT AS
DECLARE @nr char(12),@db numeric,@kr numeric
BEGIN TRANSACTION
SELECT  @nr=no_rekening,@db=debit,@kr=kredit from inserted
UPDATE REKENING SET saldo=saldo+@kr-@db where no_rekening=@nr
IF @@ERROR<>0
ROLLBACK TRANSACTION
ELSE
COMMIT TRANSACTION
--Memanggil Fungsi
insert into transaksi values (110508.00001,'2011-05-08','001-10-00001','01',100000,0)
select * from rekening
select * from transaksi

-- 2. Trigger untuk mengubah saldo ketika terjadi perubahan data transaksi
CREATE TRIGGER TgTranUpdSaldo ON transaksi
FOR UPDATE AS
DECLARE @nr char(12),@dblama numeric,@krlama numeric,@dbbaru numeric,@krbaru numeric
BEGIN TRANSACTION
SELECT  @nr=no_rekening,@dblama=debit,@krlama=kredit from deleted
SELECT  @dbbaru=debit,@krbaru=kredit from inserted
UPDATE rekening SET saldo=((saldo-@krlama)+@krbaru)+@dblama-@dbbaru where No_rekening=@nr
IF @@ERROR<>0
ROLLBACK TRANSACTION
ELSE
COMMIT TRANSACTION
--Memanggil Fungsi
UPDATE transaksi SET jenis='01',debit=0,kredit=2000000 WHERE kd_transaksi=110112.00154
select * from rekening
select * from transaksi