Set Operators and Modifying Data

--set operators--

--union
Use metroAlt

Select EmployeeLastName lastname, EmployeeFirstName firstname, 
EmployeeEmail email, EmployeeCity City
From Employee
Union
Select PersonLastName, PersonFirstName, PersonEmail, PersonAddressCity
From Community_Assist.dbo.person p
inner join Community_Assist.dbo.PersonAddress pa
on p.PersonKey=pa.PersonKey

Select EmployeeCity City 
From Employee
Intersect
Select PersonAddressCity
From Community_Assist.dbo.PersonAddress


Select PersonAddressCity city
From Community_Assist.dbo.PersonAddress
Intersect
Select EmployeeCity  
From Employee

Select PersonAddressCity city
From Community_Assist.dbo.PersonAddress
Except
Select EmployeeCity  
From Employee

Select EmployeeCity  
From Employee
Except
Select PersonAddressCity city
From Community_Assist.dbo.PersonAddress

--Modifying Data--
Use Community_Assist

Insert into Person(PersonLastName, PersonFirstName, 
PersonEmail, PersonPassWord, PersonEntryDate, 
PersonPassWordSeed)
Values('Jordan','Michael','mj@gmail.com',null,GetDate(),null)

Insert into PersonAddress(PersonAddressApt, 
PersonAddressStreet, 
PersonAddressCity, PersonAddressState, 
PersonAddressZip, PersonKey)
Values(Null,'10002 Broad Street','Charlotte','NC', '90110', IDENT_CURRENT('Person'))

Insert into Donation(PersonKey, DonationDate, 
DonationAmount)
values(3, getDate(),100),
(109, getDate(),400),
(20, getDate(),250)

Create table Person2
(
PersonKey int, 
PersonLastName nvarchar(255), 
PersonFirstName nvarchar(255), 
PersonEmail nvarchar(255), 
PersonEntryDate DateTime
)

Insert into Person2(PersonKey, PersonLastName, 
PersonFirstName, PersonEmail, PersonEntryDate)
Select PersonKey, PersonLastName, 
PersonFirstName, PersonEmail, PersonEntryDate
From Person

Update Person2
Set PersonFirstName='Jason'
Where personKey = 1

Update Person2
set PersonlastName='Manning',
PersonEmail = 'LManning@gmail.com'
Where PersonKey=3

Begin tran

Update Person2
Set PersonLastName='Smith'


Select * from Person2
rollback tran
Commit tran

Truncate table Person2

Begin tran

Delete from Person2 where Personkey=130

Rollback tran

Drop table Person2

Select * from GrantType

Update Granttype
Set GrantTypeMaximum=GrantTypeMaximum * 1.05


Update Granttype
Set GrantTypeLifetimeMaximum=GrantTypeLifetimeMaximum * 1.05