SQL 1_morning

use Community_Assist;
--These are basic select statements
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 clauses let you select rows
Select personLastName, personfirstname, personemail
From person
--character types are enclosed in single quotes
Where personlastname='Smith' and personfirstname='Jerry'
Select * from PersonAddress

Select * from PersonAddress 
where PersonAddressCity='Kent'

Select * from PersonAddress 
where Not PersonAddressCity='Seattle'

--returns nothing because time
--dates are single quoted
Select * from Donation 
Where DonationDate='8/9/2015'

Select * from Donation 
Where DonationDate between'8/9/2015' And '8/10/2015'

--numbers are not quoted
Select * from Donation 
Where donationamount > 1000

--null cannot use = > < 
Select * from personaddress
Where not PersonAddressApt is null

Select * from personaddress
Where  PersonAddressApt is null

--join tables: two tables
Select PersonlastName, personfirstname,
       Donationkey, Donationdate, Donationamount
From Person
Inner join Donation
ON person.personkey=Donation.personkey

Select * From Grantrequest

--three table join
Select GrantRequestDate, person.Personkey, PErsonlastname,
Personfirstname, Granttypename, grantrequestamount
From Grantrequest
inner join person 
On person.PersonKey=GrantRequest.personkey
inner join granttype
on granttype.GrantTypeKey=GrantRequest.GrantTypeKey

--insert update delete

--person, personaddress, donation
insert into person(personlastname, personfirstname,
PersonEmail,personentrydate)
Values('Tayor','Michael','mtaylor@gmail.com',
getdate())

Select * from PersonAddress

Insert into personaddress( 
PersonAddressApt, 
PersonAddressStreet, PersonAddressCity, 
PersonAddressState, PersonAddressZip, 
PersonKey)
Values(null,'1001 Somewhere else','Seattle',
'Wa','98001',130)

Insert into Donation(DonationDate, DonationAmount, personkey)
Values(getdate(), 250.00,130)

Select * from donation
--!!! update  updates are dangerous. If no where clause will change whole table!!!
begin tran--allows you to control the transactio
Update Person
Set personfirstname='Jason',
personemail='jason.anderson@gmail.com'
Where personkey=1
--you can only do one of the following
Rollback tran--the only way to undo
Commit tran--write the changes

--won't delete because person 130 has foreign keys in addres and donation
Delete from Person where personkey=130

Select * from person

begin tran
Delete from donation

--in a child table will delete everything
Select * from Donation

Rollback tran