Querying Microsoft SQL Server 70-461Test

You administer a Microsoft SQL Server 2012 database named ContosoDB. Tables are defined as shown in the exhibit. (Administra una base de datos de Microsoft SQL Server 2012 denominada ContosoDB. Las tablas se definen como se muestra en al imagen a continuación) 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?

You administer a Microsoft SQL Server 2012 database named ContosoDb. Tables are defined as shown in the exhibit. You need to display rows from the Orders table for the Customers row having the CustomerIdvalue set to 1 in the following XML format. <Orders OrderId="1" OrderDate="2017-01-01" Amount="2.900000000000000e+001"> <Customers Name="Cliente1" Country="Ecuador"/> </Orders> <Orders OrderId="2" OrderDate="2017-01-01" Amount="2.400000000000000e+000"> <Customers Name="Cliente1" Country="Ecuador"/> </Orders> Which Transact-SQL query should you use?

You administer a Microsoft SQL Server 2012 database named ContosoDB. Tables are defined as shown in the exhibit. 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. <CUSTOMERS Name="Customer A" Country="Australia"> <ORDERS OrderID="1" OrderDate="2001-01-01" Amount="3400.00" /> <ORDERS OrderID="2" OrderDate="2002-01-01" Amount="4300.00" /> </CUSTOMERS> Which Transact-SQL query should you use?

You have a database named Sales that contains the tables as shown in the exhibit (Click the Exhibit button) You need to create a query that meets the followlng requirements: - References columns by using one-part names only. - Groups aggregates only by SalesTerritorylD, and then by ProductlD - Orders the results in descending order by SalesTerritorylD and then by ProductlD in descending order for both Part of the correct T-SQL statement has been provided in the answer area Provide the complete code SELECT SalesTerritoryID, ProductlD AVG(UnitPrice), MAX(OrderQty) MAX(DiscountAmount) FROM Sales_DetaiIs

SIMULATION You have a database named Sales that contains the tables as shown in the exhibit. (Click the Exhibit button) You need to create a query that retums a list of products from Sales.ProductCatalog. The solution must meet the following requirements: - UnitPrice must be retumed in descending order. - The query must use two-part names to reference the table. - The query must use the RANK function to calculate the results. - The query must retum the ranking Of rows in a column named PriceRank. - The list must display the columns in the order that they are defined in the table. PriceRank must appear last. Part of the correct T-SQL statement has been provided in the answer area. Provide the complete code. SELECT CatlD, CatName, ProductID, ProdName, UnitPrice, FROM Sales.ProductCatalog ORDER BY PriceRank

DRAG DROP You have a SQL Server database named CUSTOMERS. You need to sign a stored procedure named SelectCustomers in the CUSTOMERS database. Which four statements should you execute in sequence? To answer, move the appropriate statements from the list of statements to the answer area and arrange them in the correct order.

Your database contains two tables named DomesticSalesOrders and InternationalSalesOrders. Both tables contain more than 100 million rows. Each table has a Primary Key column named SalesOrderld. The data in the two tables is distinct from one another. Business users want a report that includes aggregate information about the total number of global sales and total sales amounts. You need to ensure that your query executes in the minimum possible time. Which query should you use?

You administer a Microsoft SQL Server database that has multiple tables in the Sales schema. Some users must be prevented from deleting records in any of the tables in the Sales schema. You need to manage users who are prevented from deleting records in the Sales schema. You need to achieve this goal by using the minimum amount of administrative effort. What should you do?

DRAG DROP You develop an SQL Server database. The database contains a table that is defined by the following T-SQL statements: CREATE TABLE Employees (employeeNumber INT, surName VARCHAR(IOO), givenName VARCHAR(2S), dateOfBirth DATE, workPhone VARCHAR(12)); The table contains duplicate records based on the combination of values in the surName, givenName, and dateOBirth fields. You need to remove the duplicate records. How should you complete the relevant Transact-SQL statements? To answer, drag the appropriate code segment or segments to the correct location or locations in the answer area. Each code segment may be used once, more than once, or not at all. You may need to drag the Split bar between panes or scroll to view conten WITH CTE AS ( SELECT surName, givenName, DateOfBirth, [blank_start]Row_number()[blank_end] Over ( [blank_start]PARTITION BY surName[blank_end], givenName, DateOfBirth [blank_start]ORDER BY[blank_end] (SELECT 1)) AS Ct FROM dbo.Employees) [blank_start]DELETE FROM CTE WHERE Ct > 1[blank_end]

Your database contains a table named Products that has columns named ProductID and Name_ You want to write a query that retrieves data from the Products table sorted by Name listing 15 rows at a time. You need to view rows 31 through 45. Which Transact-SQL query should you create?

DRAG DROP You need to create a stored procedure to support the following: • TRY/CATCH error handling • Transaction management Which three statements should you include in the stored procedure in sequence? To answer, move the appropriate statements from the list of statements to the answer area and arrange them in the correct order. ===================== Answer Area ===================== ==========Statemenets==============Answers============ SET XACT_ABORT ON;------------|------------1 [blank_start]BEGIN TRANSACTION;[blank_end] BEGIN TRANSACTION;------------|------------2 [blank_start]ROLLBACK TRANSACTION;[blank_end] SAVE TRANSACTION---------------|------------3 [blank_start]COMMIT TRANSACTION;[blank_end] COMMIT TRANSACTION;---------| ROLLBACK TRANSACTION;------|

You use a Microsoft Azure SQL DataBase instance_ The instance contains a table named Customers that has columns named Id, Name, and IsPriority. You need to create a view named VwPriorityCustomers that: • retums rows from Customer thathave a value of True in the IsPriority column, and • does not allow columns to be altered or dropped in the underlying table. Which Transact-SQL statement shoul you run?

You are developing a database that Will contain price informatiom You need to store the prices that include a fixed precision and a scale of Six digits. Which data type should you use?

A database named AdventureWorks contains two tables named Production.Product and Sales.SaIesOrderDetaiI. The tables contain data on the available products and a detailed order history. The Production.Product table contains the following two columns: • ProductlD • Name The Sales. SalesOrderDetail table contains the following three columns: • SalesOrderlD • ProductlD You need to create a query listing all of the products that were never ordered Which statements should you execute?

DRAG DROP A database contains tables as shown in the exhibit. (Click the Exhibit button.) Products that are discontinued are moved from the Products table to the DiscontinuedProducts table. Any orders for discontinued products are removed from the Orders table. You write the following SELECT statement to return all the discontinued products: SELECT productld FROM You need to extend the SELECT statement to include products 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 ofTransact-SQL segments to the answer area and arrange them in the correct order Exhibit ========================================Answer Area====================================== =======================SQL Statemenets========|========Answers=========================== UNION=======================================|===1.[blank_start]UNION[blank_end] EXCEPT======================================|===2.[blank_start]SELECT ProductId FROM Products[blank_end] INTERSECT====================================|===3.[blank_start]EXCEPT[blank_end] LEFT JOIN D===================================|===4.[blank_start]SELECT ProductId FROM Orders[blank_end] INNER JOIN===================================| SELECT ProductId FROM Orders===================| SELECT ProductId FROM Products=================| ON Products.ProductId = Orders.ProductId==========|

You have a stored procedure named Procedurel_ Procedurel retrieves all order ids after a specific date. The rows for Procedurel are not sorted. Procedurel has a single parameter named Parameterl_ Parameterl uses the varchar type and is configured to pass the specific date to Procedurel. A database administrator discovers that OrderDate is not being compared correctly to Parameterl after the data type of the column is changed to datetime. You need to update the SELECT statement to meet the following requirements: • Thecode must NOT use aliases_ • The code must NOT use object delimiters_ The objects called in Procedurel must be able to be resolved by all users. OrderDate must be compared to Parameterl after the data type of Parameterl is changed to datetime. Which SELECT statement should you use? You Response : [blank_start]SELECT Orders.OrderID FROM Orders[blank_end] WHERE [blank_start]Orders.OrderDate[blank_end] > [blank_start]CONVERT(datetime, @Parameter1)[blank_end]

You use Microsoft SQL Server to create a stored procedure as shown in the following code segment. (Line numbers are included for reference only) 01 CREATE PROCEDURE DeleteCandidate 02 @InputCandidateID INT; 03 AS 04 BEGIN 05 BEGIN TRANSACTION; 06 BEGIN TRY 07 DELETE HumanResources.JobCandidate 08 WHERE JobCandidateID = @ImputCandidateID; 09 INSERT INTO Audit.Log(Operation,OperationDate) 10 VALVES ('Delete',SYSDATETIME()); 11 COMMIT TRANSACTION; 12 END TRY 13 BEGIN CATCH 14 15 COMMIT TRANSACTION; 16 ELSE 17 ROLLBACK TRANSACTION; 18 END CATCH 19 END; The procedure can be caled within Other transactions. You need to ensure that when the DELETE statement from the HumanResourcesJobCandidate table succeeds, the modification is retained even if the insert into the Audit.Log table fails. Which code segment should you add to line 14?

DRAG DROP You are a Microsoft SQL Server client tools to develop a Microsoft Azure SQL Database database that supports an e-leaming application. The database consists of a Course table, a Subject table, and a CourseSubject table as shown in the exhibit. (Click the Exhibit button.) You need to write a trigger that meets the following requirements: Subjects are not physically deleted, but are marked as deleted. When a subject is deleted, the courses that offer that subject are marked as discontinued. Which four Transact-SQL segments should you use to develop the solution? To answer, move the appropriate Transact-SQL segments from the list ofTransact-SQL segments to the answer area and arrange them in the correct order.

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?

You develop a Microsoft SQL Server 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 Transactionld 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?

DRAG DROP You administer a Microsoft SQL Server database. You use an OrderDetail table that has the following definition. CREATE TABLE [dbo].[OrderDetail] ([SalesOrderID] [INT] NOT NULL, [SalesOrderDetailID] [INT] IDENTITY(1,1) NOT NULL, [CarrierTrackingNumber] [NVARCHAR] (25) NULL, [OrderQty] [SMALLINT] NOT NULL, [SpecialOfferID] [INT] NULL, [UnitPrice] [MONEY] NOT NULL); You need to create a non-clustered index on the SalesOrderID column in the OrderDetail table to include only rows that contain a value in the SpecialOfferID column. Which four Transact-SQL statements should you use? (To answer, move the appropriate statements from the list of statements to the answer area and arrange them in the correct order.) [blank_start]CREATE NONCLUSTERED[blank_end] [blank_start]INDEX FIndx_SpecialOfferID[blank_end] [blank_start]ON dbo.OrderDetail(SalesOrderID)[blank_end] [blank_start]WHERE[blank_end] [blank_start]Special OfferID IS NOT NULL[blank_end]