SUbQueries

--Subqueries

Use Community_Assist
Select Personkey, DonationDate, max(DonationAmount) from Donation
Group by PersonKey, donationDate

Select PersonKey, donationDate, DonationAmount
From Donation
Where DonationAmount=(Select max(donationAmount) from donation)

Select PersonLastName, PersonFirstName, donationDate, DonationAmount
From person
inner join Donation
on person.personkey=donation.personkey
Where donationAmount=(Select max(donationAmount) from donation)

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

Select GrantTypeName from GrantType
Where grantTypeKey in (Select grantTypeKey from GrantRequest)


Select PersonFirstName, PersonLastName
From person
Where PersonKey in
        (Select personkey from Employee where employeekey in
      (Select EmployeeKey from GrantReviewcomment))


Select GrantTypeKey, Sum(GrantRequestAmount) as Total, 
      (select sum(GrantRequestAmount) from GrantRequest) as GrandTotal,
   (sum(GrantRequestAmount) / 
(Select sum(GrantRequestAmount) from GrantRequest)) *100 
   as [Percent]
From GrantRequest
Group by GrantTypeKey

Select GrantTypeName, Format(Sum(GrantRequestAmount), '$#,##0.00') as Total, 
      Format((select sum(GrantRequestAmount) from GrantRequest),'$#,##0.00') 
   as GrandTotal,
   Format((sum(GrantRequestAmount) / 
   (Select sum(GrantRequestAmount) from GrantRequest)), '##.00 %')
   as [Percent]
From GrantType
inner Join GrantRequest
on GrantType.GrantTypeKey = GrantRequest.GrantTypeKey
Group by GrantTypeName

--Correlated subquery
--Requests that are greater than the average request
--Want to compare rent to rent, food to food etc.
--like to like

Select GrantTypeKey, GrantRequestAmount,GrantRequestDate, 
PersonKey, GrantRequestExplanation
From GrantRequest gr1
Where GrantRequestAmount > 
(Select avg(GrantRequestAmount) From GrantRequest gr2
   Where gr1.GrantTypeKey=gr2.GrantTypeKey)

   Select Avg(GrantRequestAmount) from GrantRequest where GrantTypeKey=2

   Create table PersonT
   (
  LastName nvarchar(255),
  FirstName nvarchar(255),
  Email nvarchar(255)
   )

   Insert into PersonT(LastName, FirstName, Email)
   Select PersonLastName, PersonFirstName, PersonEmail
      From Person where PersonLastName like 'T%'

 -- fully qualified name = [server name].[Database name].[Schema].[Table name]

  Insert into PersonT(LastName, FirstName, Email)
  Select EmployeeLastName, EmployeeFirstName, EmployeeEmail
  From MetroAlt.dbo.Employee where EmployeeLastName like 'T%'

   Select * from PersonT