Certification Exam Prep Questions for Querying Microsoft SQL Server 2012 (MS-20461)


QuickStart is now offering you assessment questions Querying Microsoft SQL Server (MS-20461). Whether you are deciding which exam to sign up for, or simply want to practice the materials necessary to complete certification for this course, we have provided a practice assessment to better aid in certification. 100% of the questions are real questions from a recent version of the test you will take for Querying Microsoft SQL Server (MS-20461).


Arrow
 

1

A Microsoft SQL Server 2012 database containing the following definition has been created by you in order to support an application.

CREATE TABLE Inventory

(ItemID int NOT NULL PRIMARY KEY, ItemsInStore int NOT NULL, ItemsInWarehouse int NOT NULL)

Your job is to form a computed column which should return the sum total of ItemsInWarehouse and ItemsInStore values for all the rows. Choose the Transact-SQL statement you will use for the purpose. ALTER TABLE Inventory

ADD TotalItems = ItemsInStore + ItemsInWarehouse

2

A travel application is developed for which you are required to design database objects and tables. You begin by forming a table for Airlines Schedule. Now you need to store dates for arrivals and departures in addition to the flight timings containing the zone information as well. How will you do it?

3

A travel application is developed for which you are required to design database objects and tables. Your job is to provide a stored procedure. This procedure should also include various event names as well as their parameters and dates. How would you do it?

4

You are forming a stored procedure so multiple tables can be updated within a transaction. Before starting the stored procedure, which Transact-SQL statement would you choose to ensure that in the case where a run-time error gets raised, it results in the termination and rolling back of the entire transaction?

A. BEGIN
Incorrect.
B. SET ARITHABORT ON C. TRY
Incorrect.
C. SET XACT_ABORT ON
Correct!
D. SET ARITHABORT OFF F. SET XACT_ABORT OFF
Incorrect.
5

A software vendor has hired you as a database developer. Your job in this role is to form stored procedures that include propriety code. You have to make sure that this code never gets viewed by any of your customers. Which option will let you do that?

A. ENCRYPTBYCERT
Incorrect.
B. ENCRYPTBYPASSPHRASE
Incorrect.
C. ENCRYPTION
Correct!
D. ENCRYPTBYKEY
Incorrect.
6

Two tables by the name of ‘DomesticSalesOrders’ and ‘InternationalSalesOrders’ are present inside a database. Each table contains over a 100 million rows and each row contains a Primary Key Column called ‘SalesOrderId’. There is no similarity between the data present in both the tables. You have been asked by the business users to make a report that contains collective information regarding the total sales amount and global sales figures. To execute your query in the least amount of time, which of the following queries would you use?

7

You oversee a Microsoft SQL Server 2012 database comprising of a table called ‘OrderDetail’ in it. You find out that the non-clustered index, NCI_OrderDetail_CustomerID, is fragmented. To reduce fragmentation while keeping the index online, which Transact-SQL batch would you prefer using?

8

A Microsoft SQL Server 2012 database has been developed by you. Two web applications are using it to access the ‘Products’ table. You are required to create an object that allows the applications to access the data they need, but without having direct access to the table.

Following are the requirements that need to be met:

Any table definition modifications in the future are not supposed to affect the data accessing ability of the application. Data modification and data retrieval can be accommodated by the new object.

The existing applications should undergo the least amount of changes in order to acquire this goal. For each application, which of the following would you create?

A. stored procedures
Incorrect.
B. table-valued functions
Incorrect.
C. views
Correct!
D. table partitions
Incorrect.
9

A Microsoft SQL Server 2012 database has been developed by you. To meet the following requirements, you are required to form a batch process:

The result set that is returned is based on the parameters supplied.

The result set that is returned is enabled to join a table.

10

A Microsoft SQL Server 2012 database has been developed by you. It holds a heap called ‘OrdersHistorical’. The following Transact-SQL query is written by you.

INSERT INTO OrdersHistorical

SELECT * FROM CompletedOrders

Choose a table hint that will allow you to optimize transaction locking and logging for the statement:

A. XLOCK
Incorrect.
B. TABLOCK
Correct!
C. ROWLOCK
Incorrect.
D. HOLDLOCK
Incorrect.
E. UPDLOCK
Incorrect.
11

A tabled called, ‘Purchases’ is present in your database. It includes a DATETIME column called ‘PurchaseTime’. It stores the time and date of each purchase. This column has a non-clustered index on it. Your business team has asked you to prepare a report that shows the number of purchases made today. Which of the following Transact-SQL queries will efficiently return the most accurate results?

12

A travel application needs a database developed. For that, you have to design not only tables, but other database objects as well. You need to store each media file, typically less than 1 MB in size, in the tables. The files will need to have quick access and frequent retrieval. What do you plan to do?

13

A travel application needs a database developed. For that, you have to design not only tables, but other database objects as well. You need to store each media file, typically less than 1 MB in size, in the tables. The files will need to have quick access and frequent retrieval. What do you plan to do?

14

Within a territory, the total profit of each year is stored in a table named ‘Profits’. The columns of the table are named Year, Territory and Profit. The report that you have to create needs to display the profit of each territory for the previous year, as well as the each year.

What is the Transact-SQL query that you choose to use?

PDP Url

Practice Exam - Administering Windows Server 2012


Self-Paced

Learning Style

Beginner

Difficulty

1 Hour

Course Duration

Course Info

Download PDF

Certificate

See Sample

Buy Individually
About Individual Course:
  • Individual course plan gives you access to this course
$0.00
$0.00
/ Each

Outline

More Information

More Information
Lab Access No
Learning Style Self-Paced Learning
Difficulty Beginner
Course Duration 1 Hour
Language English

Reviews

Write Your Own Review
Only registered users can write reviews. Please Sign in or create an account

Contact A Learning Consultant


click here