SEMATEC SQL Server Design Quiz

2.

You've created a simple select statement that returns the customers first name and last name. This statement needs changing so that it only includes the customers first initial and their last name in the result set. There is no column for initial. Which function could you use to get the initial?

SUBSTRING
STUFF
REPLACE
RTRIM

Correct Answer

A. SUBSTRING

Explanation

The SUBSTRING function can be used to get the initial of the customer's first name. By specifying a start position of 1 and a length of 1, the SUBSTRING function will extract only the first character of the first name, which represents the initial. The last name can remain unchanged in the result set.

Rate this question:

3.

You develop a Microsoft SQL Server 2012 database. The database is used by two web applications that access a table named Products.You want to create an object that will prevent the applications from accessing the table directly while still providing access to the required data.You need to ensure that the following requirements are met:Future modifications to the table definition will not affect the applications’ ability to access data.The new object can accommodate data retrieval and data modification.You need to achieve this goal by using the minimum amount of changes to the existing applications.What should you create for each application?

Views
Table partitions
Table-valued functions
Stored procedures

Correct Answer

A. Views

Explanation

Views should be created for each application. Views provide a layer of abstraction between the applications and the underlying table. They allow the applications to access the required data while preventing direct access to the table. Future modifications to the table definition will not affect the applications' ability to access data as long as the views are properly maintained. Views can also accommodate both data retrieval and data modification, making them a suitable choice for this scenario. Creating views requires minimum changes to the existing applications, making them the optimal solution.

Rate this question:

4.

Choose the incorrect option about the sql server index

Two types of indexes - clustered indexes and non-clustered indexes
Both types use B-TREE for searching data
Only one clustered index on a table
More than one clustered index on a table

Correct Answer

A. More than one clustered index on a table

5.

You develop a Microsoft SQL Server 2012 database.You need to create a batch process that meets the following requirements:Returns a result set based on supplied parameters.Enables the returned result set to perform a join with a table.Which object should you use?

Inline user-defined function
Stored procedure
Table-valued user-defined function
Scalar user-defined function

Correct Answer

A. Table-valued user-defined function

Explanation

A table-valued user-defined function should be used in this scenario. This type of function can return a result set based on supplied parameters and can also be used to perform a join with a table. Unlike scalar user-defined functions, a table-valued function can return multiple rows and columns, making it suitable for joining with other tables. Stored procedures can also meet the requirements, but they are generally used for more complex tasks and may not be necessary in this case. Inline user-defined functions are not suitable as they can only return a single value.

Rate this question:

6.

How inserting data through stored procedure do reduces network traffic and increase database performance?

Stored procedure can accept parameter
Permission check is not required
The execution plan is stored in the cache after it was executed the first time.
Stored Procedure does not need to compile before every executing

Correct Answer

A. The execution plan is stored in the cache after it was executed the first time.

Explanation

When data is inserted through a stored procedure, the execution plan is stored in the cache after it is executed the first time. This means that subsequent executions of the stored procedure can reuse the cached execution plan, eliminating the need to compile it again. This reduces the amount of network traffic because the execution plan does not need to be sent over the network each time the stored procedure is executed. Additionally, since the execution plan is already stored in the cache, the database can retrieve and execute it more quickly, resulting in improved database performance.

Rate this question:

7.

Which global variables can be used to determine if a transaction is still open?

@@NESTLEVEL
@@FETCH_STATUS
@@TRANCOUNT
@@CONNECTIONS

Correct Answer

A. @@TRANCOUNT

Explanation

The global variable @@TRANCOUNT can be used to determine if a transaction is still open. This variable keeps track of the number of active transactions in the current session. If the value of @@TRANCOUNT is greater than 0, it means that there is an open transaction.

Rate this question:

8.

You are developing a database that will contain price information. You need to store the prices that include a fixed precision and a scale of six digits. Which data type should you use?

Float
Money
Smallmoney
Numeric

Correct Answer

A. Numeric

Explanation

The correct data type to use in this scenario is "Numeric". Numeric data type allows for fixed precision and scale, which means it can store numbers with a specific number of digits before and after the decimal point. In this case, the requirement is to store prices with a precision of six digits, meaning there can be up to six digits before the decimal point, and a scale of six digits, meaning there can be up to six digits after the decimal point. Numeric data type fits this requirement perfectly.

Rate this question:

9.

You develop a Microsoft SQL Server 2012 database that contains a table named Customers. This table has two columns MobileNumber and HomeNumber per each customer.You need to create an audit record only when either the MobileNumber or HomeNumber columnis updated. Which Transact-SQL query should you use?

CREATE TRIGGER TrgPhoneNumberChange ON Customers FOR UPDATE AS IF COLUMNS_UPDATED (HomeNumber, MobileNumber) - - Create Audit Records
CREATE TRIGGER TrgPhoneNumberChange ON Customers FOR UPDATE AS IF EXISTS( SELECT HomeNumber FROM inserted) OR EXISTS (SELECT MobileNumber FROM inserted) - - Create Audit Records
CREATE TRIGGER TrgPhoneNumberChange ON Customers FOR UPDATE AS IF COLUMNS_CHANGED (HomeNumber, MobileNumber) - - Create Audit Records
CREATE TRIGGER TrgPhoneNumberChange ON Customers FOR UPDATE AS IF UPDATE (HomeNumber) OR UPDATE (MobileNumber) - - Create Audit Records

Correct Answer

A. CREATE TRIGGER TrgPhoneNumberChange ON Customers FOR UPDATE AS IF UPDATE (HomeNumber) OR UPDATE (MobileNumber) - - Create Audit Records

Explanation

The correct answer is to use the query "CREATE TRIGGER TrgPhoneNumberChange ON Customers FOR UPDATE AS IF UPDATE (HomeNumber) OR UPDATE (MobileNumber) - - Create Audit Records". This query creates a trigger on the Customers table that will fire whenever an update is made. It then checks if either the HomeNumber or MobileNumber column has been updated using the UPDATE() function. If either of these columns have been updated, it will create an audit record.

Rate this question:

10.

You develop a Microsoft SQL Server 2012 server database that supports an application. The application contains a table that has the following definition:CREATE TABLE Inventory(ItemID int NOT NULL PRIMARY KEY,ItemsInStore int NOT NULL,ItemsInWarehouse int NOT NULL)You need to create a computed column that returns the sum total of the ItemsInStore and ItemsInWarehouse values for each row. Which Transact-SQL statement should you use?

ALTER TABLE InventoryADD TotalItems AS ItemsInStore + ItemsInWarehouse
ALTER TABLE InventoryADD ItemsInStore – ItemsInWarehouse = TotalItems
ALTER TABLE InventoryADD TotalItems = ItemsInStore + ItemsInWarehouse
ALTER TABLE InventoryADD TotalItems AS SUM(ItemsInStore, ItemslnWarehouse)

Correct Answer

A. ALTER TABLE InventoryADD TotalItems AS ItemsInStore + ItemsInWarehouse

Explanation

The correct answer is "ALTER TABLE Inventory ADD TotalItems AS ItemsInStore + ItemsInWarehouse". This statement adds a computed column named TotalItems to the Inventory table, which calculates the sum of the ItemsInStore and ItemsInWarehouse values for each row.

Rate this question:

Sematec SQL Server Design Quiz

Which statement is used to delete all rows in a table without having the action logged?

Quiz Preview

You've created a simple select statement that returns the customers first name and last name. This statement needs changing so that it only includes the customers first initial and their last name in the result set. There is no column for initial. Which function could you use to get the initial?

Choose the incorrect option about the sql server index

You develop a Microsoft SQL Server 2012 database.You need to create a batch process that meets the following requirements:Returns a result set based on supplied parameters.Enables the returned result set to perform a join with a table.Which object should you use?

How inserting data through stored procedure do reduces network traffic and increase database performance?

Which global variables can be used to determine if a transaction is still open?

You are developing a database that will contain price information. You need to store the prices that include a fixed precision and a scale of six digits. Which data type should you use?