Scalar Functions

Use MagazineSubscription;

--concatenate
Select CustLastName + ', ' + CustFirstName As [Name],
CustPhone
From Customer

--substring
Select magname, SUBSTRING(magname,1,5) As "First 5"
From
Magazine

Select * from Customer

Select CustPhone, SUBSTRING(Custphone,1,3) + '.'
+ SUBSTRING(custphone,4,3)
+ '.' + SUBSTRING(CustPhone,7,4) as Phone
From Customer

Select UPPER(CustLastName) From Customer
Select LOWER(custLastName) + '@' + 'gmail.com' as Email
From Customer

Select Substring(magname, 1,CHARINDEX(' ',magname, 1)) From Magazine

Select * From Subscription
Select YEAR(SubscriptionStart) as [Year] from Subscription
Select Month(SubscriptionStart) as [Month] from Subscription
Select Day (SubscriptionStart) as [Day] from Subscription
Select DATEPART(M, SubscriptionStart) as [month] from subscription
Select DATEPART(D, SubscriptionStart) as [Day] from subscription
Select DATEPART(YYYY, SubscriptionStart) as [Year] from subscription
Select DATEDIFF(YYYY,SubscriptionStart, subscriptionEnd) as [subscription length] from subscription
Select SubscriptionStart, DATEADD(M,6,SubscriptionStart)
as [End Date] From Subscription

Select CAST(SubscriptionStart as Varchar(11)) As "Start Date"
From Subscription

Select magID, SubscriptionPrice from MagazineDetail

Select magId, '$' + CAST(SubscriptionPrice as Varchar(7))
From MagazineDetail

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 * from customer

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

use CommunityAssist

Select * From PersonContact

Declare @Employee4 DateTime
Declare @Employee1 Datetime
Select @Employee1=hireDate from Employee where EmployeeKey=1
Select @Employee4=hireDate from Employee where EmployeeKey=4
Select DATEDIFF(M,@Employee1,@Employee4) As [Months Difference]

Select * From employee
Select DATEDIFF(M,'2001-02-21' ,'2003-03-10' )