Triggers and stuff

--trigger
--events insert update and delete

Use Community_Assist

Select * from GrantType
go
Create trigger tr_OneTimeMaximum on GrantRequest
instead of insert
as
--declare variables
Declare @OneTimeMax money
Declare @GrantType int
Declare @RequestAmount Money

--assign values from inserted and Granttype
Select @GrantType = GrantTypeKey from Inserted
Select @OneTimeMax=GrantTypeMaximum 
From GrantType where GrantTypeKey=@grantType
Select @RequestAmount= GrantRequestAmount 
From inserted
if @RequestAmount <= @OneTimeMax
begin
Insert into GrantRequest(GrantRequestDate, PersonKey, GrantTypeKey, 
GrantRequestExplanation, GrantRequestAmount)
Select GrantRequestDate, PersonKey, GrantTypeKey, 
GrantRequestExplanation, GrantRequestAmount
From inserted
end
Else
Begin
if not exists
  (Select name from sys.Tables where name ='DumpTable')
  Begin
  Create table Dumptable
  (
      GrantRequestDate Datetime,
   PersonKey int,
   GrantTypeKey int,
   GrantRequestExplanation nvarchar(255),
   GrantRequestAmount money
  )

  End
  Insert into Dumptable(GrantRequestDate, PersonKey, GrantTypeKey, 
 GrantRequestExplanation, GrantRequestAmount)
 Select GrantRequestDate, PersonKey, GrantTypeKey, 
    GrantRequestExplanation, GrantRequestAmount
    From inserted

End
Go
Insert into GrantRequest(GrantRequestDate, PersonKey, 
GrantTypeKey, GrantRequestExplanation, GrantRequestAmount)
Values (GetDate(), 4, 1, 'Hungry',250)

Select * from GrantRequest
Select * from DumpTable


Create Table #TempTable
(
   GrantRequestDate Datetime,
   PersonKey int,
   GrantTypeKey int,
   GrantRequestExplanation nvarchar(255),
   GrantRequestAmount money
)

Begin tran
Insert into GrantRequest(GrantRequestDate, PersonKey, 
GrantTypeKey, GrantRequestExplanation, GrantRequestAmount)
Values (GetDate(), 1, 1, 'Hungry',250)
RollBack tran
go
Create trigger tr_testInsert on Donation
after insert
As
Declare @Confirm UniqueIdentifier = NewID()
Declare @Amount money
Select @Amount = DonationAmount from inserted
if @Amount < 1000
Begin
Declare @Id int = ident_current('Donation')
update Donation
set DonationConfirmation = @confirm
where DonationKey = @ID
End

Insert into Donation(PersonKey, DonationDate, DonationAmount)
Values(3,GetDate(), 1200)

Select * from Donation

Stored Procedures

--stored procedures
--register a new person
--check to see if person exists
--pass in all the info we need
--insert to person
-- created seed hash the password 
--insert into personAddress
--insert into person Contact
--all or none happen

--or 1 = 1;Drop Table Student--

--version 1
Create procedure usp_Registration
@PersonLastName nvarchar(255), 
@PersonFirstName nvarchar(255), 
@PersonEmail nvarchar(255), 
@PersonPassWord nvarchar(20), 
@PersonAddressApt nvarchar(255)=null, 
@PersonAddressStreet nvarchar(255), 
@PersonAddressCity nvarchar(255) = 'Seattle', 
@PersonAddressState nchar(2)='Wa',
@PersonAddressZip nchar(9), 
@HomePhone nvarchar(255)=null,
@WorkPhone nvarchar(255)=null
As
Declare @seed int
Set @seed = dbo.fx_GetSeed()
Declare @pass varbinary(500)
set @pass=dbo.fx_HashPassword(@seed, @PersonPassword)

Insert into Person(
PersonLastName, 
PersonFirstName, 
PersonEmail, 
PersonPassWord, 
PersonEntryDate, 
PersonPassWordSeed)
Values(
@PersonLastName,
@PersonFirstName,
@PersonEmail,
@pass,
GetDate(),
@Seed)

Declare @PersonKey int
Set @personKey = ident_current('Person')

Insert into PersonAddress(
PersonAddressApt, 
PersonAddressStreet, 
PersonAddressCity, 
PersonAddressState, 
PersonAddressZip, 
PersonKey)
Values(
@PersonAddressApt,
@PersonAddressStreet,
@PersonAddressCity,
@PersonAddressState,
@PersonAddressZip,
@PersonKey)

IF Not @HomePhone is null
Begin
Insert into Contact(ContactNumber, ContactTypeKey, PersonKey)
Values(@HomePhone, 1, @personKey)
End


IF Not @WorkPhone is null
Begin
Insert into Contact(ContactNumber, ContactTypeKey, PersonKey)
Values(@WorkPhone, 2, @personKey)
End
go
exec usp_Registration
@PersonLastName='Rezenor', 
@PersonFirstName='Trent', 
@PersonEmail='tresenor@gmai;.com', 
@PersonPassWord='rPass', 
@PersonAddressStreet='10010 NIN South', 
@PersonAddressCity='Renton', 
@PersonAddressZip='98010', 
@HomePhone='3605551356'

go--version 2
Alter procedure usp_Registration
@PersonLastName nvarchar(255), 
@PersonFirstName nvarchar(255), 
@PersonEmail nvarchar(255), 
@PersonPassWord nvarchar(20), 
@PersonAddressApt nvarchar(255)=null, 
@PersonAddressStreet nvarchar(255), 
@PersonAddressCity nvarchar(255) = 'Seattle', 
@PersonAddressState nchar(2)='Wa',
@PersonAddressZip nchar(9), 
@HomePhone nvarchar(255)=null,
@WorkPhone nvarchar(255)=null
As
Declare @seed int
Set @seed = dbo.fx_GetSeed()
Declare @pass varbinary(500)
set @pass=dbo.fx_HashPassword(@seed, @PersonPassword)
Begin try--
Begin tran
Insert into Person(
PersonLastName, 
PersonFirstName, 
PersonEmail, 
PersonPassWord, 
PersonEntryDate, 
PersonPassWordSeed)
Values(
@PersonLastName,
@PersonFirstName,
@PersonEmail,
@pass,
GetDate(),
@Seed)

Declare @PersonKey int
Set @personKey = ident_current('Person')

Insert into PersonAddress(
PersonAddressApt, 
PersonAddressStreet, 
PersonAddressCity, 
PersonAddressState, 
PersonAddressZip, 
PersonKey)
Values(
@PersonAddressApt,
@PersonAddressStreet,
@PersonAddressCity,
@PersonAddressState,
@PersonAddressZip,
@PersonKey)

IF Not @HomePhone is null
Begin
Insert into Contact(ContactNumber, ContactTypeKey, PersonKey)
Values(@HomePhone, 1, @personKey)
End


IF Not @WorkPhone is null
Begin
Insert into Contact(ContactNumber, ContactTypeKey, PersonKey)
Values(@WorkPhone, 2, @personKey)
End
Commit tran
End try
Begin catch
Rollback tran
print Error_Message()
return -1
End catch

exec usp_Registration
@PersonLastName='Rogers', 
@PersonFirstName='Tina', 
@PersonEmail='tresenor@gmai;.com', 
@PersonPassWord='rPass', 
@PersonAddressStreet='1001 somewhere South', 
@PersonAddressCity='Seattle', 

@PersonAddressZip='98010', 
@HomePhone='3605551356'

go
Select * from Person
Select * from PersonAddress
Select * from Contact
go
Alter procedure usp_Registration
@PersonLastName nvarchar(255), 
@PersonFirstName nvarchar(255), 
@PersonEmail nvarchar(255), 
@PersonPassWord nvarchar(20), 
@PersonAddressApt nvarchar(255)=null, 
@PersonAddressStreet nvarchar(255), 
@PersonAddressCity nvarchar(255) = 'Seattle', 
@PersonAddressState nchar(2)='Wa',
@PersonAddressZip nchar(9), 
@HomePhone nvarchar(255)=null,
@WorkPhone nvarchar(255)=null
As
If not exists
  (Select PersonKey from Person 
    Where PersonEmail = @personEmail)
Begin
Declare @seed int
Set @seed = dbo.fx_GetSeed()
Declare @pass varbinary(500)
set @pass=dbo.fx_HashPassword(@seed, @PersonPassword)
Begin try--
Begin tran
Insert into Person(
PersonLastName, 
PersonFirstName, 
PersonEmail, 
PersonPassWord, 
PersonEntryDate, 
PersonPassWordSeed)
Values(
@PersonLastName,
@PersonFirstName,
@PersonEmail,
@pass,
GetDate(),
@Seed)

Declare @PersonKey int
Set @personKey = ident_current('Person')

Insert into PersonAddress(
PersonAddressApt, 
PersonAddressStreet, 
PersonAddressCity, 
PersonAddressState, 
PersonAddressZip, 
PersonKey)
Values(
@PersonAddressApt,
@PersonAddressStreet,
@PersonAddressCity,
@PersonAddressState,
@PersonAddressZip,
@PersonKey)

IF Not @HomePhone is null
Begin
Insert into Contact(ContactNumber, ContactTypeKey, PersonKey)
Values(@HomePhone, 1, @personKey)
End


IF Not @WorkPhone is null
Begin
Insert into Contact(ContactNumber, ContactTypeKey, PersonKey)
Values(@WorkPhone, 2, @personKey)
End
Commit tran
End try
Begin catch
Rollback tran
print Error_Message()
return -1
End catch
end
Else
Begin
print 'person already exists'
End

go
--update Procedure
create proc usp_UpdatePersonInfo
@PersonKey int, 
@PersonLastName nvarchar(255), 
@PersonFirstName nvarchar(255), 
@PersonEmail nvarchar(255), 
@PersonAddressApt nvarchar(255)=null, 
@PersonAddressStreet nvarchar(255), 
@PersonAddressCity nvarchar(255)='Seattle', 
@PersonAddressState nchar(2) = 'WA', 
@PersonAddressZip nchar(9)
As
Begin try
Begin tran
Update Person
Set PersonLastName =@PersonLastName,
PersonFirstName=@PersonFirstName,
PersonEmail=@PersonEmail
Where Personkey = @PersonKey

Update PersonAddress
set PersonAddressApt=@PersonAddressApt,
PersonAddressStreet=@PersonAddressStreet,
PersonAddressCity=@PersonAddressCity,
PersonAddressState=@PersonAddressState,
PersonAddressZip=@PersonAddressZip
Where PersonKey = @PersonKey
Commit tran
End Try
Begin Catch
Rollback tran
print Error_Message()
End Catch

Select * from Person

exec usp_UpdatePersonInfo
@PersonKey=4,
@PersonLastName='Carmel', 
@PersonFirstName='Bob', 
@PersonEmail='BobCarmel@gmail.com', 
@PersonAddressStreet='213 Walnut Street', 
@PersonAddressCity='Bellevue', 
@PersonAddressZip='98002'

Select * from PersonAddress where personkey=4
--another way to get a value into a variable
Declare @PersonKeyb int
Select @PersonKeyb = personKey from Person
  Where PersonEmail = 'BobCarmel@gmail.com'
print @PersonKeyb

Temp tables and Functions

Use Community_Assist

--temp table

Create table #TempPerson
(
     PersonKey int,
  PersonLastName nvarchar(255),
  PersonFirstName nvarchar(255),
  PersonEmail nvarchar(255)
)

insert into #TempPerson(PersonKey, PersonLastName, PersonFirstName, PersonEmail)
Select PersonKey, PersonLastName, PersonFirstName, PersonEmail
From Person

Select * from #TempPerson

Create table ##GlobalTempPerson
(
 PersonKey int,
  PersonLastName nvarchar(255),
  PersonFirstName nvarchar(255),
  PersonEmail nvarchar(255)
)

insert into ##GlobalTempPerson(PersonKey, PersonLastName, PersonFirstName, PersonEmail)
Select PersonKey, PersonLastName, PersonFirstName, PersonEmail
From Person

Select * from ##GlobalTempPerson

--scalar functions
go
create function fx_cube
(@number int)
returns int
As
Begin
Declare @cube int
Set @Cube=@number * @number * @number
return @Cube
End
go
Select dbo.fx_cube(7.5) as [cube]

go
Alter function fx_OneLineAddress
(@Apartment nvarchar(255),
@Street  nvarchar(255),
@City  nvarchar(255),
@State nchar(2),
@Zip nchar(9))
returns nvarchar(255)
as
Begin
Declare @address nvarchar(255)
if @Apartment is null
   Begin
     Set @Address=@Street + ', ' + @City + ', ' + @state + ' ' + @zip
   End
else
   Begin
      Set @Address= @Street + ' ' + @Apartment + ', ' + @City + ', ' + @state + ' ' + @zip
   End

return @address
End


Select PersonLastName, dbo.fx_OneLineAddress(PersonAddressApt,
[PersonAddressStreet],[PersonAddressCity],[PersonAddressState],
[PersonAddressZip]) as [Address]
From Person p
inner join PersonAddress pa
on p.PersonKey=pa.PersonKey


Create Function fx_RequestVsAllocationAmounts
(@Request money, @allocation money)
returns money
As
Begin
return @Request-@Allocation
End

Select gr.GrantRequestKey GrantRequestDate, GrantRequestAmount, 
GrantAllocationAmount, 
dbo.fx_RequestVsAllocationAmounts(GrantRequestAmount, GrantAllocationAmount) as [difference]
From GrantRequest gr
inner join grantReview rev
on gr.GrantRequestKey=rev.GrantRequestKey

Select GrantTypeKey, Year(GrantRequestDate) as [Year], Sum(GrantRequestAmount) as Request, 
sum(GrantAllocationAmount) as Allocated, 
sum(dbo.fx_RequestVsAllocationAmounts(GrantRequestAmount, GrantAllocationAmount)) as [difference]
From GrantRequest gr
inner join grantReview rev
on gr.GrantRequestKey=rev.GrantRequestKey
group by GrantTypeKey, Year(GrantRequestDate)

Set operators, Data Modifications

--Set operators
--Data modification
--Windows functions

use Community_Assist
--union
Select PersonFirstName, PersonLastName, EmployeeHiredAte
From person p
inner join Employee e
on p.PersonKey=e.PersonKey
union
Select EmployeeFirstName, EmployeeLastName, EmployeeHireDate
From MetroAlt.dbo.Employee
--intersect
Select PersonAddressCity from PersonAddress
intersect
Select  EmployeeCity from MetroAlt.dbo.Employee
--except
Select PersonAddressCity from PersonAddress
except
Select  EmployeeCity from MetroAlt.dbo.Employee

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

/***********************
Not on assignment
***********************/
--ranking function
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 [Dense Rank],
Ntile(10) over (order by  GrantRequestAmount desc) as [NTile]
From GrantRequest
Order by GrantRequestAmount desc

--windows partition function
Select distinct Year(GrantRequestDate) as[Year], GrantTypeKey, 
sum(GrantRequestAmount) over () as TotalRequests,
sum (GrantRequestAmount) over (partition by Year(GrantRequestDate)) as [AmountPerYear],
Sum(GrantRequestAmount) over (partition by GrantTypeKey) as perGrantType
From GrantRequest
order by Year(GrantRequestDate),GrantTypeKey 

--insert update delete

Insert into Person(PersonLastName, PersonFirstName,
PersonEmail,  PersonEntryDate)
Values('Conger', 'Steve', 'steve@gmail.com', GetDate())

Insert into PersonAddress(PersonAddressApt, PersonAddressStreet,
 PersonAddressCity, PersonAddressState, PersonAddressZip, PersonKey)
 Values(Null, '1701 Broadway','Seattle','Wa','98122',ident_current('Person'))

 Insert into Person(PersonLastName, PersonFirstName,
PersonEmail,  PersonEntryDate)
Values('Simpson', 'Bart', 'Bart@fox.com',GetDate()),
('Simpson', 'Homer', 'Homer@fox.com',GetDate()),
('Simpson', 'Lisa', 'Lisa@fox.com',GetDate())

 Select * from PersonAddress

 Insert into Person(PersonLastName, PersonFirstName,
PersonEmail,  PersonEntryDate)
Values(N'κονγεροσ',N'στεφανοσ', N'στεπηανοσ@γμαιλ.κομ', GetDate())

Select * from Person


Begin tran
update Person
Set PersonFirstName='Jason',
PersonEmail='jasonAnderson@gmail.com'
where PersonKey =1

Select * from Person

Commit tran
Rollback tran

Begin Tran
Delete from PersonAddress

Create
Alter
Drop

SQL

--set the database context
Use Community_Assist;

--simple selects the * is a wild card for return all columns

Select * From Person;

Select PersonLastName, PersonFirstName, PersonEmail
From Person;

Select PersonLastName, PersonFirstName, PersonEmail
From Person
Where PersonLastName='Tanner'
And not PersonFirstName='Chelsea'; --!=, <>

Select PersonLastName, PersonFirstName, PersonEmail
From Person
order by PersonLastname

Select PersonLastName, PersonFirstName, PersonEmail
From Person
order by PersonLastname desc, PersonFirstName desc;

Select * from Donation
Where DonationDate > '9/3/2015';

--date and time functions
Select Distinct Year(DonationDate) From Donation;
Select Distinct Month(DonationDate) From Donation;
Select Distinct Day(DonationDate) From Donation;
Select Distinct DatePart(hour,DonationDate) from Donation;
Select Distinct DatePart(MILLISECOND,DonationDate) from Donation;
Select GetDate();

Select * From Donation
Where Month(DonationDate) between 9 and 10;

--aggregate functions sum, avg, max, min, count

Select Sum(DonationAmount) as total From Donation
Where Month(DonationDate) between 9 and 10;

Select count(DonationAmount) as number From Donation
Where Month(DonationDate) between 9 and 10;

Select Avg(DonationAmount) as Average From Donation
Where Month(DonationDate) between 9 and 10;

Select Max(DonationAmount) as Average From Donation
Where Month(DonationDate) between 9 and 10;

Select Min(DonationAmount) as Average From Donation
Where Month(DonationDate) between 9 and 10;

--group by any field not part of an aggregate function

Select Year(DonationDate) as [Year], 
Sum(DonationAmount) as total 
From Donation
Group by Year(DonationDate);

Select * from Donation

--inner joins :Combine two tables

Select PersonLastName, PersonFirstName, PersonEmail, DonationDate, DonationAmount
From Person
inner join Donation
on Person.PersonKey=Donation.PersonKey --show how tables relate
order by DonationAmount desc

Select * From GrantRequest
--single line comment
/* this is
a multiline
comment
*/

--three table inner join
Select PersonLastName, PersonFirstName, GrantTypeName, GrantRequestDate,
GrantRequestExplanation, GrantRequestAmount
From Person
inner join GrantRequest
on Person.PersonKey=GrantRequest.PersonKey
inner join GrantType
on GrantType.GrantTypeKey=GrantRequest.GrantTypeKey

--inserts

Insert into Person(PersonLastName, PersonFirstName, 
PersonEmail,  PersonEntryDate)
Values('Simpson', 'Homer','homer@yahoo.com', GetDate())

Insert into PersonAddress( PersonAddressStreet, PersonAddressCity, 
PersonAddressState, PersonAddressZip, PersonKey)
Values('10001 Bart Street', 'Springfield', 'WA','98000', IDENT_CURRENT('Person')) 
--ident_current is a function that returns the last autonumber created in the table in 

Insert into Donation (PersonKey, DonationDate, DonationAmount)
Values(IDENT_CURRENT('Person'),GetDate(), 5)

Select * from PersonAddress
Select * from Donation

--updates change existing data. You should always
--use a where criteria
--begin tran allows an undo if you make a mistake

begin tran

Update Person
Set PersonFirstName='Jason',
PersonEmail='jasonAnderson@gmail.com'
Where Personkey=1

select * from Person

rollback tran --undo what you did

Commit Tran--commit it, write it to the database