70-461 Certification Guide and How to Pass Exam on Querying Microsoft SQL Server 2012/2014

Querying Microsoft SQL Server 2012/2014

70-461 PDF

What is Querying Microsoft SQL Server 2012/2014 Certification? Querying Query ing Mic Micro roso soft ft SQ SQL L Se Serve rverr 20 2012 12/20 /2014 14 cer certif tifica icatio tion n qu quest estion ions s and and exam exam summ summar ary y help helps s you you to get focus cused on exam. This guide also helps you to be on 700-46 461 1 ex exam am trac track k to get get cert certif ifie ied d with good score in score in final exam.

70-461 - Querying Microsoft SQL Server 2012/2014 Certification Summary Exam Name


Exam Code

70-461

Exam Duration

120 minutes

Exam Questions

45 to 55 (Since Microsoft does not pub lish this information, the number of exam questions may change without notice)

Passing Score

700 out of 1000

Exam Price

$165 (USD)

Training

20461C: Querying Microsoft SQL Server (fi ve days)

Books

Training Kit (Exam 70-461): Querying Microsoft SQL Server 2012

Exam Registration


Sample Questions

Querying Microsoft SQL Server Certification Sample Question

Practice Exam

Querying Microsoft SQL Server Certification Practice Exam

70-461 Querying Microsoft SQL Server 2012/2014 Certification Questions: 70-4 70-461 61 Quer Queryi ying ng Micr Micros osof oftt SQL SQL Serv Server er 2012 2012/2 /201 014 4 Cert Certif ific icat atio ion n Samp Sample le ques questi tion ons s are are prov provid ided ed for for each each exam exam to illu illust stra rate te the the vari variou ous s ques questi tion on type types s that that may may appe appear ar.. For For some some cred creden enti tial als, s, a practice exam is also available.

Querying Microsoft SQL Server 2012/2014 Questions - 70-461

1


70-461 PDF

Q 1: Your database contains two tables named DomesticSalesOrders and Int Interna rnatio tionalSa lSalesO lesOrd rde ers. rs. Both tab tables les contain tain more tha than 100 mill millio ion n row rows. Each tab 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? Options: A. SELECT COUNT(*) AS NumberOfSales, NumberOfSales, SUM(SalesAmount) SUM(SalesAmount) AS TotalSalesAmount TotalSalesAmount FROM Domest DomesticS icSale alesO sOrde rders rs UNION UNION SELE SELECT CT COUNT COUNT(*) (*) AS Numbe NumberOf rOfSa Sales les,, SUM(Sa SUM(Sales lesAm Amou ount) nt) AS TotalSalesAmount FROM InternationalSalesOrders B. SELE SELECT CT COUN COUNT( T(*) *) AS Numb Number erOf OfSa Sale les, s, SUM( SUM(Sa Sale lesA sAmo moun unt) t) AS Tota TotalS lSal ales esAm Amou ount nt FROM FROM ( SELECT SalesO lesOrd rde erId rId, SalesA lesAm mount FROM ROM Domes mestic ticSalesO lesOrd rde ers UNIO UNION N ALL SEL SELECT SalesOrderId, SalesAmount FROM InternationalSalesOrders ) AS p C. SELE SELECT CT COUNT( COUNT(*) *) AS Numbe NumberOf rOfSa Sales les,, SUM(Sa SUM(Sales lesAm Amoun ount) t) AS TotalS TotalSale alesAm sAmou ount nt FROM FROM ( SELECT SalesOrderId, SalesAmount FROM DomesticSalesOrders UNION SELECT SalesOrderId, SalesAmount FROM InternationalSalesOrders ) AS p D. SELE SELECT CT COUN COUNT( T(*) *) AS Numb Number erOf OfSa Sale les, s, SUM( SUM(Sa Sale lesA sAmo moun unt) t) AS Tota TotalS lSal ales esAm Amou ount nt FROM FROM DomesticSalesOrders UNION ALL SELECT COUNT(*) AS NumberOfSales, SUM(SalesAmount) AS TotalSalesAmount FROM InternationalSalesOrders Q 2: Your Your data databa base se cont contai ains ns tabl tables es name named d Prod Produc ucts ts and and Prod Produc ucts tsPr Pric iceL eLog og.. The The Prod Produc ucts ts tabl table e contains columns named ProductCode and Price. The ProductsPriceLog table contains columns named ProductCode, OldPrice, and NewPrice. The The Prod Produc ucts tsPr Pric iceL eLog og tabl table e stor stores es the the prev previo ious us pric price e in the the OldP OldPri rice ce colu column mn and and the the new new pric price e in the NewPrice column. You need to increase the values in the Price column of all products in the Products table by 5 percent. You also need to log the changes to the ProductsPriceLog table. Which Transact-SQL query should you use? Options: A. DATE Products Products SET SET Price = Price * 1.05 1.05 OUTPUT inserted.ProductCode, deleted.Price, ProductsPriceLog(ProductCode, OldPrice, NewPrice)

inserted.Price

INTO

B. UPDATE Products SET Price = Price * 1.05


2


70-461 PDF

OUTPUT inserted.ProductCode, inserted.Price, ProductsPriceLog(ProductCode, OldPrice, NewPrice) C. UPDATE Products SET Price = Price * 1.05 OUTPUT inserted.ProductCode, deleted.Price, ProductsPriceLog(ProductCode, OldPrice, NewPrice)

deleted.Price

inserted.Price

*

INTO

1.05

INTO

D. UPDATE Products SET Price = Price * 1.05 INSER INSERT T INTO INTO Produ Products ctsPri PriceL ceLog og(Pr (Prod oduct uctCod Code, e, OldPri OldPrice, ce, NewPri NewPrice ce)) SELE SELECT CT Produ ProductC ctCod ode, e, Price, Price * 1.05 FROM Products Q 3: Your database contains a table named SalesOrders. The table includes a DATETIME column named OrderTime that stores the date and time each order is placed. There is a nonclustered index on the OrderTime column. The The busi busine ness ss team team want wants s a repo report rt that that disp displa lays ys the the tota totall numb number er of orde orders rs plac placed ed on the the curr curren entt day. You need to write a query that will return the correct results in the most efficient manner. Which Transact-SQL query should you use? Options: A. SELECT SELECT COUNT(*) FROM FROM SalesOrders SalesOrders WHERE WHERE OrderTime OrderTime = GETDATE() GETDATE() B. SELE SELECT CT COUN COUNT( T(*) *) FROM FROM Sale SalesO sOrd rder ers s WHER WHERE E CONV CONVER ERT( T(VA VARC RCHA HAR, R, Orde OrderT rTim ime, e, 112) 112) = CONVERT(VARCHAR, GETDATE(), 112)) C. SELECT COUNT(*) FROM SalesOrders WHERE OrderTime >= CONVERT(DATE, GETDATE()) AND OrderTime < DATEADD(DAY, 1, CONVERT(DATE, GETDATE())) D. SELECT GETDATE())

COUNT(*) FROM SalesOrders WHERE OrderTime = CONVERT(DATE,

Q 4: 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. Ther There e is a nonc nonclu lust ster ered ed inde index x on the the Purc Purcha hase seTi Time me colu column mn.. The The busi busine ness ss team team want wants s a repo report rt that displays the total number of purchases made on the current day. You need to write rite a query that will ill retu return rn the the corr corre ect resu result lts s in the most effic ficient ient man manner. Whic Which h Transact-SQL query should you use? Options: A. SELECT COUNT(*) FROM Purchases WHERE PurchaseTime PurchaseTime = CONVERT(DATE, CONVERT(DATE, GETDATE()) B. SELECT COUNT(*) FROM Purchases WHERE PurchaseTime = GETDATE()


3


70-461 PDF

C. SELE SELECT CT COUNT( COUNT(*) *) FROM FROM Purch Purchas ases es WHERE WHERE CONVE CONVERT( RT(VA VARCH RCHAR, AR, Purch Purchase aseTim Time, e, 112) 112) = CONVERT(VARCHAR, GETDATE(), 112) D. SELECT COUNT(*) FROM Purchases WHERE PurchaseTime >= CONVERT(DATE, GETDATE()) AND PurchaseTime < DATEADD(DAY, 1, CONVERT(DATE, GETDATE())) Q 5: Your database contains a table named Customer that has columns named CustomerID and Name. You want to write a query that retrieves data from the Customer table sorted by Name listing 20 rows at a time. You need to view rows 41 through 60. Which Transact-SQL query should you create? /files/sas-exam.com/files/user22601/70-461_131.png Options: A. Option D B. Option B C. Option A D. Option C Q 6: Your application contains a stored procedure for each country. Each stored procedure accepts an employee identification number through the @EmpID parameter. You need to build ild a sin single proc rocess for for each employe loyee e that will ill exec xecute ute the the approp ropria riate sto stored red procedure based on the country of residence. Which approach should you use? Options: A. A SELECT SELECT statement statement that that includes CASE B. Cursor C. BULK INSERT D. View E. A user-defined function Q 7: You use Microsoft SQL Server 2012 to write code for a transaction that contains several sta statem tements. ts. There is hig high con contentio tion bet betwee ween rea readers and writ write ers on severa verall tab tables les used sed by your transaction. You need to minimize the use of the tempdb space.


4


70-461 PDF

You also lso need to prev revent rea reading ing querie ries fro from bloc lockin king writ writin ing g querie ries. Which ich iso isolat lation ion lev level should you use? Options: A. SERIALIZABLE SERIALIZABLE B. SNAPSHOT C. READ COMMITTED SNAPSHOT D. REPEATABLE READ Q 8: You use Mic Microso rosofft SQL SQL Serve rver 2012 to develo velop p a database applica licattion ion. Your applic licatio ation n sen sends data to an NVA NVARCHA CHAR(M R(MAX) vari varia able named @v @va ar. You need to writ write e a Tra Transac sact-S t-SQL statement that will find out the success of a cast to a decimal (36,9). Which code segment should you use? Options: A. SELECT SELECT IF(TRY_PARSE(@va IF(TRY_PARSE(@varr AS decimal(36,9)) decimal(36,9)) IS NULL, 'True', 'True', 'False' ) AS BadCast BadCast B. SELECT CASE WHE WHEN con convert vert (de (decima imal(3 l(36,9), @v @va ar) IS NULL ULL THE THEN 'Tru 'True e' ELSE 'Fals False e' END AS BadCast C. TRY( TRY( SELE SELECT CT conv conver ertt (dec (decim imal al(3 (36, 6,9) 9),, @v @var ar)) SELE SELECT CT 'Tru 'True' e' As BadC BadCas astt ) CATC CATCH( H( SELE SELECT CT 'False' As BadCast ) D. BEGIN TRY SELECT convert (decimal(36,9), @var) as Value, 'True' As BadCast END TRY BEGIN CATCH SELECT convert (decimal(36,9), @var) as Value, 'False' As BadCast END CATCH Q 9: You use Microsoft SQL Server 2012 to develop a database application. You need to impl implem emen entt a comp comput uted ed colu column mn that that refe refere renc nces es a look lookup up tabl table e by usin using g an INNE INNER R JO JOIN IN agai agains nstt another table. What should you do? Options: A. Reference Reference a user-defined user-defined function within the computed computed column. B. Create a BEFORE trigger that maintains the state of the computed column. C. Add a default constraint to the computed column that implements hard-coded values. D. Add a default constraint to the computed column that implements hard-coded CASE statements. Q 10: You use Microsoft SQL Server 2012 to develop a database application. You need to create an object that meets the following requirements:


5


70-461 PDF

- Takes an input parameter - Returns a table of values - Can be referenced within a view Which object should you use? Options: A. inline table-value table-valued d function function B. user-defined data type C. stored procedure D. scalar-valued function

Answers: Answers : Question: 1 Question: 3 Question: 5 Question: 7 Question: 9

Answer:B Answer:C Answer:B Answer:C Answer:A

Question: 2 Question: 4 Question: 6 Question: 8 Question: 10

Answer:A Answer:D Answer:E Answer:A Answer:A

Querying Microsoft SQL Server 2012/2014 Certification 70-461 Exam Syllabus: Objective

Details (get more detail on Querying Microsoft SQL Server 2012/2014 Certification Questions )

Create database objects (20–25%)

1. Create and alter tables using T-SQL syntax (simple statements) - Create tables without using the built in tools; ALTER; DROP; ALTER COLUMN; CREATE 2. Create and alter views (simple statements)


6


70-461 PDF

- Create indexed views; create views without using the built in tools; CREATE, ALTER, DROP 3. Design views - Ensure code non regression by keeping consistent signature for procedure, views and function (interfaces); security implications 4. Create and modify constraints (simple statements) - Create constraints on tables; define constraints; unique constraints; default constraints; primary and foreign key constraints 5. Create and alter DML triggers - Inserted and deleted tables; nested triggers; types of triggers; update functions; handle multiple rows in a session; performance implications of triggers Work with data (25–30%)

1. Query data by using SELECT statements - Use the ranking function to select top(X) rows for multiple categories in a single query; write and perform queries efficiently using the new (SQL 2005/8->) code items such as synonyms, and joins (except, intersect); implement logic which uses dynamic dynamic SQL and system metadata; write efficient, technically complex SQL queries, including all types of joins versus the use of derived tables; determine what code may or may not execute based on the tables provided; given a table with constraints, determine which statement set would load a table; use and understand different data access technologies; case versus isnull versus coalesce 2. Implement sub-queries - Identify problematic elements in query plans; pivot and unpivot; apply operator; cte statement; with statement 3. Implement data types - Use appropriate data; understand the uses and limitations of each data type; impact of GUID (newid, newsequentialid) on database performance, when to use what data type for columns 4. Implement aggregate queries - New analytic functions; grouping sets; spatial aggregates; apply ranking functions 5. Query and manage XML data - Understand XML datatypes and their schemas and interop w/, limitations and restrictions; implement XML schemas and handling


7


70-461 PDF

of XML data; XML data: how to handle it in SQL Server and when and when not to use it, including XML namespaces; import and export XML; XML indexing Modify data (20–25%)

1. Create and alter stored procedures (simple statements) - Write a stored procedure to meet a given set of requirements; branching logic; create stored procedures and other programmatic objects; techniques for developing stored procedures; different types ofstoreproc result; create stored procedure for data access layer; program stored procedures, triggers, functions with T-SQL 2. Modify data by using INSERT, UPDATE, and DELETE statements - Given a set of code with defaults, constraints, and triggers, determine the output of a set of DDL; know which SQL statements are best to solve common requirements; use output statement 3. Combine datasets - Difference between UNION and UNION all; case versus isnull versus coalesce; modify data by using MERGE statements 4. Work with functions - Understand deterministic, non-deterministic functions; scalar and table values; apply built-in scalar functions; create and alter user-defined functions (UDFs)

Troubleshoot and optimize (25–30%)

1. Optimize queries - Understand statistics; read query plans; plan guides; DMVs; hints; statistics IO; dynamic vs. parameterized queries; describe the different join types (HASH, MERGE, LOOP) and describe the scenarios they would be used in 2. Manage transactions - Mark a transaction; understand begin tran, commit, and rollback; implicit vs explicit transactions; isolation levels; scope and type of locks;trancount 3. Evaluate the use of row-based operations vs. set-based operations - When to use cursors; impact of scalar UDFs; combine multiple DML operations 4. Implement error handling - Implement try/catch/throw; use set based rather than row based logic; transaction management


8


70-461 PDF

How to Register for Querying Microsoft SQL Server 2012/2014 Certification Exam? Registration Registratio n Options: ●

Visit Site for Registration


9

70-461 Certification Guide and How to Pass Exam on Querying Microsoft SQL Server 2012/2014

Recommend Documents