Sub Queries

--subqueries
use Community_Assist
Select PersonLastName, PersonFIrstName, EmployeeHiredate, PositionName
from person p
inner join Employee e
on p.PersonKey = e.PersonKey
inner join EmployeePosition ep
on e.EmployeeKey = ep.EmployeeKey
inner join Position po
on po.PositionKey =ep.PositionKey

Select max(DonationAmount) From Donation

Select DonationKey, DonationDate, PersonKey, donationAmount
From Donation
Where DonationAmount = (Select Max(DonationAmount) from Donation)

Select GrantTypeName from GrantType
Where GrantTypeKey not in (Select GrantTypeKey from GrantRequest)

--meaningless
Select GrantTypeName from GrantType
Where GrantTypeKey not in (Select PersonKey from GrantRequest)

Select PersonLastName, PersonFirstname, PersonEmail
From Person
Where PersonKey in (Select Personkey from Employee)

Select PersonLastName, PersonFirstname, PersonEmail
From Person
inner join Employee
on Person.PersonKey=Employee.PersonKey

Select PersonFirstName, PersonLastname
From Person
where personKey in 
(Select PersonKey from Employee
where EmployeeKey in 
(Select employeeKey from GrantReview 
where GrantRequestStatus='Denied'))

Select GrantTypeName,format(Sum(GrantRequestAmount), '$ #,##0.00') as SubTotal,
Format((Select Sum(GrantRequestAmount) from GrantRequest), '$ #,##0.00') as Total,
format(Sum(GrantRequestAmount) / (Select Sum(GrantRequestAmount) from GrantRequest), '#0.00 %') as Percentage
From GrantRequest gr
Inner join GrantType gt
on gr.GrantTypeKey=gt.GrantTypeKey
Group by GrantTypeName

Select GrantTypeKey, Avg(GrantRequestAmount)
From GrantRequest
Group by GrantTypeKey

--correlated subquery
Select GrantTypeKey, GrantRequestAmount
From GrantRequest gr1
Where GrantRequestAmount > 
(Select avg(GrantRequestAmount)
from GrantRequest gr2
Where gr1.GrantTypeKey=gr2.GrantTypeKey)