/************************************
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)

			
/**********************************
Views
***********************************/
--here is a view that hides a complex
--set of inner joins
Create view vw_SubscriptionSummary
AS
Select CustLastName as "Last Name", 
	custfirstName as "First name", 
	Magname as "Magazine", 
	SubscriptionStart as "Start Date",
	SubscriptionEnd as "End Date",
	SubscriptionPrice as "Price"
	From Customer c
	Inner Join Subscription s
	On c.CustID=s.CustID
	Inner Join MagazineDetail md
	On md.magdetID=s.MagdetID
	Inner Join Magazine m
	On m.MagID=md.MagID

--now select from the view
Select * from vw_SubscriptionSummary

--you can't order by in a view but you can
--when selecting from a view. 
--Notice you have to use the aliases from
--the view as the column names
Select "Last Name", Magazine
From vw_SubscriptionSummary
Where "Last Name"='Camlin'
Order by "Last Name"

--Change the view to add some formatting

Alter view vw_SubscriptionSummary
AS
Select CustLastName as "Last Name", 
	custfirstName as "First name", 
	Magname as "Magazine", 
	substring(cast(SubscriptionStart as char),1,11) as "Start Date",
	Substring(cast(SubscriptionEnd as char),1,11) as "End Date",
	'$' + cast(SubscriptionPrice as char(5)) as "Price"
	From Customer c
	Inner Join Subscription s
	On c.CustID=s.CustID
	Inner Join MagazineDetail md
	On md.magdetID=s.MagdetID
	Inner Join Magazine m
	On m.MagID=md.MagID


	Select * from vw_SubscriptionSummary