Subqueries

--subqueries

use MagazineSubscription

--this returns the MagdetId and the Subscription price
--for the MagdetID that has the highest subscription price
--this can only be done with subqueries
--It is important that Subqueries in the SELECT clause and
--subqueries in the where clause that are addressed with comparitive
--operators such as = < > etc must return only a single value
Select MagDetID, subscriptionPrice
From MagazineDetail
Where SubscriptionPrice=(Select MAX(SubscriptionPrice) from MagazineDetail)

--comparing with other than the equal sine
Select MagDetID, subscriptionPrice
From MagazineDetail
Where SubscriptionPrice>(Select Avg(SubscriptionPrice) from MagazineDetail)

--this returns the subscriptionprice, and the average subscriptionprice
--and then subtracts the subscriptionprice from the average subscription price
--to get the difference. This is the kind of thing that can only be done with
--sbuqueries
Select SubscriptionPrice,
(Select AVG(SubscriptionPrice) from MagazineDetail) as Average,
((Select AVG(SubscriptionPrice) from MagazineDetail)-SubscriptionPrice) as [Difference]
From MagazineDetail



--in tests where a value is a member of a set
--here is a simple literal set
Select CustID, CustLastName, custfirstName
From Customer Where CustID in (3, 5, 7, 9)

--here is a cascading set of subqueries
--each of which tests whether the value
--in the where clause is a member of the
--result set returned by the next subquery.
--It is important to make sure you compare
--like values with like: custID with CustID,
--MagDetID, with MagDetID, etc.
Select CustID,CustLastName,CustFirstName
From Customer
Where CustID in
(Select CustID from Subscription
where MagDetID in
(Select MagdetID from MagazineDetail
where SubscriptionPrice =
(Select MIN(SubscriptionPrice) From MagazineDetail)))

--any All Exists
--All compares the chosen value to all the values in the
--subquery. in this case it must be greater than or equal
--to every value in the subquery. the effect is to return
--the maximum value. It is the only value that is bigger than
--or equal to all other values
Select magDetID, subscriptionPrice
From MagazineDetail
Where SubscriptionPrice >=all
(Select SubscriptionPrice from MagazineDetail)

--any compares the chosen value to every value
--in the subquery. In this case if it is bigger than any
--other value it will be included in the results
--the effect is all but the minimum value is included
Select magDetID, subscriptionPrice
From MagazineDetail
Where SubscriptionPrice >any
(Select SubscriptionPrice from MagazineDetail)


--exists returns a boolean. It is true or false
If exists
(Select [Name] from sys.databases
Where name='CommunityAssist')
Begin
Print 'yep it exists'
end

--Correlated sub query, queries where the sub query depends
--on a value in the top query
--this subquery returns those magazine details that are greater than
--or equal to the average for that subscription type
--so all 1s are compared to 1s
--all 2s are compared to 2s, etc

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