SEMATEC SQL Server Design Quiz

Approved & Edited by ProProfs Editorial Team

The editorial team at ProProfs Quizzes consists of a select group of subject experts, trivia writers, and quiz masters who have authored over 10,000 quizzes taken by more than 100 million users. This team includes our in-house seasoned quiz moderators and subject matter experts. Our editorial experts, spread across the world, are rigorously trained using our comprehensive guidelines to ensure that you receive the highest quality quizzes.

Learn about Our Editorial Process

| By Asgari.sematec

Asgari.sematec

Community Contributor

Quizzes Created: 8 | Total Attempts: 9,957

Questions: 10 | Attempts: 1,712 | Updated: Mar 21, 2023

Questions

Settings

Create your own Quiz

Share on Facebook Share on Twitter Share on Whatsapp Share on Pinterest Share on Email Copy to Clipboard Embed on your website

این 10 سوال مهم ترین بخش های درس را مورد آزمایش قرار می دهداگر نمره پایین تر 70 را کسب کنید، نیاز به مطالعه این درس دارید

Questions and Answers

1.

Which global variables can be used to determine if a transaction is still open?
- A.
  
  @@NESTLEVEL
- B.
  
  @@FETCH_STATUS
- C.
  
  @@TRANCOUNT
- D.
  
  @@CONNECTIONS
Correct Answer
C. @@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:
2.

How inserting data through stored procedure do reduces network traffic and increase database performance?
- A.
  
  Stored procedure can accept parameter
- B.
  
  Permission check is not required
- C.
  
  The execution plan is stored in the cache after it was executed the first time.
- D.
  
  Stored Procedure does not need to compile before every executing
Correct Answer
C. 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:
3.

Which statement is used to delete all rows in a table without having the action logged?
- A.
  
  Drop table statement
- B.
  
  Delete table statement
- C.
  
  Truncate table statement
Correct Answer
C. Truncate table statement

Explanation
The correct answer is "Truncate table statement". The TRUNCATE TABLE statement is used to delete all rows in a table without logging the action. It is a fast and efficient way to remove all data from a table, as it does not generate any transaction logs for each deleted row. Unlike the DELETE statement, which logs each deletion and can be rolled back, the TRUNCATE TABLE statement is non-logged and cannot be undone.

Rate this question:
4.

Choose the incorrect option about the sql server index
- A.
  
  Two types of indexes - clustered indexes and non-clustered indexes
- B.
  
  Both types use B-TREE for searching data
- C.
  
  Only one clustered index on a table
- D.
  
  More than one clustered index on a table
Correct Answer
D. 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?
- A.
  
  Inline user-defined function
- B.
  
  Stored procedure
- C.
  
  Table-valued user-defined function
- D.
  
  Scalar user-defined function
Correct Answer
C. 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.

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?
- A.
  
  Views
- B.
  
  Table partitions
- C.
  
  Table-valued functions
- D.
  
  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:
7.

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?
- A.
  
  Float
- B.
  
  Money
- C.
  
  Smallmoney
- D.
  
  Numeric
Correct Answer
D. 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:
8.

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?
- A.
  
  ALTER TABLE InventoryADD TotalItems AS ItemsInStore + ItemsInWarehouse
- B.
  
  ALTER TABLE InventoryADD ItemsInStore – ItemsInWarehouse = TotalItems
- C.
  
  ALTER TABLE InventoryADD TotalItems = ItemsInStore + ItemsInWarehouse
- D.
  
  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:
9.

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?
- A.
  
  SUBSTRING
- B.
  
  STUFF
- C.
  
  REPLACE
- D.
  
  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:
10.

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?
- A.
  
  CREATE TRIGGER TrgPhoneNumberChange ON Customers FOR UPDATE AS IF COLUMNS_UPDATED (HomeNumber, MobileNumber) - - Create Audit Records
- B.
  
  CREATE TRIGGER TrgPhoneNumberChange ON Customers FOR UPDATE AS IF EXISTS( SELECT HomeNumber FROM inserted) OR EXISTS (SELECT MobileNumber FROM inserted) - - Create Audit Records
- C.
  
  CREATE TRIGGER TrgPhoneNumberChange ON Customers FOR UPDATE AS IF COLUMNS_CHANGED (HomeNumber, MobileNumber) - - Create Audit Records
- D.
  
  CREATE TRIGGER TrgPhoneNumberChange ON Customers FOR UPDATE AS IF UPDATE (HomeNumber) OR UPDATE (MobileNumber) - - Create Audit Records
Correct Answer
D. 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:

Quiz Review Timeline +

Our quizzes are rigorously reviewed, monitored and continuously updated by our expert board to maintain accuracy, relevance, and timeliness.

Current Version
Mar 21, 2023

Quiz Edited by
ProProfs Editorial Team
Feb 11, 2017

Quiz Created by
Asgari.sematec

Sematec SQL Server Design Quiz

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

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

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

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?

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?

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?