Microsoft 70-461 Sample Questions

QUESTION NO: 5

You are a database developer at an independent software vendor. You create stored procedures that contain proprietary code.
You need to protect the code from being viewed by your customers.
Which stored procedure option should you use?

A. ENCRYPTBYKEY
B. ENCRYPTION
C. ENCRYPTBYPASSPHRASE
D. ENCRYPTBYCERT

Answer: B

Read More

Microsoft 70-461 Sample Questions

QUESTION NO: 4

You work as a database developer at ABC.com. You are developing a SQL server 2012 database
for ABC.com's e-Commerce application. The application allows ABC.com employees from
different regions to execute a store procedure based on their location.
The location of the ABC.com employees are determined by an input parameter named @location.
You want to develop a process that will execute the correct stored procedure for every ABC.com employee based on their location.

How would you accomplish this?

A. You should make use of a client cursor.
B. You should make use of a static cursor.
C. You should make use of a forward-only cursor.
D. You should make use of a dynamic cursor.
E. You should make use of a keyset cursor.

Answer: E

Read More

Microsoft 70-461 Sample Question

Question No:3

You work as a database developer at ABC.com. ABC.com has a SQL Server 2012 database
named SalesDB that has a table named WeeklySales. The WeeklySales table records the sales amount for each of ABC.com's 20 sales representitives.
You need to write a Transact-SQL query that ranks the sales representatives by the average sales amount for the past year. You want the sales representatives with the same average sales amount to have the same rank with the subsequent rank being skipped.

Which ranking function should you use?

A. The RANK( ) OVER function.
B. The NTILE( ) OVER function
C. The DENSE_RANK( ) OVER function
D. The ROW_NUMBER( ) OVER function
E. The FORMAT function.

Answer: C

Read More

Microsoft 70-461 Sample Question

Question No:2 CORRECT TEXT

You are employed as a SQL Server 2012 database developer at ABC.com. ABC.com has a
database named HRDB with tables named Staff and SalaryHistory. The Staff and SalaryHistory
tables were created using the following Transact-SQL code:
CREATE TABLE HRDB.Staff
(
StaffID int NOT NULL PRIMARY KEY,
FirstName nvarchar(50) NOT NULL,
LastName nvarchar(50) NOT NULL,
JobTitle nvarchar(50) NOT NULL,
StaffAddress nvarchar (200) NULL,

StaffCity nvarchar(50) NOT NULL,
StaffState nvarchar(50) NOT NULL,
StaffPhone varchar (10) NOT NULL,
Salary smallmoney NOT NULL
)
GO
CREATE TABLE HRDB.SalaryHistory
(
StaffID int NOT NULL,
IncreaseDate date DEFAULT GETDATE(),
PrevSalary smallmoney NOT NULL,
NewSalary smallmoney NOT NULL
)
GO
You must write a Transact-SQL query to affect a salary increase of 6.5% for all staff members with a JobTitle of Support Technician. Your query must also update the data in the SalaryHistory table.

What Transact SQL statement would accomplish this task?

To answer, type the correct code in the answer area.

Answer:
UPDATE Staff
SET Salary = Salary * 1.065
WHERE JobTitle = 'Support Technician'
OUTPUT inserted.StaffID, deleted.Salary, inserted.Salary
INTO SalaryHistory(StaffID, PrevSalary, NewSalary)

Read More

Microsoft 70-461 Certification Exam Course Outline & Other Information

By this Post we want to provide our readers some information and knowledge about this Exam, so that they can plan well for this and look forward to go through this exam.

Exam Highlights:

- There were total 54 Questions
- Total time to attempt the paper was 3 Hrs.
- Total Marks: 1000
- Passing Marks: 700
- The Questions in the Exam were:
1. Mostly Objective Type.
2. Some were Subjective: where you’ve to write code, like SELECT statement and/or View Definition, etc.
3. And very few were Multiple Choice.
4. A few Question were where you’ve to pick up the appropriate code snippet and arrange them in correct sequence.

The Exam is divided into 4 modules:
 
1. Create Database Objects
2. Work with Data
3. Modify Data
4. Troubleshoot & Optimize

You need to brush up on following Skills in the these Modules:

Create Database Objects | 24%
 
- CREATE/ALTER/DROP TABLE/COLUMN
- Data Types (XML, DATETIME, SPATIAL, VARCHAR)
- Constraints (Primary Key, Foreign Key, Unique, Check, Default)
- Calculated Column
- Views
- Indexed Views (SCHEMABINDING, COUNT_BIG(*), CLUSTERED INDEX)
- Stored Procedures.
- DML Triggers (INSERTED, UPDATED, UPDATE function) vs CHECK Constraint
- TRIGGER for VIEWS (INSTEAD OF)
- UDF (Functions).
- SP vs UDF.

Work with Data | 27%
 
- New Functions in SQL Server 2012 (IFF, TRY_PARSE, CONCAT, FORMAT), 
- FETCH-OFFSET.
- SEQUENCE.
- Ranking and Window Functions (ROW_NUMBER, RANK, DENSE_RANK, NTILE).
- JOINS (INNER, OUTER LEFT, OUTER RIGHT, CROSS), .
- APPLY Operators (CROSS APPLY vs OUTER APPLY).
- CTE and Sub-Queries.
- PIVOT.
- ROLLUP, CUBE & GROUPING SETS.
- Dynamic SQL
- ANY, SOME, ALL
- CASE vs ISNULL vs COALESCE.
- FOR XML RAW/AUTO/PATH [ELEMENTS].
- Implementing XML Schemas and Handling of XML data

Modify Data | 24%
 
- Stored Procedure (with EXECUTE AS, RECOMPILE)
- MERGE Statement (TARGET, SOURCE, WHEN MATCHED, WHEN NOT MATCHED, OUTPUT).
- EXCEPT vs INTERSECT
- UNION vs UNION ALL.
- SCALAR vs TABLE Valued Functions.
- Use of APPLY with UDFs.
- VARCHAR(MAX) and .WRITE().

Troubleshoot & Optimize | 25%
 
- Using Statistics
- SQL Internal JOINS (NESTED – Small, MERGE – Large Sorter, HASH – Large Unsorted).
- TRANSACTIONS (BEGIN, COMMIT, ROLLBACK, XACT_ABORT, TRANCOUNT).
- ISOLATION Levels (READ UNCOMMITTED, READ COMMITTED, REPEATABLE READ, SNAPSHOT, SERIALIZABLE)
- TRY/CATCH.
- RAISE vs THROW
- CURSORS (Row-Based) vs SET Based Approach
- Table Hints (UPDLOCK, ROWLOCK, TABLOCK, …etc)
- Query Hints (OPTION (OPTIMIZED FOR … [UNKNOWN]))

Read More

Microsoft 70-461 Sample Question

Question No: 1

You work as a database administrator at ABC.com. ABC.com has a SQL Server 2012 database
named SalesDB. The SalesDB database is shown in the following database diagram:

You create a view on the SalesDB using the following Transact-SQL code:
CREATE VIEW SalesV
WITH SCHEMABINDINGS
AS
SELECT Products.ProductID, Invoices.InvoiceDate, SUM (Products.RetailPrice *
OrderDetail.Quantity * OrderDetail.SalesDiscount) AS Price
FROM OrderDetail INNER JOIN Products ON
OrderDetail.ProductID = Products.ProducID
INNER JOIN Invoices ON
OrderDetail.InvoiceID = Invoices.InvoiceID
GROUP BY Products.ProductID, Invoices.InvoiceDate
GO

How should you alter this view to allow users to update data through the SalesV?

A. You should add a CHECK constraint to the SalesV view.
B. You should add an INSTEAD OF trigger to the SalesV view.
C. You should add a clustered index to the SalesV view.
D. You should add an AFTER UPDATE trigger to the SalesV view.
E. Create a columnstore index on all columns used in the SalesV view.

Answer: B

Read More

Learning Path: Querying SQL Server (70-461)

Before you begin, keep in mind that your preparation for the exam should include both formal training and practical experience. We strongly recommend that you associate your workout Pluralsight with extensive practical experience in a production environment.

Learning Objective trail:

This training course focuses on the skills needed to write a wide range of topics for Microsoft SQL Server. This material is applicable to Microsoft exam 70-461 Consultation SQL Server 2012, and also applies to Microsoft SQL Server 2014 (Microsoft does not review the 2014 examinations with the hardware-specific).

Target audience:

This training course is intended for Microsoft IT professionals and database administrators seeking certification and basic job skills applicable to the management of SQL Server 2012 and 2014 in a production environment.

Learn a description of route:

This learning path will help you learn how to write a wide range of topics for Microsoft SQL Server 2012 and later. These courses can also help you prepare for certification under ACEM Microsoft - SQL Server programs, by equipping it with the necessary skills to succeed in your 70-461 exam.

Read More

Contact Us

• Sales Team
  For Sales Queries
  
  sales@passin1day.com
  
   
• Support Team
  For customer support
  
  support@passin1day.com
  
   
• Refund Department
  Requests regarding refunds.
  
  refund@passin1day.com
  
   
• Mailing Address
  
  37 Pritchatts Road, Birmingham, West Midlands B15 2QU, UK
  
   
• NOTE:
  When you pay for any exam dumps, your transaction will go for fraud review. And as soon as verification team pass your transaction, you will get mail with download access in your account.

Read More