Exit demo 70-457 Transition Your MCTS on SQL Server 2008 to MCSA: SQL Server 2012, Part 1 PDF format · free preview

Microsoft 70-457 - Questions & Answers

Free preview · every answer includes a full explanation

Product page: https://prepkeys.com/70-457.html

Question 1
Single choice

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, Orderld, 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')

Question 2
Single choice

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.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, Orderld, 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')

Question 3
Single choice

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?

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, Orderld, 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')

Question 4
Single choice

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, Orderld, 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')

Question 5
Single choice

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

Question 6
Single choice

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 FROM (
SELECT StudentCode, Marks AS Marks, RANK() OVER(PARTITION BY SubjectCode ORDER BY Marks DESC) AS Rank FROM

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

Question 7
Single choice

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 FROM (
SELECT StudentCode, Marks AS Marks, RANK() OVER(PARTITION BY SubjectCode ORDER BY Marks DESC) AS Rank FROM
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

Question 8
Single choice

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
ADD TotalItems AS ItemslnStore + 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
ADD TotalItems AS SUM(ItemsInStore, ItemsInWarehouse)

Question 9
Single choice

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, MobileNumber)
- - 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

Question 10
Single choice

You develop a Microsoft SQL Server 2012 database that has two tables named SavingAccounts and LoanAccounts. Both tables have a column named AccountNumber of the nvarchar data type.

You use a third table named Transactions that has columns named TransactionId AccountNumber, Amount, and TransactionDate.

You need to ensure that when multiple records are inserted in the Transactions table, only the records that have a valid AccountNumber in the SavingAccounts or LoanAccounts are inserted.

Which Transact-SQL statement should you use?

A.

CREATE TRIGGER TrgValidateAccountNumber
ON Transactions
INSTEAD OF INSERT
AS
BEGIN
INSERT INTO Transactions
SELECT TransactionID,AccountNumber,Amount,TransactionDate FROM inserted WHERE AccountNumber IN
(SELECT AccountNumber FROM LoanAccounts
UNION SELECT AccountNumber FROM SavingAccounts))
END

B.

CREATE TRIGGER TrgValidateAccountNumber
ON Transactions
FOR INSERT
AS
BEGIN
INSERT INTO Transactions
SELECT TransactionID,AccountNumber,Amount,TransactionDate FROM inserted WHERE AccountNumber IN
(SELECT AccountNumber FROM LoanAccounts
UNION SELECT AccountNumber FROM SavingAccounts))
END

C.

CREATE TRIGGER TrgValidateAccountNumber

ON Transactions
INSTEAD OF INSERT
AS
BEGIN
IF EXISTS (
SELECT AccountNumber FROM inserted EXCEPT
(SELECT AccountNumber FROM LoanAccounts
UNION SELECT AccountNumber FROM SavingAccounts))
BEGIN
ROLLBACK TRAN
END
END

D.

CREATE TRIGGER TrgValidateAccountNumber
ON Transactions
FOR INSERT
AS
BEGIN
IF EXISTS (
SELECT AccountNumber FROM inserted EXCEPT
(SELECT AccountNumber FROM LoanAccounts
UNION SELECT AccountNumber FROM SavingAccounts))
BEGIN
ROLLBACK TRAN
END
END

Showing 10 of 183 questions · Unlock the full set