試験 70-461:

Microsoft SQL Server 2012 へのクエリ



概要

試験の準備

マイクロソフト認定資格 Web サイトおよびこの準備ガイドには、試験の準備に役立つさまざまなリソースが含まれています。試験の準備と受験 - FAQ では、次のような、試験の登録、準備、得点、およびポリシーに関してよく寄せられる質問への回答が記載されています。

試験準備の最も効果的な方法

マイクロソフトのトレーニング教材と試験内容の関係

Service Pack とリビジョンの更新の試験内容への組み入れに関するマイクロソフトのポリシー

試験の設問の種類と形式

試験の時間制限と設問数

試験のスケジュールを設定する前に、この準備ガイド全体を確認し、マイクロソフト認定資格 Web サイトの FAQ とリソースの内容を理解することをお勧めします。



受験対象者のプロファイル

この試験は、2 年以上の経験があり、クエリの作成に関するスキルと知識を証明しようとしている SQL Server データベースの管理者、導入者、システム エンジニア、および開発者を対象としています。主な責務には次の内容が含まれています。ただし、これらの内容に限定されているわけではありません。

組み込みスカラー関数を適用する。

順位付け関数を適用する。

データセットを組み合わせる。

DML トリガーの作成と変更を行う。

インデックスの作成と変更を行う。

ストアド プロシージャの作成と変更を行う。

テーブルの作成と変更を行う。

ユーザー定義関数 (UDF) の作成と変更を行う。

ビューの作成と変更を行う。

制約の作成と変更を行う。

T-SQL ストアド プロシージャを設計する。

ビューをデザインする。

集計クエリを実装する。

データ型を実装する。

エラー処理を実装する。

サブクエリを実装する。

シーケンス ジェネレーターを実装する。

トランザクションを管理する。

INSERT、UPDATE、DELETE のステートメントを使用してデータを変更する。

MERGE ステートメントを使用してデータを変更する。

クエリを最適化する。

XML データへのクエリの実行と管理を行う。

SELECT ステートメントを使用してデータにクエリを実行する。

OUTPUT 句を使用してデータを返す。



注意 この受験ガイドは、事前の通知なく、マイクロソフトの独自の判断で適宜変更される場合があります。マイクロソフトの試験には、適応性テスト テクノロジとシミュレーション問題が含まれる場合がありますが、この受験ガイドでは、特に試験の出題形式を想定していません。出題形式にこだわらず、この受験ガイドを参考にして、受験準備を行ってください。



一度JapanCertの70-461問題集を使用すると、きっと二度目を使用したいです。JapanCertは最高の70-461資料を提供するだけでなく、高品質のサービスも提供します。私達の資料についてどんなアドバイスがあってもお気軽に言ってください。受験生の皆さんを試験に合格させることを旨とするだけでなく、皆さんに最高のサービスを提供することも目標としています。



JapanCertのMicrosoftの70-461試験問題集の知名度が非常に高いことを皆はよく知っています。JapanCert は世界的によく知られているサイトです。どうしてこのような大きな連鎖反応になりましたか。それはJapanCertのMicrosoftの70-461試験トレーニング資料は適用性が高いもので、本当にみなさんが良い成績を取ることを助けられるからです。



JapanCertのMicrosoftの70-461問題集を買う前に、一部の問題と解答を無料に試用することができます。そうすると、JapanCertのMicrosoftの70-461試験問題集の品質をよく知っています。JapanCertのMicrosoftの70-461問題集は絶対あなたの最良の選択です。



JapanCertのMicrosoftの70-461試験のトレーニングキットはJapanCertのIT技術専門家たちによって開発されたのです。そのデザインは当面の急速に変化するIT市場と密接な関係があります。JapanCertのトレーニングはあなたを助けて継続的に発展している技術を利用して、問題を解決する能力を高めると同時に仕事についての満足度を向上させることができます。JapanCertのMicrosoftの70-461の認証したカバー率は１００パーセントに達したのですから、弊社の問題と解答を利用する限り、あなたがきっと気楽に試験に合格することを保証します。



試験番号：70-461問題集

試験科目：Microsoft 「Querying Microsoft SQL Server 2012」

最近更新時間：2014-02-19

問題と解答：98



購入前にお試し,私たちの試験の質問と回答のいずれかの無料サンプルをダウンロード：http://www.japancert.com/70-461.html



NO.1 DRAG DROP

You use a Microsoft SQL Server 2012 database.

You need to create an indexed view within the database for a report that displays Customer Name and the

total revenue for that customer.

Which four T-SQL statements should you use? (To answer, move the appropriate SQL statements from

the list of statements to the answer area and arrange them in the correct order.)

Answer:



NO.2 You administer a Microsoft SQL Server 2012 database named ContosoDb. Tables are defined as shown

in the exhibit. (Click the Exhibit button.)

You need to display rows from the Orders table for the Customers row having the CustomerId value set to

1 in the following XML format.

<row OrderId= "1" orderDate="2000-01-01T00:00:00", Amount="3400.00" Name="Customer A"

Country="Australia" />

<row OrderId="2" OrderDate="2001-01-01T00:00:00" Amount="4300.00" Name="Customer A"

Country-"Australia" />

Which Transact-SQL query should you use?

A. SELECT OrderId, OrderDate, Amount, Name, Country FROM Orders

INNER JOIN Customers ON Orders.CustomerId = Customers-CustomerId

WHERE Customers.CustomerId = 1

FOR XML RAW

B. SELECT OrderId, OrderDate, Amount, Name, Country FROM Orders

INNER JOIN Customers ON Orders.CustomerId = Customers.CustomerId

WHERE Customers=CustomerId = 1

FOR XML RAW, ELEMENTS

C. SELECT OrderId, OrderDate, Amount, Name, Country FROM Orders

INNER JOIN Customers ON Orders.CustomerId = Customers.CustomerId

WHERE Customers.CustomerId = 1

FOR XML AUTO

D. SELECT OrderId, OrderDate, Amount, Name, Country FROM Orders

INNER JOIN Customers ON Orders.CustomerId - Customers.CustomerId

WHERE Customers.CustomerId= 1

FOR XML AUTO, ELEMENTS

E. SELECT Name, Country, OrderId, OrderDate, Amount FROM Orders

INNER JOIN Customers ON Orders.CustomerId= Customers.CustomerId

WHERE Customers.CustomerId- 1

FOR XML AUTO

F. SELECT Name, Country, Crderld, OrderDate, Amount FROM Orders

INNER JOIN Customers ON Orders.CustomerId= Customers.CustomerId

WHERE Customers.CustomerId= 1

FOR XML AUTO, ELEMENTS

G. SELECT Name AS '@Name', Country AS '@Country', OrderId, OrderDate, Amount FROM Orders

INNER JOIN Customers ON Orders.CustomerId= Customers.CustomerId

WHERE Customers.CustomerId= 1

FOR XML PATH ('Customers')

H. SELECT Name AS 'Customers/Name', Country AS 'Customers/Country', OrderId, OrderDate, Amount

FROM Orders

INNER JOIN Customers ON Orders.CustomerId= Customers.CustomerId

WHERE Customers.CustomerId= 1

FOR XML PATH ('Customers')

Answer: A



Microsoft参考書 70-461 70-461



NO.3 You administer a Microsoft SQL Server 2012 database named ContosoDb. Tables are defined as shown

in the exhibit. (Click the Exhibit button.)

You need to display rows from the Orders table for the Customers row having the CustomerId value set to

1 in the following XML format.

Which Transact-SQL query should you use?

A. SELECT OrderId, OrderDate, Amount, Name, Country FROM Orders

INNER JOIN Customers ON Orders.CustomerId = Customers-CustomerId

WHERE Customers.CustomerId = 1

FOR XML RAW

B. SELECT OrderId, OrderDate, Amount, Name, Country FROM Orders

INNER JOIN Customers ON Orders.CustomerId = Customers.CustomerId

WHERE Customers=CustomerId = 1

FOR XML RAW, ELEMENTS

C. SELECT OrderId, OrderDate, Amount, Name, Country FROM Orders

INNER JOIN Customers ON Orders.CustomerId = Customers.CustomerId

WHERE Customers.CustomerId = 1

FOR XML AUTO

D. SELECT OrderId, OrderDate, Amount, Name, Country FROM Orders

INNER JOIN Customers ON Orders.CustomerXd - Customers.CustomerId

WHERE Customers.CustomerId= 1

FOR XML AUTO, ELEMENTS

E. SELECT Name, Country, OrderId, OrderDate, Amount FROM Orders

INNER JOIN Customers ON Orders.CustomerId= Customers.CustomerId

WHERE Customers.CustomerId- 1

FOR XML AUTO

F.SELECT Name, Country, Crderld, OrderDate, Amount FROM Orders

INNER JOIN Customers ON Orders.CustomerId= Customers.CustomerId

WHERE Customers.CustomerId= 1

FOR XML AUTO, ELEMENTS

G. SELECT Name AS '@Name', Country AS '@Country', OrderId, OrderDate, Amount FROM Orders

INNER JOIN Customers ON Orders.CustomerId= Customers.CustomerId

WHERE Customers.CustomerId= 1

FOR XML PATH ('Customers')

H. SELECT Name AS 'Customers/Name', Country AS 'Customers/Country', OrderId, OrderDate, Amount

FROM Orders

INNER JOIN Customers ON Orders.CustomerId= Customers.CustomerId

WHERE Customers.CustomerId= 1

FOR XML PATH ('Customers')

Answer: D



Microsoft参考書 70-461 70-461認証試験



NO.4 You administer a Microsoft SQL Server 2012 database named ContosoDb. Tables are defined as shown

in the exhibit. (Click the Exhibit button.)

You need to display rows from the Orders table for the Customers row having the CustomerIdvalue set to

1 in the following XML format.

Which Transact-SQL query should you use?

A. SELECT OrderId, OrderDate, Amount, Name, Country FROM Orders

INNER JOIN Customers ON Orders.CustomerId = Customers-CustomerId

WHERE Customers.CustomerId = 1

FOR XML RAW

B. SELECT OrderId, OrderDate, Amount, Name, Country FROM Orders

INNER JOIN Customers ON Orders.CustomerId = Customers.CustomerId

WHERE Customers=CustomerId = 1

FOR XML RAW, ELEMENTS

C. SELECT OrderId, OrderDate, Amount, Name, Country FROM Orders

INNER JOIN Customers ON Orders.CustomerId = Customers.CustomerId

WHERE Customers.CustomerId = 1

FOR XML AUTO

D. SELECT OrderId, OrderDate, Amount, Name, Country FROM Orders

INNER JOIN Customers ON Orders.CustomerXd - Customers.CustomerId

WHERE Customers.CustomerId= 1

FOR XML AUTO, ELEMENTS

E. SELECT Name, Country, OrderId, OrderDate, Amount FROM Orders

INNER JOIN Customers ON Orders.CustomerId= Customers.CustomerId

WHERE Customers.CustomerId- 1

FOR XML AUTO

F. SELECT Name, Country, Crderld, OrderDate, Amount FROM Orders

INNER JOIN Customers ON Orders.CustomerId= Customers.CustomerId

WHERE Customers.CustomerId= 1

FOR XML AUTO, ELEMENTS

G. SELECT Name AS '@Name', Country AS '@Country', OrderId, OrderDate, Amount FROM Orders

INNER JOIN Customers ON Orders.CustomerId= Customers.CustomerId

WHERE Customers.CustomerId= 1

FOR XML PATH ('Customers')

H. SELECT Name AS 'Customers/Name', Country AS 'Customers/Country', OrderId, OrderDate, Amount

FROM Orders

INNER JOIN Customers ON Orders.CustomerId= Customers.CustomerId

WHERE Customers.CustomerId= 1

FOR XML PATH ('Customers')

Answer: C



Microsoft練習問題 70-461 70-461認定試験 70-461認定試験 70-461過去問



NO.5 You administer a Microsoft SQL Server 2012 database named ContosoDb. Tables are defined as shown

in the exhibit. (Click the Exhibit button.)

You need to display rows from the Orders table for the Customers row having the CustomerId value set to

1 in the following XML format.

Which Transact-SQL query should you use?

A. SELECT OrderId, OrderDate, Amount, Name, Country FROM Orders

INNER JOIN Customers ON Orders.CustomerId = Customers-CustomerId

WHERE Customers.CustomerId = 1

FOR XML RAW

B. SELECT OrderId, OrderDate, Amount, Name, Country FROM Orders

INNER JOIN Customers ON Orders.CustomerId = Customers.CustomerId

WHERE Customers=CustomerId = 1

FOR XML RAW, ELEMENTS

C. SELECT OrderId, OrderDate, Amount, Name, Country FROM Orders

INNER JOIN Customers ON Orders.CustomerId = Customers.CustomerId

WHERE Customers.CustomerId = 1

FOR XML AUTO

D. SELECT OrderId, OrderDate, Amount, Name, Country FROM Orders

INNER JOIN Customers ON Orders.CustomerXd - Customers.CustomerId

WHERE Customers.CustomerId= 1

FOR XML AUTO, ELEMENTS

E. SELECT Name, Country, OrderId, OrderDate, Amount FROM Orders

INNER JOIN Customers ON Orders.CustomerId= Customers.CustomerId

WHERE Customers.CustomerId- 1

FOR XML AUTO

F.SELECT Name, Country, Crderld, OrderDate, Amount FROM Orders

INNER JOIN Customers ON Orders.CustomerId= Customers.CustomerId

WHERE Customers.CustomerId= 1

FOR XML AUTO, ELEMENTS

G. SELECT Name AS '@Name', Country AS '@Country', OrderId, OrderDate, Amount FROM Orders

INNER JOIN Customers ON Orders.CustomerId= Customers.CustomerId

WHERE Customers.CustomerId= 1

FOR XML PATH ('Customers')

H. SELECT Name AS 'Customers/Name', Country AS 'Customers/Country', OrderId, OrderDate, Amount

FROM Orders

INNER JOIN Customers ON Orders.CustomerId= Customers.CustomerId

WHERE Customers.CustomerId= 1

FOR XML PATH ('Customers')

Answer: E



Microsoft 70-461 70-461認定証 70-461過去問 70-461問題集



NO.6 You develop a Microsoft SQL Server 2012 database that contains a table named Customers.

The Customers table has the following definition:

You need to create an audit record only when either the MobileNumber or HomeNumber column is

updated.

Which Transact-SQL query should you use?

A. CREATE TRIGGER TrgPhoneNumberChange

ON Customers FOR UPDATE

AS

IF COLUMNS_UPDATED (HomeNumber, MobiieNumber)

- - Create Audit Records

B. CREATE TRIGGER TrgPhoneNumberChange

ON Customers FOR UPDATE

AS

IF EXISTS( SELECT HomeNumber from inserted) OR

EXISTS (SELECT MobileNumber FROM inserted)

- - Create Audit Records

C. CREATE TRIGGER TrgPhoneNumberChange

ON Customers FOR UPDATE

AS

IF COLUMNS_CHANGED (HomeNumber, MobileNumber)

- - Create Audit Records

D. CREATE TRIGGER TrgPhoneNumberChange

ON Customers FOR UPDATE

AS

IF UPDATE (HomeNumber) OR UPDATE (MobileNumber)

- - Create Audit Records

Answer: D



Microsoft 70-461 70-461認定資格 70-461 70-461参考書 70-461認定資格



NO.7 You create a table that has the StudentCode, SubjectCode, and Marks columns to record mid-year

marks for students. The table has marks obtained by 50 students for various subjects.

You need to retrieve the students who scored the highest marks for each subject along with the marks.

Which Transact-SQL query should you use?

A. SELECT StudentCode as Code,

RANK ( ) OVER (ORDER BY AVG (Marks) DESC) AS Value FROM StudentMarks

GROUP BY StudentCode

B. SELECT Id, Name, Marks, DENSE_RANK () OVER (ORDER BY Marks DESC) AS Rank FROM

StudentMarks

C. SELECT StudentCode as Code,

DENSE_RANK () OVER (ORDER BY AVG (Marks) DESC) AS Value FROM StudentMarks

GROUP BY StudentCode

D. SELECT StudentCode as Code,

NTILE (2) OVER (ORDER BY AVG (Marks) DESC) AS Value FROM StudentMarks

GROUP BY StudentCode

E. SELECT StudentCode AS Code,Marks AS Value

FROM (SELECT StudentCode, Marks AS Marks,

RANK () OVER (PARTITION BY SubjectCode ORDER BY Marks ASC) AS Rank FROM StudentMarks)

tmp WHERE Rank = 1

F. SELECT StudentCode AS Code,Marks AS Value FRCM (

SELECT StudentCode, Marks AS Marks,

RANK() OVER (PARTITION BY SubjectCode ORDER 3Y Marks DESC) AS Rank FRCM StudentMarks)

tmp WHERE Rank = 1

G. SELECT StudentCode AS Code,Marks AS Value FROM

(SELECT StudentCode, Marks AS Marks,

RANK () OVER (PARTITION BY StudentCode ORDER BY Marks ASC) AS Rank FROM StudentMarks)

tmp WHERE Rank = 1

H. SELECT StudentCode AS Code,Marks AS Value FROM

(SELECT StudentCode, Marks AS Marks,

RANXO OVER (PARTITION BY StudentCode ORDER BY Marks DESC) AS Rank FROM StudentMarks)

tmp WHERE Rank = 1

Answer: F



Microsoft 70-461認証試験 70-461 70-461認定資格



NO.8 You develop a Microsoft SQL Server 2012 server database that supports an application. The application

contains a table that has the following definition:

CREATE TABLE Inventory

(ItemID int NOT NULL PRIMARY KEY,

ItemsInStore int NOT NULL,

ItemsInWarehouse int NOT NULL)

You need to create a computed column that returns the sum total of the ItemsInStore and

ItemsInWarehouse values for each row.

The new column is expected to be queried heavily, and you need to be able to index the column.

Which Transact-SQL statement should you use?

A. ALTER TABLE Inventory

All TotalItems AS Item3lnStore + ItemsInWarehouse

B. ALTER TABLE Inventory

ADD TotalItems AS ItemsInStore + ItemsInWarehouse PERSISTED

C. ALTER TABLE Inventory

ADD TotalItems AS SUM (ItemsInStore, ItemsInWarehouse) PERSISTED

D. ALTER TABLE Inventory

All TotalItems AS SUM (ItemsInStore, ItemsInWarehouse)

Answer: C



Microsoft練習問題 70-461 70-461過去問



NO.9 You create a table that has the StudentCode, SubjectCode, and Marks columns to record mid-year

marks for students. The table has marks obtained by 50 students for various subjects.

You need to ensure that the top half of the students arranged by their average marks must be given a rank

of 1 and the remaining students must be given a rank of 2.

Which Transact-SQL query should you use?

A. SELECT StudentCode as Code,

RANK ( ) OVER (ORDER BY AVG (Marks) DESC) AS Value FROM StudentMarks

GROUP BY StudentCode

B. SELECT Id, Name, Marks, DENSE_RANK () OVER (ORDER BY Marks DESC) AS Rank FROM

StudentMarks

C. SELECT StudentCode as Code,

DENSE_RANK () OVER (ORDER BY AVG (Marks) DESC) AS Value FROM StudentMarks

GROUP BY StudentCode

D. SELECT StudentCode as Code,

NTILE (2) OVER (ORDER BY AVG (Marks) DESC) AS Value FROM StudentMarks

GROUP BY StudentCode

E. SELECT StudentCode AS Code,Marks AS Value

FROM (SELECT StudentCode, Marks AS Marks,

RANK () OVER (PARTITION BY SubjectCode ORDER BY Marks ASC) AS Rank FROM StudentMarks)

tmp WHERE Rank = 1

F. SELECT StudentCode AS Code,Marks AS Value FRCM (

SELECT StudentCode, Marks AS Marks,

RANK() OVER (PARTITION BY SubjectCode ORDER 3Y Marks DESC) AS Rank FRCM StudentMarks)

tmp WHERE Rank = 1

G. SELECT StudentCode AS Code,Marks AS Value FROM

(SELECT StudentCode, Marks AS Marks,

RANK () OVER (PARTITION BY StudentCode ORDER BY Marks ASC) AS Rank FROM StudentMarks)

tmp WHERE Rank = 1

H. SELECT StudentCode AS Code,Marks AS Value FROM

(SELECT StudentCode, Marks AS Marks,

RANXO OVER (PARTITION BY StudentCode ORDER BY Marks DESC) AS Rank FROM StudentMarks)

tmp WHERE Rank = 1

Answer: D



Microsoft練習問題 70-461問題集 70-461



NO.10 You create a table that has the StudentCode, SubjectCode, and Marks columns to record mid-year

marks for students. The table has marks obtained by 50 students for various subjects.

You need to ensure that the following requirements are met:

Students must be ranked based on their average marks.

If one or more students have the same average, the same rank must be given to these students.

Consecutive ranks must be skipped when the same rank is assigned.

Which Transact-SQL query should you use?

A. SELECT StudentCode as Code,

RANK ( ) OVER (ORDER BY AVG (Marks) DESC) AS Value FROM StudentMarks

GROUP BY StudentCode

B. SELECT Id, Name, Marks, DENSE_RANK () OVER (ORDER BY Marks DESC) AS Rank FROM

StudentMarks

C. SELECT StudentCode as Code,

DENSE_RANK () OVER (ORDER BY AVG (Marks) DESC) AS Value FROM StudentMarks

GROUP BY StudentCode

D. SELECT StudentCode as Code,

NTILE (2) OVER (ORDER BY AVG (Marks) DESC) AS Value FROM StudentMarks

GROUP BY StudentCode

E. SELECT StudentCode AS Code,Marks AS Value

FROM (SELECT StudentCode, Marks AS Marks,

RANK () OVER (PARTITION BY SubjectCode ORDER BY Marks ASC) AS Rank FROM StudentMarks)

tmp WHERE Rank = 1

F. SELECT StudentCode AS Code,Marks AS Value FRCM (

SELECT StudentCode, Marks AS Marks,

RANK() OVER (PARTITION BY SubjectCode ORDER 3Y Marks DESC) AS Rank FRCM StudentMarks)

tmp WHERE Rank = 1

G. SELECT StudentCode AS Code,Marks AS Value FROM

(SELECT StudentCode, Marks AS Marks,

RANK () OVER (PARTITION BY StudentCode ORDER BY Marks ASC) AS Rank FROM StudentMarks)

tmp WHERE Rank = 1

H. SELECT StudentCode AS Code,Marks AS Value FROM

(SELECT StudentCode, Marks AS Marks,

RANXO OVER (PARTITION BY StudentCode ORDER BY Marks DESC) AS Rank FROM StudentMarks)

tmp WHERE Rank = 1

Answer: A



Microsoft 70-461認定試験 70-461練習問題 70-461認定資格



JapanCertは最新の70-410問題集と高品質のC_TADM53_70問題と回答を提供します。JapanCertのC4040-224 VCEテストエンジンと200-120試験ガイドはあなたが一回で試験に合格するのを助けることができます。高品質のHP2-E58 PDFトレーニング教材は、あなたがより迅速かつ簡単に試験に合格することを100％保証します。試験に合格して認証資格を取るのはそのような簡単なことです。



記事のリンク：http://www.japancert.com/70-461.html