SubQueries

Use Community_Assist

Select Max(DonationAmount) [Max]
From Donation

-- simple subquery in where clause
Select PersonFirstName, PersonLastName, DonationAmount
from person 
inner join Donation 
on person.PersonKey=donation.PersonKey
Where DonationAmount = (Select Max(donationAmount) from Donation)

Select PersonAddressStreet, PersonAddressCity, 
PersonKey, PersonAddressZip
From PersonAddress
where PersonAddressCity in ('Bellevue', 'Kent', 'Shoreline')

Select PersonLastName, PersonfirstName, PersonEmail
From person
Where Personkey in (Select personkey from Employee)

Select PersonLastName, PersonfirstName, PersonEmail
From person
Where Personkey in 
(Select personkey from Employee
where EmployeeKey in 
(Select EmployeeKey from GrantReview))

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

--sub queries in the select
Select GrantTypeName, 
Sum(GrantRequestAmount) Total,
(Select sum(GrantRequestAmount) From GrantRequest) GrantTotal,
(Sum(GrantRequestAmount) / 
    (Select Sum(GrantRequestAmount) from GrantRequest)) * 100  [Percent]
From GrantType
inner join GrantRequest
on GrantType.GrantTypeKey=GrantRequest.GrantTypeKey
Group by GrantTypeName

--Coordinated subqueries are queries in which the subquery 
--references the outer query. The subquery matches
--the granttypekey from gr1, the outer query with the granttypeKey
--from gr2 inside the subquery. The effect is to make sure
--that like is matched with like. GrantTypeKey 1 (Food) is matched 
--only against other granttypeKey 1 values, GrantTypeKey 2 (Rent)
--is matched only against other grantTypeKey 2 values, etc.
--a coordinated subquery is the SQL equivalent of a recursive
--function in other programming languages, and like them  
-- requires a lot of processor time 


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

Select GrantTypeKey, avg(GrantRequestAmount) Average from GrantRequest
Group by GrantTypeKey

use Community_Assist
Create table PersonZ
(
  PersonKey int primary key,
  Lastname nvarchar(255),
  FirstName nvarchar(255),
  Email nvarchar(255)
)

Insert into PersonZ(PersonKey, Lastname, FirstName, Email)
Select [PersonKey],[PersonLastName],[PersonFirstName],[PersonEmail]
From Person
Where PersonLastName Like 'Z%'

Select * From Personz