Scalar(in-line) Functions

Use MagazineSubscription

--concatenate

Select CustLastName + ', ' + CustFirstName AS [Name], CustPhone
From Customer

--Substring (string name, starting position, number of characters)
Select Magname, SUBSTRING(magname, 1, 5) [First Five]
From Magazine

--find the character index of a space
Select CHARINDEX(' ', MagName, 1) from Magazine

--use the character index to return the first full word
Select SUBSTRING(magname, 1, CHARINDEX(' ', Magname, 1)) as [first word]
From Magazine

Select UPPER(magname) as [Upper Case]
From magazine
Select lower(magname) as [lower Case]
From magazine

Select * From Subscription

--date and time functions

Select MONTH(SubscriptionStart) from Subscription
Select DAY (SubscriptionStart) from Subscription
Select YEAR(SubscriptionStart) from Subscription

--datepart does the same as the above but also more
Select DATEPART(M, SubscriptionStart) from Subscription
Select DATEPART(yy, SubscriptionStart) from Subscription
Select DATEPART(d, SubscriptionStart) from Subscription
Select DATEPART(Hour, SubscriptionStart) from Subscription

--get the difference between dates
Select DATEDIFF(mm,'12/1/2006','4/11/2011') as [Difference]

Select DATEDIFF(mm,subscriptionStart, SubscriptionEnd) as [length]
From Subscription

--add two dates
Select SubscriptionStart,DATEADD(mm,12,SubscriptionStart) as [One Year] From subscription

--cast a date as a character type
Select CAST(SubscriptionStart as Varchar(11)) from Subscription
Select * From MagazineDetail
--case a number as a character to format
Select MagDetID, '$' + CAST(SubscriptionPrice as Varchar) as Price
From MagazineDetail

--use a case to assign values to a new column in the result set
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

Select * from Customer

--substitute your value for nulls
--also looks for that substituted value by repeating the function
--in the where clause
Select CustFirstName, CustLastName, Coalesce(CustPhone, 'unknown') as Phone
From Customer
Where Coalesce(CustPhone, 'unknown')='unknown'

Use CommunityAssist
--on way to answer the question in the assignments
Declare @Emp1HireDate as datetime
Select @Emp1HireDate = HireDate from Employee Where EmployeeKey=1
Select DATEDIFF(mm, @Emp1HireDate, hiredate) From Employee where EmployeeKey=4