Tugas : Tugas PBD
Dosen : Tan Amelia
1)
Procedure Penghitungan Total Pendapatan
create procedure TotalPendapatan
as
begin
declare @total1 numeric;
declare @total2 numeric;
set @total1 = (select sum(harga) 'Total Desember 2010'
from peminjaman
where tanggalpinjam between '12/1/2010' and '12/31/2010')
set @total2 = (select sum(harga) 'Total Januari 2011'
from peminjaman
where tanggalpinjam between '1/1/2011' and '1/31/2011')
print 'Total Pendapatan Bulan Desember 2010 = ' + convert(varchar, @total1)
print 'Total Pendapatan Bulan Januari 2011 = ' + convert(varchar, @total2)
end
exec TotalPendapatan
2)
-- Procedure Penentuan Mobil Yang Paling Diminati
alter procedure Minat
as
begin
declare @minat numeric;
declare @nomor varchar(10);
declare @nama varchar(10);
select @nomor=nomorpolisi, @minat=count(nomorpolisi)
from peminjaman
group by nomorpolisi
having count(nomorpolisi) = (select max(Hitung)
from (select nomorpolisi,count(nomorpolisi) 'Hitung'
from peminjaman
group by NomorPolisi) Tabel)
set @nama = (select namamobil from mobil where nomorpolisi = @nomor)
print 'Mobil yang paling diminati = ' + @nama + ' sebanyak ' + convert(varchar, @minat)
end
exec Minat
3)
CREATE PROCEDURE SupplierCountry
@Country varchar(15)
AS SELECT SupplierID, CompanyName, Country
FROM [Suppliers]
WHERE Country = @Country
EXEC SupplierCountry 'UK'
@Country varchar(15)
AS SELECT SupplierID, CompanyName, Country
FROM [Suppliers]
WHERE Country = @Country
EXEC SupplierCountry 'UK'
4)
ALTER PROC Coba_product
@CountryName VARCHAR(25), @SumOfStock INT OUTPUT
AS
SELECT Suppliers.SupplierID, Suppliers.Country,Products.UnitsInStock,
SUM (ProductID) AS SumOfProducts
FROM Suppliers, Products
WHERE Country like @CountryName and UnitsInStock like@SumOfStock
GROUP BY Suppliers.SupplierID, Country, UnitsInStock
ALTER PROC Coba_product
@CountryName VARCHAR(25), @SumOfStock INT OUTPUT
AS
SELECT Suppliers.SupplierID, Suppliers.Country,Products.UnitsInStock,
SUM (ProductID) AS SumOfProducts
FROM Suppliers, Products
WHERE Country like @CountryName and UnitsInStock like@SumOfStock
GROUP BY Suppliers.SupplierID, Country, UnitsInStock
5)
ALTER PROCEDURE QuantityOrder(@total SMALLINT OUTPUT )
AS
SELECT OrderID,ProductID,UnitPrice,Quantity,count(Quantity)
as SumOfOrderDetails
FROM [Order Details]
WHERE Quantity like @total
group by OrderID,ProductID,UnitPrice, Quantity
EXEC QuantityOrder '15'
Function
1)
SET SERVEROUTPUT ON
DECLARE
X VARCHAR2(20);
BEGIN
X := tulis_teks;
DBMS_OUTPUT.PUT_LINE(X);
END;
DECLARE
X VARCHAR2(20);
BEGIN
X := tulis_teks;
DBMS_OUTPUT.PUT_LINE(X);
END;
2)
SET SERVEROUTPUT ONDECLARE
H INTEGER;
BEGIN
H := pangkat(2, 3);
DBMS_OUTPUT.PUT_LINE(‘Hasil = ‘ || TO_CHAR(H));
END;
3)
CREATE OR REPLACE FUNCTION kuadrat (X NUMBER)RETURN NUMBER AS
HASIL NUMBER(10);
BEGIN
HASIL := X * X;
RETURN HASIL;
END;
4)
Trigger
1)
CREATE TRIGGER tr_status ON daftarnilaiFOR INSERT, UPDATE
AS
DECLARE @kode char(4)
DECLARE @nilai float
SELECT @kode = kode, @nilai = nilai FROM daftarNilai
IF @nilai >= 60
UPDATE daftarSiswa SET status = ‘Lulus’ WHERE kode=@kode
ELSE UPDATE daftarSiswa SET status = ‘Tidak Lulus’ WHERE kode=@kode
go
