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

Question 1

You have a database that contains the tables shown in the exhibit. (Click the Exhibit button.)

You deploy a new server that has SQL Server 2012 installed. You need to create a table named Sales.OrderDetails on the new server. Sales.OrderDetails must meet the following requirements:
* Write the results to a disk.
* Contain a new column named LineItemTotal that stores the product of ListPrice and Quantity for each row.
* The code must NOT use any object delimiters.
The solution must ensure that LineItemTotal is stored as the last column in the table. Which code segment should you use?

A. CREATE TABLE Sales.OrderDetails (
Quantity int not null,
LineItemTotal as (ListPrice * Quantity) PERSISTED)

B. CREATE TABLE Sales.OrderDetails (
ListPrice money not null,
Quantity int not null,
LineItemTotal as (ListPrice * Quantity) PERSISTED)

Correct Answer: B

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

Question 2

A database contains tables as shown in the exhibit. (Click the Exhibit button.) Customer who are inactive are moved from the Customers table to the InactiveCustomers table. Any orders for inactive customers are removed from the Orders table.
You write the following SELECT statement to return all the inactive customers:
SELECT CustomerId FROM InactiveCustomers
You need to extend the SELECT statement to include customers who do not have any orders.
Which four Transact-SQL segments should you use to develop the solution? To answer, move the appropriate Transact-SQL segments from the list of Transact-SQL segments to the answer area and arrange them in the correct order.
Exhibit

Correct Answer:

Explanation:
EXCEPT returns distinct rows from the left input query that aren't output by the right input query.
References: https://docs.microsoft.com/en-us/sql/t-sql/language-elements/set-operators-except-and-intersect-transact-sql?view=sql-server-2017

Question 3

You are developer for a Microsoft Azure SQL Database instance.
You are creating a new stored procedure. The procedure must perform the following tasks in this order:
* 1. Update a table named OrderHistory.
* 2. Delete rows from a table named Orders.
* 3. Delete rows from a table named Customers.
* 4. Insert rows into a table named ProcessHistory.
You need to ensure that the procedure meets the following requirements:
* If either DELETE operation fails, the rest of operation must continue.
* If either the UPDATE operation or the INSERT operation fails, the whole procedure should fail and no changes should be retained.
Which four Transact-SQL segments should you use to develop the solution? To answer, move the appropriate Transact-SQL segments from the list of Transact-SQL segments to the answer area and arrange them in the correct order.

Correct Answer:

Explanation:
When SET XACT_ABORT is ON, if a Transact-SQL statement raises a run-time error, the entire transaction is terminated and rolled back.
When SET XACT_ABORT is OFF, in some cases only the Transact-SQL statement that raised the error is rolled back and the transaction continues processing.
References: https://docs.microsoft.com/en-us/sql/t-sql/statements/set-xact-abort-transact-sql?view=sql-server-2017

Question 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 CustomerId value set to 1 in the following XML format.

Which Transact-SQL query should you use?

A. 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')

B. SELECT OrderId, OrderDate, Amount, Name, Country FROM Orders
INNER JOIN Customers ON Orders.CustomerId - Customers.CustomerId
WHERE Customers.CustomerId = 1
FOR XML AUTO, ELEMENTS

C. SELECT OrderId, OrderDate, Amount, Name, Country FROM Orders
INNER JOIN Customers ON Orders.CustomerId = Customers.CustomerId
WHERE Customers.CustomerId = 1
FOR XML RAW, ELEMENTS

D. SELECT OrderId, OrderDate, Amount, Name, Country FROM Orders
INNER JOIN Customers ON Orders.CustomerId = Customers.CustomerId
WHERE Customers.CustomerId = 1
FOR XML RAW

E. SELECT OrderId, OrderDate, Amount, Name, Country FROM Orders
INNER JOIN Customers ON Orders.CustomerId = Customers.CustomerId
WHERE Customers.CustomerId = 1
FOR XML AUTO

F. 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')

G. SELECT Name, Country, OrderId, OrderDate, Amount FROM Orders
INNER JOIN Customers ON Orders.CustomerId = Customers.CustomerId
WHERE Customers.CustomerId = 1
FOR XML AUTO

H. SELECT Name, Country, OrderId, OrderDate, Amount FROM Orders
INNER JOIN Customers ON Orders.CustomerId = Customers.CustomerId
WHERE Customers.CustomerId = 1
FOR XML AUTO, ELEMENTS

Correct Answer: A

Question 5

You develop a Microsoft SQL Server 2012 database.
You need to create and call a stored procedure that meets the following requirements:
* Accepts a single input parameter for CustomerID.
* Returns a single integer to the calling application.
Which Transact-SQL statement or statements should you use? (Each correct answer presents part of the solution. Choose all that apply.)

A. EXECUTE dbo.GetCustomerRating 1745

B. CREATE PROCEDURE dbo.GetCustomerRating
@CustomerID INT,
@CustomerRating INT OUTPUT
AS
SET NOCOUNT ON
SELECT @Result = CustomerOrders/CustomerValue
FROM Customers
WHERE CustomerID = @CustomerID
RETURN @Result
GO

C. DECLARE @CustomerRatingByCustomer INT
DECLARE @Result INT
EXECUTE @Result = dbo.GetCustomerRating
1745,
@CustomerRatingByCustomer

D. CREATE PROCEDURE dbo.GetCustomerRating
@CustomerID INT
AS
DECLARE @Result INT
SET NOCOUNT ON
SELECT @Result = CustomerOrders/CustomerValue
FROM Customers
WHERE CustomerID = @CustomerID
RETURNS @Result
GO

E. DECLARE @CustomerRatingByCustomer INT
EXECUTE dbo.GetCustomerRating
@CustomerID = 1745,
@CustomerRating = @CustomerRatingByCustomer OUTPUT

F. CREATE PROCEDURE dbo.GetCustomerRating
@CustomerID INT,
@CustomerRating INT OUTPUT
AS
SET NOCOUNT ON
SELECT @CustomerRating = CustomerOrders/CustomerValue
FROM Customers
WHERE CustomerID = @CustomerID
RETURN
GO

Correct Answer: E,F

Question 6

You have a database that contains the tables shown in the exhibit. (Click the Exhibit button.)

You need to create a view named uv_CustomerFullName to meet the following requirements:
* The code must NOT include object delimiters.
* The view must be created in the Sales schema.
* Columns must only be referenced by using one-part names.
* The view must return the first name and the last name of all customers.
* The view must prevent the underlying structure of the customer table from being changed.
* The view must be able to resolve all referenced objects, regardless of the user's default schema.
Which code segment should you use?
To answer, type the correct code in the answer area.

A. CREATE VIEW Sales.uv_CustomerFullName
WITH SCHEMABINDING
AS
SELECT FirstName, LastName
FROM Sales.Customers

B. CREATE VIEW Sales.uv_CustomerFullName
WITH SCHEMABINDING
AS
SELECT FirstName, LastName

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

B. SELECT Name, Country, OrderId, OrderDate, Amount
FROM Orders INNER JOIN Customers ON Orders.CustomerId= Customers.CustomerId WHERE Customers.CustomerId = 1 FOR XML AUTO, ELEMENTS

C. SELECT Name, Country, OrderId, OrderDate, Amount
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 RAW, ELEMENTS

E. SELECT OrderId, OrderDate, Amount, Name, Country
FROM Orders INNER JOIN Customers ON Orders.CustomerId = Customers.CustomerId WHERE Customers.CustomerId = 1 FOR XML AUTO

F. SELECT OrderId, OrderDate, Amount, Name, Country
FROM Orders INNER JOIN Customers ON Orders.CustomerId = Customers.CustomerId WHERE Customers.CustomerId = 1 FOR XML AUTO, ELEMENTS

G. SELECT OrderId, OrderDate, Amount, Name, Country
FROM Orders INNER JOIN Customers ON Orders.CustomerId = Customers.CustomerId WHERE Customers.CustomerId = 1 FOR XML RAW

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

Correct Answer: E

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

Question 8

Question 9

You administer a Microsoft SQL Server 2012 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 newest Products row.
Which Transact-SQL query should you use?

A. WITH CTEDupRecords
AS
(
SELECT MIN(CreatedDateTime) AS CreatedDateTime, ProductName
FROM Products
GROUP BY ProductName
)
DELETE p
FROM Products p
JOIN CTEDupRecords cte ON
p.ProductName = cte.ProductName

B. WITH CTEDupRecords
AS
(
SELECT MAX(CreatedDateTime) AS CreatedDateTime, ProductName
FROM Products
GROUP BY ProductName
HAVING COUNT(*) > 1
)
DELETE p
FROM Products p
JOIN CTEDupRecords cte ON
p.ProductName = cte.ProductName

C. WITH CTEDupRecords
AS
(
SELECT MAX(CreatedDateTime) AS CreatedDateTime, ProductName
FROM Products
GROUP BY ProductName
HAVING COUNT(*) > 1
)
DELETE p
FROM Products p
JOIN CTEDupRecords cte ON
cte.ProductName = p.ProductName
AND cte.CreatedDateTime > p.CreatedDateTime

D. WITH CTEDupRecords
AS
(
SELECT MAX(CreatedDateTime) AS CreatedDateTime, ProductName
FROM Products
GROUP BY ProductName
HAVING COUNT(*) > 1
)
DELETE p
FROM Products p
JOIN CTEDupRecords cte ON
p.ProductName = cte.ProductName
AND p.CreatedDateTime > cte.CreatedDateTime

Correct Answer: C

Question 10

You have a database that contains a table named Customer. The customer table contains a column named LastName that has a column definition of varchar(50).
An application named App1 reads from the table frequently.
You need to change the column definition to nvarchar(100). The solution must minimize the amount of time it takes for App1 to read the data.
Which statement should you execute?

A. Option D

B. Option C

C. Option A

D. Option B

Correct Answer: B

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

Question 11

You administer a Microsoft SQL Server 2012 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 newest Products row.
Which Transact-SQL query should you use?

A. WITH CTEDupRecords
AS
(
SELECT MIN(CreatedDateTime) AS CreatedDateTime, ProductName
FROM Products
GROUP BY ProductName
HAVING COUNT(*) > 1
)
DELETE p
FROM Products p
JOIN CTEDupRecords cte ON
cte.ProductName = p.ProductName
AND cte.CreatedDateTime >
p.CreatedDateTime

B. WITH CTEDupRecords
AS
(
SELECT MAX(CreatedDateTime) AS CreatedDateTime, ProductName
FROM Products
GROUP BY ProductName
HAVING COUNT(*) > 1
)
DELETE p
FROM Products p
JOIN CTEDupRecords cte ON
p.ProductName = cte.ProductName
AND p.CreatedDateTime <
cte.CreatedDateTime

C. WITH CTEDupRecords
AS
(
SELECT MIN(CreatedDateTime) AS CreatedDateTime, ProductName
FROM Products
GROUP BY ProductName
)
DELETE p
FROM Products p
JOIN CTEDupRecords cte ON
p.ProductName = cte.ProductName

D. WITH CTEDupRecords
AS
(
SELECT MAX(CreatedDateTime) AS CreatedDateTime, ProductName
FROM Products
GROUP BY ProductName
HAVING COUNT(*) > 1
)
DELETE p
FROM Products p
JOIN CTEDupRecords cte ON
p.ProductName = cte.ProductName

Correct Answer: B

Question 12

You develop a Microsoft SQL Server database that contains tables as shown in the exhibit. (Click the Exhibit button.)

You need to retrieve a list of clients for whom there is no corresponding information in the Projects table.
Which Transact-SQL statements should you run?
A)