Set Operators and modifying data

USE [Community_Assist]
GO

/****** Object:  View [dbo].[vw_HumanResources]    Script Date: 5/11/2016 10:11:00 AM ******/


ALTER view [dbo].[vw_HumanResources] with Schemabinding
AS
Select PersonLastName [LastName],
PersonFirstName [FirstName],
PersonEmail Email,
PersonAddressStreet [Address],
PersonAddressCity City,
PersonAddressZip ZipCode,
EmployeeHireDate HireDate
From dbo.Person p
Inner Join dbo.PersonAddress pa
on p.PersonKey=pa.PersonKey
inner join dbo.Employee e
on e.PersonKey = p.PersonKey

GO

--cross apply
Select Distinct a.GrantTypeKey, c.GrantRequestAmount
From dbo.GrantRequest a
cross Apply
(Select grantTypeKey, grantRequestAmount
From GrantRequest as b
Where b.GrantTypeKey = a.GrantTypeKey
Order By b.GrantRequestAmount desc, GrantTypeKey desc
Offset 0 rows fetch first 3 rows only) as c



Begin tran

Alter table person
Drop column PersonEmail

Rollback tran

--Set operators and modifying data
--Windows functions pivot

Select PersonKey, PersonFirstName, PersonLastName, PersonEmail
From Person
Union
Select EmployeeKey,EmployeefirstName, EmployeeLastName, EmployeeEmail
From MetroAlt.dbo.Employee


Select PersonAddressCity From PersonAddress
intersect
Select EmployeeCity from MetroAlt.dbo.Employee

Select PersonAddressCity From PersonAddress
Except
Select EmployeeCity from MetroAlt.dbo.Employee

Select EmployeeCity from MetroAlt.dbo.Employee
except
Select PersonAddressCity From PersonAddress

/******************************************
*********Not part of assignment*********/

--ranking functions

Select GrantRequestKey, GrantTypeKey, GrantRequestAmount,
Row_Number() over (order by GrantRequestAmount desc) as RowNumber,
Rank() over (order by GrantRequestAmount desc) as [Rank],
Dense_rank() over (order by GrantRequestAmount desc) as [DenseRank],
Ntile(10) over (order by GrantRequestAmount desc) as [NTile]
From GrantRequest
Order by GrantRequestAmount desc

--partition functions
Select GrantRequestKey, GrantTypeKey, GrantRequestAmount,
Sum(GrantRequestAmount) over() as TotalAllocation,
sum(GrantRequestAmount) over(partition by GrantTypeKey) as PerType,
(sum(GrantRequestAmount) over(partition by GrantTypeKey) /
Sum(GrantRequestAmount) over() * 100) as [TypePercentOfWhole],
GrantRequestAmount/
sum(GrantRequestAmount) over(partition by GrantTypeKey) * 100 as GrantPercentOfType
From GrantRequest
Order by GrantTypeKey 

--pivot
Select * from GrantRequest

Select  [Month], [1], [2], [3], [4], [5], [6], [7], [8], [9], [10]
From (Select Month(GrantRequestDate) as [Month] ,GrantTypeKey, GrantRequestKey
From dbo.GrantRequest) as a
pivot (Count(GrantRequestKey) for GrantTypeKey in 
    ([1], [2], [3], [4], [5], [6], [7], [8], [9], [10])) AS b

/*************************************************/
--Inserts
Insert into person
(PersonLastName, PersonFirstName, PersonEmail,  PersonEntryDate)
Values('Simpson', 'Homer', 'Homer@springfield.com', GetDate()),
('Simpson','Marge','Marge@springfield.com',GetDate())

Select * from Person

Create Table Person2
(
    Lastname nvarchar(225),
 FirstName nvarchar(255),
 Email nvarchar(255)
)

Insert into Person2(LastName, firstname, email)
Select PersonLastName, PersonFirstName, PersonEmail
From Person

Select * from Person2

Begin tran

Update Person2
Set FirstName='Jason'
Where Email='JAnderson@gmail.com'

Commit Tran

Update Person2
Set LastName='Smith'

Rollback tran

Delete from Person2
Where PersonKey=130

Select * from Person
Drop Table Person2