subqueries

--subqueries
Use CommunityAssist

--can't get who donated max amont
Select max(donationAmount) from Donation
Group by PersonKey

--but with a subquery in the criteria you can
--which donation is equal to the maximum donation
Select lastname, firstname, DonationDate, DonationAmount
From Person p
inner join Donation d
on p.Personkey = d.Personkey
Where DonationAmount = (Select Max(donationamount) From Donation)

--which donation is greater than the average donation
Select lastname, firstname, DonationDate, DonationAmount
From Person p
inner join Donation d
on p.Personkey = d.Personkey
Where DonationAmount >(Select Avg(donationamount) From Donation)

--here we have added the avg donation as a column. You
--can do subqueries in the select
--we also added a calculate column in which the 
--avg donation (as a subquery) is subtracted from the donation
--amount, and lastly the criteria is only returning the amounts
--that are greater than the average donation
Select lastname, firstname, DonationDate, DonationAmount, (Select Avg(donationamount) from Donation) as Average,
DonationAmount - (Select avg(DonationAmount) From Donation) as Difference
From Person p
inner join Donation d
on p.Personkey = d.Personkey
Where DonationAmount >(Select Avg(donationamount) From Donation)

--return only the names of donors
--in allows the criteria to return a set of values
--and matches the values to the field in the where clause
--it is important to match like to like (personkey to personkey)
Select FirstName, LastName from Person
Where Personkey in (Select personkey from Donation)

--in using literal values
Select * from PersonAddress
Where city in ('Bellevue', 'Kent', 'Shoreline')

--in with a subquery that returns identical results
Select * from PersonAddress
Where city in (Select city from PersonAddress where not city = 'Seattle')

--which employees have worked on service grant
Select LastName, firstname From Person
Where personkey in
  (Select PersonKey from Employee 
  where employeekey in 
   (Select EmployeeKey from ServiceGrant))

--we added an employee so that there would be one that
--did not work on grants
Insert into Person(lastname, firstname)
Values('Vader','Darth')


Insert into Employee(HireDate, SSNumber, Dependents, PersonKey)
values(GetDate(),'999999999',null,(Select max(personkey) from Person))

--return any employees who never worked on a grant
Select LastName, firstname From Person
Where personkey in
  (Select PersonKey from Employee 
  where employeekey not in 
   (Select EmployeeKey from ServiceGrant))

--same as the outer join we did last time
Select ServiceName from Service
 Where ServiceKey not in (Select ServiceKey from ServiceGrant)

--means that we will return all donation amounts
--that are greater than any other donation amount in the list.
--so the only one that is not returned is the smallest donation
--because it is not greater than "any" other donation
 Select DonationAmount from Donation
 where DonationAmount > any (Select DonationAmount from Donation)

--for all it must be larger that all the other values in the set
-->= returns the maximum donation
 Select DonationAmount from Donation
 where DonationAmount >= all (Select DonationAmount from Donation)

--correlated subquery
use MagazineSubscription

Select avg(subscriptionPrice) from magazineDetail
 where SubscriptTypeID=5

 --a correlated subquery is when the subquery uses a value in the 
 --outer query as part of its criteria
 --it results in something resembling a recursive function
 --in this case what it does is makes sure that
 --like is compared to like
 --subscription type 1 (one year) is compared only to other
 --subscription type 1's and subscription type 5 (five year) 
 --is compared only to other subscription type 5's etc.
Select subscriptTypeID, MagDetID, SubscriptionPrice
From MagazineDetail md
Where subscriptionPrice >=
(Select Avg(SubscriptionPrice) from magazineDetail md2
 where md.SubscriptTypeID=md2.SubscriptTypeID)