Querying Microsoft SQL Server 2012/2014 (070-461) Free Practice Test

Question 1

You have a database that contains the following related tables:

You create a view named OrderSummary by using the following Transact-SQL statement:

For each of the following statements, select Yes if the statement is true. Otherwise, select No.

Correct Answer:

Explanation:
The SalesOrderID column is used in a join statement and cannot be updated.
The Status column is used can be updated.
The SubTotal column is an aggregate column and cannot be updated.

Question 2

Question 3

Question 4

Your database contains tables named Products and ProductsPriceLog. The Products table contains columns named ProductCode and Price. The ProductsPriceLog table contains columns named ProductCode, OldPrice, and NewPrice.
The ProductsPriceLog table stores the previous price in the OldPrice column and the new price in the NewPrice column.
You need to increase the values in the Price column of all products in the Products table by 5 percent. You also need to log the changes to the ProductsPriceLog table.
Which Transact-SQL query should you use?

A. UPDATE Products SET Price = Price * 1.05
INSERT INTO ProductsPriceLog(ProductCode, OldPrice, NewPrice)
SELECT ProductCode, Price, Price * 1.05 FROM Products

B. UPDATE Products SET Price = Price * 1.05
OUTPUT inserted.ProductCode, deleted.Price, inserted.Price * 1.05
INTO ProductsPriceLog(ProductCode, OldPrice, NewPrice)

C. UPDATE Products SET Price = Price * 1.05
OUTPUT inserted.ProductCode, inserted.Price, deleted.Price
INTO ProductsPriceLog(ProductCode, OldPrice, NewPrice)

D. UPDATE Products SET Price = Price * 1.05
OUTPUT inserted.ProductCode, deleted.Price, inserted.Price
INTO ProductsPriceLog(ProductCode, OldPrice, NewPrice)

Correct Answer: D

Question 5

You develop a Microsoft SQL Server 2012 database. You create a view from the Orders and OrderDetails tables by using the following definition.

You need to improve the performance of the view by persisting data to disk. What should you do?

A. Create an INSTEAD OF trigger on the view.

B. Modify the view to use the WITH VIEW_METADATA clause.

C. Create an AFTER trigger on the view.

D. Create a clustered index on the view.

Correct Answer: D

Explanation: Only visible for TestSimulate members. You can sign-up / login (it's free).

Question 6

You use a Microsoft SQL Server database that contains a table. The table has records of customer orders.
Your company has three divisions that have the following names:
East
Central
West
You need to create a query that displays the following information:
* The number of sales for each product (ProductName) grouped by the division (Division) that sold the product
* A column for each division
Which Transact-SQL query should you use?

A. Option D

B. Option C

C. Option A

D. Option B

Correct Answer: A

Explanation: Only visible for TestSimulate members. You can sign-up / login (it's free).

Question 7

You administer a Microsoft SQL Server database that includes a table named Products. The Products table has columns named ProductId, ProductName, and CreatedDateTime.
The table contains a unique constraint on the combination of ProductName and CreatedDateTime.
You need to modify the Products table to meet the following requirements:
* Remove all duplicates of the Products table based on the ProductName column.
* Retain only the oldest Products row.
Which Transact-SQL query should you run?

A. Option D

B. Option C

C. Option A

D. Option B

Correct Answer: D

Explanation: Only visible for TestSimulate members. You can sign-up / login (it's free).

Question 8

You are maintaining a SQL Server database that uses the default settings. The database contains a table that is defined by the following Transact-SQL statement:

You must write a query that returns the AddressLine1, AddressLine2, and Region fields separated by carriage returns. You must return an empty string for any null values.

A. Option D

B. Option C

C. Option A

D. Option B

Correct Answer: C

Explanation: Only visible for TestSimulate members. You can sign-up / login (it's free).

Question 9

You have a view named Person.vAdditionalContactInfo that uses data from a table named Customers. The Customers table was created by running the following Transact-SQL statement:

You need to create a query that returns the first and last names of a customer from the view. If the CustomerID column does not have a value defined, the following message must be added to the query result: "Customer ID Does Not Exist." Construct the query using the following guidelines:
* Do not use aliases for column or table names.
* Display the columns in the same order as they are defined in the Customers table.
Part of the correct Transact-SQL has been provided in the answer area below. Enter the code in the answer area that resolves the problem and meets the stated goals or requirements. You can add code within code that has been provided as well as below it.

Use the 'Check Syntax' button to verify your work. Any syntax or spelling errors will be reported by line and character position.

A. 1 SELECT FirstName, LastName, CustomerID =
2 CASE
3 WHEN CustomerID IS NULL THEN "Customer ID Does Not Exist"
4 ELSE CustomerID
5 END
6 FROM Person.vAdditionalContactInfo;
7
To line 1 add: CustomerID =
To line 5 add: END

B. 1 SELECT FirstName, LastName, CustomerID =
2 CASE
3 WHEN CustomerID IS NULL THEN "Customer ID Does Not Exist"
4 ELSE CustomerID
5 END
6 FROM Person.vAdditionalContactInfo;
7
To line 1 add: CustomerID =
To line 5 add: END

Correct Answer: B

Explanation: Only visible for TestSimulate members. You can sign-up / login (it's free).

Welcome to TestSimulate

Microsoft Querying Microsoft SQL Server 2012/2014 (070-461) Free Practice Test