Developing SQL Databases - Exam 70-762 Certification Mock Test

You are using SQL Server 2016 server named Server_1. You are using the SQL server Resource Governor to manage your database workloads.

You are creating a separate Resource Pool for the Administrative Team.

You need to limit the number of CPUs available to a Resource Pool by attaching the Resource Pool to specific schedulers.

Your solution should not set a hard limit on CPU bandwidth resources.

Which setting should you use?

Options are :

  • AFFINITY (Correct)
  • MAX_MEMORY_PERCENT
  • CAP_CPU_PERCENT
  • MAX_CPU_PERCENT

Answer : AFFINITY

Explanation You should use the AFFINITY setting. This setting limit the number of COUs a resource pool could use. But it would not set a hard limit on CPU bandwidth resources. You should not use the CAP_CPU_PERCENT. It will take a hard limit on the CPU bandwidth resource requests in the pool. You should not use the MAX_CPU_PERCENT setting. It will place a guaranteed average CPU bandwidth for all requests in the resource pool. You should not use the MAX_MEMORY_PERCENT setting. It will place a hard limit on the amount of memory resources requested by the resource pool.

You used SQL Azure database. You are implementing elastic scale on the database.

You need to ensure that the database engine will process queries in parallel across shared.

Which feature of the elastic database client library should you implement?

Options are :

  • Multi-shared querying (Correct)
  • Shared elasticity
  • Data-dependent routing
  • Shared map management

Answer : Multi-shared querying

Explanation You should implement multi-shared querying. This feature is used by the database engine to process queries in parallel across shards. You should not use shared map management because it registers each database as a shared. The data-dependent routing allows to automatically define and assign a connection to the correct shared. You should not use shared elasticity because it is used to monitor resource consumption and it will dynamically assign more resources or deallocate resources.

You used SQL Azure database. You are implementing elastic scale on the database.

You need to register each database as a shared.

Which feature of the elastic database client library should you implement?

Options are :

  • Multi-shared querying
  • Shared elasticity
  • Data-dependent routing
  • Shared map management (Correct)

Answer : Shared map management

Explanation You should use shared map management because it registers each database as a shared. You should not implement multi-shared querying. This feature is used by the database engine to process queries in parallel across shards. The data-dependent routing allows to automatically define and assign a connection to the correct shared. You should not use shared elasticity because it is used to monitor resource consumption and it will dynamically assign more resources or deallocate resources.

You used SQL Azure database. You are implementing elastic scale on the database.

You need to ensure that you can monitor resource consumption and dynamically allocate or deallocate resources.

Which feature of the elastic database client library should you implement?

Options are :

  • Multi-shared querying
  • Shared elasticity (Correct)
  • Data-dependent routing
  • Shared map management

Answer : Shared elasticity

Explanation You should use shared elasticity because it is used to monitor resource consumption and it will dynamically assign more resources or deallocate resources. You should not use shared map management because it registers each database as a shared. You should not implement multi-shared querying. This feature is used by the database engine to process queries in parallel across shards. The data-dependent routing allows to automatically define and assign a connection to the correct shared.

You detects deadlocks on a database.

Which two trace flags in the Microsoft SQL Server error log should you locate?

(choose two)

Options are :

  • 1222 (Correct)
  • 3205
  • 1204 (Correct)
  • 1211

Answer : 1222 1204

Explanation Trace flag 1204 returns the resource and types of locks participating in a deadlock and also the current command affected. Trace flag 1222 returns the resource and type of locks that are participating in a deadlock and also the current command affected, in an XML format.

You are developing a database reporting solution for a table that contains 800 million rows and is 102GB.

The table is updated thousands of times a day, but data is not deleted. The SELECT statements vary in the number of columns used and the amount of rows retrieved. You need to reduce the amount of time it takes to retrieve data from the table. It must revent data duplication.

Which index strategy should you use?

Options are :

  • A clustered index for the table and nonclustered indexes for nonkey columns.
  • A Hash index for the table
  • A nonclustered index for each column in the table
  • A clustered columnstore index for the table (Correct)

Answer : A clustered columnstore index for the table

Explanation Columnstore indexes are the standard for storing and querying large data warehousing face tables. It uses column-based data storage and query processing to achieve up to 10x query performance gains in your data warehouse over traditional row-oriented storage.

You need to implement trigger to prevent a user from establishing a session if they have an existing session.

Which trigger should you use?

Options are :

  • INSTEAD OF INSERT
  • LOGON (Correct)
  • AFTER INSERT
  • DDL

Answer : LOGON

Explanation When a user session is established with an instance of SQL server Logon trigger is fire after the authentication phase of logging in finishes, but before the user session is actually established.

You need to implement trigger to insert the column name, table name and user name. when a column is added to a table.

Which trigger should you use?

Options are :

  • INSTEAD OF INSERT
  • LOGON
  • AFTER INSERT
  • DDL (Correct)

Answer : DDL

Explanation You should use a DDL trigger. DDL triggers can be associated with the database or with a schema. There are two types of DDL trigger: Server-Level DDL trigger and Database-Level DDL trigger. Use Database-Level DDL trigger.

You have a disk-based table that contains 15 columns.

You query the table for the number of new rows created during the current day. You need to create an index for the query.

The solution must generate the smallest possible index.

Which type of index should you create?

Options are :

  • Nonclustered index with compression enabled
  • Clustered index
  • Hash index
  • Filtered nonclustered index with a getdate() predicate in the WHERE clause (Correct)

Answer : Filtered nonclustered index with a getdate() predicate in the WHERE clause

Explanation A filtered index is an optimized nonclustered index especially suited to cover queries that select from a well-defined subset of data. It uses a filter predicate to index a portion of rows in the table. Creating a filtered index can reduce disk storage for nonclustered indexes when a full-table index is not necessary.

You develop a database that contains a table named Item. Item table has the following definition:

CREATE TABLE [dbo]. [Item]

(

            [Id] [int] NOT NULL,

            [Retailprice] [nvarchar] (20) NOT NULL,

            [Name] [nvarchar] (20) NOT NULL,

            [Wholesaleprice] [nvarchar]          (20) NULL,

            [Category] [nvarchar] (20) NOT NULL,

            CONSTRAINT [PK_Item] PRIMARY KEY CLUSTERED

            (

                        [Id] ASC

)           ON [PRIMARY]

) ON [PRIMARY] 

You need to create record only when either Retailprice or Wholesaleprice column is updated.

Which T-SQL query should you use?

Options are :

  • CREATE TRIGGER PriceChange ON Item FOR UPDATE AS IF UPDATE(Retailprice) OR UPDATE(Wholesaleprice) - - Create Record (Correct)
  • CREATE TRIGGER PriceChange ON Item FOR UPDATE AS IF CCLUMNS_CHANGED (Retailprice, Wholesaleprice) - - Create Record
  • CREATE TRIGGER PriceChange ON Item FOR UPDATE AS IF EXISTS (SELECT Retailprice from inserted) OR EXISTS (SELECT Wholesaleprice FROM inserted) - - Create Record
  • CREATE TRIGGER PriceChange ON Item FOR UPDATE AS IF COLUMNS_UPDATED (Retailprice, Wholesaleprice) - - Create Record

Answer : CREATE TRIGGER PriceChange ON Item FOR UPDATE AS IF UPDATE(Retailprice) OR UPDATE(Wholesaleprice) - - Create Record

Explanation COLUMNS_UPDATED tests for INSERT or UPDATE actions performed on multiple columns. Use UPDATE (), to test for UPDATE or INSERT attempts on one column.

You create a view from the Order and Details tables by using the following definition.

Create view vOrder

With schemabinding

As

Select o.itemId, o.date, sum(od.price*od.Qty) as amount

From Details as od inner join

       Order as o on od.orderid=o.orderid

Where od.saleorderid=o.saleorderid

Group by o.date,o.itemid

Go

You need to improve the performance of the view by persisting data to the disk. What should you do?

Options are :

  • Modify the view to use the WITH VIEW_METADATA clause.
  • Create a clustered index on the view. (Correct)
  • Create an AFTER trigger on the view.
  • Create an INSTEAD OF trigger on the view.

Answer : Create a clustered index on the view.

Explanation Using an index, we can improve query performance.

Generally, a savepoint is a location from which a transaction can restart if part of it is conditionally canceled. A ROLLBACK TRANSACTION resets the value of @@TRANCOUNT to 0. When a transaction rolls back to a savepoint, @@TRANCOUNT is not reset.

Is it true or false?

Options are :

  • True (Correct)
  • False

Answer : True

Explanation When transaction rolls back to a savepoint, @@TRANCOUNT is not reset because save transaction statement also has no effect on @@TRANCOUNT.

You need to view the definition of a DML trigger named DemoTrigger.

Solution: You run the following T-SQL statement:  

EXEC sp_helptext ' DemoTrigger '


SELECT definition from sys.sql_modules where object_id = OBJECT_ID(N' DemoTrigger ') Does the solution meet the goal?

Options are :

  • No
  • Yes (Correct)

Answer : Yes

Explanation SELECT definition from sys.sql_modules where object_id = OBJECT_ID(N' DemoTrigger ') statement will provide the definition of the DemoTrigger. Executing EXEC sp_helptext ' DemoTrigger ' statement will provide the definition of the DemoTrigger.

You have noticed that a block of T-SQL code triggers Error 1105. You need to determine the error state number of that error. You can configure the appropriate TRY . . . CATCH construct, using the code so that you can diagnose the error.

Which of the following functions can you use?

Options are :

  • ERROR_STATE() (Correct)
  • ERROR_NUMBER()
  • ERROR_LINE()
  • ERROR_SEVERITY()

Answer : ERROR_STATE()

Explanation You can use the ERROR_STATE() function to determine the error state number. The 1105 error can have a state of 0, 1, 2 or 3.

Which type of index is used in case our query has an ORDER BY clause on an indexed column, or our query returns a very few records by performing range selection against an index column?

Options are :

  • Memory-optimized nonclustered B-tree index (Correct)
  • Columnstore index
  • Memory-optimized hash index
  • Rowstore index

Answer : Memory-optimized nonclustered B-tree index

Explanation We can use memory-optimized nonclustered B-tree index when our query return few records by performing range selections against index column.

You are configuring transaction isolation level for a session. You need to ensure the following:

- The data read by any statement in the transaction must be transactionally consistent with how that data existed at the start of the transaction and when the transaction has yet to commit, other transactions should be able to write data to rows selected by the transaction.

Which of the following statements can you use to complete this goal?

Options are :

  • SET TRANSACTION ISOLATION LEVEL SNAPSHOT (Correct)
  • SET TRANSACTION ISOLATION LEVEL REPEATABLE READ
  • SET TRANSACTION ISOLATION LEVEL SERIALIZABLE
  • SET TRANSACTION ISOLATION LEVEL READ UNCOMMITTED

Answer : SET TRANSACTION ISOLATION LEVEL SNAPSHOT

Explanation When you want to ensure that data read by any statement in a transaction will be transaction ally consistent with how that data existed at the start of the transaction then you can use the SET TRANSACTION ISOLATION LEVEL SNAPSHOT statement. The SNAPSHOT transactions do not block other transactions from writing data.

Which of the following T-SQL statements transfer the Category table from the Construction schema to the Sale schema?

Options are :

  • ALTER SCHEMA [Sale] TRANSFER [Category].[ Construction]
  • ALTER SCHEMA [Sale] TRANSFER [Construction].[ Category] (Correct)
  • ALTER SCHEMA [Construction] TRANSFER [Category].[Sale]
  • ALTER SCHEMA [Construction] TRANSFER [Sale].[ Category]

Answer : ALTER SCHEMA [Sale] TRANSFER [Construction].[ Category]

Explanation You should use the ALTER SCHEMA [Sale] TRANSFER [Construction].[ Category] statement. This statement will transfer the Category table from the Construction schema to the Sale schema.

You need to create a unique constraint on the Test column of the existing Demo table. This table is associated with the People schema. The Test column is configured to not allow nulls.

Which of the following T-SQL statements can you use to complete this task?

Options are :

  • ALTER TABLE People.Demo ADD CONSTRAINT AK_ Test UNIQUE (Test) (Correct)
  • CREATE TABLE People.Demo ADD CONSTRAINT AK_ Test UNIQUE (Test)
  • CREATE TABLE People.Demo ADD CONSTRAINT AK_ Test PRIMARY KEY (Test)
  • ALTER TABLE People.Demo ADD CONSTRAINT AK_ Test PRIMARY KEY (Test)

Answer : ALTER TABLE People.Demo ADD CONSTRAINT AK_ Test UNIQUE (Test)

Explanation You should use the ALTER TABLE People.Demo ADD CONSTRAINT AK_ Test UNIQUE (Test) statement because this statement creates a unique constraint named AK_ Test on the Test column of the People.Demo table.

You need to change the definition of a DML trigger.

Which of the following T-SQL statements would you use to achieve this goal?

Options are :

  • CREATE TRIGGER
  • ALTER TRIGGER (Correct)
  • DROP TRIGGER
  • ALTER SCHEMA

Answer : ALTER TRIGGER

Explanation You can use the ALTER TRIGGER statement to modify the definition of a DML trigger.

You need to add a new column to an existing table in your company's database. This column should have the name Demo and use nvarchar 100 type.

Which of the following statement should you use to complete this task?

Options are :

  • ALTER TABLE (Correct)
  • ALTER COLUMN
  • ALTER SCHEMA
  • ALTER DATABASE

Answer : ALTER TABLE

Explanation You should use ALTER TABLE statement to add a column to an existing table.

You have a database that contains a table named Employee. And table store information about the employees of the company.

You need to implement the below auditing rules for employee table:

  • Record any changes that made to the data in employee table
  • Customize the data recorded by the audit operations

Which of the solution meet the requirements?

Options are :

  • Common Table Expression(CTE)
  • Stored Procedure
  • User-defined functions (Correct)
  • Check Constraint

Answer : User-defined functions

Explanation SQL server 2016 provides two features that track changes to data in database: change data capture and change tracking. These features enable applications to determine the DML changes that are made to tables.

You need to create markers within a transaction that a user can return to without necessary rolling back the entire transaction.

Which of the following T-SQL statements can you use to complete this goal?

Options are :

  • SAVE TRANSACTION (Correct)
  • END TRANSACTION
  • BEGIN TRANSACTION
  • COMMIT TRANSACTION

Answer : SAVE TRANSACTION

Explanation You should use the SAVE TRANSACTION statement to create a savepoint within a transaction.

You need to ensure that values inserted in the record column of the Result table are between 12 and 19, inclusive.

Which of the following T-SQL statements can you use to complete this task?

Options are :

  • ALTER TABLE dbo.Result ADD CONSTRAINT PK_Result PRIMARY KEY (Result)
  • ALTER TABLE dbo.Result ADD CONSTRAINT CHK_Result CHECK (record > 11 and record < 20) (Correct)
  • ALTER TABLE dbo.Result ADD CONSTRAINT UN_Result UNIQUE (Result)
  • ALTER TABLE dbo.Result ADD CONSTRAINT CHK_Result CHECK (record < 11 and record > 20)

Answer : ALTER TABLE dbo.Result ADD CONSTRAINT CHK_Result CHECK (record > 11 and record < 20)

Explanation You should use the ALTER TABLE dbo.Result ADD CONSTRAINT CHK_Result CHECK (record > 11 and record < 20) statement because this statement creates a check constraint that ensures that values inserted in the record column are between 12 and 19, inclusive.

You have a database named INFORMATION and it contains Customers, CustomertoAccBridge, Customerinfo. You create the Customers table by using the Follwing T-SQL statement.

CREATE TABLE Customers (

            Customer_no int NOT NULL,

            Customer_name varchar(50) NOT NULL,

            Gender bit,

            Address varchar(50),

            Status char(2),

            Customer_status bit NOT NULL,

            Postal_code char(5),

            Account1 char(7),

            Account1_status bit,

           Account2 char(7),

           Account2_status bit,

           CONSTRAINT pk_customer PRIMARY KEY CLUSTERED (Customer_no)

);

The value of the Customer_status column is equal to one for active customer. The value of the Account1_status and Account2_status equal to one for active accounts. You have a web schema.

You have to create a view named web.customer and it must meet the following requirements:

            - allows users to access the Customer_name and Customer_no columns for active customers.

            - allows changes to the columns that the view references. Modified data must be visible through the view.

            - prevent the view from being published as part of Microsoft SQL server replication.

For creating view web.customer you have to select the appropriate options?

CREATE VIEW web.customer

OPTION 1

As

SELECT cust.Customer_name, cust.Customer_no

FROLM Customers as cust

WHERE cust.Customer_status=1

OPTION 2

Options are :

  • OPTION 1= WITH SCHEMABINDING OPTION 2= WITH CHECK OPTION
  • OPTION 1= WITH CHECK OPTION OPTION 2= WITH SCHEMABINDING
  • OPTION 1= WITH SCHEMABINDING OPTION 2= WITH ENCRYPTION
  • OPTION 1= WITH ENCRYPTION OPTION 2= WITH CHECK OPTION (Correct)

Answer : OPTION 1= WITH ENCRYPTION OPTION 2= WITH CHECK OPTION

Explanation Option 1: WITH ENCRYPTION, because it prevents the view from being published as a part of SQL server replication. Option 2: WITH CHECK OPTION, because data modification statements are executed against the conditions. When a data is modified through a view using CHECK OPTION make sure the data remains visible through the view after the modification is committed.

You used SQL server 2016 database that contains a table named People. The statistics for People have been configured using AUTO_UPDATE_STATISTICS set to ON.

When the statistics are updated, users report timeout errors for their queries.

You need to prevent query timeout errors during statistic update for Table People.

What should you do?

Options are :

  • Execute UPDATE STATISCTICS with the FULLSCAN option.
  • Configure the DELAYED_DURABLITY database option to ALLOWED.
  • Execute UPDATE STATISTICS with the RESAMPLE option.
  • Configure the AUTO_UPDATE_STATISTICS_ASYNC database option to ON. (Correct)

Answer : Configure the AUTO_UPDATE_STATISTICS_ASYNC database option to ON.

Explanation You should configure the AUTO_UPDATE_STATISTICS_ASYNC database option to ON. It will allow the statistics to be updated asynchronously. The reason that queries receive timeout errors because the statistics are being updated and queries need to wait for the update to complete. You should not use DELAYED_DURABLITY database option to ALLOWED. It will increase the performance of the transaction log at the risk of data loss, it will not fix the timeout problems with table People during statistic updates.

You want to inverse changes made during a transaction to the beginning of that transaction.

Which of the following statements can you use to complete this goal?

Options are :

  • ROLLBACK WORK (Correct)
  • COMMIT WORK
  • SAVE TRANSACTION
  • BEGIN TRANSACTION

Answer : ROLLBACK WORK

Explanation You should use the ROLLBACK WORK statement to roll back an implicit or explicit transaction. This statement works in the same way that the ROLLBACK TRANSACTION statement works except that it can only roll back to the beginning of the transaction, not to the savepoint.

You are a database developer for companyX. Your company hosts a web application.

The database contains a table named Item that is defined by the following statements:

CREATE TABLE dbo.Item

(

            ItemID INT IDENTITIY(1,1) PRIMARY KEY CLUSTERED NOT NULL,

            ItemName NVARCHAR(100) NOT NULL,

            ItemPrice DECIMAL(10,2) NOT NULL,

            ISValid BIT DEFAULT(1) NOT NULL

)

You have a table named SaleOrder that contains information about items sold to clients. The lines of the table definition are as follows (Here, Linenumbers included for reference only):

01 CREATE TABLE dbo.SaleOrder

02 (

03        SaleOrderId INT IDENTITY(1,1) PRIMARY KEY NONCLUSTERED NOT NULL,

04        ItemID INT NOT NULL,

05        SaleDate DATETIME DEFAULT (GETDATE()),

06        Qty DECIMAL(10,2) NOT NULL

07 )

You have the following requirements:

  • Ensue that the ItemPrice column in the Item table always has a positive value.
  • Ensure that deleted items are retained in the table with the ISValid flag set to 0.
  • Extend the existing Item table by adding a field ItemISONum. That field will store numeric values that are smaller than 180 and this field will always have a value.

You have to create a function that returns all unsold items for a given month.

Solution: Introduce a check constraint that will perform a check: ItemPrice>=0.

Does this solution meet the goal?

Options are :

  • No
  • Yes (Correct)

Answer : Yes

Explanation This solution meets the goal. You should introduce a check constraint that will perform a check: ItemPrice>=0. The goal is that the value in the ItemPrice field is a positive value.

You want to create a stored procedure that returns the minimum price for ItemID stored in the Demo.Items table.

Which of the following T-SQL statements can you use to complete this task?

Options are :

  • CREATE PROCEDURE Demo.uspItemMinData AS SET NOCOUNT ON; SELECT ItemID, MAX(price) FROM Demo.Items WHERE ItemID IS NOT NULL GROUP BY ItemID;
  • CREATE PROCEDURE Demo.uspItemMinData AS SET NOCOUNT ON; SELECT ItemID, AVG(price) FROM Demo.Items WHERE ItemID IS NOT NULL GROUP BY ItemID;
  • CREATE PROCEDURE Demo.uspItemMinData AS SET NOCOUNT ON; SELECT ItemID, STDEV(price) FROM Demo.Items WHERE ItemID IS NOT NULL GROUP BY ItemID;
  • CREATE PROCEDURE Demo.uspItemMinData AS SET NOCOUNT ON; SELECT ItemID, MIN(price) FROM Demo.Items WHERE ItemID IS NOT NULL GROUP BY ItemID; (Correct)

Answer : CREATE PROCEDURE Demo.uspItemMinData AS SET NOCOUNT ON; SELECT ItemID, MIN(price) FROM Demo.Items WHERE ItemID IS NOT NULL GROUP BY ItemID;

Explanation You should use this statement because this statement creates a stored procedure that returns the minimum price for ItemID stored in the Demo.Items table.

Comment / Suggestion Section
Point our Mistakes and Post Your Suggestions