記得多穿一件衣服

LAB 09 Exercise 1 --------------------------------------------------------------------- -- Task 2 --  -- Write a SELECT statement that will return groups of customers that made a purchase. -- The SELECT clause should include the custid column from the Sales.Orders table  -- and the contactname column from the Sales.Customers table.  -- Group by both columns and filter only the orders from the sales employee  -- whose empid equals five. --------------------------------------------------------------------- 寫法1 select c.custid,c.contactname from Sales.Customers c inner join Sales.Orders o on c.custid = o.custid where o.empid = 5 group by c.custid,c.contactname 寫法2 select distinct(o.custid), c.contactname,o.empid from Sales.Orders o inner join Sales.Customers c on o.custid = c.custid where o.empid = 5 order by o.custid asc --------------------------------------------------------------------- -- Task 3 --  -- Copy the T-SQL statement ...

=== IIF ( boolean_expression, true_value, false_value ) 範例： select productid,unitprice, iif(unitprice>50 ,'high','low') as pricepoint from Production.Products === PARSE、TRY_PARSE -- 繁體中文，日期的順序是：ymd  SELECT PARSE('01/02/03' AS  datetime2 USING 'zh-TW') N'日期' GO  -- 英文 us_english，日期的順序是：mdy SELECT PARSE('01/02/03' AS  datetime2 USING 'en-US') N'日期' GO == CHOOSE(index,condition 1,condition 2...) select CHOOSE(3,'Japan','China','Taiwan') -- result  'Taiwan'  == CAST CONVERT 區別 CONVERT是專對SQL Server使用的，使日期與時間值，小數之間轉換具有更寬的靈活性。 CAST是兩種功能中更具ANSI標準的功能，即雖然更具便攜性（比如，使用CAST的函數能更容易的被其它資料庫軟體使用），但功能相對弱一些。不過，當小數轉化為數值，並保留原始運算式中的小數數值時，仍然需要使用CAST == NULLIF COALESCE 區別 NULLIF是專對SQL Server使用的，盡可能使用規範中所支援的標準函數COALESCE

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,因為有金額欄位接成字串需要做轉換 --------------------------------------------...