Tuesday, January 30, 2018

First SQL

--Select, Join, inserts
--fourth generation language
--2nd generation assembly
--3rd generation Cobol C C++ C# Python procedural print
--fourth generation how, what to do

use Community_Assist

Select * from Person

Select PersonFirstName, 
PersonLastName, 
PersonEmail
From Person
Order by PersonLastName;

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

Select * from PersonAddress
Where PersonAddressCity='Kent'

Select * from PersonAddress
Where PersonAddressCity='Seattle'

Select * from PersonAddress
Where Not PersonAddressCity='Seattle'

Select * from PersonAddress
Where PersonAddressApt is Not null

Select * from Donation 
where DonationDate between'8/10/2015' and '8/11/2015'

Select Count(DonationKey) as [Count] from Donation
Where DonationDate between'8/10/2015' and '8/11/2015'

Select sum(DonationAmount) as Total from Donation
Where DonationDate between'8/10/2015' and '8/11/2015'

Select * from Donation

Select PersonFirstName, PersonLastName,
PersonEmail, DonationDate, DonationAmount
From Person
inner join Donation
On Person.PersonKey=Donation.PersonKey

Select * from GrantRequest

Select GrantRequestDate, PersonLastName, 
GrantTypeName, GrantRequestAmount
From GrantRequest
inner join Person
on Person.PersonKey=GrantRequest.PersonKey
inner join GrantType
on GrantType.GrantTypeKey=GrantRequest.GrantTypeKey

Insert into Person(PersonLastName, 
PersonFirstName, PersonEmail,  
PersonEntryDate)
Values('Phalan','Declan','pd@gmail.com',getDate())

Insert into PersonAddress(PersonAddressApt, 
PersonAddressStreet, 
PersonAddressCity, PersonAddressState, 
PersonAddressZip, PersonKey)
Values(null,'1234 South','Marysville','WA', '98100', 
IDENT_CURRENT('Person'))

Select * from PersonAddress

Insert into Donation(PersonKey, DonationDate, 
DonationAmount)
Values(130,getDate(),5.00)

Select * from Position

Insert into Position(PositionName, PositionDescription)
Values('Professor','Teaches economics'),
('HR', 'Human Resources Manager'),
('Advertising', 'Publicize the charity')

No comments:

Post a Comment