Set operators, Windows Functions, Data Modification

--Set operators
--Data manipulation
-- Windows Functions
-- union Intersect Except

use CommunityAssist
--union
Select PersonLastName, PersonFirstName,
EmployeeHireDate
From Person p
join Employee  e on p.PersonKey=e. PersonKey
Union
Select EmployeeLastName, EmployeeFirstName, 
EmployeeHireDate
From MetroAlt.dbo.Employee
use MetroAlt
Select PersonLastName, PersonFirstName,
EmployeeHireDate
From CommunityAssist.dbo.Person p
join CommunityAssist.dbo.Employee  e on p.PersonKey=e. PersonKey
Union All
Select EmployeeLastName, EmployeeFirstName, 
EmployeeHireDate
From MetroAlt.dbo.Employee

--intersect
Select City from PersonAddress
Intersect
Select EmployeeCity from MetroAlt.dbo.Employee

--except
Select City from PersonAddress
Except 
Select EmployeeCity from MetroAlt.dbo.Employee

Select EmployeeCity from MetroAlt.dbo.Employee
Except
Select City from PersonAddress

--ranking functions
Select GrantKey, ServiceKey, GrantAllocation,
Row_Number() over (Order by GrantAllocation desc) as RowNumber,
Rank() over (Order by GrantAllocation desc) as [Rank],
Dense_Rank() over (Order by GrantAllocation desc) as [DenseRank],
NTILE(10) over (Order by GrantAllocation desc) as [NTILE]
From ServiceGrant
Order by GrantAllocation desc

--windows partition functions
Select Grantkey, ServiceKey, GrantAllocation,
Sum(GrantAllocation) over () as TotalAllocations,
Sum(GrantAllocation) over(Partition by ServiceKey) as PerService
From ServiceGrant
order by GrantAllocation Desc

--pivot
Select EmployeeKey, [1], [2], [3], [4], [5], [6], [7], [8], [9], [10]
From (Select EmployeeKey,  ServiceKey, GrantKey From
dbo.ServiceGrant) as a
pivot (Count(GrantKey) for ServiceKey in ([1], [2], [3], [4], [5], [6], [7], [8], [9], [10])) as b

-- modifying data

Insert into Person  (PersonLastName, PersonFirstName, PersonUsername, PersonPlainPassword, Personpasskey, PersonEntryDate, PersonUserPassword)
Values('Alberts','Larry','larryA@gmail.com',null, 12345678,
GetDate(), dbo.fx_HashPassword('AlbertPass')),
('Able','Luke','luke.able@gmail.com',null, 12345678,
GetDate(), dbo.fx_HashPassword('AblePass'))

Select * from Person order by PersonLastName

Begin tran
Update Person 
Set PersonFirstName = 'Jason',
PersonPlainpassword='JasonPass'
Where Personkey=1
Commit Tran
Rollback tran

Delete from Person where Personkey =1
 Begin Tran
 Truncate Table GrantReview

 Select * From GrantReview

 Rollback Tran

 Insert into person(personLastname, PersonFirstName)
 Values('Lucas', 'George')
 Insert into Donation(DonationDate, DonationAmount, PersonKey)
 Values(GetDate(), 1500,Ident_Current('Person'));
 @@Identity

 Select * from Person
 Select * From Donation