- Popular Certifications
- ITIL 4 Foundation
- Project Management Certification (PMP)
- Certified Agile SCRUM Master
- Popular Practice Exams
- ITIL 4 Foundation
Learn how you can get certified, advance your career, and get promoted with our self-paced subscription of over 900 self-paced courses.
Learn MoreLearn how you can achieve your goals and increase the performance of your teams through our workforce readiness platform.
Learn MoreGet Your Questions Answered & Connect With Other IT Professionals In The Areas Of DevOps, Cloud, Infosec, Data Science And SDN
Find Answers To Your Questions & Solve Your IT Problems
Learn MoreCreate, Teach & Sell Courses
Learn MoreQuickStart 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).
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
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?
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?
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 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?
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?
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?
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 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.
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 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?
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?
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?
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?
Learning Style
Learning Style
Difficulty
Course Duration
Buy Individually Timezone |
---|
tab | ||||
---|---|---|---|---|
About Individual Course:
$109.00
/ Each |
Lab Access | No |
---|---|
Learning Style | Self-Paced Learning |
Learning Type | Practice Exam |
Difficulty | Beginner |
Course Duration | 1 Hour |
Language | English |
Our experts featured on QuickStart are driven by our ExpertConnect platform, a community of professionals focused on IT topics and discussions. Interact with these experts, create project opportunities, gain help and insights on questions you may have, and more.