Developing SQL Databases Free Sample Questions

Background
The HumanResources database contains a table, named Staff.
A number of read-only, historical reports that make use of various queries, which execute simultaneously, to assess workforce costs, include totals that transform on a regular basis. When you are informed that the running of the workforce assessment reports is not constant, you are required to examine the database to detect the reason for this happening.
It is your intention to install the application on a database server supporting other applications. The storage space required by the database should be reduced.

Application
An application that updates the Staff table calls two stored procedures, named UspStaffA and UspStaffB, simultaneously and asynchronously. UspStaffA only updates the Staffstatus column, while UspStaffB only updates the StaffPayRate column
The application controls access to information by making use of views that permit user access to all columns in the tables that the view accesses, but also limit updates to the rows that the view returns.
You are required to create a view that will be used to permit users to modify data in the Staff table. This view must also block users from accessing the view definition in catalog views.

Which of the following is the view attribute that should be used to block users from accessing the view definition in catalog views?

Note: The question is included in a number of questions that depicts the identical set-up. However, every question has a distinctive result. Establish if the solution satisfies the requirements.
A database, named Transactions, three tables named Client, Purchases and Items. The Client table has a column to record information regarding the last purchase made by the client. The Items table has the following fields:

ItemlD
ItemName
Description
QtyonHand
MerchantName
MerchantlD
Obsolete

The Purchases table has the following fields:

PurchaselD
ItemName
ItemlD
EmployeelD
PurchaseDate

You are preparing to execute a stored procedure that removes an obsolete item from the Items table. The stored procedure should allow for the information for the item to remain in the event that an open purchase contains an obsolete item. The stored procedure should then produce a custom error message, which identifies the PurchaselD for the open purchase

Solution: You include the Try/Parse Transact-SQL segment to handle errors.
Has the requirement been satisfied?

Note: The question is included in a number of questions that depicts the identical set-up. However, every question has a distinctive result. Establish if the solution satisfies the requirements.

A reporting database contains a non-partitioned fact table, which is persisted on disk. The following problems exist with regards to the table:

The completion of user queries is a lengthy process.
The table requires excessive storage in the database.
Indexes on the table are nonexistent.
A number of columns include repeating values.

You want to make use of an index that is the most effective at sorting out the problems listed.
Solution: You create a hash index on the table. Has the requirement been satisfied?

Note: The question is included in a number of questions that depicts the identical set-up. However, every question has a distinctive result.

You have been tasked with creating two modules for a database with disk-based tables and memory-optimized tables.
You are informed that encryption should be configured for the first module via the ENCRYPTION option. The first module should allow for updates on disk-based tables and memory-optimized tables, as well as support OUTPUT parameters.

You are also informed that the second module should only access memory-optimized tables, and allow for updates on those tables. Furthermore, the second module should allow for substantial aggregations with maximum execution, as well as support OUTPUT parameters. Which of the following should you use for the first module?