Monday, October 23, 2017

SQL Code from Morning Class

Use Community_Assist;

/* first use of SQL
10/23/2017 */

Select PersonLastName, 
PersonFirstName, 
PersonEmail
From Person;

Select * from Person;

--sort
Select PersonLastName, PersonFirstName, PersonEmail
From Person
order by PersonLastName, PersonFirstName

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

Select PersonLastName, PersonFirstName, PersonEmail
From Person
where PersonLastName='Tanner'

--all last names with T
Select PersonLastName, PersonFirstName, PersonEmail
From Person
where PersonLastName like'T%'

Select * from Donation
Where not DonationAmount >= 1000
order by DonationAmount desc

Select * from Donation
where DonationDate = '2015-08-11 20:00:35:890'

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

Select GetDate()

Select DonationAmount as Amount, DonationAmount * .65 as CharityPercent
From Donation
--*, +, -, /
--=, !=, <>, >, <, >=, <=

Select PersonLastname, PersonEmail, DonationAmount
From Person
inner Join Donation
on Person.PersonKey=Donation.Personkey
order by PersonlastName

Select PersonLastname, PersonEmail, Sum(DonationAmount)
From Person
inner Join Donation
on Person.PersonKey=Donation.Personkey
Group by PersonEmail, PersonLastName
order by PersonlastName

Select * from GrantRequest

Select PersonLastname, PersonFirstName, GrantRequestDate,
GrantTypeName, GrantRequestAmount
From Person
Inner Join GrantRequest
on Person.PersonKey=GrantRequest.PersonKey
Inner join GrantType
on GrantType.GrantTypeKey=GrantRequest.GrantTypeKey

Insert into Person(PersonLastName, PersonFirstName, 
PersonEmail, PersonEntryDate)
Values('Richards','Kieth','kr@gmail.com',GetDate())

Insert into PersonAddress(PersonAddressStreet, 
 PersonAddressZip, PersonKey)
Values('1000 Somewhere blvd', '98122',IDENT_CURRENT('Person'))

Insert into Donation(PersonKey, DonationDate, DonationAmount)
Values(IDENT_CURRENT('Person'),getDate(),5000.00)

Select * from Person
Select * from PersonAddress
Select * from Donation

No comments:

Post a Comment