Set operators and Inserts, Updates and Deletes

--set operators
--union joins two different tables
--both sides of the union need to have a similar structure
use Community_Assist
Select PersonLastName, PersonFirstName, PersonEmail
From Person
Union
Select EmployeeLastName, EmployeeFirstName, EmployeeEmail
From MetroAlt.dbo.Employee


Select PersonLastName, PersonFirstName, PersonEmail, PersonAddressCity
From Person p
Inner Join PersonAddress pa
ON p.PersonKey=pa.PersonKey
Union
Select EmployeeLastName, EmployeeFirstName, EmployeeEmail, EmployeeCity
From MetroAlt.dbo.Employee

--intersect returns all the values that are in both
--selects
Select  PersonAddressCity
From PersonAddress pa
Intersect
Select EmployeeCity
From MetroAlt.dbo.Employee

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

--Except returns only those values that are in 
--the first query that are NOT in the second

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

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

--modify data

--basic insert
Insert into Person(PersonLastName, PersonFirstName, PersonEmail,PersonEntryDate)
values ('Johnson','Rupert','rj@outlook.com',getDate())

--insert multiple rows
Insert into Person(PersonLastName, PersonFirstName, PersonEmail,PersonEntryDate)
values ('Lexington','Mark','marklex@gmail.com', GetDate()),
('Ford', 'Harrison', 'Hansolo@starwars.com', GetDate())

--create variable for password and seed
Declare @seed int = dbo.fx_getseed()
Declare @password varbinary(500) = dbo.fx_HashPassword(@seed, 'MoonPass')

Insert into Person(PersonLastName, PersonFirstName, 
PersonEmail, PersonPassWord, PersonEntryDate, 
PersonPassWordSeed)
Values('Moon', 'Shadow','shadow@gmail.com', @password, GetDate(),@seed)

--the ident_current function returns the last autonumber (identity) created
--in the database named
Insert into PersonAddress( 
PersonAddressStreet, 
PersonAddressCity, PersonAddressState, 
PersonAddressZip, PersonKey)
Values ('1010 South Street', 'Seattle', 'WA', '98000',IDENT_CURRENT('Person'))



Select * from PersonAddress
--update changes existing data.
--it is one of the most dangerous
--SQL Commands
Update Person
Set PersonFirstName='Jason'
where PersonKey =1 

--begin a manual transaction (allows undo)
Begin tran

Update Person
Set PersonLastName='Smith',
PersonEmail='rs@outlook.com'
Where personKey=130

Select * from Person

Select * from GrantType

Rollback tran --undo transaction
Commit Tran -- write the results to the database

--update everything on purpose
Update GrantType
Set GrantTypeMaximum=GrantTypeMaximum * 1.05,
GrantTypeLifetimeMaximum=GrantTypeLifetimeMaximum * 1.1

Delete From Person where personkey=1 
--won't work because of referential integrity constraints


begin tran
--but it will work on a child table
--this command will delete all the records
--in the personAddress table
Delete from Personaddress
Select * from personAddress
rollback tran

Create table People
(
   lastname nvarchar(255),
   firstname nvarchar(255),
   email nvarchar(255)
)

Insert into people(lastname, firstname, email)
select personlastname, personfirstName, personEmail
from person

Select * from people

Delete from people
Where email ='JAnderson@gmail.com'

--another way to delete all
--the records in a table
Truncate table people

--Drops the whole table
Drop table people