Set Operators and modifying Data

--set operators and modifying data

--union

use Community_Assist
Select PersonLastName, PersonFirstName, PersonEmail, PersonAddressCity, 'person' [table]
From Person
Inner join PersonAddress
on Person.PersonKey=PersonAddress.PersonKey
Union
Select EmployeeLastName, EmployeeFirstName, EmployeeEmail, EmployeeCity, 'Employee'
From MetroAlt.dbo.Employee

--intersection
Select PersonAddressCity [City] From PersonAddress
Intersect
Select EmployeeCity from MetroAlt.dbo.Employee


Select EmployeeCity [City]from MetroAlt.dbo.Employee
Intersect
Select PersonAddressCity  From PersonAddress

--except
Select EmployeeCity [City]from MetroAlt.dbo.Employee
Except
Select PersonAddressCity  From PersonAddress

Select PersonAddressCity [city] from PersonAddress
Except
Select EmployeeCity from MetroAlt.dbo.Employee

--Modifying data
Insert into Donation(PersonKey, DonationDate, DonationAmount)
Values(4,getDate(),1000.00),
(7,getDate(),500.00),
(34,getDate(),100.00)

Select * from Donation

--insert a new person, new Address, new contact and donation
Declare @Seed int
Set @seed=dbo.fx_GetSeed()
Insert into Person(PersonLastName, PersonFirstName, PersonEmail,
PersonPassWord, PersonEntryDate, PersonPassWordSeed)
Values('Curry','Steph','steph.curry@msn.com',
dbo.fx_HashPassword(@seed,'CurryPass'),getDate(),@seed)

Insert into PersonAddress(PersonAddressApt, PersonAddressStreet,
 PersonAddressZip, PersonKey)
Values(null,'101010 Broadway','98100', ident_current('Person'))

Insert into Contact(ContactNumber, ContactTypeKey, PersonKey)
Values('2065551029',1,ident_current('Person'))

Insert into Donation(PersonKey, DonationDate, DonationAmount)
Values(ident_current('Person'), GetDate(),1200.00)

Select * From Person
Select * from PersonAddress
Select * from Contact
Select * from Donation

--Updates

Create table Person2
(
    personLastName nvarchar(255),
 personFirstName nvarchar(255),
 personEmail nvarchar(255)
)

Insert into person2
Select Personlastname, PErsonfirstName, PersonEmail
From PErson

Select * from Person2

Update Person2
Set personFirstName='Jason',
personlastName='Andrews'
where PersonEmail='JAnderson@gmail.com'

Begin tran
Update Person2
Set personLastName='Smith'
Rollback tran
Commit tran

Update Person
Set personFirstName='Jason',
personlastName='Andrews',
personEmail='Jandrews@gmail.com'
Where personkey=1

Select * from Person
order by PersonLastName

Select * from GrantType

Update GrantType
Set GrantTypeMaximum=GrantTypeMaximum * 1.05,
GrantTypeLifetimeMaximum=GrantTypeLifetimeMaximum * 1.05

Begin tran
Delete from PersonAddress
Select * from PersonAddress
Rollback tran

Select * from Donation

Begin tran
Delete from donation 
where donationKey =52
Commit tran

Truncate table Person2
Select * from Person2

--Get rid of object
Drop table Person2