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

さてと、12時から勉強始めたので、今で1時間20分か。

CBTNugett のKaplanという会社（オンライン大学とかやってる）がつくったプレップテストを20問に絞ってやってみた。

これを考察していこうと思う。点数は驚異の50点！

おわた。

でも、驚くべきことは過去問とはほぼかけ離れた難しさでアール。なんでや。

 

---

 

---

 

---

【間違いの理由別考察】

１．読み落とし

パターン１「Which clauses should you luse to construct a common table expression for the Transact-SQL query?」

でこの「a common table expression」を読み落として一つチェックマークが抜けていた。

 A) GROUP BY

B) INTO

C) OVER

D) WITH

E) HAVING

ここからだったら、問題読まなくても明らかにA,D,Eだ。Eのチェックを忘れていた。

 

---

２．忘れている

パターン１　DELETE Products output ProdID, Desciption, AltDescription, UnitPrice;

A)All rows are permanently removed from the Products table, and the deleted rows are stored in a temporary table.

B)All rows are permanently removed from the Products table, and the deleted rows are displayed.

C)The DELETE statement fails because it does not include the FROM keyword.

D)The DELETE statement fails because the OUTPUT clause is invalid.

完全に忘れている。では、今からも一度調べます。自分へのパニッシュメントです。

★OUTPUT clauseを使うなら、INSERTED や DELETED

 prefixesをつかってコラム名を明記しなければならない。だから答えはDです。

で、正しくは、

DELETE Products
OUTPUT DELETED.ProdID, DELETED.Description, DELETED.AltDescription, DELETED.UnitPrice;

 

では、OUTPUTについて。

①OUTPUTを使ってDMLオペレーションの『inserts, updates, deletes, merges』を格納することができる。

②INTO clauseをつかって、本チャンのテーブルに移すか、もしくはSQLServer v16.0からOKAYになった、Temporary Tableに格納することができる。

 

 

パターン２

<question id="1.2.3.51" type="SAMC">
  <question>こんな感じでXMLの例がかいてあるけど、しっかり読まなくてもいいかなと思う</question>
  <alternatives>
  <option>tissue</option>
  <option correct="true">cell</option>
  <option>organism</option>
  <option>organ</option>
  <opton>system</option>
  </alternatives>
</question>

You need to choose a data type for the table column and justify your choice to the database administrator.

A)The varbinary data type, because varbinary supports conversion to xml.

B)The ntext data type, because xml supports conversion to ntext.

C)The nvarchar(max) data type, because the id attribute can then be indexed.

D)The xml data type, because the value can exceed 2GB.

 

完全に忘れている。「nvarchar」と「varbinary」だけがコンバージョンサポートされている。

「nvarchar(max)」を選ぶと自動的にIDがインデックス化されてしまうそうだ。だからダメ。

「ntext」ってのは、「nvarchar」と「varbinary」がコンバージョンされた後にこの形になるそうです。

これは、『３．詳細勉強不足』にもなるね。

 

 

３．ケアレスミス

 

パターン１

The query must return only those products that have no matching sales orders, as well as those sales orders that are not matched to a product .

You create the following query to meet the objective:

USE NutexSales;
GO
SELECT p.Name, sod.SalesOrderID
FROM Production.Product p
OUTER JOIN　Sales.SalesOrderDetail sod
ON p.ProductID = sod.ProductID
WHERE p.ProductID IS NULL
OR sod.ProductID IS NULL
ORDER BY p.Name

これは痛い。。。だいたい、「

WHERE p.ProductID IS NULL
OR sod.ProductID IS NULL

」を見た時点で、完全に『FULL OUTER JOIN』なのに、なぜ『FULL』をわすれたのか！！！おわてよし！

 

パターン２

To create this stored procedure, fill in the missing code that meets the stated requirements. (Do not use a BEGIN and END statements when writing this code.)  

CREATE PROCEDURE CustRowsReturned @Count integer ASselect top @Count, Name from Customer
ORDER BY Name

＜答え＞

• SELECT TOP (@Count) Name FROM Customer
• SELECT TOP(@Count) Name FROM Customer

まぁね、コンピュータ試験だからね。。。あぁ～っ、ビール飲んで寝よ。って感じですな。

 

４．ふたを開けたらわかってなかった

パターン１

複数選択式

Which of the following statements are true?

A)The MONTH function is always nondeterministic

B)The CHECKSUM function is always deterministic

C)The ISDATE function is always deterministic

D)The YEAR function is always deterministic

E)The CAST function is always deterministic

F)The RANK function is always nondeterministic

G)The GETDATE

 function is always deterministic

昨日で『nondeterministic』『deterministic』はわかったはずやったのに。。。

【Deterministic and Nondeterministic Functionsってなに？】

確定的関数（Deterministic ）は、特定の入力値のセットで呼び出され、データベースの状態が同じである場合、常に同じ結果を返します。 非決定的関数（Nondeterministic ）は、アクセスするデータベースの状態が同じ場合でも、特定の入力値のセットで呼び出されるたびに異なる結果を返す場合があります。

たとえば、関数AVGは上記の条件が与えられると常に同じ結果を返しますが、現在の日時値を返すGETDATE関数は常に異なる結果を返します。

なんてことを書いてたけど、どうやらファンクションのターゲットにもよるようなことを書いてある。例えば、『CONVERT』のターゲットが『DATETIME』だったりしたら、非決定的関数（Nondeterministic ）になるそうです。

だったら、もう覚えてしまおう！

【確定的関数（Deterministic ）表】ーここにないのは、非決定的関数（Nondeterministic ）だと思えばよいさ。

 

パターン２

『CROSS JOIN』と『FULL JOIN』、いっつも勘違いします。

『CROSS JOIN』Cartesian productはこちら。ぜーんぶのマッチングパターンを出すから、気を付けてね、のほうです。

『FULL JOIN』『FROM』で指定したベーステーブルのIDに対して、マッチしてようが、してまいが『FULL JOIN』したパターンが全部出てきます。でもCartesian productではないです。

Each instructor can teach several courses. The course schedule changes frequently as old courses are cancelled and new courses are made available. Some of the instructors whose courses have been cancelled may not be currently assigned to teach any courses. Some of the new courses may not have instructors assigned to them. To facilitate the assignment of instructors to courses, you must produce a report that will list all courses and all instructors. The courses for which no instructors are currently assigned should be grouped together.

これはもう、英語力がものを言いますな。全然わからんかった。絶対『LEFT JOIN』やと思った。

さて、読み方を考えてみよう。

『Each instructor can teach several courses』

➡こんな関係かな？　instructor＊－＞⁂courses

『Some of the instructors whose courses have been cancelled may not be currently assigned to teach any courses』

➡coursesが『NULL』の可能性があるな？

『Some of the new courses may not have instructors assigned to them』

➡おぉ、instructorが『NULL』と表示されるようにJOINさせなきゃならないのか。

『you must produce a report that will list all courses and all instructors』

➡ここかぁっ！『all courses』 と『all instructors』だ！ということは、両者『NULL』が含まれているテーブルであるということかな？

『The courses for which no instructors are currently assigned should be grouped together』

➡これは、ちょっとひっかけもあるかな。。。最後に『ORDER BY』でくくってやればよいだけのように思う。

＜答え＞

SELECT CourseID, Subject, i.InstructorID, FirstName, LastName
FROM Courses c FULL JOIN Instructors i
ON c.InstructorID = i.InstructorID
ORDER BY i.InstructorID

なぜなら、『FULL JOIN』はこんな感じになるからです。そう、どっちに『NULL』が入ってもよいのです。！！！でも、気を付けて、「CROSS JOIN」とは違うよ。

 

 

５．もう諦めなきゃならんレベル

パターン１

What is the most appropriate data type for the column that holds the resume content?

A)ntext

B)varbinary(max)

C)binary

D)varbinary(8000)

 

これは『varbinary(max)』を使うそうです。まる。

 

パターン２

You run the following Transact-SQL statement:

CREATE PRIMARY XML INDEX PIdx_Customer_Demographics ON Customer(Demographics);

You need to create a secondary XML index for queries searching for the Active attribute value. The index should be named SIdx_Customer_Active. Which Transact-SQL statement should you use? (To answer, type the Transact-SQL statement in the textbox.)  

XML_VALUE();

もちろん間違ってますがな。これが答え。

CREATE XML INDEX SIdx_Customer_Active ON Customer(Demographics) USING XML INDEX PIdx_Customer_Demographics FOR PATH

これは余裕があったら勉強しよう。。。

パターン３

The Verigon Company maintains the Customers table and the Orders table in a SQL Server 2016 application.

You need to create a historical table that contains data on orders placed during 2012.The table should aggregate (sum) the Amount column by month, so that the table would only contain twelve rows, regardless of the number of customers or orders placed. The sum of all the amounts ordered in January would be the first row, the sum of all the amounts ordered in February would be the second row, and so forth. No updates, insertions, or deletions would be performed on the data.

What object type most efficiently supports these requirements?

A)Heap

B)Check Constraint

C)Indexed View

D)Non-Clustered

E)View

F)Default

 

この問題の大事なところは、辛抱して読み続ける能力と、『VIEW』と『INdexed VIEW』の使い分けを知っているかどうかにかかってる。。。

『You need to create a historical table that contains data on orders placed during 2012.』

➡DMLオペレーションの『inserts, updates, deletes, merges』は必要ないな。

『 No updates, insertions, or deletions would be performed on the data.』

➡DMLオペレーションの『inserts, updates, deletes, merges』がないって、しっかりかいてある。

『What object type most efficiently supports these requirements?

A)Heap

B)Check Constraint

C)Indexed View

D)Non-Clustered

E)View

F)Default

』

➡おぉ、これはもう『VIEW』しかないな。でも『VIEW』と『Indexed VIEW』のどっちだろう。

➡。。。わからん。

➡。。。わからん。

➡表みてもわからん。。。

➡。。。わからん。 

『The Verigon Company maintains the Customers table and the Orders table in a SQL Server 2016 application.』

➡なるほど、２０１６年からってかいてあるから、２０１２年の分を格納させてやらなければならない。そしたら、名前つけて格納できる『Indexed VIEW』だ！（実際はてんでわかりませんでした。。。でも同じ問題出たらだいじょうぶ。ばかじゃないから。）

 

ここに書いてありました。

• An indexed view is physically stored ("materialised") on disk = requires memory
• ➡インデックス付きVIEWは物理的に名前がついて格納されているってことですね。
• A standard view is simply an expandable macro: there is no persistence of the data and the base tables are always used
• ➡普通のVIEWは、その場だけの処理で終わるようです。

それから、こちらの方のサイトがとってもわかりやすいので、参照させていただきました。ありがとうございます。（Microsoft.Docでは情報量多すぎ、読むの大変！）

SQL Server ではビューにインデックスを設定してパフォーマンスの向上を図ることができます。

インデックス付きのビューを作成するには以下の条件があります。

1. CREATE VIEW ステートメント実行時、ANSI_NULLSオプションとQUOTED_IDENTIFIERオプションがONに設定されていること。
2. ビューで参照するベーステーブルの作成時にもANSI_NULLSオプションがONに設定されていること。
3. ビューが参照しているのはベーステーブルのみで、他のビュー参照していないこと
4. ビューで参照されるすべてのベーステーブルは、ビューと同じデータベース内に存在し、ビューと同じ所有者であること。
5. ビューはWITH SCHEMABINDINGオプションを使用して作成されていること。
6. ビューで参照されるユーザ定義関数は、SCHEMABINDINGオプションを使用して作成済みであること。
7. ビューでは、テーブルとユーザ定義関数について、スキマー名.オブジェクト名の２つの要素で構成される名前で参照すること。
8. ビュー内の式で参照される関数は決定的であること。決定的であるとは、関数の引数に同じ値を渡した場合、常に同じ値が返される状態を指す。これに対して、GETDATEやNEWID関数は、実行する度に異なる値が返されるので非決定的関数と言える。

【作り方】

SET ANSI_NULLS ON
GO

SET QUOTED_IDENTIFIER ON
GO

--ビューを作成する
CREATE VIEW [dbo].[ビュー名] WITH SCHEMABINDING
AS
SELECT COL001
,COL002
,COL003
,COL004
FROM dbo.TABLE_A

--クラスタ化インデックスを作成する
CREATE UNIQUE CLUSTERED INDEX PK_インデックス名
ON dbo.ビュー名 ( COL001,COL002 )

--非クラスタ化インデックスを作成する
CREATE NONCLUSTERED INDEX IDX_インデックス名
ON dbo.ビュー名 ( COL003,COL004 )

 

疲れた。ここまでで、２時間３０分かかりました。「わかること」って難しい。。。