Notes

1. TarimUretim adında bir veri tabanını T-SQL dili kullanarak oluşturunuz.

2. Aşağıda verilen tabloları oluşturmuş olduğunuz TarimUretim adında veri tabanı içinde T-SQL dili
ile oluşturunuz.

3. T-SQL dili kullanarak SP_BolgeEkle adında bir stored procedure yazınız. Bu stored procedure ile
aşağıdaki tabloda yer alan verileri bölgelerin bulunduğu tabloya giriniz.

4. T-SQL dili kullanarak SP_IlEkle adında bir stored procedure yazınız. Bu stored procedure ile
aşağıdaki tabloda yer alan verileri illerin bulunduğu tabloya giriniz. Stored Procedure’den eklenen
toplam satır sayısını OUTPUT parametre ile alınız.

5. T-SQL dili kullanarak SP_DonemEkle adında bir stored procedure yazınız. Bu stored procedure ile
aşağıdaki tabloda yer alan verileri dönemlerin bulunduğu tabloya giriniz. Eğer ekleme işleminde bir
hata varsa bu hatanın kodunu, hata yoksa eklenen kayıt sayısını procedure’den geri döndürün.

6. T- SQL dili kullanarak SP_UrunEkle adında bir stored procedure yazınız. Bu stored procedure ile
aşağıdaki tabloda yer alan verileri ürünlerin bulunduğu tabloya giriniz. Eğer var olan bir ürün tekrar
eklenmeye çalışılıyorsa procedure’den geriye 0, ürün ekleme işlemi başarılı ise geriye 1, herhangi bir
hata varsa -1 değerini döndürün.

7. T- SQL dili kullanarak SP_IlUrunleriEkle adında bir stored procedure yazınız. Bu stored procedure ile
aşağıdaki tabloda yer alan verileri illere ait ürünlerin yer aldığı tabloya giriniz.
İl Adı Ürün Dönem Miktar
Çanakkale Domates 2010 Yılı 500000
İzmir İncir 2013 Yılı 250000
Stored procedure bir OUTPUT parametre aracılığıyla belirli mesajlar vermelidir. Eğer tanımlı olmayan
herhangi bir il için tanımlı olmayan bir ürünü, tanımlı olmayan bir döneme eklenmeye çalışılıyorsa
stored procedure’den ‘Eklemeye çalıştığınız verilerden biri ya da bir kaçı tanımlı değil!’ mesajı
OUTPUT parametre ile alınmalıdır.

8. Aşağıda yer alan özet tabloyu elde eden SQL sorgusunu şifreli bir WIEW olarak oluşturunuz.
Bölge Adı İl Adı Ürün Dönem Miktar
Marmara Bölgesi Çanakkale Domates 2010 Yılı 500000
Ege Bölgesi İzmir İncir 2013 Yılı 250000

9. Dışarıdan bölge no bilgisi verildiğinde o bölgedeki tüm illere ait tüm ürünlerin dönem fark etmeksizin
toplam miktarını bulan FN_BolgeUretimHesapla adında bir UDF(User Defined Function) nesnesini
T-SQL dili ile oluşturunuz ve örnek kullanımını gösteriniz.

10. Parametre olarak bölge no verildiğinde o bölgeye ait tüm illerin tüm ürünlerinin tüm dönemlere ait
üretim miktarlarını veren FN_BolgeyeGoreUretim adındaki (geriye tablo olarak aşağıdaki gibi bir
çıktı vermeli) UDF’i T-SQL dili ile oluşturunuz ve örnek kullanımını gösteriniz.
Bölge Adı İl Adı Ürün Dönem Miktar
Karadeniz Bölgesi Ordu Fındık 2015 Yılı 456325
Karadeniz Bölgesi Giresun Fındık 2014 Yılı 120000

11. Parametre olarak dönem no bilgisi verildiğinde o dönemde tüm bölgelere ait tüm illerde üretilen tüm
ürünleri miktarlarıyla birlikte elde eden FN_DonemeGoreUretim adındaki UDF’i T-SQL dili ile
oluşturunuz ve örnek kullanımını gösteriniz. (Örnek çıktı aşağıdaki gibidir.)
Bölge Adı İl Adı Ürün Dönem Miktar
İç Anadolu Bölgesi Konya Buğday 2014 Yılı 354235
Güneydoğu Anadolu Bölgesi Şanlıurfa Mercimek 2014 Yılı 2125
Karadeniz Bölgesi Giresun Fındık 2014 Yılı 120000

12. Bölgeler, iller ve ürün dönemleri tabloları için ayrı ayrı olmak üzere TRIGGER(tetikleyici)
oluşturunuz. Bu triggerlar ilgili tablolardan kayıt silinmeye çalışıldığında Aktif alanının False – 0
olarak güncellemesini sağlar.

13. Veri tabanınızda DonemUretim adında bir tabloyu T-SQL dili oluşturunuz. Tablo alanları ve tipleri
aşağıdaki gibidir.
Alan Adı Alan Tipi
DonemId Int
ToplamUretim Int

14. İllerin ürünlerinin yer aldığı il ürünleri tablosu üzerine aşağıda açıklaması bulunan TRIGGER
nesnesini T-SQL dili ile oluşturunuz.
 TR_UrunEklendi adında bir TRIGGER oluşturunuz.
 TRIGGER il ürünleri tablosuna kayıt girildikten sonra çalışacaktır.
 TRIGGER kayıt girme işleminden sonra dönem bilgisine göre o dönem için DonemUretim
tablosunda eğer hiçbir kayıt yoksa girilen üretim miktarı kadar miktarı kayıt edecek.
 Eğer önceden döneme ait bilgi varsa il ürünleri tablosuna girilen miktar kadar miktarı
DonemUretim tablosunun ToplamUretim alanına ekleyecek.


Create Database TarimUretim
Go
Use TarimUretim;
Go
Create Table Bolgeler
(
BolgeNo Int IDentity Primary Key,
BolgeAdi Varchar(100) Unique,
Aktif Bit Default 1
)
Go
Create Table Iller
(
PlakaKodu Int Primary Key,
BolgeNo Int,
Ad Varchar(50) Unique,
TelefonKodu Varchar(5) Unique,
Aktif Bit Default 1,
Constraint FK_Iller_BolgeNo
Foreign Key(BolgeNo)
References Bolgeler(BolgeNo)
)
Go
Create Table Urunler
(
UrunNo Int Primary Key Identity,
UrunAdi Varchar(100) Unique
)
Go
Create Table UrunDonemleri
(
DonemNo Int Primary Key Identity,
DonemTanimi Varchar(50) Unique,
Aktif Bit Default 1
)
Go
Create Table IlUrunleri
(
PlakaKodu Int,
DonemNo Int,
UrunNo Int,
Miktar Int,
Constraint FK_IlUrunleri_PlakaKodu
Foreign Key(PlakaKodu)
References Iller(PlakaKodu),
Constraint FK_IlUrunleri_DonemNo
Foreign Key(DonemNo)
References UrunDonemleri(DonemNo),
Constraint FK_IlUrunleri_UrunNo
Foreign Key(UrunNo)
References Urunler(UrunNo)
)
Go
Create Proc SP_BolgeEkle
@BolgeAdi Varchar(100),
@Aktif Bit
As
Insert Into Bolgeler Values(@BolgeAdi,@Aktif)
Go
Exec SP_BolgeEkle 'Marmara Bölgesi',1;
Go
Exec SP_BolgeEkle 'Ege Bölgesi',1;
Go
Exec SP_BolgeEkle 'Akdeniz Bölgesi',1;
Go
Exec SP_BolgeEkle 'İç Anadolu Bölgesi',1;
Go
Exec SP_BolgeEkle 'Karadeniz Bölgesi',1;
Go
Exec SP_BolgeEkle 'Doğu Anadolu Bölgesi',1;
Go
Exec SP_BolgeEkle 'Güneydoğu Anadolu Bölgesi',1;
Go
Create Proc SP_IlEkle
@PlakaKodu Int,
@BolgeNo Int,
@Ad Varchar(50),
@TelefonKodu Varchar(5),
@Aktif Bit,
@ReturnValue Int Output
As
Insert Into Iller Values(@PlakaKodu,@BolgeNo,@Ad,@TelefonKodu,@Aktif)
Set @ReturnValue = @@ROWCOUNT
Go
Declare @ss Int;
Exec SP_IlEkle 17, 1, 'Çanakkale','286',1,@ss output;
Exec SP_IlEkle 35, 2, 'İzmir','232',1,@ss output;
Exec SP_IlEkle 7, 3, 'Antalya','242',1,@ss output;
Exec SP_IlEkle 42, 4, 'Konya','332',1,@ss output;
Exec SP_IlEkle 52, 5, 'Ordu','452',1,@ss output;
Exec SP_IlEkle 25, 6, 'Erzurum','442',1,@ss output;
Exec SP_IlEkle 63, 7, 'Şanlıurfa','414',1,@ss output;
Go
Create Proc SP_DonemEkle
@DonemTanimi Varchar(50),
@Aktif Bit
As
Declare @retVal Int;
Begin Try
Insert Into Bolgeler Values(@DonemTanimi,@Aktif)
Set @retVal = @@ROWCOUNT
End Try
Begin Catch
Select @retVal = ERROR_NUMBER()
End Catch
Return @retVal
Go
Exec SP_DonemEkle '2010 Yılı',1;
Go
Exec SP_DonemEkle '2011 Yılı',1;
Go
Exec SP_DonemEkle '2012 Yılı',1;
Go
Exec SP_DonemEkle '2013 Yılı',1;
Go
Exec SP_DonemEkle '2014 Yılı',1;
Go
Exec SP_DonemEkle '2015 Yılı',1;
Go
Exec SP_DonemEkle '2016 Yılı',1;
Go
Create Proc SP_UrunEkle
(
@Ad Varchar(100)
)
As
Begin
Declare @retVal Int
Begin Try
if exists(select * from Urunler where UrunAdi = @Ad)
Select @retVal = 0
else
begin
insert into Urunler Values(@Ad)
Select @retVal = 1
end
End Try
Begin Catch
Select @retVal = -1
End Catch
Return @retVal
End
Go
Exec SP_UrunEkle 'Domates';
Go
Exec SP_UrunEkle 'İncir';
Go
Exec SP_UrunEkle 'Portakal';
Go
Exec SP_UrunEkle 'Buğday';
Go
Exec SP_UrunEkle 'Fındık';
Go
Exec SP_UrunEkle 'Arpa';
Go
Exec SP_UrunEkle 'Mercimek';
Go
Create Proc SP_IlUrunleriEkle
(
@PlakaKodu Int,
@DonemNo Int,
@UrunNo Int,
@Miktar Int,
@Mesaj Varchar(150) Output
)
As
Begin
Begin Try
Insert Into IlUrunleri
Values(@PlakaKodu,@DonemNo,@UrunNo,@Miktar)
Set @Mesaj='Kayıt başarılı!'
End Try
Begin Catch
Set @Mesaj='Eklemeye çalıştığınız verilerden biri ya da bir kaçı tanımlı değil!'
End Catch
End
Go
Declare @m varchar(150)
Exec SP_IlUrunleriEkle 17,1,1,500000,@m output;
Exec SP_IlUrunleriEkle 35,2,2,250000,@m output;
Exec SP_IlUrunleriEkle 7,3,3,125000,@m output;
Exec SP_IlUrunleriEkle 42,4,4,354235,@m output;
Exec SP_IlUrunleriEkle 52,5,5,225000,@m output;
Exec SP_IlUrunleriEkle 25,6,6,154200,@m output;
Exec SP_IlUrunleriEkle 63,7,7,25000,@m output;
Go
Create View VW_OzetTablo
With Encryption
As
Select
B.BolgeAdi,
I.Ad 'IlAdi',
U.UrunAdi,
UD.DonemTanimi,
IU.Miktar
From Bolgeler B
Join Iller I On I.BolgeNo = B.BolgeNo
Join IlUrunleri IU On IU.PlakaKodu = I.PlakaKodu
Join Urunler U On U.UrunNo = IU.UrunNo
Join UrunDonemleri UD On UD.DonemNo = IU.DonemNo
Go
Create Function FN_BolgeUretimHesapla
(
@BolgeNo Int
)
Returns Int
As
Begin
Declare @Miktar Int
Select
@Miktar = SUM(IU.Miktar)
From Bolgeler B
Join Iller I On I.BolgeNo = B.BolgeNo
Join IlUrunleri IU On IU.PlakaKodu = I.PlakaKodu
Where B.BolgeNo = @BolgeNo

Return @Miktar
End
Go
Create Function FN_BolgeyeGoreUretim
(
@BolgeNo Int
)
Returns Table
As
Return Select
B.BolgeAdi,
I.Ad 'İl Adı',
U.UrunAdi 'Ürün Adı',
UD.DonemTanimi,
IU.Miktar
From Bolgeler B
Join Iller I On I.BolgeNo = B.BolgeNo
Join IlUrunleri IU On IU.PlakaKodu = I.PlakaKodu
Join Urunler U On U.UrunNo = IU.UrunNo
Join UrunDonemleri UD On UD.DonemNo = IU.DonemNo
Where B.BolgeNo = @BolgeNo
Go
Create Function FN_DonemeGoreUretim
(
@DonemNo Int
)
Returns Table
as
Return Select
B.BolgeAdi,
I.Ad 'İl Adı',
U.UrunAdi 'Ürün Adı',
UD.DonemTanimi,
IU.Miktar
From Bolgeler B
Join Iller I On I.BolgeNo = B.BolgeNo
Join IlUrunleri IU On IU.PlakaKodu = I.PlakaKodu
Join Urunler U On U.UrunNo = IU.UrunNo
Join UrunDonemleri UD On UD.DonemNo = IU.DonemNo
Where UD.DonemNo = @DonemNo
Go
Create Trigger TR_BolgeSilme
On Bolgeler
Instead Of Delete
As
Declare @bolgeNo Int
Select @bolgeNo = BolgeNo from deleted
Update Bolgeler Set Aktif = 0 Where BolgeNo = @bolgeNo
Go
Create Trigger TR_IlSilme
On Iller
Instead Of Delete
As
Declare @plakaNo Int
Select @plakaNo = PlakaKodu from deleted
Update Iller Set Aktif = 0 Where PlakaKodu = @plakaNo
Go
Create Trigger TR_UrunDonemiSilme
On UrunDonemleri
Instead Of Delete
As
Declare @donemNo Int
Select @donemNo = DonemNo from deleted
Update UrunDonemleri Set Aktif = 0
Where DonemNo = @donemNo
Go
Create Table DonemUretim
(
DonemId Int,
ToplamUretim Int
)
Go
Create Trigger TR_UrunEklendi
On IlUrunleri
For Insert
As
Declare @donemNo Int
Declare @toplamMiktar Int
Declare @miktar Int
Select @donemNo = DonemNo,@miktar=Miktar From inserted
If Exists(Select * From IlUrunleri Where DonemNo = @donemNo)
Begin
Update DonemUretim
Set ToplamUretim =ToplamUretim+ @miktar
Where DonemId = @donemNo
End
Else
Begin
Select @toplamMiktar = SUM(Miktar)
From IlUrunleri
Where DonemNo = @donemNo
Insert Into DonemUretim Values(@donemNo,@toplamMiktar)
End