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