Developing SQL Databases - Exam 70-762 Certification

Which type of lock also knows as read lock, and it is used for SELECT, INSERT, UPDATE, and DELETE operations and is released as soon as data has been read from the locked resources?

Options are :

  • Update(U)
  • Exclusive(X)
  • Intent shared(IS)
  • Shared(S) (Correct)

Answer : Shared(S)

Explanation Shared(S) lock also called read lock and it is used with DML statements and it released lock when data is read from the locked resources.

You have to shrink a database file because it grows too large. Your manager recommends use the T-SQL command called SHRINKDATABASE with 2 parameters. What unit of the second parameter?

DBCC SHRINKDARABASE (UserDB, 10);

Options are :

  • System code
  • MB
  • Percentage (Correct)
  • Threads to use

Answer : Percentage

Explanation The second parameter sets the percentage of free space in the database after the shrink operation.

You have an existing unique constraint named UQ_Pwd on the PwdHash column of the Password table located in the People schema. You need to extend this unique constraint to include the PwdSalt column. The table has an existing primary key constraint.

Solution: You run the following T-SQL statement:

  ALTER TABLE People.Password DROP CONSTRAINT UQ_Pwd

  ALTER TABLE People.Password ADD CONSTRAINT UQ_Pwd UNIQUE (PwdSalt, PwdHash)

Does the solution meet the goal?

Options are :

  • Yes (Correct)
  • No

Answer : Yes

Explanation To modify an existing unique constraint, you need to drop the original and then create a new constraint with the same name. So, you should use the ALTER TABLE People.Password DROP CONSTRAINT UQ_Pwd statement. You should use the ALTER TABLE People.Password ADD CONSTRAINT UQ_Pwd UNIQUE (PwdSalt, PwdHash) statement because After you have dropped the original constraint, you can create a new unique constraint on the appropriate columns by executing this statement.

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

- Statements in other transactions should not be able to read data altered by the current transaction until the current transaction commits and Statements in the transaction should not be able to read data altered by other transactions if those transactions have not been committed.

You want to also ensure that other transactions should be able to insert new rows that meet the search conditions of statements in the current transaction.

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

Options are :

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

Answer : SET TRANSACTION ISOLATION LEVEL REPEATABLE READ

Explanation When the REPEATABLE READ isolation level is set, it is possible for other transactions to insert new rows that meet search conditions of statements included in the current transaction.

There are very large tables in the Demo database. You want to create a database object that users in the Indian office can issue select queries against that will return only results that have the Country column of the Sale table set to the value India.

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

Options are :

  • DROP VIEW
  • CREATE PROCEDURE
  • DROP PROCEDURE
  • CREATE VIEW (Correct)

Answer : CREATE VIEW

Explanation You should use the CREATE VIEW statement because users can execute select queries against views. You could create a view on the Sale table that was defined so that the Country column equaled the value India. You should not use the CREATE PROCEDURE statement because users cannot execute select queries against stored procedures. You should not use DROP VIEW statement because it allows you to remove an existing view. You should not use the DROP PROCEDURE statement because it allows you to remove a stored procedure.

You have a SQL Server 2016 database. You need to monitor I/O performance.

Which three types of counter should you track? (Choose all possible options)

Options are :

  • Throughput (Correct)
  • Latency (Correct)
  • Used space
  • I/O operation per second (Correct)

Answer : Throughput Latency I/O operation per second

Explanation You should track Latency, Throughput, I/O operation per second to monitor I/O performance. A latency counter tracks how long I/O commands take to be executed. A throughput counter tracks the volume of data that can pass through the I/O channel. I/O operation per second counter tracks the total number of I/O operations that can occur per second.

You have a database named Demo. Database does not have a memory optimized filegroup. You create a table by running the following T-SOL statement:

CREATE TABLE demoTransaction

(

            [ID] [int] NOT NULL PRIMARY KEY,

            [Date][date] NOT NULL,

            [AccountId] [int] NOT NULL,

            [ValueType] [char] (3) NOT NULL,

            [Amount] [decimal] (20,2) NULL

);

Table is currently used for OLTP workloads. The analytics user group needs to perform real-time operational analytics that scan records in the table to aggregate on many columns. You need to add the most efficient index to support analytics workload without changing the OLTP application.

What should you do?

Options are :

  • Create a nonclustered index on the table
  • Create a nonclustered column store index on the table (Correct)
  • Create a hash index on the table
  • Create a clustered column store index on the table

Answer : Create a nonclustered column store index on the table

Explanation A nonclustered column store index enables real-time operational analytics in which the OLTP workload uses the underlying clustered index, while analytics run concurrently on the column store index.

You need to disable the existing trigger named reminder that applies to the Demo table. You need to re-enable this trigger in the future.

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

Options are :

  • ALTER TRIGGER reminder ON Demo
  • DROP TRIGGER reminder
  • DISABLE TRIGGER reminder ON Demo (Correct)
  • ENABLE TRIGGER reminder ON Demo

Answer : DISABLE TRIGGER reminder ON Demo

Explanation You should use the DISABLE TRIGGER reminder ON Demo statement to disable the reminder trigger that applies to the Demo table.

You need to inverse changes made during a transaction to a specific savepoint.

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

Options are :

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

Answer : ROLLBACK TRANSACTION

Explanation You should use ROLLBACK TRANSACTION statement because it is used to rollback an implicit or explicit transaction. You can use this statement to rollback to a savepoint or to the starting of the transaction.

You are a Database Administrator for a company. Company has a server that contains many physical disks and these disks are not part of Redundant Array of independent disk(RAID). The server hosts four Microsoft SQL server instances. Many SQL jobs runs during off-peak hours.

You have to monitor the SQL server instances in real time and optimize server to maximize throughput, response time and overall SQL performance.

What should you do?

Options are :

  • Create a SQL Profiler trace.
  • Create a sys.dm_os_waiting_tasks query.
  • Create a Performance Monitor Data CollectorSet.
  • Create a sys.dm_exec_sessions query. (Correct)

Answer : Create a sys.dm_exec_sessions query.

Explanation SQL server sys.dm_exec_sessions returns one row per authenticated session on SQL server. It gives information about all active user connection and internal tasks.

You have created a new table named Resource in the Demo database. This table is in the Test schema and has a DemoID column that is set for not allow null values. You need to create a relationship between DemoID column in this new table and the DemoID column in the Stock table and located in the Test schema.

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

Options are :

  • ALTER TABLE Resource.Test ADD CONSTRAINT FK_ DemoID PRIMARY (DemoID) REFERENCES Stock.Test (DemoID)
  • ALTER TABLE Test.Resource ADD CONSTRAINT FK_ DemoID FOREIGN KEY (DemoID) REFERENCES Test.Stock (DemoID) (Correct)
  • ALTER TABLE Resource.Test ADD CONSTRAINT FK_ DemoID FOREIGN KEY (DemoID) REFERENCES Stock.Test (DemoID)
  • ALTER TABLE Test.Resource ADD CONSTRAINT FK_ DemoID PRIMARY (DemoID) REFERENCES Test.Stock (DemoID)

Answer : ALTER TABLE Test.Resource ADD CONSTRAINT FK_ DemoID FOREIGN KEY (DemoID) REFERENCES Test.Stock (DemoID)

Explanation You should use the ALTER TABLE Test.Resource ADD CONSTRAINT FK_ DemoID ID FOREIGN KEY (DemoID) REFERENCES Test.Stock (DemoID) statement. This statement creates the correct foreign key constraint between the DemoID column in the Test.Resource table and the DemoID column in the Test.Stock table.

Which of the following transaction isolation level avoid most of blocking problems without the risk of dirty reads?  

Options are :

  • Serializable or Snapshot
  • Read committed or Repeatable Read
  • Snapshot or Read committed snapshot (Correct)
  • Read Uncommitted or Read committed

Answer : Snapshot or Read committed snapshot

Explanation Snapshot or Read committed snapshot avoid blocking problems without risk of dirty reads and both of these options require plenty of space in tempdb database.

Which one of the following conversion is NOT allowed either implicitly or explicitly using CONVERT () or CAST () conversion functions?

Options are :

  • Datetime to text (Correct)
  • Date to varchar
  • Varchar to decimal
  • Decimal to bit

Answer : Datetime to text

Explanation We are allowed to convert text data type into very few data type. It include varchar, char, ntext, nchar. Conversion of datetime to text is not allowed using either CAST or CONVERT.

You are writing a set of queries against FILESTREAMS enabled database. You have to create a stored procedure that will update multiple tables within a transaction. You have to ensure that if the stored procedure raises a runtime error, the entire transaction is terminated and rolled back.

Which T-SQL statement should you include at the starting of the stored procedure?

Options are :

  • SET XACT_ABORT ON (Correct)
  • SET XACT_ABORT OFF
  • SET IMPLICIT_TRANSACTIONS ON
  • SET IMPLICIT TRANSACTIONS OFF

Answer : SET XACT_ABORT ON

Explanation When SET XACT_ABORT is ON, T-SQL statement raises a runtime error, the entire transaction is terminated and rolled back.

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

Options are :

  • Money
  • Smallmoney
  • Float
  • Numeric (Correct)

Answer : Numeric

Explanation Numeric data type can give a fixed precision and scale of six digits.

You are developing an application to track people sales.

You need to create a database object that meets the following requirements:

- Launch when table data is modified.

- Evaluate state a table after and before a data modification and act based on the difference.

- To Prevent incorrect or malicious table data operations and Prevent changes that violate referential integrity by cancelling the attempted data modification.

- Run managed code packaged in an assembly that is created in the 

Microsoft.NET Framework and located into MS SQL Server.

What should you create?

Options are :

  • User defined procedure
  • CLR Procedure
  • DML trigger (Correct)
  • Table-valued function

Answer : DML trigger

Explanation DML triggers are used to enforce business rules when data is modified and to extend the integrity checking logic of MS SQL Server constraints, rules and defaults.

You are developing an application to track people sales. You need to return sum of orders that have been finalized, given a specified order identifier. This value will be used in other T-SOL statements. You need to create a database object.

What should you create? 

Options are :

  • CLR Procedure
  • table-valued function
  • scalar-valued function (Correct)
  • DML trigger

Answer : scalar-valued function

Explanation Scalar function returns a single data value. Table-valued functions return a table data type.

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: Alter the Item table by adding a field named ItemISONum tinyint null.

Does this solution meet the goal?

Options are :

  • Yes
  • No (Correct)

Answer : No

Explanation This solution does not meet the goal because you should not alter the table Item by adding a field ItemISONum tinyint null. The requirement is that ItemISONum field always has a value. The tinyint data type can store values from 0 up to 255.

You are modifying an existing database. You want to remove a primary key constraint named PK_DemoID from the Login table. This table is in the Demo schema of the newdemo database. The constraint is applied on the DemoID column.

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

Options are :

  • ALTER TABLE Demo.Login DROP CONSTRAINT DemoID
  • ALTER TABLE Demo.Login DROP CONSTRAINT PK_ DemoID (Correct)
  • ALTER COLUMN PK_ DemoID DROP CONSTRAINT DemoID
  • ALTER COLUMN DemoID DROP CONSTRAINT PK_ DemoID

Answer : ALTER TABLE Demo.Login DROP CONSTRAINT PK_ DemoID

Explanation You should use the ALTER TABLE Demo.Login DROP CONSTRAINT PK_ DemoID statement. This statement drops the constraint PK_ DemoID from the Login table.

You are the administrator at a company that uses SQL Server 2016 database.

You are developing an application to manage tracing data from the database. This data needs to be correlated with system data from the server before sending it to the application.

You need to provide tracing information to the application.

Which utility should you use?

Options are :

  • Event Notification
  • Query plans
  • System Data collection sets
  • SQL server Extended Events (Correct)

Answer : SQL server Extended Events

Explanation You should use SQL Server Extended Event. It is used to generate event data, process that data, correlate it with system events if necessary, and send it to a target. You should not use Event Notification, because it is used to send event information to a service broker service and used to send information to an application, they cannot correlate event information from the system. You should not use Query plans, because it used when analyzing query performance in a database Query plans, and not used to send tracing information to an application. You should also not use System Data Collection sets, because it is used to monitor Disk usage, Server activity and query statistics.

You want to create a stored procedure that outputs customer's first name, last name, phone number, and email address. You want to ensure that the definition of this procedure is not available to anyone who can query the systemcatalogs.

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

Options are :

  • CREATE PROCEDURE dbo.spNamePhoneEmail WITH ENCRYPTION AS SET NOCOUNT ON; SELECT MiddleName, LastName, City, PhoneNumber FROM dbo.vCustomer;
  • CREATE PROCEDURE dbo.spNamePhoneEmail AS SET NOCOUNT ON; SELECT FirstName, LastName, EmailAddress, PhoneNumber FROM dbo.vCustomer;
  • CREATE PROCEDURE dbo.spNamePhoneEmail AS SET NOCOUNT ON; SELECT MiddleName, LastName, City, PhoneNumber FROM dbo.vCustomer;
  • CREATE PROCEDURE dbo.spNamePhoneEmail WITH ENCRYPTION AS SET NOCOUNT ON; SELECT FirstName, LastName, EmailAddress, PhoneNumber FROM dbo.vCustomer; (Correct)

Answer : CREATE PROCEDURE dbo.spNamePhoneEmail WITH ENCRYPTION AS SET NOCOUNT ON; SELECT FirstName, LastName, EmailAddress, PhoneNumber FROM dbo.vCustomer;

Explanation Executing this statement creates a stored procedure that outputs customer’s first name, last name, email address, and phone number and has a definition that cannot be extracted by querying the systemcatalogs. Using the WITH ENCRYPTION option enables you to obfuscate the stored procedure's definition.

Your company has workers in different regions around the world. You have to create a database table that stores following workers attendance information:

-Worker_id

-Date and time worker checked into work

-Date and time worker checked out of work

Date and time information must be time zone aware and not store fractional seconds.

Solution: you run the following T-SQL statement:

CREATE TABLE Worker (

            Worker_id int NOT NULL.

            dateCheckedIn Datetimeoffset(0) NOT NULL,

            dateCheckedOut Datetimeoffset(0) NOT NULL);

Does the given solution meet the goal?

Options are :

  • Yes (Correct)
  • No

Answer : Yes

Explanation Datetimeoffset(0), not datetimeofset defines a date that is combined with a time of a day that has time zone awareness and it is based on a 24-hour clock. When we use datetimeoffset (0) fractional second precision is not store. Syntax: datetimeoffset [(fractional second precision)]

You are developing a table named Items that has a column named ItemNumber.

The ItemNumber column requires a special format. The contents of the ItemNumber column must always start with the letters “IT_? followed by 4 digits.

The ItemNumber column is also used in other tables. The special formatting requirement for the ItemNumber column applies to all tables in which the ItemNumber column is used.

You have to ensure that the special formatting is implemented on the ItemNumber column. Your solution must minimize the effort required.

Solution: You create a user defined data type.

Does this solution meet the goal?

Options are :

  • No
  • Yes (Correct)

Answer : Yes

Explanation This solution meets the goal. The user defined data type can be used directly in the table definition and can be used in multiple tables.

You need to remove a table in a database.

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

Options are :

  • CREATE TABLE
  • ALTER TABLE
  • SELECT . . . INTO
  • DROP TABLE (Correct)

Answer : DROP TABLE

Explanation You should use the DROP TABLE to remove a table from a database.

You need to create a stored procedure that accepts worker first name and last name as input parameters and returns first name, last name and address as output.

Solution: You run the following T-SQL statement:

   CREATE PROCEDURE Demo.uspWorkerAddress

   @LastName nvarchar(40),

   @FirstName nvarchar(40)

   AS

   SET NOCOUNT ON;

   SELECT FirstName, LastName, Address

   FROM Demo.vWorker

   WHERE FirstName = @FirstName AND LastName = @LastName;

  Does the solution meet the goal?

Options are :

  • Yes (Correct)
  • No

Answer : Yes

Explanation This statement creates a stored procedure that accepts worker first name and last name as input parameters and returns first name, last name and address as output.

You are developing a table named Items that has a column named ItemNumber.

The ItemNumber column requires a special format. The contents of the ItemNumber column must always start with the letters “IT_? followed by 4 digits.

The ItemNumber column is also used in other tables. The special formatting requirement for the ItemNumber column applies to all tables in which the ItemNumber column is used.

You have to ensure that the special formatting is implemented on the ItemNumber column. Your solution must minimize the effort required.

Solution: You add a default definition.

Does this solution meet the goal?

Options are :

  • No (Correct)
  • Yes

Answer : No

Explanation This solution does not meet the goal because a DEFAULT definition would have to be included in every table that uses the ItemNumber column.

AFTER triggers are used for doing data validations. Is the statement true?

Options are :

  • False
  • True (Correct)

Answer : True

Explanation AFTER triggers perform after a DML operation. They are used for doing data validations.

Comment / Suggestion Section
Point our Mistakes and Post Your Suggestions