VTYS1-UEE.docx

-- 10.Hafta
--Western bölgesinde yaşayan personelin adı soyadı ve işe başlama tarihi bilgisini veren kodu yazınız. (Bolge-Personeller)

select p.Adi,p.SoyAdi,p.IseBaslamaTarihi from Personeller p Join PersonelBolgeler pb on p.PersonelID=p.PersonelID join Sehirler s on s.SehirID=s.SehirID join Bolge b on s.BolgeID=b.BolgeID where BolgeTanimi like 'Western%'

--NewYork'ta yaşayan personelin Adı, Soyadı ve Doğum tarihi bilgisini veren kodu yazınız. (Şehirler-Personeller)

select distinct p.adi, p.soyadi, p.DogumTarihi from Personeller p Join PersonelBolgeler pb on p.PersonelID=p.PersonelID join Sehirler s on s.SehirID=pb.SehirID where SehirAdi like 'New York%'

--Seafood kategorisinde bulunan ürünlerin adı, birim fiyatı ve tedarikçi IDlerini bulunuz. (Ürünler-Kategoriler)

select UrunAdi,BirimFiyati,TedarikciID from Urunler inner join Kategoriler on KategoriAdi like 'seafood'

--Indirim oranı 0.10 olan ürünlerin satış toplamlarını ve urun adlarını listeleyiniz. (Satış detayları-Ürünler)

select sum(sd.BirimFiyati*Miktar), UrunAdi from Urunler u join [Satis Detaylari] sd on sd.UrunID=u.UrunID where 0.10=indirim group by UrunAdi

--TedarikçiID'si 5 olan ürünlerin adları ve kategori adları listeleyiniz. (Ürünler-Kategoriler)
select UrunAdi, KategoriAdi from Urunler join Kategoriler on Kategoriler.KategoriID=Urunler.UrunID join Tedarikciler where Tedarikciler.TedarikciID=5

-- 11.Hafta
declare @sayi int
set @sayi=10

declare @ad nvarchar(50)
set @ad=(select Adi from Personeller where PersonelID=3)

select @ad --Üstteki satırlarla birlikte seçildiğinde çalışır.

declare @adi nvarchar(50), @id int
set @adi='Ahmet' set @id=2

select @adi,@id

select @adi=Adi,@id=PersonelID from Personeller where PersonelID=2

select @adi,@id

--12.Hfta
-- View:
--Güvenlik amacıyla SQL'de kullanılır.
--Yazılan gerçek sorgunun diğer kişiler tarafından görüntülenmesini önlemek amacıyla View yapısına başvurulur.
--Karmaşık sorguların daha basit hale getirerek tek sorgu üzerinden çalışmasını sağlamak amacıyla kullanılır.
--Normal sorgulardan daha yavaş çalışırlar.
--Fiziksel olarak var olan yapılardır.
--Bu sorgularla veri ekleme, silme, güncelleme yapılabilir.
--Kendi içerisinde yazılan sorgunun sonucunu tablo olarak getirerek bu sonuçtan başka sorgular çalıştıırılmasına olanak sağlar.
--Create komutu ile oluşturulur.
--Create View View_Adi as Select ....(Asıl Sorgu) şeklinde oluşturulur.
--select * from View_Adi şeklinde kullanılır.
--View yapısı kullanılırken oluşturulan aliaslar select ile View çalıştırılırken kolon adı olarak kullanılır. Böylece gerçek kolon isimleri de tablo isimleri gibi gizlenmiş olur.
--Order By komtu View yapısı içerisindeki sorguda değil View oluşturulduktan sonra yazılan sorguda kullanılır. Eğer view içerisindeki sorguda kullanılmak isteniyorsa 'top' komutu ile birlikte kullanılması gerekir.
  
  use Ticaret
  -- Her bir kategorideki ürünlerin satış toplamlarını listeleyiniz.

  select KategoriAdi, UrunAdi, SUM(SD.BirimFiyati*Miktar) from Kategoriler K join Urunler U on U.KategoriID=K.KategoriID join [Satis Detaylari] SD on SD.UrunID=U.UrunID group by KategoriAdi, UrunAdi order by KategoriAdi

  create view KUSatis as 
  select KategoriAdi, UrunAdi, SUM(SD.BirimFiyati*Miktar) as Toplam from Kategoriler K join Urunler U on U.KategoriID=K.KategoriID join [Satis Detaylari] SD on SD.UrunID=U.UrunID group by KategoriAdi, UrunAdi

  select * from KUSatis
  -- Yukarıdaki kodu yazmış olduk. (Fonksiyon oluşturmak gibi)

  select * from KUSatis where KategoriId=5 --KatID 5 olanları listeler.

  create view KUSatis2
  with schemabinding 
  -- Bu kod sayesinde tablonun sütünlerı ile ilgili yapısal bir değişiklik yapılması söz konusu değildir. 
  -- Schema Binding özeliği olan view oluşturulurken Tablo önüne "dbo" ön takısı konulmalıdır.
   as 
  select KategoriAdi, UrunAdi, SUM(SD.BirimFiyati*Miktar) as Toplam from dbo.Kategoriler K join dbo.Urunler U on U.KategoriID=K.KategoriID join dbo.[Satis Detaylari] SD on SD.UrunID=U.UrunID group by KategoriAdi, UrunAdi

alter table [Satis Detayları] alter column Miktar varchar(50) -- Tablonun yapısını değiştiremiyoruz.

  create view KUSatis3
  with encryption 
  -- dbo. takısı kullanılsa da olur kullanılmasa da

   as 
  select KategoriAdi, UrunAdi, SUM(SD.BirimFiyati*Miktar) as Toplam from Kategoriler K join Urunler U on U.KategoriID=K.KategoriID join dbo.[Satis Detaylari] SD on SD.UrunID=U.UrunID group by KategoriAdi, UrunAdi

  -- with encryption özelliği ile view oluşturduğumuzda eğer oluşturduğumuz kodları silersek biz dahil hiç kimse kaynak kodlara ulaşamaz.

  -- Müşteri adı, SatışID, Satış Tarihi bilgilerini veren kodu yazınız

select MusteriID, SatisID, SatisTarihi from Satislar

create view 
select MusteriAdi, SatisID, SatisTarihi from Satislar S join Musteriler M on S.MusteriID=M.MusteriID

-- View oluştururken Aggregate fonksiyon kullanıyorsan, bu fonksiyonun sonuç söndüreceği sütuna mutlaka bir alias kullanılmalıdır. 

create view ToplamSatis as 
select UrunId as ÜrünKodu, AVG(BirimFiyati*Miktar) as Ortalama from [Satis Detaylari] group by UrunID 

select * from ToplamSatis

/* Stored Procedure:
Procedure nedir? Neden kullanılır?

Bazen veritabanından veri çekerken satırlarca sorgu yazmamız gerekebilir. Bu sorguları her defasında uzun uzun yazmak sorun olabilir.
Bir başka sebebi ise bu uzun sorguların yalnızca sizin değil sizin gibi başka kullanıcıların da yazması gerekebilir. Yazım hataları yaşanabilir, aynı işlem için farklı kişiler aynı sorguları yazmak durumunda kalabilir.
Bu problemlerin ortadan kalkması için sqlde stored procedure'ler kullanılır.
Uzun ve karmaşık sorguları procedure olarak yazıp vt'nin procedure kısmına koyduğumuzda tüm kullanıcılar o procedure'u çağırdığında çalıştırabilir.
Bir veya daha fazla SQL ifadesinin bir kod bloğu şeklinde bir kez yazılıp saklanması ve ihtiyaç duyulduğunda kullanılabilmesi için oluşturulan SQL objeleridir. Procedure'ler normal sorgulardan farklı olarak ilk oluşturulduğunda bazı işlemler yapılıp cache belleğe kaydedilir ve sonraki çalıştırılmalarında bu meta verileri tekrar üretmek yerine daha önce oluşturup kaydettiği cache P bellekten okur ve böylece prosedürler normal sorgulara göre daha performanslı çalışırlar.

Normal bir sorgunun yol haritası:
Query-Parse-Optimize-Compile-Execute-Results
Precedure'un Yol haritası:
Execute-Results
- Geniş kapsamlı prosedürler normal sorgulara göre daha az network trafiği oluşturular. Özellikle sık kullanılan ve uzun kod blokları içeren prosedürler bu anlamda ciddi performans artışı sağlarlar.
- Tıpkı fonksiyonlardaki gibi kodun farklı parametreler ile çalışmasını sağlar. (drop down)
- Veri tabanı güvenliğinin sağlanması amacıyla kullanılır. Yazılan prosedürün kodları gizlenerek kullanıcının yalnızca prosedürün yaptığı işi ve nasıl çalıştığını görmesi sağlanabilir.
- Kod bloklarını stabil hale getirmeyi sağlar. Normal sorgularda kod bloğu her defasında sıfırdan çalışırken prosedürlerle optimizasyon bir kez yapıldığı için tekrar yapılmaz.
- Create komutu ile oluşturulurlar. Bellekte kalıcı yer alırlar. Bu nedenle bir defa oluşturulmaları yeterlidir.
- Programmability - Stored Procedure klasöründen erişilebilir.
- Insert, delete, update işlemleri yapılabilir.
- With Encryption özelliği kullanılarak kilitlenebilirler.
*/
use Ticaret
create procedure P_UrunSatis(@id int)
as
select SatisId, SUM(Birimfiyati*Miktar) from [Satis Detaylari] where UrunID=@id group by SatisID

execute P_UrunSatis @id=11

-- Londra da yaşayan müşterilere yapılan satışların id ve Satış tarihlerini veren prosedürü yazınız.

create proc PMSatis
as
select SatisId, SatisTarihi from Satislar S join Musteriler M on M.MusteriID=S.MusteriID where Sehir like 'London%'

exec PMSatis

-- ID'si girilen personelin satışlarından elde ettiği %10luk prim tutarını veren prosedürü yazınız.

create proc PersonelPrim(@id int)
with encryption
as
select SUM(BirimFiyati*Miktar)*0.20 as Prim from [Satis Detaylari] SD join Satislar S on S.SatisID=SD.SatisID where PersonelID=@id

exec PersonelPrim @id=7

-- Personel ve Ürün
create proc PersonelPrimUrun(@P int, @U int)
as
select SUM(BirimFiyati*Miktar)*0.20 as Prim from [Satis Detaylari] SD join Satislar S on S.SatisID=SD.SatisID where PersonelID=@P and UrunID=@U

exec PersonelPrimUrun @P=5, @U=11