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

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

Frequently Asked Questions

FAQ's

Frequently Asked Questions About Virtual Instructor-Led Courses

I can't connect to my class, what are my options?

The link to the class is available upon logging in to your dashboard. If you are unable to see it, please contact our support team at 1-855-800-8240 and they will be happy to provide you the direct link via email or the dial in number.


I can't make it to attend to class. Can I reschedule?

Yes, you can reschedule your class. Please contact your Sales representative and they will arrange this for you. If you forgot his/her name, feel free to contact our support team at support@quickstart.com or 1-855-800-8240.


Will I get my certificate upon completion?

Yes. Upon completion of the course, it will be available on your course as a Trophy Icon for you to download. If you do not see this, you will need to contact support@quickstart.com with the following details so they can email you the certificate: Class Name, Class Date, Account Rep, and Your Email.


I cannot connect to my lab. Help!

Your Lab is accessible on the bottom part of your course. You will see a button that says "LAB". Just click it to launch the lab. Please note that some classes don’t need/require a LAB. You can verify with our support team by calling them at 1-855-800-8240 or by email at support@quickstart.com. You can also check with your Instructor or the Associate Instructor if your class includes one.


What is my access code for Skillpipe?

A. Not all of the classes have or require Skillpipe. If your class includes one, please check your email as you should have received one from noreply@skillpipe.com. In case you do not find it in your inbox, please check the Spam / Junk folder. For any further assistance, you can call the support at 1-855-800-8240 or contact them via email at support@quickstart.com.


I don't have audio. I can't hear the instructor.

Make sure you are using a compatible headset for your laptop or computer. If you don’t have a headset, you can use the built-in speaker of your laptop. Otherwise, you can use the dial in option by calling the dial in number provided in the class joining email. You may also contact support team for the dial in numbers associated for your training at 1-855-800-8240 or contact them via email at support@quickstart.com.


How can I reach student support?

Support can be reach via phone at 1855-800-8240; via email at support@quickstart.com or via chat support through the chat button on our website. Please note that support office hours will be from 8am-5pm CST Monday to Friday. Any concerns after office hours will be attended the following business day.

REQUEST MORE INFORMATION

click here