Get real exam questions for 70-461 Querying Microsoft SQL Server 2012. 100% Free.
Q1. You are developing a database application by using Microsoft SQL Server 2012. An application that uses a database begins to run slowly. You discover that during reads, the transaction experiences blocking from concurrent updates. You need to ensure that throughout the transaction the data maintains the original version. What should you do? A. Add a HASH hint to the query. B. Add a LOOP h
Q1. Your database contains a table named Purchases. The table includes a DATETIME column named PurchaseTime that stores the date and time each purchase is made. There is a non-clustered index on the PurchaseTime column. The business team wants a report that displays the total number of purchases made on the current day. You need to write a query that will return the correct results in the most e
Q1. CORRECT TEXT You have a database named Sales that contains the tables shown in the exhibit. (Click the Exhibit button.) You have an application named Appl. You have a parameter named @Count that uses the int data type. App1 is configured to pass @Count to a stored procedure. You need to create a stored procedure named usp_Customers for App1 that returns only the number of rows specified b
Q1. You administer a Microsoft SQL Server 2012 database. The database contains a table named Employee. Part of the Employee table is shown in the exhibit. (Click the Exhibit button.) Unless stated above, no columns in the Employee table reference other tables. Confidential information about the employees is stored in a separate table named EmployeeData. One record exists within EmployeeData for
Q1. DRAG DROP You administer a Microsoft SQL Server 2012 database. You use an OrderDetail table that has the following definition: 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 t
Q1. You are a database developer of a Microsoft SQL Server 2012 database. You are designing a table that will store Customer data from different sources. The table will include a column that contains the CustomerID from the source system and a column that contains the SourceID. A sample of this data is as shown in the following table. You need to ensure that the table has no duplicate CustomerI
Q1. DRAG DROP You use Microsoft SQL Server 2012 to develop a database application. You create two tables by using the following table definitions. Which six 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.) View AnswerAnswer: Q2. CORRECT TEXT You have an XML schema col
Q1. You administer a Microsoft SQL Server 2012 database. The database contains a table named Employee. Part of the Employee table is shown in the exhibit. (Click the Exhibit button.) Unless stated above, no columns in the Employee table reference other tables. Confidential information about the employees is stored in a separate table named EmployeeData. One record exists within EmployeeData for
Q1. You are a database developer of a Microsoft SQL Server 2012 database. The database contains a table named Customers that has the following definition: You need to ensure that the CustomerId column in the Orders table contains only values that exist in the CustomerId column of the Customer table. Which Transact-SQL statement should you use? A. ALTER TABLE Orders ADD CONSTRAINT FX_Orders_Cu
Q1. You develop a Microsoft SQL Server 2012 database. You need to create a batch process that meets the following requirements: . Returns a result set based on supplied parameters. . Enables the returned result set to perform a join with a table. Which object should you use? A. Inline user-defined function B. Stored procedure C. Table-valued user-defined function D. Scalar user-defined fun
Q1. 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 Cu
Q1. You use a Microsoft SQL Server 2012 database that contains two tables named SalesOrderHeader and SalesOrderDetail. The indexes on the tables are as shown in the exhibit. (Click the Exhibit button.) You write the following Transact-SQL query: You discover that the performance of the query is slow. Analysis of the query plan shows table scans where the estimated rows do not match the actual ro
Q1. DRAG DROP You want to add a new GUID column named BookGUID to a table named dbo.Book that already contains data. BookGUID will have a constraint to ensure that it always has a value when new rows are inserted into dbo.Book. You need to ensure that the new column is assigned a GUID for existing rows. Which four Transact-SQL statements should you use? (To answer, move the appropriate SQL sta