/*********************************
this script provides examples
for the chapter on operators 
and functions. It uses the 
MagazineSubscription Database
*********************************/

Use MagazineSubscription

--operators and calculations
Select 3 * 4 + 12 / 3 as "Simple Order"
Select ((3 * 4) + 12) / 3 as "Simple Order"

--If shipping is 5% of the price of a magazine,
--what is the shipping cost of each magazine

Select MagID, SubscriptionPrice, SubscriptionPrice * .05 as Shipping
From MagazineDetail

/******************************************
String functions
*******************************************/
--Concatinating strings
Select CustLastName + ', ' + custFirstName "Name",
CustAddress + ', ' + CustCity + ', ' + CustZipcode "Address"
From Customer

--Substring get the distinct area codes from the customer table
Select Distinct substring(CustPhone, 0,4) as Prefix
From Customer

--Return the length of customer addresses 
--Notice that you can sort by the Alias

Select Len(CustAddress) "Length"
From Customer
Order by Length DESC

--Throw all lastnames to Upper Case 
--Lower is identical in syntax
Select Upper(CustLastName) As "Last Name"
from Customer

--Cast from one type to another
--Multiply each zip code by 100(no good reason)
Select Cast(custZipcode as Int) * 100 "Silliness"
From customer

--use charindex to locate a space and substring to return
--all the characters up to that space
Select substring(MagName,0,Charindex(' ',Magname)) as "First Word"
From Magazine

/*Here is a really complex set of functions
that uses Concatination, cast and substring to produce
a more formatted output */
Select MagID "Magazine", '$' + Cast(SubscriptionPrice As Char(6)) "Price", 
'$' + substring(Cast(SubscriptionPrice * .05 as char),0,5) "Shipping"
From MagazineDetail

/****************************************
Date Time functions
****************************************/
--Get the current date and time

Select GetDate() "Now"
Select Current_TimeStamp "Now"

--Select Parts
Select DatePart("yyyy",Current_TimeStamp)
Select DatePart("m", Current_timeStamp)
Select DatePart("hh", Current_timestamp)
Select DatePart("mm", GetDate())
Select Year(GetDate())
Select Month(GetDate())
Select Day(SubscriptionStart)
From Subscription


--DateDiff
Select SubscriptionStart, SubscriptionEnd, 
dateDiff("m",SubscriptionStart,SubscriptionEnd) as "Length in Months"
From Subscription

--dateAdd plus some casting and substrings
--notice what happens when you cast a date to char
Select Substring(Cast(SubscriptionEnd as char),0,12)"Subscription End",
Substring(Cast(DateAdd("m",3,SubscriptionEnd) as char),0,12)
AS "Three month bonus"
From Subscription

/*******************************************
Case structures
******************************************/

Select Magname, MagType,
Case MagType
	When 'Monthly'
		Then 'Six Months'
	When 'Quarterly'
		Then 'One Year'
	When 'Weekly'
		Then 'Three months'
	Else 'Unknown'
End
As "Minimum Subscription"
From Magazine
Order by MagName
		
--Nulls, Coalesce
--First insert a customer with no phone
Insert into Customer (custLastName, CustfirstName, CustAddress, 
CustCity,CustState,Custzipcode)
Values('Ansi', 'Norm','1000 Coop Blvd','Olympia','WA','98320')

Select CustLastName, custcity, Coalesce(custPhone, 'Unknown')"Phone"
From Customer
Where custcity ='Olympia'