Microsoft 70-461 Sample Questions Pack

QUESTION NO: 1

Which of the following datatypes has a fixed precision and a scale of six digits?

A. Double
B. Money
C. Int
D. Numeric
E. SmallInt
F. VarInt
G. Float

Answer: B

QUESTION NO: 2

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 Salary History 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)GOCREATE TABLE HRDB.SalaryHistory(StaffID int NOT NULL,IncreaseDate date DEFAULT GETDATE(),PrevSalary smallmoney NOT NULL,NewSalary smallmoney NOT NULL)GOYou must write a Transact-SQL query to affect a salary increase of 6.5% for all staff members witha JobTitle of Support Technician. Your query must also update the data in the SalaryHistory table.What Transact SQL statementwould accomplish this task?To answer, type the correct code in the answer area.

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

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 representatives.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

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

QUESTION NO: 5

Which of the following statements regarding SQL Server 2012 objects is TRUE?

A. A user-defined data type can accept an input variable and return a table of results but cannot be used within a view.
B. A scalar function can accept an input variable and return a table of results but cannot be used within a view.
C. A table-valued function can accept an input variable and return a table of results but cannot be used within a view.
D. A table-valued type can accept an input variable and return a table of results but cannot be used within a view.

Answer: A

QUESTION NO: 6

You work as a database administrator at ABC.com. You are developing a database that will be used by a web site analysis application name ABCWeb1.The ABCWeb1 application must display the date and time each visitor visits a page on a website as well as the date and time they leave that web page. This data needs to be displayed in different date and time formats.How would you accomplish this?

A. You should make use of a scalar user-defined function.
B. You should make use of the SET CONTEXT_INFO statement in the stored procedure.
C. You should make use of the DATETIMEOFFSET data type.
D. You should make use of the FORMAT function.
E. You should make use of the SET FORCEPLAN ON statement in the stored procedure.
F. You should make use of a cursor.

Answer: D

QUESTION NO: 7

Which of the following can be used to protect the code in a stored procedure?

A. The ENCRYPBYKEY statement.
B. The ENCRYPBYASYMKEY statement.
C. The SET TRUSTWORTHY ON option.
D. The SET XACT_ABORT ON statement.
E. The ENCRYPTBYPASSPHRASE statement.
F. The ENCRYPTBYCERT statement.
G. The SIGNBYASYMKEY statement.
H. The CRYPT_GEN_RANDOM statement.

Answer: A

QUESTION NO: 8

You work as a database administrator at manufacturing company named ABC.com. ABC.com has a SQL Server 2012 database named ProductionDB. The ProductionDB database has a table named Sites that was created using the following Transact-SQL code:CREATE TABLE Sites (SiteID int NOT NULL PRIMARY KEY,Location int NOT NULL,Manager nvarchar(200) NOT NULL,Week smallint NOT NULL, ItemsProduced int NOT NULL )You want to write the Transact-SQL query that returns the number of items produced at each location for each week. In addition, you want the result set to include a column named PrevItemsProduced that holds the number of items produced at each location for the week before.What Transact SQL statement would accomplish this task?

A. SELECT Location, Week, ItemsProduced,
LEAD(ItemsProduced, 1, 0) OVER (PARTITION BY Location ORDER BY Week) AS PrevItemsProduced FROM Sites
B. SELECT Location, Week, Items Produced,
FIRST_VALUE(ItemsProduced) OVER (PARTITION BY Location ORDER BY Week) AS PrevItems Produced FROM Sites
C. SELECT Location, Week, Items Produced,
LAG(ItemsProduced, 1, 0) OVER (PARTITION BY Location ORDER BY Week) AS PrevItems Produced FROM Sites
D. SELECT Location, Week, Items Produced,
LAST_VALUE(ItemsProduced) OVER (PARTITION BY Location ORDER BY Week) AS PrevItems Produced FROM Sites
E. SELECT Location, Week, Items Produced,CUME_DIST( ) OVER (PARTITION BY Location ORDER BY Week) AS Prev Items Produced FROM Sites

Answer: C

QUESTION NO: 9

You work as a database developer at ABC.com. ABC has an in-house application named ABCApp3 that runs a Transact-SQL query against a SQL Server 2012 database.You want to run an execution plan against the query that will provide detailed information on missing indexes.How would you accomplish this task?

A. You should make use of the READPAST hint in the queries.
B. You should make use of the READCOMMITTED hint in the queries.
C. You should make use of the SET SHOWPLAN_XML ON statement in the query.
D. You should make use of the SET STATISTICS XML ON statement in the query.
E. You should make use of the SET XACT_ABORT OFF statement in the query.
F. You should make use of the SET CONTEXT_INFO statement in the query.

Answer: C

Read More