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