Microsoft MCSA SQL Server 2016 (70-761) むぼーな挑戦 2

いまから以下の70-761関係勉強始めます。ご飯作る時間が4時ごろだから、それまでに2時間ぐらいしかない。。。

１．明日はパケットの＃７

*outer applyとcross applyがいまいちはっきりしないので、こちらをプリントアウトして壁に張った。

http://stevestedman.com/wp-content/uploads/TSqlJoinTypePoster1.pdf 

引き続き、やっつけてしまうぞ！

*Temporal dataのクエリのメインは、

『AS OF』で時間を絞ってやるとき

『Contained In』History Table からのときのみ

---

---

*問題の読み方

➞　'You need to create...'を探してみる。そこで、CTEをつくるのか、Stored Procedureを作るのかなど、何を聞かれているのか、はっきりわかっておくこと。

➞　先にSQLのステートメントがないか、見ちゃう。

➞　テーブルの表があれば、だいたいどんな問題か見当がつく場合がある。すると、最初は飛ばして読める。

➞　'Following Requirements'は必ず読むこと。

➞　'Part of the correct Transact-SQL has been provided in the...'とあれば、これはもう『穴埋め』問題です。

➞　まず、明らかに足りてない部分、例えば、『unpivot』の『FOR』『IN』とかは先に当てはまめておく。

➞　全部目ぼしいものが足りているのに、「なんだかなぁ、足りん！」という場合は、だいたいひっかけで、先にどんな問題か見当付けていたことが間違っている可能性がある。その場合、もう一度'Following Requirements'を読むこと。

➞　➞　➞　ここまでしてわからん場合は、明らかに勉強不足。今は諦めて答えを見よう！

 

---

 

*もう、CTEとか、UDFとか、作り方をよく覚えてないんで、ここに書いときます。こんな感じででてたら、これだって思ってください。

【CTE】＊[WITH][parameter][AS]があるか、まず確認しよう！＜－『Expresstion Table』を必ず読むこと

WITH Sales_CTE (SalesPersonID, SalesOrderID, SalesYear)

AS

(

SELECT

・・・

)

SELECT

・・・;

【UDF】

CREATE　FUNCTION dbo.ufnGetInventoryStock(@ProductID int)

RETURNS　int

AS

BEGIN

・・・

RETURN @ret;

END;

【Deterministic and Nondeterministic Functionsってなに？】

【確定的と非確定的ファンクション】だって。と言いつつも、日本語でどう説明されているのかわからん。なので、読むのめんどくさいからGoogle Translate使ってやった。これで十分。

Deterministic functions always return the same result any time they are called with a specific set of input values and given the same state of the database. Nondeterministic functions may return different results each time they are called with a specific set of input values even if the database state that they access remains the same.
For example, the function AVG always returns the same result given the qualifications stated above, but the GETDATE function, which returns the current datetime value, always returns a different result.

確定的関数は、特定の入力値のセットで呼び出され、データベースの状態が同じである場合、常に同じ結果を返します。 非決定的関数は、アクセスするデータベースの状態が同じ場合でも、特定の入力値のセットで呼び出されるたびに異なる結果を返す場合があります。
たとえば、関数AVGは上記の条件が与えられると常に同じ結果を返しますが、現在の日時値を返すGETDATE関数は常に異なる結果を返します。

【Stored Procedure】

CREATE PROCEDURE HumanResources.uspGetEmployeesTest2

@LastName nvarchar(50),

@FirstName nvarchar(50)

AS

SET NOCOUNT ON;

SELECT FirstName, LastName, Department FROM HumanResources.vEmployeeDepartmentHistory

WHERE FirstName = @FirstName

AND LastName = @LastName AND EndDate IS NULL;

 

---

---

---

---

えらいことに気が付いた。。。

CTEおぼえるだけじゃだめなんや。

問題の傾向について、私が解けそうなのは。

１．SQLクエリの中の穴埋め問題

２．専門的なターム（CAUCSESや、IIFなど）に対してのチェックマーク問題

３．エラーの位置問題（begin, commit, rollback, try, catch, @@trancount）

だとしてたら、『１．SQLクエリの中の穴埋め問題』の問題を確実にするために、最低でも覚えりゃ住む問題を解くこと。

『Expresstion Table』,『UDF』、『Outer Apply, Crossf Apply, Except, Intersect, Union, Union Allの違い』

問題には、大きく分けて、①覚えりゃよいもの　②考えて解くもの　③ひっかけ　と傾向がある。

 

---

では、まずは、

『Expresstion Table』

CTE (Common Table Expression)*self-referencable *Can be referenced more than once without query.ここがDerived Tableと違うよ。

１．『WITH』からはじまること

２．パラメータと『AS（』の後のSELECTのコラムがマップされていなければならいこと

３．CTEを呼ぶときには『FROM』からフツーに呼ぶこと

４．CTEを呼ぶときには『SELECT』のコラムがマップされていること

５．CTEは一つのトランザクションの中に何回も入れて使うことができるそうだ。（追加）

WITH CTEA(ID, colA,colB)

AS(Select ID, colA,colb from tableA group by colA, colB)

WITH CTEB(ID, colD, colE)

AS(Select ID,  colD,colE from tableB Group by colD, colE)

Select a.ID a.colA, a.colB, b.colD, b.colE

from CTEA a

INNER JOIN CTEB b ON a.ID = b.ID

Inline TVF(Inline Table-Valued Function)

１．『CREATE　FUNCTION』からはじまること

２．パラメータは『itvf_InlineTVF(@text nvarchar(1000))』みたいにすること

３．『RETURNS』は宣言部、そして『RETURN』は『BEGIN』『END』の間にあること。

４．『BEGIN』『END』はトランザクションと勘違いしないこと

５．宣言部の『RETURNS』は『TABLE』を返すようにすること。

RETURNS @results TABLE

(

ItemID int,

SupplierID int,

ColorID int,

ItemName nvarchar(100)

)

６．『AS BEGIN～END』があること

７．『INSERT』や『DELETE』などのデータを扱えること

８．TABLEのように『FROM』から呼び出せること

 

 

Derived Tables*Not self-referencable *Cannot be referenced more than once without query.ここがCTEと違うよ。

例題）

SELECT 

CASE

WHEN UnitPrice < 100 THEN 'OK'

WHEN UnitPrice < 200 THEN 'NOT OK'

WHEN UnitPrice < 300 THEN 'Whoa!'

 ELSE 'Yikes!'

END AS PriceDescription,

COUNT(*) AS NumberOfItems

FROM WareHouse.StockItems

 

GROUP BY ⇐『FROM』『WHERE』『GROUP BY』『HAVING』『SELECT』『ORDER BY』の順番に読むから、この『SELECT』にあるコラムは丸写ししなきゃならない。だから。。。↓

CASE

WHEN UnitPrice < 100 THEN 'OK'

WHEN UnitPrice < 200 THEN 'NOT OK'

WHEN UnitPrice < 300 THEN 'Whoa!'

ELSE 'Yikes!'

END 

ORDER BY NumberOfItems ⇐これも『FROM』『WHERE』『GROUP BY』『HAVING』『SELECT』『ORDER BY』の順番だから、そのままエリアスネームが使える。

 

さぁて、Derived Tableにかえると。

SELECT　

PriceDescription,

COUNT(*) AS NumberOfItems

FROM

(

SELECT 

CASE

WHEN UnitPrice < 100 THEN 'OK'

WHEN UnitPrice < 200 THEN 'NOT OK'

WHEN UnitPrice < 300 THEN 'Whoa!'

ELSE 'Yikes!'

END AS PriceDescription

FROM WareHouse.StockItems

) si⇐『FROM』『WHERE』『GROUP BY』『HAVING』『SELECT』『ORDER BY』の順番に読むから、この『FROM』にある『CASE』はすべて最初に呼ばれるぞ！

GROUP BY PriceDescription 

ORDER BY NumberOfItems 

 

 

View　*パラメータが使えない

１．『CREATE VIEW』からはじまること

２．テーブルのように『FROM』でつかうこと