NEW QUESTION 1
You have a database that contains a table named Products in the sales schema. The table was created by running the following Transact-SQL statement:

The table includes the data shown below:

You are developing a report that displays the following values and column headers in the order listed below:
• average price of a product named Average
• the smallest number of products in stocK-named LowestNumber
• the highest product price named HighestPrice
You need to write a query to return the results for the report The query must meet the following requirements: You need to write a query to return the results for the report. The query must meet the following requirements:
• Use built-in, aggregate anfa*mathematical functions.
• Use two-part names and tables.
• Use the table alias to qualify column names.
• Define the alias for all fields by using the AS keyword.
• Use the first letter of the table name as the table alias.
• Do not use the Row_number function.
• Do not surround object names with square brackets.
• Do not use variables.
Part of the correct Transact-SQL has been provided in the answer area below. Enter the code in the answer area that resolves the problem and meets the stated goals or requirements. You can add code within the code that has been provided as well as below it.

• A. Mastered
• B. Not Mastered

Answer: A

Explanation:
TRY_Convert

NEW QUESTION 2
Note: This question is part of a series of questions that present the same scenario. Each question in the series contains a unique solution that might meet the stated goals. Some question sets might have more than one correct solution, while others might not have a correct solution.
After you answer a question in this section. You will NOT be able to return to it. As a result, these questions will not appear in the review screen.
You create a table named Products by running the following Transact-SQL statement:

You have the following stored procedure:

You need to modify the stored procedure to meet the following new requirements:
Insert product records as a single unit of work.
Return error number 51000 when a product fails to insert into the database.
If a product record insert operation fails, the product information must not be permanently written to the database.
Solution: You run the following Transact-SQL statement:

Does the solution meet the goal?

• A. Yes
• B. No

Answer: B

NEW QUESTION 3
Note: This question is part of a series of questions that use the same or similar answer choices. An answer choice may be correct for more than one question in the series. Each question is independent of the other questions in this series. Information and details provided in a question apply only to that question.
You have a database that contains tables named Customer_CRMSystem and Customer_HRSystem. Both tables use the following structure:

The tables include the following records: Customer_CRMSystem

Customer_HRSystem

Records that contain null values for CustomerCode can be uniquely identified by CustomerName. You need to display a list of customers that do not appear in the Customer_HRSystem table. Which Transact-SQL statement should you run?

• A. Option A
• B. Option B
• C. Option C
• D. Option D
• E. Option E
• F. Option F
• G. Option G
• H. Option H

Answer: D

Explanation:
EXCEPT returns distinct rows from the left input query that aren’t output by the right input query. References: https://msdn.microsoft.com/en-us/library/ms188055.aspx

NEW QUESTION 4
Note: This question is part of a series of questions that present the same scenario. Each question in the series contains a unique solution that might meet the stated goals. Some question sets might have more than one correct solution, while others might not have a correct solution.
After you answer a question in this section. You will NOT be able to return to it. As a result, these questions will not appear in the review screen.
You create a table named Customer by running the following Transact-SQL statement:

You must insert the following data into the Customer table:

You need to ensure that both records are inserted or neither record is inserted. Solution: You run the following Transact-SQL statement:

Does the solution meet the goal?

• A. Yes
• B. No

Answer: B

NEW QUESTION 5
You have the following Transact-SQL statement: DELETE FROM Person
WHERE PersonID = 5
You need to implement error handling.
How should you complete Transact-SQL statement? To answer, select the appropriate options in the answer area.
NOTE: Each correct selection is worth one point.

• A. Mastered
• B. Not Mastered

Answer: A

Explanation:

NEW QUESTION 6
You are building a stored procedure named SP1 that calls a stored procedure named SP2.
SP2 calls another stored procedure named SP3 that returns a Recordset. The Recordset is stored in a
temporary table.
You need to ensure that SP2 returns a text value to SP1. What should you do?

• A. Create a temporary table in SP2, and then insert the text value into the table.
• B. Return the text value by using the ReturnValue when SP2 is called.
• C. Add the txt value to an OUTPUT parameter of SP2.
• D. Create a table variable in SP2, and then insert the text value into the table.

Answer: C

NEW QUESTION 7
You have a database that contains the following tables:

A delivery person enters an incorrect value for the CustomerID column in the Invoices table and enters the following text in the ConfirmedReceivedBy column: “Package signed for by the owner Tim.”
You need to find the records in the Invoices table that contain the word Tim in the CustomerName field. How should you complete the Transact-SQL statement? To answer, drag the appropriate Transact-SQL
segments to the correct locations. Each Transact-SQL segment may be used once, more than once, or not at all. You may need to drag the split bar between panes or scroll to view content.
NOTE: Each correct selection is worth one point.

• A. Mastered
• B. Not Mastered

Answer: A

Explanation:
Box 1: SELECT CustomerID FROM Sales.Invoices
Box 2: INNER JOIN Sales.Customers.CustomerID = Sales.Invoices.CustomerID Box 3: WHERE CustomerName LIKE '%tim%'
Box 4: WHERE ConfirmedReceiveBy IN (SELECT CustomerName FROM Sales.Customers)

NEW QUESTION 8
Note: This question is part of a series of questions that present the same scenario. Each question in the series contains a unique solution that might meet the stated goals. Some question sets might have more than one correct solution, while others might not have a correct solution.
After you answer a question in this section, you will NOT be able to return to it. As a result, these questions will not appear in the review screen.
You have a database named DB1 that contains two tables named Sales.Customers and Sales.Orders. Sales.Customers has a foreign key relationship to a column named CustomerID in SalesOrders.
You need to recommend a query that returns all the customers. The query must also return the number of orders that each customer placed in 2021.
Solution: You recommend the following query:

Does this meet the goal?

• A. Yes
• B. No

Answer: B

NEW QUESTION 9
You have a table named Cities that has the following two columns: CityID and CityName. The CityID column uses the int data type, and CityName uses nvarchar(max).
You have a table named RawSurvey. Each row includes an identifier for a question and the number of persons that responded to that question from each of four cities. The table contains the following representative data:

A reporting table named SurveyReport has the following columns: CityID, QuestionID, and RawCount, where RawCount is the value from the RawSurvey table.
You need to write a Transact-SQL query to meet the following requirements:
- Retrieve data from the RawSurvey table in the format of the SurveyReport table.
- The CityID must contain the CityID of the city that was surveyed.
- The order of cities in all SELECT queries must match the order in the RawSurvey table.
- The order of cities in all IN statements must match the order in the RawSurvey table. Construct the query using the following guidelines:
- Use one-part names to reference tables and columns, except where not possible.
- ALL SELECT statements must specify columns.
- Do not use column or table aliases, except those provided.
- Do not surround object names with square brackets.


Part of the correct Transact-SQL has been provided in the answer area below. Enter the code in the answer area that resolves the problem and meets the stated goals or requirements. You can add code within the code that has been provided as well as below it.

Use the Check Syntax button to verify your work. Any syntax or spelling errors will be reported by line and character position.

• A. Mastered
• B. Not Mastered

Answer: A

Explanation:
UNPIVOT must be used to rotate columns of the Rawsurvey table into column values. References: https://technet.microsoft.com/en-us/library/ms177410(v=sql.105).aspx

NEW QUESTION 10
You have a database that includes the following tables. All of the tables are in the Production schema.

You need to create a query that returns a list of product names for all products in the Beverages category. Construct the query using the following guidelines:
Use the first letter of the table name as the table alias.
Use two-part column names.
Do not surround object names with square brackets.
Do not use implicit joins.
Do not use variables.
Use single quotes to surround literal values.
Part of the correct Transact-SQL has been provided in the answer area below. Enter the code in the answer area that resolves the problem and meets the stated goals or requirements. You can add code within the code that has been provided as well as below it.

Use the Check Syntax button to verify your work. Any syntax or spelling errors will be reported by line and character position. You may check syntax as many times as needed.

• A. Mastered
• B. Not Mastered

Answer: A

Explanation:
1 SELECT p.productname
2 FROM Production.categories AS c
3 inner join production.products as p on c.categoryid=p.categoryid 4 WHERE c.categoryname = 'Beverages'
Note: On line 3 change * to =

NEW QUESTION 11
You have a database named MyDb. You run the following Transact-SQL statements:

A value of 1 in the IsActive column indicates that a user is active.
You need to create a count for active users in each role. If a role has no active users. you must display a zero as the active users count.
Which Transact-SQL statement should you run?

• A. Option A
• B. Option B
• C. Option C
• D. Option D

Answer: C

NEW QUESTION 12
You have a table that was created by running the following Transact-SQL statement:

You need to query the Courses table and return the result set as JSON. The output from the query must resemble the following format:

• A. SELECT CourseID AS [Course ID], Course as Name FROM CoursesFOR JSON PATH('Courses')
• B. SELECT CourseID AS 'Course ID', Course AS Name FROM CoursesFOR JSON ROOT('Courses')
• C. SELECT CourseID AS [Course ID], Course AS Name FROM CoursesFOR JSON AUTO, ROOT('Courses')
• D. SELECT CourseID AS 'Course ID', Course AS Name FROM CoursesFOR JSON AUTO, INCLUDE_NULL_VALUES('Courses')

Answer: D

Explanation:
References:
https://docs.microsoft.com/en-us/sql/relational-databases/json/include-null-values-in-json-include-null-values-op

NEW QUESTION 13
Note: This question is part of a series of questions that use the same scenario. For your convenience, the scenario is repeated in each question. Each question presents a different goal and answer choices, but the text of the scenario is exactly the same in each question on this series.
You have a database that tracks orders and deliveries for customers in North America. System versioning is enabled for all tables. The database contains the Sales.Customers, Application.Cities, and Sales.CustomerCategories tables.
Details for the Sales.Customers table are shown in the following table:

Details for the Application.Cities table are shown in the following table:

Details for the Sales.CustomerCategories table are shown in the following table:

The marketing department is performing an analysis of how discount affect credit limits. They need to know the average credit limit per standard discount percentage for customers whose standard discount percentage is between zero and four.
You need to create a query that returns the data for the analysis.
How should you complete the Transact-SQL statement? To answer, drag the appropriate Transact-SQL segments to the correct locations. Each Transact-SQL segments may be used once, more than once, or not at all. You may need to drag the split bar between panes or scroll to view content.

• A. Mastered
• B. Not Mastered

Answer: A

Explanation:
Box 1: 0, 1, 2, 3, 4
Pivot example:
-- Pivot table with one row and five columns
SELECT 'AverageCost' AS Cost_Sorted_By_Production_Days, [0], [1], [2], [3], [4]
FROM
(SELECT DaysToManufacture, StandardCost FROM Production.Product) AS SourceTable PIVOT
(
AVG(StandardCost)
FOR DaysToManufacture IN ([0], [1], [2], [3], [4])
) AS PivotTable; Box 2: [CreditLimit]
Box 3: PIVOT
You can use the PIVOT and UNPIVOT relational operators to change a table-valued expression into another table. PIVOT rotates a table-valued expression by turning the unique values from one column in the expression into multiple columns in the output, and performs aggregations where they are required on any remaining column values that are wanted in the final output.
Box 4: 0, 1, 2, 3, 4
The IN clause determines whether a specified value matches any value in a subquery or a list. Syntax: test_expression [ NOT ] IN ( subquery | expression [ ,...n ] )
Where expression[ ,... n ]
is a list of expressions to test for a match. All expressions must be of the same type as test_expression. References: https://technet.microsoft.com/en-us/library/ms177410(v=sql.105).aspx

NEW QUESTION 14
Note: This question is part of a series of questions that present the same scenario. Each question in the series contains a unique solution that might meet the stated goals. Some question sets might have more than one correct solution, while others might not have a correct solution.
After you answer a question in this section. You will NOT be able to return to it. As a result, these questions will not appear in the review screen.
You have a database that contains a single table named tblVehicleRegistration. The table is defined as follows:

You run the following query:

The query output window displays the following error message: “Conversion failed when converting the varchar value ‘AB012’ to data type int.”
You need to resolve the error.
Solution: You modify the Transact-SQL statement as follows:

Does the solution meet the goal?

• A. Yes
• B. No

Answer: B

NEW QUESTION 15
You need to create a stored procedure that meets the following requirements:
*Produces a warning if the credit limit parameter is greater than 7,000
*Propagates all unexpected errors to the calling process
How should you complete the Transact-SQL statement? To answer, drag the appropriate Transact-SQP segments to the correct locations. Each Transact-SQL segments may be used once, more than once, or not at all. You may need to drag the split bar between panes or scroll to view content.

• A. Mastered
• B. Not Mastered

Answer: A

Explanation:
Box 1: THROW 51000, 'Warning: Credit limit is over 7,000!",1
THROW raises an exception and transfers execution to a CATCH block of a TRY…CATCH construct in SQL Server.
THROW syntax:
THROW [ { error_number | @local_variable },
{ message | @local_variable },
{ state | @local_variable } ] [ ; ]
Box 2: RAISERROR (@ErrorMessage, 16,1)
RAISERROR generates an error message and initiates error processing for the session. RAISERROR can either reference a user-defined message stored in the sys.messages catalog view or build a message dynamically. The message is returned as a server error message to the calling application or to an associated CATCH block of a TRY…CATCH construct. New applications should use THROW instead.
Severity levels from 0 through 18 can be specified by any user. Severity levels from 19 through 25 can only be specified by members of the sysadmin fixed server role or users with ALTER TRACE permissions. For severity levels from 19 through 25, the WITH LOG option is required.
On Severity level 16. Using THROW to raise an exception
The following example shows how to use the THROW statement to raise an exception. Transact-SQL
THROW 51000, 'The record does not exist.', 1;
Here is the result set.
Msg 51000, Level 16, State 1, Line 1 The record does not exist.
Note: RAISERROR syntax:
RAISERROR ( { msg_id | msg_str | @local_variable }
{ ,severity ,state }
[ ,argument [ ,...n ] ] )
[ WITH option [ ,...n ] ]
Note: The ERROR_MESSAGE function returns the message text of the error that caused the CATCH block of a TRY…CATCH construct to be run.
References:
https://msdn.microsoft.com/en-us/library/ms178592.aspx https://msdn.microsoft.com/en-us/library/ms190358.aspx https://msdn.microsoft.com/en-us/library/ee677615.aspx

NEW QUESTION 16
Note: This question is part of a series of questions that present the same scenario. Each question in the series contains a unique solution that might meet the stated goals. Some question sets might have more than one correct solution, while others might not have a correct solution.
After you answer a question in this section. You will NOT be able to return to it. As a result, these questions will not appear in the review screen.
You have a database that tracks orders and deliveries for customers in North America. The database contains the following tables:
Sales.Customers

Application.Cities

Sales.CustomerCategories

Your company is developing a new social application that connects customers to each other based on the distance between their delivery locations.
You need to write a query that returns the nearest customer. Solution: You run the following Transact-SQL statement:

The variable @custID is set to a valid customer. Does the solution meet the goal?

• A. Yes
• B. No

Answer: A

Explanation:
ShortestLineTo (geometry Data Type) Returns a LineString instance with two points that represent the shortest distance between the two geometry instances. The length of the LineString instance returned is the distance between the two geometry instances.
STLength (geometry Data Type) returns the total length of the elements in a geometry instance. References: https://docs.microsoft.com/en-us/sql/t-sql/spatial-geometry/shortestlineto-geometry-data-type

NEW QUESTION 17
Note: This question is part of a series of questions that use the same or similar answer choices. An answer choice may be correct for more than one question in the series. Each question is independent of the other questions in this series. Information and details provided in a question apply only to that question.
You create a table by running the following Transact-SQL statement:

You are developing a report that displays customer information. The report must contain a grand total column. You need to write a query that returns the data for the report.
Which Transact-SQL statement should you run?

• A. Option A
• B. Option B
• C. Option C
• D. Option D
• E. Option E
• F. Option F
• G. Option G
• H. Option H

Answer: E

Explanation:
Calculate aggregate column through AVG function and GROUP BY clause.

NEW QUESTION 18
You need to create a database object that meets the following requirements:
accepts a product identifies as input
calculates the total quantity of a specific product, including quantity on hand and quantity on order
caches and reuses execution plan
returns a value
can be called from within a SELECT statement
can be used in a JOIN clause
What should you create?

• A. a temporary table that has a columnstore index
• B. a user-defined table-valued function
• C. a memory-optimized table that has updated statistics
• D. a natively-complied stored procedure that has an OUTPUT parameter

Answer: B

Explanation:
A table-valued user-defined function can also replace stored procedures that return a single result set. The table returned by a user-defined function can be referenced in the FROM clause of a Transact-SQL statement, but stored procedures that return result sets cannot.
References: https://technet.microsoft.com/en-us/library/ms191165(v=sql.105).aspx

NEW QUESTION 19
Note: This question is part of a series of questions that use the same or similar answer choices. An answer choice may be correct for more than one question in the series. Each question is independent of the other questions in this series. Information and details provided in a question apply only to that question.
Multiple processes use the data from a table named Sales and place it in other databases across the organization. Some of the processes are not completely aware of the data types in the Sales table. This leads to data type conversion errors.
You need to implement a method that returns a NULL value id data conversion fails instead of throwing an error.
What should you implement?

• A. the COALESCE function
• B. a view
• C. a table-valued function
• D. the TRY_PARSE function
• E. a stored procedure
• F. the ISNULL function
• G. a scalar function
• H. the TRY_CONVERT function

Answer: H

Explanation:
TRY_CONVERT returns a value cast to the specified data type if the cast succeeds; otherwise, returns null. References: https://docs.microsoft.com/en-us/sql/t-sql/functions/try-convert-transact-sql

NEW QUESTION 20
You have a database that contains the following tables.

You need to create a query that returns each complaint, the names of the employees handling the complaint, and the notes on each interaction. The Complaint field must be displayed first, followed by the employee’s name and the notes. Complaints must be returned even if no interaction has occurred.
Construct the query using the following guidelines:
- Use two-part column names.
- Use one-part table names.
- Use the first letter of the table name as its alias.
- Do not Transact-SQL functions.
- Do not use implicit joins.
- Do not surround object names with square brackets.
Part of the correct Transact-SQL has been provided in the answer area below. Enter the code in the answer area that resolves the problem and meets the stated goals or requirements. You can add code within the code that has been provided as well as below it.


1 SELECT c.Complaint, e.Name, i.Notes 2 FROM Complaints c
3 JOIN
4 JOIN

• A. Mastered
• B. Not Mastered

Answer: A

Explanation:
1 SELECT c.Complaint, e.Name, i.Notes
2 FROM Complaints c
3 JOIN Interactions i ON c.ComplaintID = i.ComplaintID
4 JOIN Employees e ON i.EmployeeID = E.EmployeeID

NEW QUESTION 21
Note: This question is part of a series of questions that use the same scenario. For your convenience, the scenario is repeated in each question. Each question presents a different goal and answer choices, but the text of the scenario is exactly the same in each question in this series.
You query a database that includes two tables: Project and Task. The Project table includes the following columns:


You plan to run the following query to update tasks that are not yet started:

You need to return the total count of tasks that are impacted by this UPDATE operation, but are not associated with a project.
What set of Transact-SQL statements should you run?

• A. Option A
• B. Option B
• C. Option C
• D. Option D

Answer: B

Explanation:
The WHERE clause of the third line should be WHERE ProjectID IS NULL, as we want to count the tasks that are not associated with a project.

NEW QUESTION 22
Note: This question is part of a series of questions that present the same scenario. Each question in the series contains a unique solution that might meet the stated goals. Some question sets might have more than one correct solution, while others might not have a correct solution.
After you answer a question in this section. You will NOT be able to return to it. As a result, these questions will not appear in the review screen.
You have a table named Products that stores information about products your company sells. The table has a column named ListPrice that stores retail pricing information for products.
Some products are used only internally by the company. Records for these products are maintained in the Products table for inventory purposes. The price for each of these products is $0.00. Customers are not permitted to order these products.
You need to increase the list price for products that cost less than $100 by 10 percent. You must only increase pricing for products that customers are permitted to order.
Solution: You run the following Transact-SQL statement:

Does the solution meet the goal?

• A. Yes
• B. No

Answer: A

NEW QUESTION 23
Note: This question is part of a series of questions that present the same scenario. Each question in the series contains a unique solution that might meet the stated goals. Some question sets might have more than one correct solution, while others might not have a correct solution.
After you answer a question in this section. you will NOT be able to return to it. As a result, these questions will not appear in the review screen.
You create a table named Customer by running the following Transact-SQL statement:

You must insert the following data into the Customer table:

You need to ensure that both records are inserted or neither record is inserted. Solution: You run the following Transact-SQL statement:

Does the solution meet the goal?

• A. Yes
• B. No

Answer: B

Explanation:
As there are two separate INSERT INTO statements we cannot ensure that both or neither records is inserted.

NEW QUESTION 24
......