Praktikum Pemrograman Basis Data (Pertemuan 4)

Bagi sobat duniaku yang kuliah di Stikom Prak. PBD ini adalah mata kuliah di semester 3. monggo di pelajari source code jawaban pertemuan 4 :

/*
alter function fna (@tugas numeric(30,2), @uts numeric(30,2), @uas numeric(30,2))
returns numeric(30,2)
as
begin
    declare @na numeric(30,2)
    set @na = 0.4*@tugas+0.3*@uas+0.3*@uts
    return @na
end
*/

/*
alter function fhitung_bobot(@na numeric(30,2))
returns numeric(30,2)
as
begin
declare @bobot numeric(30,2)
if @na <= 44
    begin
    set @bobot=0
    end
    else if @na <=54
    begin
    set @bobot=1
    end
    else if @na <=59
    begin
    set @bobot=2
    end
    else if @na <=64
    begin
    set @bobot=2.5
    end
    else if @na <=74
    begin
    set @bobot=3
    end
    else if @na <=79
    begin
    set @bobot=3.5
    end
    else
    begin
    set @bobot=4
    end
return @bobot
end
*/

/*
alter function fhitung_ipmk(@bobot numeric(30,2), @kode_mk varchar(6))
returns numeric(30,2)
as
begin
declare @ipmk numeric(30,2), @sks int
set @sks = (select sks from mk where kode_mk=@kode_mk)
set @ipmk = @bobot * @sks
return @ipmk
end
*/


alter function fhitung_ipk (@sum_ipmk numeric(30,2), @nim varchar(11))
returns numeric(30,2)
as
begin
declare @ipk numeric(30,2), @sum_sks int, @sks int
set @sum_sks = 0
declare cnilai cursor for select sks from mk, nilai where mk.kode_mk = nilai.kode_mk and nilai.nim = @nim
open cnilai
fetch next from cnilai into @sks
while @@fetch_status = 0
    begin
    set @sum_sks = @sum_sks + @sks
    fetch next from cnilai into @sks
    end
close cnilai
deallocate cnilai
set @ipk = @sum_ipmk/@sum_sks
return @ipk
end

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

begin
declare cMhs cursor for select n.nim,m.nama from mahasiswa m join nilai n on m.nim = n.nim join dosen d on n.nid = d.nid where n.nid = '010103'
declare @nama varchar(100), @nim varchar(11)
open cMhs
fetch next from cMhs into @nim, @nama
while @@fetch_status = 0
    begin
    print 'Nim        : '+@nim
    print 'Nama    : '+@nama
    declare cNilai cursor for (select tugas, uts, uas, kode_mk from nilai where nim = @nim)
    declare @kode_mk varchar(6), @uts numeric(30,2), @uas numeric(30,2), @tugas numeric(30,2), @ipmk numeric(30,2), @sum_ipmk numeric(30,2), @na numeric(30,2), @bobot numeric(30,2)
    set @sum_ipmk = 0
    open cNilai
    fetch next from cNilai into @tugas, @uts, @uas, @kode_mk
    while @@fetch_status = 0
    begin
    set @na = dbo.fna(@tugas, @uts, @uas)
    set @bobot = dbo.fhitung_bobot(@na)
    set @ipmk = dbo.fhitung_ipmk(@bobot, @kode_mk)
    set @sum_ipmk = @sum_ipmk + @ipmk
    fetch next from cNilai into @tugas, @uts, @uas, @kode_mk
    end
    declare @ipk numeric(30,2)
    set @ipk = dbo.fhitung_ipk(@sum_ipmk, @nim)
    close cNilai
    deallocate cNilai
    print 'IPK        : '+convert(varchar,@ipk)
    print ''
    fetch next from cMhs into @nim, @nama
    end
close cMhs
deallocate cMhs
end

SEMOGA BERMANFAAT