Download 70-461 Querying Microsoft SQL Server 2012 Exam Questions and Answers from PassLeader and more Exams Database Programming in PDF only on Docsity!
70-461 Exam Dumps 70-461 Exam Questions 70-461 PDF Dumps 70-461 VCE Dumps
Vendor: Microsoft
Exam Code: 70- 461
Exam Name: Querying Microsoft SQL Server 2012
Question 1 -- Question 20
Visit PassLeader and Download Full Version 70-461 Exam Dumps
QUESTION 1
You develop a Microsoft SQL Server 2012 server database that supports an application. The application contains a table that has the following definition: CREATE TABLE Inventory (ItemID int NOT NULL PRIMARY KEY, ItemsInStore int NOT NULL, ItemsInWarehouse int NOT NULL) You need to create a computed column that returns the sum total of the ItemsInStore and ItemsInWarehouse values for each row. Which Transact-SQL statement should you use?
A. ALTER^ TABLE^ Inventory ADD TotalItems AS ItemsInStore + ItemsInWarehouse B. ALTER^ TABLE^ Inventory ADD ItemsInStore - ItemsInWarehouse = TotalItems C. ALTER^ TABLE^ Inventory ADD TotalItems = ItemsInStore + ItemsInWarehouse D. ALTER TABLE Inventory ADD TotalItems AS SUM(ItemsInStore, ItemslnWarehouse);
Answer: A Explanation: http://technet.microsoft.com/en-us/library/ms190273.aspx
QUESTION 2 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?
70-461 Exam Dumps 70-461 Exam Questions 70-461 PDF Dumps 70-461 VCE Dumps
A. Create an INSTEAD OF trigger on the view. B. Create an AFTER trigger on the view. C. Modify the view to use the WITH VIEW_METADATA clause. D. Create a clustered index on the view.
Answer: D Explanation: http://msdn.microsoft.com/en-us/library/ms188783.aspx
QUESTION 3 You develop a database for a travel application. You need to design tables and other database objects. You create the Airline_Schedules table. You need to store the departure and arrival dates and times of flights along with time zone information. What should you do?
A. Use the CAST function. B. Use the DATE data type. C. Use the FORMAT function. D. Use an appropriate collation. E. Use a user-defined table type. F. Use the VARBINARY data type. G. Use the DATETIME data type. H. Use the DATETIME2 data type. I. Use the DATETIMEOFFSET data type. J. Use the TODATETIMEOFFSET function.
Answer: I Explanation: http://msdn.microsoft.com/en-us/library/ff848733.aspx http://msdn.microsoft.com/en-us/library/bb630289.aspx
QUESTION 4 You develop a database for a travel application. You need to design tables and other database objects. You create a stored procedure.
70-461 Exam Dumps 70-461 Exam Questions 70-461 PDF Dumps 70-461 VCE Dumps
Answer: CREATE TABLE Sales.OrderDetails ( ListPrice money not null, Quantity int not null, LineItemTotal as (ListPrice * Quantity) PERSISTED ) Explanation: http://msdn.microsoft.com/en-us/library/ms174979.aspx http://technet.microsoft.com/en-us/library/ms188300.aspx
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.
70-461 Exam Dumps 70-461 Exam Questions 70-461 PDF Dumps 70-461 VCE Dumps
- 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.
Answer: CREATE VIEW Sales.uv_CustomerFullName WITH SCHEMABINDING AS SELECT FirstName, LastName FROM Sales.Customers Explanation: http://msdn.microsoft.com/en-us/library/ms187956.aspx
QUESTION 7 You have a database that contains the tables shown in the exhibit. (Click the Exhibit button.) You need to create a query that calculates the total sales of each OrderId from the Sales.Details table. The solution must meet the following requirements:
- Use one-part names to reference columns.
- Sort the order of the results from OrderId.
- NOT depend on the default schema of a user.
70-461 Exam Dumps 70-461 Exam Questions 70-461 PDF Dumps 70-461 VCE Dumps
Answer: CREATE FUNCTION Sales.fn_OrdersByTerritory (@T int) RETURNS TABLE AS RETURN ( SELECT OrderID,OrderDate,SalesTerrirotyID,TotalDue FROM Sales.OrdersByTerritory WHERE SalesTerritoryID = @T )
QUESTION 9 You have 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 include customers who have not placed any orders. Which Transact-SQL query should you use?
A. SELECT^ CustomerName,^ OrderDate FROM Customers RIGHT OUTER JOIN Orders ON Customers.CustomerID = Orders.CustomerID B. SELECT^ CustomerName,^ CrderDate FROM Customers JOIN Orders ON Customers.CustomerID = Orders.CustomerID C. SELECT CustomerName, OrderDate FROM Customers CROSS JOIN Orders ON Customers.CustomerID = Orders.CustomerID D. SELECT^ CustomerName,^ OrderDate FROM Customers LEFT OUTER JOIN Orders ON Customers.CustomerID = Orders.CustomerID
Answer: D Explanation: http://msdn.microsoft.com/en-us/library/ms177634.aspx
QUESTION 10 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
70-461 Exam Dumps 70-461 Exam Questions 70-461 PDF Dumps 70-461 VCE Dumps
terminated and rolled back. Which Transact-SQL statement should you include at the beginning of the stored procedure?
A. SET^ XACT_ABORT^ ON
B. SET^ ARITHABORT^ ON
C. TRY
D. BEGIN
E. SET^ ARITHABORT^ OFF
F. SET^ XACT_ABORT^ OFF
Answer: A Explanation: http://msdn.microsoft.com/en-us/library/ms190306.aspx http://msdn.microsoft.com/en-us/library/ms188792.aspx
QUESTION 11 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 SalesOrderId. 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?
A. SELECT COUNT() AS NumberOfSales, SUM(SalesAmount) AS TotalSalesAmount FROM (SELECT SalesOrderId, SalesAmountFROM DomesticSalesOrders UNION ALL SELECT SalesOrderId, SalesAmountFROM InternationalSalesOrder s ) AS p B. SELECT^ COUNT()^ AS NumberOfSales, SUM(SalesAmount) AS TotalSalesAmount FROM (SELECT SalesOrderId, SalesAmountFROM DomesticSalesOrders UNION SELECT SalesOrderId, SalesAmountFROM InternationalSalesOrder s ) AS p C. SELECT^ COUNT()^ AS NumberOfSales, SUM(SalesAmount) AS TotalSalesAmountFROM DomesticSalesOrders UNION SELECT COUNT() AS NumberOfSales, SUM(SalesAmount) AS TotalSalesAmountFROM InternationalSalesOrders D. SELECT^ COUNT(*)^ AS
70-461 Exam Dumps 70-461 Exam Questions 70-461 PDF Dumps 70-461 VCE Dumps
[Document] VARBINARY(MAX) FILESTREAM NULL ) GO
Answer: A Explanation: http://msdn.microsoft.com/en-us/library/gg471497.aspx http://msdn.microsoft.com/en-us/library/ff929144.aspx
QUESTION 14 You administer a Microsoft SQL Server 2012 database that contains a table named OrderDetail. You discover that the NCI_OrderDetail_CustomerID non-clustered index is fragmented. You need to reduce fragmentation. You need to achieve this goal without taking the index offline. Which Transact-SQL batch should you use?
A. CREATE^ INDEX^ NCI_OrderDetail_CustomerI^ D ON OrderDetail.CustomerID WITH DROP EXISTING B. ALTER^ INDEX^ NCI_OrderDetail_CustomerID ON OrderDetail.CustomerID REORGANIZE C. ALTER INDEX ALL ON OrderDetail REBUILD D. ALTER INDEX NCI_OrderDetail_CustomerID ON OrderDetail.CustomerID REBUILD
Answer: B Explanation: http://msdn.microsoft.com/en-us/library/ms188388.aspx
QUESTION 15 You develop a Microsoft SQL Server 2012 database. The database is used by two web applications that access a table named Products. You want to create an object that will prevent the applications from accessing the table directly while still providing access to the required data. You need to ensure that the following requirements are met:
- Future modifications to the table definition will not affect the applications' ability to access data.
- The new object can accommodate data retrieval and data modification.
- You need to achieve this goal by using the minimum amount of changes to theexisting applications. What should you create for each application?
A. views B. table partitions C. table-valued functions D. stored procedures
Answer: A
QUESTION 16 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.
70-461 Exam Dumps 70-461 Exam Questions 70-461 PDF Dumps 70-461 VCE Dumps
Which object should you use?
A. Inline user-defined function B. Stored procedure C. Table-valued user-defined function D. Scalar user-defined function
Answer: C
QUESTION 17 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.)
70-461 Exam Dumps 70-461 Exam Questions 70-461 PDF Dumps 70-461 VCE Dumps
D. Option D E. Option E F. Option F
Answer: CF
QUESTION 18 You develop a Microsoft SQL Server 2012 database that contains a heap named OrdersHistoncal. You write the following Transact-SQL query: INSERT INTO OrdersHistorical SELECT * FROM CompletedOrders You need to optimize transaction logging and locking for the statement. Which table hint should you use?
A. HOLDLOCK
B. ROWLOCK
C. XLOCK
D. UPDLOCK
E. TABLOCK
Answer: E Explanation: http://technet.microsoft.com/en-us/library/ms189857.aspx http://msdn.microsoft.com/en-us/library/ms187373.aspx
QUESTION 19 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.)
70-461 Exam Dumps 70-461 Exam Questions 70-461 PDF Dumps 70-461 VCE Dumps
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 rows for SalesOrderHeader by using an unexpected index on SalesOrderDetail. You need to improve the performance of the query. What should you do?
A. Use a FORCESCAN hint in the query. B. Add a clustered index on SalesOrderId in SalesOrderHeader. C. Use a FORCESEEK hint in the query. D. Update statistics on SalesOrderId on both tables.
Answer: D Explanation: http://msdn.microsoft.com/en-us/library/ms187348.aspx
