Nim / Nama : 10.41010.0250 / Dinda Ayu Dwi Agustina
STORED PROCEDURES
1. Ini Stored procedures untuk menampilkan productID dan productName dari tabel Products yang di ambil dari database Northwind
CREATE PROCEDURE [dbo].[GetProducts] AS SELECT productID, productName FROM Products
EXEC GetProducts
2. Ini Stored procedures untuk menampilkan CategoryID , CategoryName dan SomeProduct dari tabel Categories yang di INNER JOIN dengan tabel Products yang di ambil dari database Northwind
ALTER PROCEDURE [dbo].[GetCategory]
AS SELECT Products.CategoryID, CategoryName, count (Products.CategoryID)AS SomeProduct
FROM Products INNER JOIN Categories on Products.CategoryID=Categories.CategoryID
GROUP BY Products.CategoryID, CategoryName
EXEC [GetCategory]
3. Ini Stored procedures untuk menghitung stok barang masuk dan keluar
SET QUOTED_IDENTIFIER ON
GO
SET ANSI_NULLS ON
GO
ALTER PROC sp_hitungStok AS
create table #tb_hasil(kode char(4), qty float)
insert into #tb_hasil
select kode, qty from tb_masuk
insert into #tb_hasil
select kode, qty from tb_keluar
select kode, sum(qty)from #tb_hasil group by kode order by kode
GO
SET QUOTED_IDENTIFIER OFF
GO
SET ANSI_NULLS ON
GO
EXEC sp_hitungStok
4. Ini Stored procedures untuk menampilkan SupplierID , ProductName dan SomeProduct dari tabel Suppliers yang di INNER JOIN dengan tabel Products yang di ambil dari database Northwind
CREATE PROCEDURE [dbo].[GetSuppliers]
AS SELECT Products.SupplierID, ProductName, count (Products.SupplierID)AS SomeProduct
FROM Products INNER JOIN Suppliers on Products.SupplierID=Suppliers.SupplierID
GROUP BY Products.SupplierID, ProductName
EXEC [GetSuppliers]
5. Ini Stored procedures untuk menampilkan CustomerID , OrderDate , CompanyName dan SomeProduct dari tabel Customers yang di INNER JOIN dengan tabel Orders yang di ambil dari database Northwind
CREATE PROCEDURE [dbo].[GetCustomers]
AS SELECT Orders.CustomerID, OrderDate , CompanyName, count (Orders.CustomerID)AS SomeProduct
FROM Orders INNER JOIN Customers on Orders.CustomerID=Customers.CustomerID
GROUP BY Orders.CustomerID, OrderDate , CompanyName
EXEC [GetCustomers]
FUNCTION
1.
1. Ini sebuah function yang digunakan untuk rata-rata (AVG)
SELECT CustomerID FROM Orders
WHERE Freight>(SELECT AVG(Freight) FROM Orders)
2. Ini sebuah function yang digunakan untuk menjumlahkan (SUM)
SELECT CustomerID,Freight,SUM(Freight) FROM Orders
GROUP BY CustomerID,Freight
3. Ini sebuah function COUNT
SELECT COUNT(CustomerID) AS CustomerNilsen FROM Orders
WHERE CustomerID='Nilsen'
4. Ini sebuah function MAX
SELECT MAX(Freight) AS LargestFreight FROM Orders
5. Ini sebuah function MIN
SELECT MIN(Freight) AS SmallestFreight FROM Orders
TRIGGER
1. Ini trigger untuk mengupdate data penjualan
CREATE TRIGGER [ISIDATABULANDANTAHUN] ON dbo.PENJUALAN
FOR INSERT,UPDATE
AS
DECLARE
@NO_PENJUALAN int,
@BULAN int,
@TAHUN int
SELECT
@NO_PENJUALAN=NOMOR_PENJUALAN,
@BULAN=MONTH(TANGGAL_PENJUALAN),
@TAHUN=YEAR(TANGGAL_PENJUALAN)
FROM INSERTED;
BEGIN
UPDATE PENJUALAN SET BULAN=@BULAN,TAHUN=@TAHUN
WHERE NOMOR_PENJUALAN=@NO_PENJUALAN;
END
2. ini trigger untuk menginsertkan
CREATE TRIGGER testref BEFORE INSERT ON test1
FOR EACH ROW BEGIN
INSERT INTO test2 SET a2 = NEW.a1;
DELETE FROM test3 WHERE a3 = NEW.a1;
UPDATE test4 SET b4 = b4 + 1 WHERE a4 = NEW.a1;
END;
3. Ini trigger untuk menginsertkan
create trigger tambahStockbarang on pembelian
for insert
as
update b set b.bar_stock = b.bar_stock + i.pem_jumlah
from barang b join inserted i on b.bar_id = i.bar_id
4. Ini trigger untuk mengisertkan
CREATE TRIGGER tr_status
ON daftarnilai
FOR 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
5. Ini trigger untuk men-delete
CREATE TRIGGER CustomerHasOrders
ON Customers
FOR DELETE
AS
IF EXISTS
(
SELECT 'True'
FROM Deleted d
JOIN Orders o
ON d.CustomerID = o.CustomerID
)
BEGIN
RAISERROR('Customer has Order History. Delete failed!', 16,1)
ROLLBACK TRAN
END
GO
DELETE Customers
WHERE CustomerID = 'WHITC'
GO
