Sub Queries

Here are the samples for subqueries:

/************************************
This is the beginning of Subqueries
*************************************/

use MagazineSubscription

--simple subquery to see which magazine
--has the highest price
Select MagID, subscriptionPrice
From MagazineDetail
Where subscriptionPrice=
(Select Max(subscriptionPrice)
From magazineDetail)

--inner join and sub query
Select MagName, subscriptionPrice
From Magazine m
Inner Join MagazineDetail md
On m.MagID=md.Magid
Where subscriptionPrice=
(Select Max(subscriptionPrice)
From magazineDetail)

--which magazines are greater than average in price
Select MagID, subscriptionprice
From MagazineDetail
Where SubscriptionPrice >
(Select Avg(SubscriptionPrice)
From MagazineDetail)

--sub query in the select
Select MagID, subscriptionprice,
(Select Avg(subscriptionPrice) From magazineDetail) as Average
From MagazineDetail
Where SubscriptionPrice >
(Select Avg(SubscriptionPrice)
From MagazineDetail)

Select MagID, subscriptionprice,
(Select Avg(subscriptionPrice) From magazineDetail) as Average
From MagazineDetail
Where SubscriptionPrice <
(Select Avg(SubscriptionPrice)
From MagazineDetail)


--correlated subqueries
--see exists

--using in
Select 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

--all means to use the comparitive to all values
--in the subquery; the query below lists only
--those subscriptionprices that are greater than
--or equal to all other values. This is
--equivalent to getting the max value
Select MagDetID, subscriptionPrice
from MagazineDetail
Where SubscriptionPrice >= All
(Select subscriptionPrice
From MagazineDetail)

--any means that at least one value meets the criteria
--what the query below means is that the subscriptionprice
--listed is greater than or equal to at least one value
--in the sub query (which really applies to every record
--since it is at least equal to itself

Select MagDetID, subscriptionPrice
from MagazineDetail
Where SubscriptionPrice >= Any
(Select subscriptionPrice
From MagazineDetail)

--exists. Notice also that this is a
--correlated query where the inner query
--depends on the outer
Select MagName
From Magazine m
Where exists
(select *
from MagazineDetail md
Where m.magid=md.magid
And subscriptTypeID=3)

--same with not exists
Select MagName
From Magazine m
Where Not exists
(select *
from MagazineDetail md
Where m.magid=md.magid
And subscriptTypeID=3)