SubQueries

--subqueries
Use CommunityAssist
Select PersonKey, max(DonationAmount) From Donation
Group by Personkey

--which donor gave the largest donor
Select Lastname, Firstname, DonationAmount
From Donation d
inner Join Person p
On p.personKey=d.Personkey
Where donationAmount=(Select Max(DonationAmount)from Donation)

--a sub query in the select clause--any subquery
--in the select clause can only return one value
Select Lastname, Firstname, DonationAmount,
(Select avg(donationAmount) from Donation) as Average
From Donation d
inner Join Person p
On p.personKey=d.Personkey
Where donationAmount>(Select avg(DonationAmount)from Donation)

--the in keyword, the value must be "in" the result set
Select LastName, Firstname
From Person
Where Personkey in (Select Personkey from donation)

Select * From PersonAddress
Where City in ('Bellevue', 'kent', 'shoreline')

--return the names of the employees
--who have worked on service Grants
Select Lastname, Firstname
From Person
Where PersonKey in
(Select PersonKey from Employee 
 where EmployeeKey in
 (Select EmployeeKey from ServiceGrant))

--same as left outer join
Select ServiceName from Service
Where ServiceKey not in
(Select ServiceKey from ServiceGrant)

--the join version
Select ServiceName, ServiceGrant.ServiceKey
From Service
Left outer Join ServiceGrant
on Service.ServiceKey=ServiceGrant.ServiceKey
Where ServiceGrant.ServiceKey is null

--this one uses sub queries to get the average,
--see how much the donors donation is above the average,
--get the total of all donations (sum) and thenn
--calculate what percent the donor's donation is
--of the total
--all for those donors whose donations are above
--the average donation
Select Lastname, Firstname, DonationAmount,
(Select avg(donationAmount) from Donation) as Average,
DonationAmount-(Select avg(donationAmount) from Donation) 
as [Difference],
(Select Sum(DonationAmount) From Donation) as Total,
(DonationAmount / (Select Sum(DonationAmount) From Donation)) * 100
As Percentage
From Donation d
inner Join Person p
On p.personKey=d.Personkey
Where DonationAmount > (Select avg(DonationAmount) from Donation)

--any, all
--any means the value is greater (or less then) any other donation
--in the list (excludes the min)
--all means it is greater than or equal to all the other donations.
--results in a max

Select DonationAmount from donation
where donationAmount > any
(Select DonationAmount from Donation)

Select DonationAmount from donation
where donationAmount >= all
(Select DonationAmount from Donation)

---correlated subquery
--A correlated subquery is a sub query where
--the subquery in the critera refers to a value
--in the outer query. This has the effect of 
--creating a recursive query. In our example
--the subquery matches the subscripttypeID in the 
--main query. This makes it so likes are matched to
--like, three year subscriptions are only compared
--to other three year subscriptions, one year subscriptions
--to other one year subscriptions etc.

use MagazineSubscription

Select SubscriptTypeID, MagDetID, SubscriptionPrice
From MagazineDetail md
Where SubscriptionPRice >=
(Select Avg(subscriptionPrice) From magazineDetail md2
where md.SubscriptTypeID=md2.SubscriptTypeID)

Select Avg(SubscriptionPrice) From MagazineDetail
Where SubscriptTypeID=5

Select * from SubscriptionType
Select * From MagazineDetail



Select