[SQL][MCSA]70-461 筆記 (二)

[SQL][MCSA]70-461 筆記 (二)

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;
-- 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

-- 定義 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

image

    • Unpivot Sample
select Custom,Quality,Production from #summary pt
  unpivot ( Quality for Production in ( [A01],[A02]) ) p

image

 

  • 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 值)

 

-- 遞迴
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

 


 

Aggregate

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 (平面) 座標系統表示的資料