SQL Afternoon

use Community_Assist
Select * from person;
Select PersonLastname, Personfirstname,
  personemail from person
Select PersonLastname, Personfirstname,
  personemail from person
  order by personlastname

Select PersonLastname, Personfirstname,
  personemail from person
  order by personlastname desc

--Where clause, select among the rows
Select PersonLastname, Personfirstname,
  personemail from person
  Where personlastname='Smith'

Select PersonLastname, Personfirstname,
  personemail from person
  Where personlastname='Smith' 
  and Personfirstname ='Jerry'

Select * from Personaddress
Where not PersonAddressCity='Seattle'

Select * from personaddress
Where not PersonAddressApt is null

Select * from Donation 
where donationamount >= 1000

Select * From donation
Where donationdate='8/9/2015'

Select * From donation
Where donationdate 
between'8/9/2015' and '8/10/2015'
--join bringing tables together
Select * from GrantRequest

select grantrequestkey, grantrequestdate,
  Personlastname, personfirstname,
  granttypekey, grantrequestamount
  From Grantrequest
  inner join Person
  on person.personkey=grantrequest.personkey

  Select * 
  From Grantrequest
  inner join Person
  on person.personkey=grantrequest.personkey

  select grantrequestkey, grantrequestdate,
  Personlastname, personfirstname,
  granttypename, grantrequestamount
  From Grantrequest
  inner join Person
  on person.personkey=grantrequest.personkey
  inner join granttype
  On GrantType.GrantTypeKey=GrantRequest.GrantTypeKey

  --insert-- updates-- deletes
  --person, personaddress, donation
  Insert into person(PersonLastName, 
  PersonFirstName, 
  PersonEmail, 
  PersonEntryDate)
  Values('Smith','Fred','fsmith@fedex.com',
  GetDate())
  Select * from person

  Insert into PersonAddress
  (PersonAddressApt, 
  PersonAddressStreet, PersonAddressCity, 
  PersonAddressState, PersonAddressZip, 
  PersonKey)
  Values(null, '10001 Nowhere ave','Seattle',
  'WA', '9800', 130)

  Select * from Personaddress

  Insert into donation(Donationdate, 
  Donationamount, personkey)
  Values(getdate(), 1000.00, 130)

  Select * from donation

--update
Begin tran
Update person
Set personfirstname='Jason',
Personemail='Jason.Anderson@gmail.com'
Where personkey=1
Commit Tran

Select * from Person
Rollback tran

Delete from Person where personkey=130

Begin tran

Delete from donation

Select * from Donation

Rollback tran