五餅二魚工作室

Work with Data (27%)

• Query data by using SELECT statements.

  • May include but not limited to: use the ranking function to select top(X) rows for multiple categories in a single query; write and perform queries efficiently using the new (SQL 2005/8->) code items such as synonyms, and joins (except, intersect); implement logic which uses dynamic SQL and system metadata; write efficient, technically complex SQL queries, including all types of joins versus the use of derived tables; determine what code may or may not execute based on the tables provided; given a table with constraints, determine which statement set would load a table; use and understand different data access technologies; case versus isnull versus coalesce

• Implement sub-queries.

  • May include but not limited to: identify problematic elements in query plans; pivot and unpivot; apply operator; cte statement; with statement

• Implement data types.

  • May include but not limited to: use appropriate data; understand the uses and limitations of each data type; impact of GUID (newid, newsequentialid) on database performance,when to use what data type for columns

• Implement aggregate queries.

  • May include but not limited to: new analytic functions; grouping sets; spatial aggregates; apply ranking functions

• Query and manage XML data.

  • May include but not limited to: understand xml datatypes and their schemas and interop w/, limitations & restrictions; implement XML schemas and handling of XML data; XML data: how to handle it in SQL Server and when and when not to use it, including XML namespaces; import and export xml; xml indexing

SELECT 語法

• RANKing Function ( MSDN Link : http://msdn.microsoft.com/zh-tw/library/ms189798.aspx )
  • ROW_NUMBER() : 資料分割內某資料列的序號，序號從 1 開始，每個資料分割第一個資料列的序號是 1。 ( 1 , 2 , 3, 4 , 5 ..... )
  • RANK() : 資料分割內每個資料列的次序。 資料列的次序等於一加上問題資料列前面的次序數目。 ( 1 , 1, 3, 4 )
  • DENSE_RANK() : 資料分割內之資料列次序，次序中沒有任何間距。 資料列次序是一個加上相關資料列前面之相異次序的數目。 ( 1 , 1 , 2 ,  3 )
  • NTILE() : 將排序資料分割中的資料列散發到指定數目的群組中。 這些群組從 1 開始編號。 對於每個資料列，NTILE 都會傳回資料列所屬群組的號碼。

SELECT ROW_NUMBER() OVER(PARTITION BY PostalCode ORDER BY SalesYTD DESC) AS "Row Number", 

    p.LastName, s.SalesYTD, a.PostalCode

FROM Sales.SalesPerson AS s 

    INNER JOIN Person.Person AS p ON s.BusinessEntityID = p.BusinessEntityID

    INNER JOIN Person.Address AS a ON a.AddressID = p.BusinessEntityID

WHERE TerritoryID IS NOT NULL AND SalesYTD <> 0

ORDER BY PostalCode;

• EXCEPT 和 INTERSECT ( MSDN Link : http://msdn.microsoft.com/zh-tw/library/ms188055.aspx )
  • EXCEPT 會從左側查詢中傳回在右側查詢中找不到的任何個別值。
  • INTERSECT 會傳回 INTERSECT 運算元左右兩側查詢都有的任何個別值。

-- returns all rows from the Orders table where Quantity is between 50 and 100.

SELECT * FROM Orders WHERE  Quantity BETWEEN 1 AND 100

INTERSECT

SELECT * FROM Orders WHERE  Quantity BETWEEN 50 AND 200;

-- returns all rows from the Orders table where Quantity is between 1 and 49, and those with a Quantity between 76 and 100.

SELECT * FROM Orders WHERE  Quantity BETWEEN 1 AND 100

EXCEPT

SELECT * FROM Orders WHERE  Quantity BETWEEN 50 AND 75;

• CASE versus ISNULL versus COALESCE ( 參考德瑞克 Blog http://sharedderrick.blogspot.tw/2012/06/t-sql-coalesce.html )
  • COALESCE() function is in SQL-92 standard and supported by more different DBMS. The ISNULL() function is not in SQL-92 standard.
  • COALESCE() 支援多個輸入參數。若只有使用兩個參數，則 COALESCE() 函數的類似於 ISNULL() 函數。若以效能觀點來看，在某些情境下，ISNULL() 函數可能會優於 COALESCE() 函數。

-- 回傳 ABC

SELECT ISNULL(NULL, 'ABC') 

-- 回傳 NULL

SELECT ISNULL(NULL, NULL) 

-- 回傳 ABC

SELECT COALESCE(NULL, 'ABC')

-- Error

SELECT COALESCE(NULL, NULL)

SubQuery

• PIVOT ( MSDN Link : http://msdn.microsoft.com/zh-tw/library/ms177410.aspx )
  • Pivot Sample

-- 定義 Table 型態變數

declare @SaleInfo table( Custom VARCHAR(25), OrderDate Date, Product VARCHAR(20), QTY INT);

-- Demo 資料

insert into @SaleInfo values

( 'Tom' , '2011-01-02' , 'A01' , 10 ), ( 'John', '2011-01-04' , 'A02' , 30 ),

( 'Mary', '2011-01-05' , 'A01' , 20 ), ( 'John', '2011-01-07' , 'A03' , 20 ),

( 'Mary', '2011-01-08' , 'A02' , 20 ), ( 'Tom' , '2011-01-10' , 'A01' , 10 ),

( 'John', '2011-01-11' , 'A02' , 40 ), ( 'Mary', '2011-01-13' , 'A03' , 30 ),

( 'Tom' , '2011-01-15' , 'A03' , 30 )

select * into #summary from (

  select Custom, Product, QTY from @SaleInfo ) p

  pivot 

  ( sum(QTY) for Product in ([A01],[A02],[A03])

  ) pt

select * from #summary

• • Unpivot Sample

select Custom,Quality,Production from #summary pt

  unpivot ( Quality for Production in ( [A01],[A02]) ) p

• APPLY ( MSDN Link : http://msdn.microsoft.com/zh-tw/library/ms175156.aspx ) Reference ( http://www.simple-talk.com/sql/t-sql-programming/sql-server-apply-basics/ ) 
  • 當你查詢資料表的時候，需要將每筆資料都去呼叫特定個資料表函數的時候，可以配合 APPLY 運算子使用。APPLY 有兩種格式：CROSS APPLY 與 OUTER APPLY
  • CROSS APPLY : 類似 INNER  JOIN 的處理，當呼叫的函數如果沒有任何回傳值的時候，則該筆資料則不顯示
  • OUTER APPLY : 類似 OUTER JOIN 的處理，則可傳回能產生結果集的資料列，以及不會產生結果集的資料列 (在資料表值函式所產生的欄位中會顯示 NULL 值)

• CTE ( MSDN Link : http://msdn.microsoft.com/zh-tw/library/ms175972.aspx )

-- 遞迴

WITH myRecursiveCTE(col1, col2, ... coln) AS

( 

  -- Anchor Member Query

  UNION ALL

  -- Recursive Member Query that references myRecursiveCTE

)

SELECT * FROM myRecursiveCTE  OPTION (MAXRECURSION 7)

Data Types

• MSDN Link ( http://msdn.microsoft.com/zh-tw/library/ms187752.aspx )

 

Aggregate

• Analytic Function ( MSDN Link : http://msdn.microsoft.com/zh-tw/library/hh213234.aspx )
  • 一般不單獨使用，需要配合 Window Function

SELECT BusinessEntityID, TerritoryID 

    ,CONVERT(varchar(20),SalesYTD,1) AS  SalesYTD

    ,DATEPART(yy,ModifiedDate) AS SalesYear

    ,CONVERT(varchar(20),SUM(SalesYTD) OVER (PARTITION BY TerritoryID 

                                             ORDER BY DATEPART(yy,ModifiedDate) 

                                             ROWS BETWEEN CURRENT ROW AND 1 FOLLOWING ),1) AS CumulativeTotal

FROM Sales.SalesPerson

WHERE TerritoryID IS NULL OR TerritoryID < 5;

• Spatial Aggregate ( MSDN Link : http://msdn.microsoft.com/zh-tw/library/ff848797 )
  • Geography : 類型代表圓形地球座標系統中的資料。 SQL Server geography 資料類型會儲存橢圓體 (圓形表面) 資料，例如 GPS 經緯度座標
  • Germetry : 此類型代表以 Euclidean (平面) 座標系統表示的資料