Microsoft 70-461 Sample Questions

QUESTION NO: 6

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 = TotalItemss
C. ALTER TABLE Inventory ADD TotalItems = ItemsInStore + ItemsInWarehouse
D. ALTER TABLE Inventory ADD TotalItems AS SUM(ItemsInStore, ItemslnWarehouse);

Answer: A

Read More

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