Monday, October 23, 2017

SQL Code from Afternoon Class

Use Community_Assist;

Select PersonLastName, PersonFirstName, PersonEmail
From Person;

Select * from Person
Select PersonLastName, PersonFirstName, PersonEmail
From Person
order by PersonLastName 

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

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

Select PersonLastName, PersonFirstName, PersonEmail
From Person
Where PersonLastname='Tanner'
order by PersonLastName desc, PersonFirstName desc

Select * from Donation

Select DonationKey, donationDate, DonationAmount
From Donation
where donationAmount > 1000
--=, <, >, <=, >=, !=, <>, 

Select * from Donation where DonationDate='2015-8-11'

Select * from Donation where DonationDate 
between '2015-8-11' and '2015-8-12'

Select * from Donation where DonationDate not
between '2015-8-11' and '2015-8-12'

Select * from PersonAddress 
where Not PersonAddressCity = 'Seattle'
And PersonAddressApt is null

Select * from PersonAddress 
where Not PersonAddressCity = 'Seattle'
OR PersonAddressApt is null

Select * From Person where PersonLastName like 'T%r'

Select * From Person where PersonEmail like '%msn.com'

--Joins

Select * from Donation

Select PersonLastName, PersonEmail, DonationAmount
From Person
inner join Donation
on Person.PersonKey=Donation.PersonKey
Where PersonLastName='Mann'

Select PersonLastName, PersonEmail, sum(DonationAmount) as Total
From Person
inner join Donation
on Person.PersonKey=Donation.PersonKey
Where PersonLastName='Mann'
Group by PersonLastName, PersonEmail

Select * from GrantRequest
/* 
This is a multiline
comment
*/
Select PersonLastName, PersonEmail, GrantRequestDate,
GrantTypeName, GrantRequestAmount
From Person
inner join GrantRequest
on Person.PersonKey = GrantRequest.PersonKey
inner join GrantType
on GrantType.GrantTypeKey=GrantRequest.GrantTypeKey

Select DonationKey, DonationAmount, 
DonationAmount * .7 as ToCharity, 
DonationAmount * .3 as ToProgram
From Donation
--* mult, + add, - sub, / div, % modulus 

Insert into Person(PersonLastName, PersonFirstName, 
PersonEmail,  PersonEntryDate)
Values('Turner','Tina','tt@gmail.com',GetDate())

Insert into PersonAddress( 
PersonAddressStreet,PersonAddressZip, PersonKey)
Values('1000 Elsewhere blv', '98122', IDENT_CURRENT('Person'))

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

Select * from PersonAddress
Select * from Donation

No comments:

Post a Comment