/*use TSQLV4 DataBase

Question 1

Using the dbo.Digits table with itself to create the units, tens and hundreds table alias CalculatedNumber

select Hundreds.digit as Hundreds

, Tens.digit as Tens

, Units.digit as Units

, concat(Hundreds.digit, Tens.digit, Units.digit) as CalculatedNumber

from dbo.digits as Hundreds

cross join dbo.digits as Tens

cross join dbo.digits as Units

Generate the predicate of two sets of numbers From 000 to 999 in ascending order through a cartesian product relatve to the hundreds digit

CalculatedNumber Tens digit Units digit

Set 1 Hundreds equals Tens + 2 Hundreds equals Units + 3

Set 2 Hundreds equals Tens - 2 Hundreds equals Units - 3

/*

Question 2

Using the dbo.Nums table derive the calander year (January 15-2015 through December 15-2015) months.

Make sure the result set has only 12 rows

Question 3

Use the Sales.OrderValues view

Create a column alias has a space for the name "Order Number".

The case statement where the conditions are the following based upon the column val:

Condition then Label

======================= ==========================

mod(orederid,10) greater then 7 "PO"+orderid

mod(orederid,10) less then 2 "DISC"+orderid

mod(orederid,2) equal 1 "SO"+orderid

mod(orederid,2) equal 0 "OMPA"+orderid

Create a column alias has a space for the name "Price Category".

The case statement where the conditions are the following based upon the column val:

Condition then Label

======================= ==========================

Less than 1000 'Low Priced'

Between 1000 and 3000 'Average Price'

More than 3000 'High End Price'

otherwise 'Missing Value'

Hint:

select orderid

, [Order Number] = case

-- Build the case statement

end

, custid

, val

, [Price Category] = case

-- Build the case statement

end

from Sales.OrderValues

Make sure the result set has only 830 rows