TSQL Lab 08 習題

LAB 08

Exercise 1


---------------------------------------------------------------------
-- Task 1
-- 
-- Write a SELECT statement against the Production.Products table to retrieve a calculated column named productdesc. The calculated column should be based on the columns productname and unitprice and look like this:
--  The unit price for the Product HHYDP is 18.00 $.
--
-- Execute the written statement and compare the results that you got with the desired results shown in the file 52 - Lab Exercise 1 - Task 1 Result.txt. 
--
-- Did you use the CAST or the CONVERT function? Which one do you think is more appropriate to use?
---------------------------------------------------------------------

select productname,'The unit price for the Product ' + SUBSTRING(productname,9,LEN(productname)) + ' is ' + convert(varchar(10),unitprice) + ' $.' as productdesc
from Production.Products


YES,因為有金額欄位接成字串需要做轉換

---------------------------------------------------------------------
-- Task 2
-- 
-- The US marketing department has supplied you with a start date 4/1/2007 (using US English form, read as April 1, 2007) and an end date 11/30/2007 (using US English form, read as November 30, 2007). Write a SELECT statement against the Sales.Orders table to retrieve the orderid, orderdate, shippeddate, and shipregion columns. Filter the result to include only rows with the order date between the specified start date and end date and have more than 30 days between the shipped date and order date. Also check the shipregion column for missing values. If there is a missing value, then return the value 'No region'
--
-- In this SELECT statement, you can use the CONVERT function with a style parameter or the new PARSE function.
--
-- Execute the written statement and compare the results that you got with the desired results shown in the file 53 - Lab Exercise 1 - Task 2 Result.txt. 
---------------------------------------------------------------------

select orderid, orderdate, shippeddate, ISNULL(shipregion,'No region') as shipregion
--,DATEDIFF(DAY,orderdate,shippeddate) howlong
from Sales.Orders
where orderdate between PARSE('4/1/2007' as datetime using 'en-US')
and PARSE('11/30/2007' as datetime using 'en-US')

and DATEDIFF(DAY,orderdate,shippeddate) > 30

---------------------------------------------------------------------
-- Task 3
-- 
-- The IT department would like to convert all the information about phone numbers in the Sales.Customers table to integer values. The IT staff indicated that all hyphens, parentheses, and spaces have to be removed before the conversion to an integer data type. 
--
-- Write a SELECT statement to implement the requirement of the IT department. Replace all the specified characters in the phone column of the Sales.Customers table and then convert the column from the nvarchar datatype to the int datatype. The T-SQL statement must not fail if there is a conversion error, but rather it should return a NULL. (Hint: First try writing a T-SQL statement using the CONVERT function and then use the new functionality in SQL Server 2012). Use the alias phoneasint for this calculated column.
--
-- Execute the written statement and compare the results that you got with the desired results shown in the file 54 - Lab Exercise 3 - Task 3 Result.txt. 
---------------------------------------------------------------------

select phone,TRY_CONVERT(int,REPLACE(REPLACE(REPLACE(REPLACE(phone,'-',''),'(',''),')',''),' ','')) phoneasint
from Sales.Customers

--int 範圍　2147483648(-2^31)~2147483647(2^31-1)



Exercise 2


---------------------------------------------------------------------
-- Task 1
-- 
-- Write a SELECT statement against the Sales.Customers table and retrieve the custid and 
--contactname columns. Add a calculated column named segmentgroup using a logical function IIF 
--with the value Target group for customers that are from Mexico and have in the contact title 
--the value Owner Use the value Other for the rest of the customers. 
--
---------------------------------------------------------------------

select custid,contactname,contacttitle,country,
IIF(country = N'Mexico' and contacttitle = N'Owner','Target Group','Other') as segmentgroup

from Sales.Customers

---------------------------------------------------------------------
-- Task 2
-- 
-- Modify the T-SQL statement from task 1 to change the calculated column 
--to show the value Target group for all customers without a missing value 
--in the region attribute or with the value Owner in the contact title attribute.
---------------------------------------------------------------------

select custid,contactname,contacttitle,region,
IIF(region is not null or contacttitle = N'Owner','Target Group','Other') as segmentgroup

from Sales.Customers

---------------------------------------------------------------------
-- Task 3
-- 
-- Write a SELECT statement against the Sales.Customers table and 
--retrieve the custid and contactname columns. 
--Add a calculated column named segmentgroup using the logic function CHOOSE 
--with four possible descriptions (Group One  Group Two Group Three Group Four. )
--Use the modulo operator on the column custid. 
--(Use the expression custid % 4 + 1 to determine the target group.)
--
---------------------------------------------------------------------


select custid,contactname,
CHOOSE(custid % 4+1 ,'Group One','Group Two','Group Three','Group Four') as segmentgroup
from Sales.Customers

order by segmentgroup asc



Exercise 3

---------------------------------------------------------------------
-- Task 1
-- 
-- Write a SELECT statement to retrieve the contactname and fax columns from the Sales.Customers table.
-- If there is a missing value in the fax column, return the value No information
--
-- Write two solutions, one using the COALESCE function and the other using the ISNULL function.
--
-- What is the difference between the ISNULL and COALESCE functions?
---------------------------------------------------------------------

select contactname,ISNULL(fax,'No information') as fax
from Sales.Customers

select contactname,COALESCE(fax,'No information') as fax
from Sales.Customers

ISNULL 判斷欄位值是否為NULL，只能判斷一欄
COALESCE 回傳第一個非NULL的值，可以帶入多個欄位判斷(範例詳見自我研讀(二))

---------------------------------------------------------------------
-- Task 2
-- 
-- Update the provided T-SQL statement with a WHERE clause to filter the region column 
-- using the provided variable @region, which can have a value or a NULL. 
-- Test the solution using both provided variable declaration cases.
---------------------------------------------------------------------

DECLARE @region AS NVARCHAR(30) = NULL;

SELECT
custid, region
FROM Sales.Customers
WHERE region = @region OR (region IS NULL AND @region IS NULL);



DECLARE @region AS NVARCHAR(30) = N'WA';

SELECT
custid, region
FROM Sales.Customers
WHERE region = @region OR (region IS NULL AND @region IS NULL);


---------------------------------------------------------------------
-- Task 3
-- 
-- Write a SELECT statement to retrieve the contactname, city, and region columns 
-- from the Sales.Customers table. 
-- Return only rows that do not have two characters in the region column, 
-- including those with an inapplicable region (where the region is NULL).
--------------------------------------------------------------------

select custid,contactname,city,region
from Sales.Customers
where LEN(region)<>2 or region is NULL