SQL Part 2

use CommunityAssist
--another join of three tables
--the as Aliases a column giving it a temporary
--name for the result table
--the as key word is actually optional
Select p.Personkey, PersonLastName as "Last Name", 
   PersonFirstname as "First Name",
   [Street],[Apartment],[State],[City],[Zip],
 [DonationDate],[DonationAmount]
 From Person  p
 inner join PersonAddress pa
 on p.PersonKey=pa.PersonKey
 inner join donation d
 on p.PersonKey=d.PersonKey
 where DonationAmount > 1000
 order by DonationAmount desc

 --a cross join matches every value in the first table
 --to every value in the second table
 Select PersonLastName, DonationAmount
 From Person
 cross Join Donation

 --getdate is a function that returns the current date and time
 select GetDate() as Today

 Select * from Donation

 --some date functions
 Select Distinct Year(DonationDate) as [Year] from Donation
 Select Distinct Month(DonationDate) as [Month] from Donation
 Select Distinct Day(DonationDate) as [Day] from Donation

 --DateDiff is a function that subtracts one date from
 --another. You have to specify the unit, the one below
 --specifies days, yy is years, mm is month
 Select DonationDate, DonationConfirmDate, 
 DateDiff(dd,DonationDate, DonationConfirmDate) 
 as "Time to Confirmation"
 From Donation

 --these are the basic aggregate functions
 Select Sum(DonationAmount)From Donation
 Select Avg(DonationAmount)From Donation
 Select Count(DonationAmount)From Donation
 Select Max(DonationAmount)From Donation
 Select Min(DonationAmount)From Donation

 --any column that is not included in the aggregate function
 --in this case sum must be include in a group by statment
 --the group by prioritizes the grouping from left to right
 Select Year(DonationDate) as [year],
  Month(DonationDate) as [Month],
  Sum(donationAmount) as total
 From Donation
 group by Year(DonationDate), Month(DonationDate)

  Select Year(DonationDate) as [year],
  Month(DonationDate) as [Month],
  Avg(donationAmount) as Average
 From Donation
 group by Year(DonationDate), Month(DonationDate)

 --the having clause is like the where clause
 --but is used when the criteria includes
 --an aggregate function, in this case count
 Select Month(DonationDate) as [Month],
  Day(DonationDate) as [Day], 
  count(*) as [Count]
  From donation
  group by Month(DonationDate), Day(DonationDate)
  Having Count(DonationAmount) > 2

  --insert a new person and then a new donation
  Insert into Person(PersonLastName, PersonFirstName)
  values('Bird','Larry')
  --the ident_current function returns the last autonumber
  --created in the table listed--it only works with autonumbers
  --(identities)
  Insert into Donation([DonationDate],[DonationAmount],[PersonKey])
  Values(GetDate(), 5000.00,ident_Current('Person'))

  Select * from Person

  Select * From Donation

  --update changes existing data
  --it is crucial to have a where clause
  Update Person
  set PersonFirstname = 'Jason'
  where PersonKey=1

  --transition manually creates a transistion
  --this allows you the possiblity of a undo (Rollback)
  Begin tran

  update Person
  Set PersonLastName='Smith'

  --undo the above transaction
  rollback tran
  --or write it
  commit tran

  --won't work because person 3 has related records 
  --in other tables
  Delete from Person where personkey=3

  Begin tran

  --this however will delete eveything in donation
  Delete from Donation