Untitled

use E_Commerce
select Colors.ColorName,Sizes.SizeName from Colors cross join Sizes

select convert(varchar,ct.ContractDate,103),ct.Subject,ct.Details, u.Fullname 
as 'imzalayan',u2.Fullname as 'Onaylayan'  from Contracts ct
  join Users u on ct.imzalayan= u.ID 
join Users u2 on u2.ID= ct.onaylayan

use Northwind
select e.LastName, e.FirstName,e.EmployeeID, e.ReportsTo,er.FirstName 
from Employees e left join Employees er on e.ReportsTo=er.EmployeeID
 

 select LastName, FirstName, convert(varchar,BirthDate,103) as BirthDate,
 case when datepart(year,Birthdate)<1950 then 'Silent Generation'
	  when datepart(year,Birthdate)>=1950 and datepart(year,Birthdate)<1970 then 'Baby Boomers'
	  when datepart(year,Birthdate)>=1970 and datepart(year,Birthdate)<1980 then 'X Gen'
	  when datepart(year,Birthdate)>=1980 and datepart(year,Birthdate)<1995 then 'Y Gen'
	  when datepart(year,Birthdate)>=1995  then 'Z Gen'
	 
end as Generation
 from Employees order by Generation
 --insert komutu tabloya satır ekler
 /*insert into tabloadı (alan1,alan2,alan3,...,alanx)
			values(değer1,değer2,değer3,...,değerx)
			
			insert ifadelerinde into kelimesi opsiyoneldir
			alan adlarının tamamı yazılmak zorunda değildir ama zorunlu alanlar
			yazılmalıdır
			Autoidentity özelliği olan alanlara değer yazılmaz
			alan sayısı ile gönderilen değerlerin sayısı eşit olmalı ve değerler aynı sıra
			ie eşleşmelidir

			


			
			*/
INSERT INTO Categories (CategoryName)
VALUES ('Akşam')

/*
UPDATE komutu, satır veya satırlardaki değerleri güncelleme işlemi yapar
UPDATE tabloadı SET alan1=deger1, alan2=deger2.....
WHERE koşul
UPDATE ifadelerinde where kısmı opsiyoneldir, olmasa da çalışabilir ama dikkat edilmelidir
alan adı seçiminde özgürüz, istediğimiz kadar alanı değer belirtmek suretiyle değiştirebiliriz.
autoidentity alanların değerleri değiştirilemez
*/
UPDATE Categories SET CategoryName='Akşamcılar'
WHERE CategoryID=18

--DELETE
DELETE Categories
WHERE CategoryID=18

DBCC CHECKIDENT('Categories',RESEED,8)-- İÇİNDE VERİ OLAN TABLODA EN YÜKSEK ID VERİLİR,
--1FAZLASINDAN DEVAM EDER
DBCC CHECKIDENT('Categories',RESEED,0)-- İÇİNDE VERİ OLAN TABLODA 0 VERİLİR,
--1'den DEVAM EDER
SELECT IDENT_CURRENT('Categories')

---Alter komutu nesnelerde(tabloi view, stored proc., function, role, user, login, database) değişiklik yapar
USE BigKahunaBurger
ALTER TABLE Customers
ADD Notes varchar(100)

ALTER TABLE	Customers
DROP COLUMN Notes

ALTER TABLE Customers
ALTER COLUMN Notes varchar(1200) --veri türünü değiştirir

DROP TABLE Customers --bağlantılı(referanslı) tablolar silinemez

/*
GRANT
REVOKE
DENY*/

GRANT INSERT ON Customers TO bekir;

REVOKE SELECT ON Customers TO bekir;

DENY SELECT ON Customers TO bekir;

grant select on Orders to AA

grant select on Customers to StandartCalisan
grant select on Foods to StandartCalisan
/*
sql'e yeni bir kişi gelecek adı sezen
bu kişi Northwind veritabanında sadece categories ve employees tablolarında select işlemi yapabilecek

ek olarak bi grup(şarkıcılar) kullanıcıya tüm tablolar için insert ve update yetkisi verilecek
bi kullanıcı daha tanımlanacak adı nilüfer, kendi yetkilerini direkt şarkıcıar olarak alacak ama suppliers tablosunda işlem yapamayacak
bi de kayahan olacak bu kişi tüm yetkilerini şarkıcılardan alacak ve ek olarak tüm tablolara select yapabilecek
*/
grant select on Categories to Sezen 
grant select on Employees to Sezen 
deny delete on Suppliers to Nilüfer
deny update on Suppliers to Nilüfer
deny select on Suppliers to Nilüfer
revoke select on CustomerCustomerDemo to sarkicilar
revoke select on CustomerDemographics to sarkicilar
revoke select on Customers to sarkicilar
revoke select on Employees to sarkicilar
revoke select on EmployeeTerritories to sarkicilar
revoke select on [Order Details] to sarkicilar
revoke select on Orders to sarkicilar
revoke select on Products to sarkicilar
revoke select on Region to sarkicilar
revoke select on Shippers to sarkicilar
revoke select on Suppliers to sarkicilar
revoke select on Territories to sarkicilar

grant select on CustomerCustomerDemo to Kayahan
grant select on CustomerDemographics to Kayahan
grant select on Customers to Kayahan
grant select on Employees to Kayahan
grant select on EmployeeTerritories to Kayahan
grant select on [Order Details] to Kayahan
grant select on Orders to Kayahan
grant select on Products to Kayahan
grant select on Region to Kayahan
grant select on Shippers to Kayahan
grant select on Suppliers to Kayahan
grant select on Territories to Kayahan

-- Login->user 
				-->Role

deny select on Categories to Alex
grant update on Employees to Alex
--tüm kullanıcılar başlangıçta 0 yetkiye sahiptir, hiçbir işlem yapamazlar
-- kullanıcılara direkt kendilerine veya roller üzerinden roller verilebilir
--örneğin db_datawriter rolüne atanan bir kullanıcı tüm tablolarda insert, update,delete
--yapabilir
--özel olarak bir kullanıcıda rodle verilen yetkiyi ezen yetki verilebilir, örneğin ek bir
--select hakkı tanınabilir
--deny işlemi diğer bütün işlemleri ezer ve yetkiyi tamamen kaldırır, yok eder


create view vw_ProductWCategories 
as
select ProductName, isnull(CategoryName,'-') as CategoryName
from Products as p
left join Categories c on c.CategoryID=p.CategoryID

alter view vw_ProductWCategories 
as
select ProductName, isnull(CategoryName,'-') as CategoryName, p.UnitPrice
from Products as p
left join Categories c on c.CategoryID=p.CategoryID

drop view [vw_ProductWCategories]

select * from vw_ProductWCategories


grant select on vw_ProductWCategories to student
--scalar fonksiyon bir şey hesaplayıp değer döndürür


/*create*/alter function dbo.CalculatedRisk(@ProductID int)
RETURNS varchar(50)
as
begin
	declare @ToplamSatis AS int, @ToplamStok AS int, @myReturn AS varchar(50)
	
	select @ToplamStok=UnitsInStock from Products where ProductID=@ProductID
	select @ToplamSatis=sum(Quantity) from [Order Details] where ProductID= @ProductID

	if @ToplamSatis/20 > @ToplamStok
		begin
			SET @myReturn = 'Risksiz'
		end
	else
		begin
			set @myReturn = 'Riskli'
		end
	return @myReturn
end

select ProductID,ProductName, dbo.CalculatedRisk(ProductID) as 'Risk Durumu' from Products order by ProductID





SELECT SUM(Quantity)
FROM [Order Details]
WHERE ProductID=5

SELECT p.ProductID, p.ProductName, SUM(od.Quantity)
FROM Products p LEFT JOIN [Order Details] od ON p.ProductID=od.ProductID
GROUP BY p.ProductName,p.ProductID

CREATE FUNCTION dbo.SumSales(@ProductID int)
RETURNS int
AS
BEGIN
	DECLARE @toplam AS int
	SELECT @toplam=SUM(Quantity)
	FROM [Order Details]
	WHERE ProductID=@ProductID
	RETURN @toplam
END
 select ProductName, dbo.SumSales(ProductID) from Products

 --rezervasyon
 /* oda ve masalar -> kaynak tipleri (kat bilgisi/ oda mı masa mı/no )
 kullanıcılar (çalışanlar/ üyeler)
 rezervasyon yap/ iptal et*/
 select convert(varchar, r.StartDateofStay,103) as 'StartDateofDay', 
convert(varchar, r.StartDateofStay,108) as 'StartTimeofStay',s.Status,m.MemberFullName,a.AreaNo 
from Reservations r join Areas a on r.AreaId=a.ID 
join Members m on m.ID=r.MemberId 
join Statuses s on s.ID=r.StatusId

set dateformat dmy
select a.AreaNo, s.Status , f.KatNo, a.AreaNo
from  Areas a join Reservations r on r.AreaId=a.ID 
join Members m on m.ID=r.MemberId 
join Statuses s on s.ID=r.StatusId
join Floors f on f.ID=a.FloorId
where  a.ID not in (select ID
from Reservations
where  convert(varchar, StartDateofStay,103) = '11/12/2021' and StatusId=1)


select count(a.AreaNo) as sayı, f.KatNo, at.AreaTypeName
from Areas a join Floors f on a.FloorId= f.ID
join AreaTypes at on at.ID=a.AreaTypeId
where a.ID
not in (
select ID
from Reservations
where  convert(varchar, StartDateofStay,103) = '11/12/2021')
group by AreaTypeName, f.KatNo

select   top 1 count (StatusId) as adet, m.MemberFullName from Reservations r 
join Members m on r.MemberId= m.ID

group by m.MemberFullName
order by count(StatusId)Desc


-------------------------!!!!!!!!!!!!!!!!!!!!!!!!!!--------------------------
select MemberFullName, 0 as Adet
from Members
where ID not in (select MemberId from Reservations)

union
select m.MemberFullName, count(*) as Adet
from Reservations r
join Members m on m.ID= r.MemberId 
group by m.MemberFullName
Having Count(*)<2

-----------------------!!!!!!!!!!!!!!!!!!!!!!!!!!!!--------------------------
/*
use Northwind
select t.CompanyName, t.CompanyType
from (select CompanyName, 'Shipper' as CompanyType from Shippers
union
select CompanyName, 'Supplier' as CompanyType from Suppliers) as t
order by t.CompanyType, t.CompanyName

*/

select MemberId,StartDateofStay,EndDateofStay,
DATEDIFF(d,StartDateofStay,EndDateofStay) from Reservations 
where DATEDIFF(hour,StartDateofStay,EndDateofStay)>20


select * from Reservations

select MemberId,sum(DATEDIFF(hour,StartDateofStay,EndDateofStay)) as 'toplam saat' 
from Reservations 
group by MemberId
having sum(DATEDIFF(hour,StartDateofStay,EndDateofStay))>20



---!!!!!!!!!!!!!!!!!!!!!--
alter procedure dbo.YilAdetHesapla
as
 delete YilAdet
 insert into YilAdet (Yil,Adet)
 select datepart(year,o.OrderDate) as 'Yıl', sum(od.Quantity) as 'toplam satış'
 from [Order Details] od join Orders o on od.OrderID=o.OrderID  
 group by datepart(year,o.OrderDate)
 order by datepart(year,o.OrderDate)
-----------!!!!!!!!!!!!!!!-------
exec dbo.YilAdetHesapla --procedure'lar exec komutu ile çalışır



grant control on dbo.CalculatedRisk to bekir1
grant control on dbo.YilAdetHesapla to bekir1

create trigger CategorySilinemez on Categories
instead of delete
--categories tablosunda delete işlemi denenirse bu trigger devreye girer ve hata verip sonra da
--transaction'ı geri alır, yani silmeye izin vermez.
as 
begin
	raiserror ('Categories Tablosu üzerinde kayıt silinemez',1,1)
	rollback tran
end

select * into Products_cpy from Products



----
alter trigger [dbo].[PriceHistory] on [dbo].Products
after update
as
begin
set nocount on
declare @ProductID as int, @OldPrice as money, @NewPrice as money
select @ProductID = ProductID
from deleted

select @OldPrice=UnitPrice
from deleted

select @NewPrice=UnitPrice
from inserted

if @OldPrice!= @NewPrice
	begin
		insert into PriceHistoryy (ProductID, OldPrice, NewPrice, IslemDate)
		values(@ProductID,@OldPrice,@NewPrice,getdate())
	end
end
-----
--set nocount on
update Products set UnitPrice=12121 where ProductID=1
 


 alter trigger [dbo].[CustomerBackup] on [dbo].Customers
after delete
as
begin

declare @CustomerID as varchar, @CompanyName as varchar(50), @ContactName as varchar
select @CustomerID = CustomerID
from deleted

select @CompanyName=CompanyName
from deleted

select @ContactName=ContactName
from deleted

	begin
		insert into DeletedOnes (CustomerID, CompanyName, ContactName)
		values(@CustomerID,@CompanyName,@ContactName)
	end

	/*
	insert into Customers_DeletedOnes
	select * from deleted
	*/
end

-------------------!!!!!!!!!!!!----------
alter trigger [dbo].[ProductEntrancee] on [dbo].Products
after update, insert
as
begin


declare @ProductID as int, @ProductName as varchar(50), @Amount as int

select @ProductID = ProductID
from inserted

select @ProductName = ProductName
from inserted

select @Amount = UnitsInStock
from inserted



	
	insert into ProductEntrance (ProductID, ProductName, Amount,EntranceDate)
	values(@ProductID,@ProductName,@Amount,getdate())

	
	
end
----------!!!!!!!!!!!!!!!!----------		   
create trigger [dbo].[xxx] on [dbo].[ProductEntrance]
instead of update

as 
begin
	raiserror('ProductEntrance tablosu üzerinde kayıt değiştirilemez',1,1)
	rollback tran
end
after delete 
as
begin
 --!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!
 --!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!
end
--işlem adetlerine göre en fazla kaydı olan 3 çalışanın adını ve işlem adedini yazın ve
--bunun bir view olarak kaydedip test edin sonra da view'a yeni bir kullanıcı için select yetkisi verin

alter view EnCokSatan
as
select top 3 with ties e.EmployeeID,e.FirstName+' '+e.LastName as 'Employee', count(o.OrderID) as 'Sayı' 
from Orders o join Employees e on o.EmployeeID=e.EmployeeID

group by e.EmployeeID, e.FirstName+' '+e.LastName
having count(o.OrderID)>125
order by Sayı desc

--top olmazsa view'da order by yapılamaz

grant select on EnCokSatan to Bekir1

select * from EnCokSatan

alter view Listele
as
select top 1000 p.ProductName, s.CompanyName,c.CategoryName, p.UnitPrice from Products p 
left join Suppliers s on p.SupplierID=s.SupplierID 
left join Categories c on p.CategoryID=c.CategoryID
where UnitPrice between 20 and 25
order by p.ProductName desc

select * from Listele
grant select on Listele to Bekir1

-------!!!!!!!!!!!!!!!!!!1


alter function fn_Listele (
 @min as money,
 @max as money )
  returns @Listeli table (
 id int identity(1,1),
 [ProductName] varchar(50),
 [CompanyName] varchar(50),
 [CategoryName] varchar(50),
 [UnitPrice] money
)
as
begin


 begin
  insert into @Listeli
  
  select  p.ProductName, s.CompanyName,c.CategoryName, p.UnitPrice from Products p 
left join Suppliers s on p.SupplierID=s.SupplierID 
left join Categories c on p.CategoryID=c.CategoryID
where UnitPrice between @min and @max
order by p.ProductName desc

 end

 return;
end

------------!!!!!!!!!!!!!!!!!!!!!

select * from [dbo].[fn_Listele](12, 110)

create view EmployeeManagerList
as
select e.FirstName+' '+e.LastName as Employee,
 e.HomePhone, e2.FirstName+' '+e2.LastName as 'Reports to'
from Employees e left join Employees e2 on e.ReportsTo=e2.EmployeeID

select * from EmployeeManagerList

select convert(varchar,OrderDate,103) as orderDate, convert(varchar,ShippedDate,103) as ShippedDate,
 CustomerID, Freight 
from Orders 
where datepart(YEAR,OrderDate)=1997 and datepart(month,OrderDate) = 5 
and datepart(DAY,OrderDate)=19
---????????????
select * from Employees 
where Country='USA' and not in (select *  from Employees where City='Seattle' and Country='USA')
-----??????????

select City, CompanyName, ContactName from Customers
where City like('a%') or City like('b%')
order by ContactName desc
----????
select count(*) as sayi, c.ContactName from Orders o left join Customers c 
where datepart(year, o.OrderDate)>1996 group by c.ContactName
-----????
set dateformat dmy
select c.CompanyName, COUNT(*) as NumOrders
from Orders as o 
join Customers c on c.CustomerID=o.CustomerID
where OrderDate>'31.12.1996'
group by c.CompanyName
having COUNT(*)>15
order by count(*) desc


select od.OrderID,(od.UnitPrice*od.Quantity)*(1-od.Discount) as TotalPrice, c.CompanyName from [Order Details] od join Orders o on od.OrderID=o.OrderID
join Customers c on o.CustomerID=c.CustomerID
where (od.UnitPrice*od.Quantity)*(1-od.Discount)>10000 
order by (od.UnitPrice*od.Quantity)*(1-od.Discount) desc


select o.OrderID, e.LastName from orders o join Employees e on o.EmployeeID=e.EmployeeID
where o.ShippedDate>o.RequiredDate

-----
alter trigger [dbo].[YedekleShipper] on [dbo].Shippers
after delete
as
begin
declare @ShipperID as int, @CompanyName as nvarchar(40), @Phone as nvarchar(24)

select @ShipperID = ShipperID
from deleted

select @CompanyName = CompanyName
from deleted

select @Phone = Phone
from deleted
	insert into DeletedShipper (ShipperID, CompanyName, Phone)
	values(@ShipperID,@CompanyName,@Phone)
end
------
select database_id, name
from sys.databases--sunucudaki veritabanları

select * from sys.tables--norhwinddeki tablo adları

select * from sys.views--northwinddeki view adları

select t.name as triggername,o.name as tablename  from sys.triggers as t join sys.objects as o on o.object_id=t.parent_id
order by tablename, triggername -- northwinddeki trigger ve tablo adları

select * from sys.objects 


create trigger [dbo].[UpdateProducts] on [dbo].[Order Details]
instead of insert
as
begin
	declare @Quantity as smallint, @UnitsInStock as smallint
	select @Quantity = Quantity from inserted
	select @UnitsInStock = UnitsInStock 
	from Products where ProductID = (select ProductID from inserted)

	if @Quantity> @UnitsInStock
	begin
		raiserror('Stok Yeterli Değil',1,1)
		rollback tran
		
	end
	else
	begin
		insert into [Order Details] (OrderID,ProductID,UnitPrice,Quantity,Discount)
		select OrderID,ProductID,UnitPrice,Quantity,Discount
		from inserted
	end
end



------------------!!!!!!!!!!!!!!!!!!!-----------------
CREATE trigger [dbo].[UpdatedProducts] on [dbo].[Order Details]
after insert
as
begin

declare @Quantity as smallint, @UnitsInStock as smallint
	select @Quantity = Quantity from inserted
	select @UnitsInStock = UnitsInStock 
	from Products where ProductID = (select ProductID from inserted)


	update Products set UnitsInStock= UnitsInStock-@Quantity where ProductID=(select ProductID from inserted)
end
--------------!!!!!!!!!!!!!!!!!!!------------

delete from [Order Details] where OrderID=11000 and ProductID=59

----------------!!!!!!!!!!!!!!!!!!!!1--------------------

CREATE trigger [dbo].[Updated2Products] on [dbo].[Order Details]
after delete
as
begin

declare @Quantity as smallint, @UnitsInStock as smallint
	select @Quantity = Quantity from deleted
	select @UnitsInStock = UnitsInStock 
	from Products where ProductID = (select ProductID from deleted)


	update Products set UnitsInStock= UnitsInStock+@Quantity where ProductID=(select ProductID from deleted)
end

---------------!!!!!!!!!!!!!!!!----------------------