Tugas SMBD 7 & 8

-- tips
    -- melihat list stored procedure yang di buat // jangan lupa ganti nama database
    SELECT routine_name, routine_type, routine_schema
    FROM db_202053010.INFORMATION_SCHEMA.ROUTINES
    WHERE routine_type = 'PROCEDURE'

    -- menghapus stored procedure yang di buat // jangan lupa ganti nama stored procedure KES
    DROP PROCEDURE KES
    GO

-- praktikum stored procedure
-- 1. Stored Procedure tanpa parameter
CREATE PROCEDURE TampilPresensi
AS
SELECT * FROM Presensi
GO
EXEC TampilPresensi

-- 2. Stored Procedure dengan parameter sederhana
CREATE PROCEDURE TampilNama @MasukkanNama CHAR(9)
AS
SELECT * FROM Presensi WHERE Nama = @MasukkanNama
GO
EXEC TampilNama 'Semar'

-- 3. Stored Procedure dengan parameter sederhana (2)
CREATE PROCEDURE TampilkanNotaJual @MasukkanNoNota VARCHAR(9)
AS
SELECT
    NoNota,
    Transaksi.Seri,
    Jumlah, Harga * Jumlah AS JumlahHrg
FROM Barang, Transaksi
WHERE Barang.Seri = Transaksi.Seri
AND NoNota = @MasukkanNoNota
GO
EXEC TampilkanNotaJual '03.0407-1'

-- praktikum trigger
-- 1. Membuat Contoh Program Tigger, InPelanggan
CREATE TRIGGER InPelanggan
ON Pelanggan
FOR INSERT
AS
BEGIN
    PRINT 'Data telah berhasil disimpan'
END

INSERT INTO Pelanggan VALUES
('Petruk', 'Gg Merah Putih, Sengon', 'Jepara', '4214346')

-- 2. Implementasi Trigger dengan keterangan pada CRUD tabel pada database
CREATE TABLE Kejadian
(Keterangan CHAR(100), Waktu datetime)

INSERT INTO Kejadian VALUES
('Coba', GETDATE())

SELECT * FROM Kejadian

CREATE TRIGGER SimpanKejadian
ON Pelanggan
FOR INSERT, UPDATE, DELETE
AS
BEGIN
    INSERT INTO Kejadian VALUES
    ('Telah di masukkan data di tabel pelanggan pada', GETDATE())
END

INSERT INTO Pelanggan VALUES
('Gareng', 'Gg Merah Muda, Sengon', 'Kudus', '4214322')

SELECT * FROM Kejadian

UPDATE Pelanggan SET Kota = 'Surakarta' WHERE Nama = 'Bagong'

SELECT * FROM Kejadian

DELETE FROM Pelanggan WHERE Nama = 'Petruk'

SELECT * FROM Kejadian

-- soal latihan
-- 1.Buat STORED PROCEDURE berparameter, dengan parameter kode Guru yang mewakili jurusannya, dengan menampilkan nama guru dan nama jurusannya, jika kode gurunya diawali dengan KES maka jurusannya adalah Kesehatan,  Jika diawali dengan INF maka Teknik Komputer dan Jaringan, Jika diawali dengan ELK maka Elektronika  dan jika EKN maka Jurusannya adalah Manajemen Bisnis.
CREATE PROCEDURE [Kode Guru] @KodeJurusan CHAR(3) AS
SELECT Nama,
CASE LEFT (NIG,3)
    WHEN 'EKN' THEN 'Manajemen Bisnis'
    WHEN 'KES' THEN 'Kesehatan'
    WHEN 'INF' THEN 'Teknik Komputer dan Jaringan'
    WHEN 'ELK' THEN 'Elektronika'
END
AS [Nama Jurusan]
FROM Guru WHERE  LEFT (NIG,3)=@KodeJurusan
GO
    -- EKN
    EXEC [Kode Guru] @KodeJurusan = 'EKN'
    -- KES
    EXEC [Kode Guru] @KodeJurusan = 'KES'
    -- INF
    EXEC [Kode Guru] @KodeJurusan = 'INF'
    -- ELK
    EXEC [Kode Guru] @KodeJurusan = 'ELK'

-- 2.Buat STORED PROCEDURE berparameter, dengan parameter NAMA HARI, dengan menampilkan Nama Guru, Nama Bulan, Nama Hari dan JamKerjanya, jika Hari Minggu JamKerjanya 0, jika Hari Jumat JamKerjanya adalah 240 menit, selain hari Minggu dan Jumat Jamkerjanya adalah 480 menit.
CREATE PROCEDURE Hari @NamaHari CHAR(8) AS
SELECT Nama AS [Nama Guru],
DATENAME(MONTH, Tanggal) AS [Bulan Mengajar],
DATENAME(dw, Tanggal) AS [Hari Mengajar],
CASE DATENAME(dw, Tanggal)
    WHEN 'Sunday' THEN '0 Menit'
    WHEN 'Friday' THEN '240 Menit'
    ELSE '480 menit'
END AS [Jam Kerja]
FROM Guru JOIN [Presensi Guru] ON Guru.NIG = [Presensi Guru].NIG
WHERE DATENAME(dw, Tanggal) = @NamaHari
GO
    -- Sunday
    EXEC Hari @NamaHari = 'Sunday'
    -- Friday
    EXEC Hari @NamaHari = 'Friday'
    -- Monday
    EXEC Hari @NamaHari = 'Monday'
    -- Saturday
    EXEC Hari @NamaHari = 'Saturday'