You develop a Microsoft SQL Server 2012 database that contains tables named Customers and Orders. The tables are related by a column named CustomerId . You need to create a query that meets the following requirements:
Returns the CustomerName for all customers and the OrderDate for any orders that they have placed.
Results must not include customers who have not placed any orders. Which Transact-SQL query should you use?

• A. SELECT CustomerName, OrderDate FROM Customers JOIN Orders ON Customers.CustomerId = Orders.CustomerId
• B. SELECT CustomerName, OrderDate FROM Customers LEFT OUTER JOIN Orders ON Customers.CuscomerlD = Orders.CustomerId
• C. SELECT CustomerName, OrderDate FROM Customers CROSS JOIN Orders ON Customers.CustomerId = Orders.CustomerId
• D. SELECT CustomerName, OrderDate FROM Customers RIGHT OUTER JOIN Orders ON Customers.CustomerID = Orders.CustomerId

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

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
  IF EXISTS (
  SELECT AccountNumber FROM inserted EXCEPT
  (SELECT AccountNumber FROM LoanAccounts
  UNION SELECT AccountNumber FROM SavingAccounts))
  BEGIN
  ROLLBACK TRAN
  END
  END
• C. 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
• D. 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

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

You use Microsoft SQL Server 2012 to develop a database application. You create a table by using the following definition:
CREATE TABLE Prices (
PriceId int IDENTITY(1,1) PRIMARY KEY,
ActualPrice NUMERIC(16,9),
PredictedPrice NUMERIC(16,9) )
You need to create a computed column based on a user-defined function named udf_price_index. You also need to ensure that the column supports an index. Which three Transact-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.)
Build List and Reorder:

You have a view that was created by using the following code:

You need to create an inline table-valued function named Sales.fn_OrdersByTerritory, which must meet the following requirements:
Accept the @T integer parameter.
Use one-part names to reference columns.
Filter the query results by SalesTerritoryID.
Return the columns in the same order as the order used in OrdersByTerritoryView.
Which code segment should you use?
To answer, type the correct code in the answer area.

• A. CREATE FUNCTION Sales.fn_OrdersByTerritory (@T int) RETURNS TABLE AS RETURN ( SELECT OrderID,OrderDate,SalesTerrirotyID,TotalDue FROM Sales.OrdersByTerritory WHERE SalesTerritoryID = @T )

--Burgos -NO
CREATE FUNCTION Sales.fn_OrdersByTerritory (@T int) RETURNS TABLE AS RETURN (
SELECT OrderID,OrderDate,SalesTerrirotyID,TotalDue
FROM Sales.OrdersByTerritory
WHERE SalesTerritoryID = @T
)

You are writing a set of queries against a FILESTREAM-enabled database. You create a stored procedure that will update multiple tables within a transaction. You need to ensure that if the stored procedure raises a run-time error, the entire transaction is terminated and rolled back. Which Transact-SQL statement should you include at the beginning of the stored procedure?

• A. SET TRANSACTION ISOLATION LEVEL SERIALIZABLE
• B. SET TRANSACTION ISOLATION LEVEL SNAPSHOT
• C. SET IMPLICIT TRANSACTIONS OFF
• D. SET XACT_ABORT OFF
• E. SET IMPLICIT_TRANSACTIONS ON
• F. SET XACT_ABORT ON

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