BAHAN UAS

TRIGGER INSERT NILAI

CREATE trigger [dbo].[trgINSERT_Nilai] on [dbo].[TBNILAI]
for INSERT
as
declare @v1 as char(3)
declare @v2 as char(3)
select @v1=KdMK,@v2=NIM from inserted
update tbNilai
set NAkhir=CONVERT(INT, 0.4*UTS + 0.6*UAS)
where KdMK=@v1 and NIM=@v2
return

TRIGGER UPDATE NILAI


CREATE trigger trgNilai on TBNilai for update
as
declare @v1 as char(3)
declare @v2 as char(3)
select @v1=KdMK,@v2=NIM from inserted
if update(UTS) or update(UAS) begin
update TBNilai
set NAkhir=0.4*UTS + 0.6*UAS
where KdMK=@v1 and NIM=@v2
end
return


CREATE TRIGGER trTbBayar_Insert
ON Bayar
after INSERT
AS
BEGIN
DECLARE @Jumlah int
DECLARE @Idpemasok varchar(5)
SELECT @Idpemasok=Idpemasok, @Jumlah=totalbayar FROM INSERTED
UPDATE Pemasok SET Hutang=Hutang-@Jumlah WHERE Idpemasok=@Idpemasok
END

select * from pemasok

insert bayar (idbayar,idpemasok,totalbayar)values('002','001','5000')

select * from bayar





CREATE TRIGGER trTbBeli_Insert
ON Beli
after INSERT
AS
BEGIN
DECLARE @Jumlah int
DECLARE @Idpemasok varchar(5)
SELECT @Idpemasok=Idpemasok, @Jumlah=totalbeli FROM INSERTED
UPDATE Pemasok SET Hutang=Hutang+@Jumlah WHERE Idpemasok=@Idpemasok
END

select * from pemasok

insert beli (idbeli,idpemasok,totalbeli)values('002','001','5000')

select * from beli

select * from pemasok





TRANSACT


CREATE TABLE tbCustomer
(
IdCust varchar(10),
Customer varchar(30),
primary key (IdCust)
)

--lihat data sebelum di update
SELECT * FROM tbCustomer
BEGIN TRAN

--lakukan insert
INSERT tbCustomer VALUES ('001','Nugraha')
INSERT tbCustomer VALUES ('002', 'Agung')

--lihat data sesudah diinsert
SELECT * FROM tbCustomer

ROLLBACK TRAN

--lihat data sesudah dirollback
SELECT * FROM tbCustomer


CREATE TABLE tbCustomer1 (
IdCust varchar(10),
Customer varchar(30),
Piutang money default 0,
primary key (IdCust)
)

CREATE TABLE tbJual(
NoJual varchar(10),
IdCust varchar(10),
Jumlah money,
primary key (NoJual)
)

CREATE PROCEDURE spInsert
@NoJual varchar(10),
@IdCust varchar(10),
@Jumlah money
AS
DECLARE @piutang money
BEGIN TRAN
SELECT @piutang = piutang from dbo.tbCustomer1 WHERE IdCust = @IdCust
IF @piutang is null SET @piutang=0
UPDATE dbo.tbCustomer1 SET Piutang=@piutang + @Jumlah WHERE IdCust=@IdCust
INSERT dbo.tbJual (NoJual, IdCust, Jumlah) VALUES (@NoJual, @IdCust, @Jumlah)
IF @@ERROR != 0
ROLLBACK TRAN
ELSE
COMMIT TRAN

INSERT dbo.tbCustomer1(IdCust,Customer,Piutang) VALUES ('C001','Antoni',0)
SELECT * FROM dbo.tbCustomer1
exec dbo.spInsert 'J001', 'C001',1000
SELECT * FROM dbo.tbCustomer1

SELECT * FROM dbo.tbCustomer1
exec dbo.spInsert 'J002', 'C001',1000
SELECT * FROM dbo.tbCustomer1


create table tbNilai (
nim varchar(7),
kodemk varchar(7),
nilai char(1),
primary key (nim,kodemk)
)

create trigger trTbNilai_Insert on tbNilai
for insert, update
as
declare @nilai char(1)
select @nilai=nilai from inserted
if @nilai not in ('A','B','C','D','E')
begin
raiserror ('nilai harus A-E', 1,1)
ROLLBACK TRANSACTION
end

insert dbo.tbNilai values ('001','IF002','A')
select * from dbo.tbNilai
insert dbo.tbNilai values ('002','IF002','B')
select * from dbo.tbNilai
insert dbo.tbNilai values ('003','IF002','F')
select * from dbo.tbNilai



CREATE TABLE MyTranTest
(
OrderID INT PRIMARY KEY IDENTITY
)
select * from mytrantest ---------------------(A)

BEGIN TRAN TranStart

INSERT INTO MyTranTest
DEFAULT VALUES
INSERT INTO MyTranTest
DEFAULT VALUES
INSERT INTO MyTranTest
DEFAULT VALUES
INSERT INTO MyTranTest
DEFAULT VALUES

select * from mytrantest ---------------------(B)


SAVE TRAN FirstPoint
INSERT INTO MyTranTest
DEFAULT VALUES
INSERT INTO MyTranTest
DEFAULT VALUES
INSERT INTO MyTranTest
DEFAULT VALUES
select * from mytrantest ---------------------(C)

ROLLBACK TRAN FirstPoint
select * from mytrantest ---------------------(D)

INSERT INTO MyTranTest
DEFAULT VALUES
INSERT INTO MyTranTest
DEFAULT VALUES
INSERT INTO MyTranTest
DEFAULT VALUES
select * from mytrantest ---------------------(E)

SAVE TRAN SecondPoint
INSERT INTO MyTranTest
DEFAULT VALUES
INSERT INTO MyTranTest
DEFAULT VALUES
INSERT INTO MyTranTest
DEFAULT VALUES
select * from mytrantest ---------------------(F)


ROLLBACK TRAN SecondPoint
select * from mytrantest
INSERT INTO MyTranTest
DEFAULT VALUES
INSERT INTO MyTranTest
DEFAULT VALUES
INSERT INTO MyTranTest
DEFAULT VALUES
select * from mytrantest ---------------------(G)


COMMIT TRAN TranStart
SELECT TOP 3 OrderID
FROM MyTranTest
ORDER BY OrderID ASC ---------------------(H)


FUNCTION


alter function No1
(
@tahun varchar(50)
)
returns varchar (50)
as
begin
declare @noakhir varchar(50)
declare @NoPO varchar(50)
declare @nobaru varchar(50)

select @NoAkhir = convert(int, Max(right(NoPO,5)))
from tbPO
where substring(NoPO,4,4) = @tahun

if @NoAkhir is null
Set @Nobaru=0
else
Set @NoBaru = @noakhir+0
select @NoPO='PO-'+@tahun+'-'+RIGHT('00000'+Convert(varchar, @Nobaru), 5)
return @NoPO

end

select dbo.No1(2020)


create function No2
(
@tahun varchar(50)
)
returns varchar (50)
as
begin
declare @noakhir varchar(50)
declare @NoPO varchar(50)
declare @nobaru varchar(50)

select @NoAkhir = convert(int, Max(right(NoPO,5)))
from tbPO
where substring(NoPO,4,4) = @tahun

if @NoAkhir is null
Set @Nobaru=1
else
Set @NoBaru = @NoAkhir+1
select @NoPO='PO-'+@tahun+'-'+RIGHT('00000'+Convert(varchar, @NoBaru), 5)
return @NoPO

end

select dbo.No2(2010)



alter function CariUmur
(
@nama varchar(50)
)
returns int
as
begin
declare @tgl datetime
declare @umur int
select @tgl=TanggalLahir from TPelamar
where IDPelamar=@nama
set @umur=datediff(dd,@tgl,getdate())/365
return @umur
end
select dbo.CariUmur (IDPelamar) as Umur from TPelamar



alter function PendidikanTerakhir
(
@IDPelamar varchar(10)
)
returns varchar(50)
as
begin
declare @IDPendidikan varchar(10)
declare @NamaPendidikan varchar(50)
declare @ket varchar(50)

select top (1) @ket = NamaPendidikan from TPendidikan
where @IDPelamar = IDPelamar
order by IDPendidikan desc
return @ket
end
select distinct dbo.PendidikanTerakhir (IDPelamar) from TPendidikan




STORE PROCEDURE


create proc SP_InsPenjualan
@pKodePelanggan nvarchar(12),
@pNoFJ nvarchar(12),
@pTotalFaktur money
as
begin
if exists (select KodePelanggan from Pelanggan where KodePelanggan = @pKodePelanggan)
begin
if exists (select NoFJ from FJ where NoFJ = @pNoFJ)
select 'NoFJ Sudah ada'
else
begin
insert into FJ(NoFJ, KodePelanggan, TotalFaktur)
values(@pNoFJ, @pKodePelanggan, @pTotalFaktur)
update Pelanggan set Piutang = @pTotalFaktur where KodePelanggan = @pKodePelanggan
end
end
else
select 'data pelanggan tidak ada'
select * from FJ
select * from Pelanggan
end

exec SP_InsPenjualan 'FJ-0000001','C-00001',50000



alter PROCEDURE SP_TambahPOGudang
@TglPO datetime, @Gudang varchar(6),@KodeSupplier varchar(10)
AS
BEGIN
DECLARE @NOAKHIR INT
DECLARE @NOBaru INT
DECLARE @NomorPO varchar(20)

--1.Cari Nomor Akhir
select @NoAkhir = convert(int, Max(right(NomorPO,5)))
from TGudang
where substring(NomorPO,4,4) = convert(char(4), Datepart(yyyy, @TglPO))
and substring(NomorPO,9,2) = @Gudang

--2.Cari Nomor Baru
if @NoAkhir is null
Set @Nobaru=1
else
Set @NoBaru = @NoAkhir + 1

Set @NomorPO = 'PO-' + convert(char(4), Datepart(yyyy, @TglPO )) + '-' + @Gudang + '-' +
RIGHT('00000'+Convert(varchar, @NoBaru), 5)

--3.Insert Data baru
Insert TGudang (NomorPO, TglPO, GUDANG, KodeSupplier) Values (@NomorPO, @TglPO, @Gudang, @KodeSupplier)
END

exec SP_TambahPOGudang '06/05/1980','G1','S-00001'
select * from TGudang

delete from TGudang



alter PROCEDURE SP_TambahBarang
@NamaBarang varchar(50) ,@Stok int
AS
BEGIN
DECLARE @NOAKHIR INT
DECLARE @NOBaru INT
DECLARE @Kode varchar(20)

--1.Cari Nomor Akhir
select @NoAkhir = convert(int, Max(right(KodeBarang,3)))
from TBarang
where LEFT(KodeBarang,1) = LEFT(@NamaBarang,1)

--2.Cari Nomor Baru
if @NoAkhir is null
Set @Nobaru=1
else
Set @NoBaru = @NoAkhir + 1

Set @Kode = LEFT(@NamaBarang,1) + RIGHT('00000'+Convert(varchar, @NoBaru), 3)

--3.Insert Data baru
Insert TBarang (KodeBarang, NamaBarang, Stok) Values (@Kode,@NamaBarang, @Stok)
END

exec SP_TambahBarang 'Bangke',7
select * from TBarang
delete from TBarang



CURSOR


alter proc SP_cursor3
as
begin

declare csrGaji cursor
for select departemen,count(NIK) from Gaji group by departemen

open csrGaji
declare @jumlah int
declare @count int
declare @departemen nvarchar(15)
set @jumlah=0


print '--------------------------------------'
print '||DEPARTEMEN |JUMLAH||'
print '--------------------------------------'

FETCH NEXT FROM csrGaji into @departemen, @count
while @@fetch_status=0
begin
print '||' +convert(varchar, @departemen)+' '+' |'+convert(varchar,@count) + ' ORANG||'
set @jumlah=@jumlah+@count

FETCH NEXT FROM csrGaji into @departemen, @count


end
print '----------------------------------'
print '||Total |'+''+convert(char(2),@jumlah)+' ORANG||'
print '----------------------------------'

close csrGaji
deallocate csrGaji
end



alter proc sp_Nilai
@mk varchar(7)
as
begin
declare crJum cursor
for select nilai
from tbnilai1 n join tbmahasiswa m on n.nrp=m.nrp
where mk=@mk
open crJum
declare @a int set @a=0
declare @b int set @b=0
declare @c int set @c=0
declare @d int set @d=0
declare @e int set @e=0
declare @NILAI int

FETCH NEXT FROM crJum into @NILAI
WHILE @@FETCH_STATUS=0
BEGIN
IF @NILAI>84 SET @a=@a+1
ELSE
IF @NILAI>69 SET @b=@b+1
ELSE
IF @NILAI>54 SET @c=@c+1
ELSE
IF @NILAI>39 SET @d=@d+1
ELSE SET @e=@e+1
FETCH NEXT FROM crJum into @NILAI
END
print('Jumlah nilai A :'+convert(varchar,@a)+' orang')
print('Jumlah nilai B :'+convert(varchar,@b)+' orang')
print('Jumlah nilai C :'+convert(varchar,@c)+' orang')
print('Jumlah nilai D :'+convert(varchar,@d)+' orang')
print('Jumlah nilai E :'+convert(varchar,@e)+' orang')
CLOSE crJum
DEALLOCATE crJum
end

exec sp_Nilai 'MK02'


create proc SP_CursorNilai
@MK varchar(5)
as
begin

declare csrNilai cursor
for select MK,count(NRP) from tbNilai where @MK = MK group by MK

open csrNilai
declare @jumlah int
declare @count int
set @jumlah=0
if @Nilai >= 85 and @Nilai <=90
begin
raiserror ('nilai harus A-E', 1,1)
ROLLBACK TRANSACTION
end
FETCH NEXT FROM csrNilai into @departemen, @count
while @@fetch_status=0
begin
print '||' +convert(varchar, @departemen)+' '+' |'+convert(varchar,@count) + ' ORANG||'
set @jumlah=@jumlah+@count

FETCH NEXT FROM csrGaji into @departemen, @count


end
print 'Jumlah Nilai A: '+convert(char(2),@jumlah)+' ORANG'
print 'Jumlah Nilai B: '+convert(char(2),@jumlah)+' ORANG'
print 'Jumlah Nilai C: '+convert(char(2),@jumlah)+' ORANG'
print 'Jumlah Nilai D: '+convert(char(2),@jumlah)+' ORANG'
print 'Jumlah Nilai E: '+convert(char(2),@jumlah)+' ORANG'



close csrGaji
deallocate csrGaji
end


alter procedure SP_cursor1
as
begin

--1 Mendeklarasikan Cursor
DECLARE CsrMsiswa CURSOR
FOR Select NIK,Nama from dbo.Msiswa Where NIK>=1 and NIK <=5

--2 Membuka Cursor
OPEN CsrMsiswa


--3 Memproses Cursor
DECLARE @No int
DECLARE @NIK int
DECLARE @Nama char(7)
declare @Kode varchar(2)

set @Kode = 0
PRINT 'DAFTAR MAHASISWA'
PRINT 'MATAKULIAH:'
PRINT 'DOSEN:'
PRINT '================================================='
PRINT '=No =NIK =Nama =1 =2 =3 =4 =5 =Rata-2 ='
FETCH NEXT FROM CsrMsiswa into @NIK, @NAMA
WHILE @@FETCH_STATUS=0
BEGIN
set @kode = @Kode + 1

PRINT '='+CONVERT(varCHAR,@Kode)+' ='+CONVERT(varCHAR,@NIK)+' ='+CONVERT(varCHAR,@NAMA)+'='+' ='+' ='+' ='+' ='+' ='+' ='
FETCH NEXT FROM CsrMsiswa into @NIK, @NAMA
END
PRINT '================================================='
PRINT '= = = = = = = = = ='
PRINT '================================================='


--4 Menutup Cursor
CLOSE CsrMsiswa

--5 Mendealokasikan Cursor
DEALLOCATE CsrMsiswa
end

exec dbo.SP_Cursor1




LATIHAN UAS PRAK



ALTER PROCEDURE [dbo].[SP_BUKU_TABUNGAN]
AS
BEGIN
DECLARE CsrMsiswa_ CURSOR
FOR SELECT No_Rekening,Jenis_Tabungan,Nama,alamat FROM tbNasabah

OPEN CsrMsiswa_

DECLARE @NOM varchar(50)
DECLARE @JENIS varchar(50)
DECLARE @NAMA varchar(50)
DECLARE @ALAMAT varchar(50)


FETCH NEXT FROM CsrMsiswa_ into @NOM, @JENIS,@NAMA, @ALAMAT
WHILE @@FETCH_STATUS=0
BEGIN

DECLARE CsrMsiswa CURSOR
FOR Select Tanggal,Jenis_trans,Keterangan,Jumlah,Saldo,NIK from tbTransaksi where No_Rekening = @NOM

--2 Membuka Cursor
OPEN CsrMsiswa


--3 Memproses Cursor

DECLARE @Tanggal datetime
DECLARE @Jenis_ varchar(50)
DECLARE @KET varchar(50)
DECLARE @Jumlah int
DECLARE @Saldo varchar(50)
DECLARE @NIK varchar(50)
DECLARE @DEF int
DECLARE @DEB int
DECLARE @KRE int
SET @DEF =1

print '------------------------------------------------------------------------------------------------------------------------------------------'
print 'No Rekening : '+@NOM
print 'Jenis Tabungan : '+@JENIS
print 'Nama : '+@NAMA
print 'Alamat : '+@ALAMAT
print '------------------------------------------------------------------------------------------------------------------------------------------'
PRINT 'No |'+'Tanggal |'+'NIK |'+'Keterangan |'+'Debet |'+'Kredit |'+'Saldo |'
FETCH NEXT FROM CsrMsiswa into @Tanggal, @Jenis_,@KET, @Jumlah, @Saldo, @NIK
WHILE @@FETCH_STATUS=0
BEGIN
if(@Jenis_ = 'Debet')
BEGIN
SET @DEB = CONVERT(INT,@JUMLAH)
SET @KRE = 0
END
ELSE
BEGIN
SET @KRE = CONVERT(INT,@JUMLAH)
SET @DEB = 0
END

PRINT CONVERT(CHAR(3),@DEF)+'|'+CONVERT(VARCHAR,@Tanggal)+' |'+@NIK+' |'+CONVERT(CHAR(30),@KET)+' |'+CONVERT(CHAR(10),@DEB)+' |'+CONVERT(CHAR(10),@KRE)+' |'+CONVERT(CHAR(20),@Saldo)+'|'
SET @DEF = @DEF+1

FETCH NEXT FROM CsrMsiswa into @Tanggal, @Jenis_,@KET, @Jumlah, @Saldo, @NIK
END
print '============================================================='

--4 Menutup Cursor
CLOSE CsrMsiswa

--5 Mendealokasikan Cursor
DEALLOCATE CsrMsiswa
FETCH NEXT FROM CsrMsiswa_ into @NOM, @JENIS,@NAMA, @ALAMAT
END


--4 Menutup Cursor
CLOSE CsrMsiswa_

--5 Mendealokasikan Cursor
DEALLOCATE CsrMsiswa_
END