70-761 Querying Data with Transact-SQL Practice Questions
Prepare for 70-761 with more than an answer.
- 1
You are required to generate a table named Expenses that includes an ID, a Motivation, and a Total column.
You informed that the ID column should be configured as the table’s primary key, which has to be a value that is globally exclusive. When INSERT procedures are executed, the value must be created automatically.
The Motivation column should be configured to allow for values from various languages, with a maximum of 600 characters for explaining why a payment was made.
The Total column should be configure to contain monetary values, and should not result in computations generating rounding inaccuracies.
You need to use the correct data type for the Motivation column.
Solution: You make use of the Nvarchar data type.
Has the requirement been satisfied?
Show answer details
Correct answer: B
Explanation:
Nvarchar stores UNICODE data. If you have requirements to store UNICODE or multilingual data, nvarchar is the choice. Varchar stores ASCII data and should be your data type of choice for normal use. - 2
You are preparing to create a table using a Transact-SQL statement. You need to assign a property to a column, which prompts the Database Engine to supply a unique, incremental value when a new row is added to the table.
Which of the following is property you should assign?
Show answer details
Correct answer: C
Explanation:
IDENTITY indicates that the new column is an identity column. When a new row is added to the table, the Database Engine provides a unique, incremental value for the column. Identity columns are typically used with PRIMARY KEY constraints to serve as the unique row identifier for the table. msdn.microsoft.com/en-us/library/ms174979.aspx">References: https://msdn.microsoft.com/en-us/library/ms174979.aspx - 3
You are tasked with making sure that Users are capable of obtaining data from the tables in a specific database in an informal manner. The data retrieved should be referenced as a single table.
You are informed that users must be able to retrieve the necessary information according to the variables stated when the query is made.
Solution: Include a TRY_PARSE function in the Transact-SQL query.
Has the requirement been satisfied?
Show answer details
Correct answer: A
A
- 4
You informed that the ID column should be configured as the table’s primary key, which has to be a value that is globally exclusive. When INSERT procedures are executed, the value must be created automatically.
The Motivation column should be configured to allow for values from various languages, with a maximum of 600 characters for explaining why a payment was made.
The Total column should be configure to contain monetary values, and should not result in computations generating rounding inaccuracies.
You need to use the correct data type for the Total column.
Solution: You make use of the Float data type.
Has the requirement been satisfied?
Show answer details
Correct answer: B
Explanation: docs.microsoft.com/en-us/sql/t-sql/data-types/float-and-real-transact-sql">References: https://docs.microsoft.com/en-us/sql/t-sql/data-types/float-and-real-transact-sql
- 5
A table, named Deals, is referenced by a number of processes that places the information in various databases throughout the business. Data type conversion errors are produced as a result of certain processes not being entirely cognizant of the data types in the Deals table.
You need to make sure that, rather than triggering an error, a NULL value is returned for data conversion failure.
Solution: You make use of the TRY_CONVERT function in your Transact-SQL statement.
Has the requirement been satisfied?
Show answer details
Correct answer: B
Explanation: TRY_CONVERT returns a value cast to the specified data type if the cast succeeds; otherwise, returns null. docs.microsoft.com/en-us/sql/t-sql/functions/try-convert-transact-sql">References: https://docs.microsoft.com/en-us/sql/t-sql/functions/try-convert-transact-sql
- 6
Your organization’s Sales department has two tables named Clients and Purchases.
The Sales department requires an inventory of all client IDs and the date of the last purchase made by each client. This info should be recorded in two columns, named ClientID and LatestPurchaseDate.
A specified date has to be returned for clients that have not made any purchases.
Your Transact-SQL statement has to include a specific type of JOIN.
Solution: You include a CROSS JOIN.
Has the requirement been satisfied?
Show answer details
Correct answer: B
B
