Nama : Khoerun
Nim : 15.1.0147
Mata Kuliah : SQL Server
Dosen : Hermin Susilo [Link]
Status : Tugas
DATABASE PEMBAYARAN KULIAH
CREATE DATABASE DB_PEMBAYARAN_K;
USE DB_PEMBAYARAN_K;
CREATE TABLE Tbl_Mhs (
Nim int identity (1,1)not null primary key,
Nama_Mhs varchar (50),
Alamat varchar (50),
Telpon varchar (15),
Email varchar (30),
Jurusan varchar (25)
);
CREATE TABLE Tbl_Kampus (
Id_Kampus int identity (1,1)not null primary key,
Nama_Kampus varchar (50),
Alamat varchar (50),
Telpon varchar (15),
Email varchar (30),
Website varchar (30)
);
insert into tbl_kampus(nama_kampus,alamat,telpon,email,website) values
('Unj','[Link] wiri','021-977899','umy@[Link]','[Link]')
CREATE TABLE Tbl_Tagihan(
Id_Tagihan int identity (1,1)not null primary key,
Nim int foreign key references tbl_mhs (nim),
Id_Kampus int foreign key references tbl_kampus (id_kampus),
Semester int,
Tag_SPB money,
Biaya_RKS money,
Tag_SPP money,
Jml_Sks int,
Biaya_Sks money,
Rencana date,
Realisasi date,
);
insert into
Tbl_Tagihan(Nim,Id_kampus,Semester,Tag_SPB,Biaya_RKS,Tag_SPP,Jml_Sks,Bi
aya_Sks,rencana,realisasi)
values (1,1,1,'2000','100','3500','24','150','2017-1-12','2017-1-19')
---VIEW---
CREATE VIEW VW_INFO_PEMBAYARAN AS
SELECT
Tbl_Tagihan.Id_Tagihan,
Tbl_Tagihan.Nim,
Tbl_Mhs.Nama_Mhs,
Tbl_Kampus.Nama_Kampus,
Tbl_Tagihan.Semester,
Tbl_Tagihan.Tag_SPB,
Tbl_Tagihan.Biaya_RKS,
Tbl_Tagihan.Tag_SPP,
Tbl_Tagihan.Jml_Sks,
Tbl_Tagihan.Biaya_Sks,
(Tbl_Tagihan.Tag_SPB + Biaya_RKS + Tag_SPP + (Jml_Sks*Biaya_Sks))
AS TotalTagihan,
Tbl_Tagihan.Rencana,
Tbl_Tagihan.Realisasi
FROM Tbl_Tagihan LEFT JOIN Tbl_Mhs ON Tbl_Tagihan.Nim=Tbl_Mhs.Nim
LEFT JOIN Tbl_Kampus ON Tbl_Tagihan.Id_Kampus =
Tbl_Kampus.Id_Kampus
SELECT * FROM VW_INFO_PEMBAYARAN;
----STORE PROCEDURE----
----SP INPUT---
CREATE PROCEDURE Sp_Input_Mhs (
@Nama_Mhs varchar (50),
@Alamat varchar (50),
@Telpon varchar (15),
@Email varchar (30),
@Jurusan varchar (25)
)
AS BEGIN
INSERT INTO Tbl_Mhs (Nama_Mhs,Alamat,Telpon,Email,Jurusan) VALUES
(@Nama_Mhs,@Alamat,@Telpon,@Email,@Jurusan)
END
EXEC Sp_Input_Mhs 'Ahmad','Jl. Anggrek No.3, Jakarta Timur','0812-2233-
3323','a@[Link]','ekonomi'
SELECT * FROM Tbl_Mhs
----SP UPDATE---
CREATE PROCEDURE SP_UPDATE_MHS
(
@Nim int,
@Nama_Mhs varchar (50),
@Alamat varchar (50),
@Telpon varchar (15),
@Email varchar (30),
@Jurusan varchar (25)
)
AS BEGIN
UPDATE TBL_MHS SET NAMA_MHS = @NAMA_MHS, ALAMAT =
@ALAMAT, TELPON = @TELPON, EMAIL = EMAIL, JURUSAN = @JURUSAN
WHERE NIM = NIM
END
EXEC SP_UPDATE_MHS 1, 'Fakhri','Jl. Beringin No.3, Jakarta Timur','0812-2233-
3324','f@[Link]','hukum'
---SP DELETE---
CREATE PROCEDURE SP_DELETE_MHS
( @Nim int )
AS BEGIN
DELETE FROM TBL_MHS WHERE NIM = NIM
END
EXEC SP_DELETE_MHS '1'
----TRIGGER----
--TRIGGER--INPUT--
CREATE TRIGGER Tgr_Input_Mhs ON Tbl_Mhs
FOR INSERT
AS
DECLARE @Nama_Mhs varchar (50)
DECLARE @Alamat varchar (50)
DECLARE @Telpon varchar (15)
DECLARE @Email varchar (30)
DECLARE @Jurusan varchar (25)
SELECT @Nama_Mhs=i.Nama_Mhs FROM Inserted i;
SELECT @Alamat=[Link] FROM Inserted i;
SELECT @Telpon=[Link] FROM Inserted i;
SELECT @Email=[Link] FROM Inserted i;
SELECT @Jurusan=[Link] FROM Inserted i;
--set @Audit_Action = 'Inserted Record -- After Insert Trigger.';
INSERT INTO Tbl_Mhs_Trigger (Nama_Mhs,Alamat,Telpon,Email,Jurusan)
VALUES
(@Nama_Mhs,@Alamat,@Telpon,@Email,@Jurusan,GETDATE () );
PRINT 'AFTER INSERT Trigger fired.'
GO
---TRIGER UPDATE---
CREATE TRIGGER Tgr_Update_Mhs ON Tbl_Mhs
FOR UPDATE
AS
DECLARE @Nim int
DECLARE @Nama_Mhs varchar (50)
DECLARE @Alamat varchar (50)
DECLARE @Telpon varchar (15)
DECLARE @Email varchar (30)
DECLARE @Jurusan varchar (25)
SELECT @Nim=[Link] FROM Updated u;
SELECT @Nama_Mhs=u.Nama_Mhs FROM updated u;
SELECT @Alamat=[Link] FROM updated u;
SELECT @Telpon=[Link] FROM updated u;
SELECT @Email=[Link] FROM updated u;
SELECT @Jurusan=[Link] FROM updated u;
UPDATE TBL_MHS SET NAMA_MHS = @NAMA_MHS, ALAMAT =
@ALAMAT, TELPON = @TELPON, EMAIL = EMAIL, JURUSAN =
@JURUSAN WHERE NIM = NIM
PRINT 'AFTER UPDATE Trigger fired.'
GO
----TRIGGER DELETE---
CREATE TRIGGER Tgr_Delete_Mhs ON Tbl_Mhs
FOR DELETE
AS
DECLARE @Nim int
SELECT @Nim=[Link] FROM Deleted d;
DELETE FROM Tbl_Mhs WHERE Nim = @Nim
PRINT 'AFTER DELETE Trigger fired.'
GO
----FUNCTION---
CREATE FUNCTION Chek_Realisasi
( @Nim int )
RETURNS INT AS
BEGIN
DECLARE @REALISASI AS int;
SELECT @REALISASI = DATEDIFF(day,REALISASI,GETDATE()) FROM
TBL_TAGIHAN
WHERE NIM = @NIM;
RETURN @REALISASI;
END;
