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 customers who appear in both tables and have a proper CustomerCode.
Which Transact-SQL statement should you run?

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?

DRAG DROP
You create three tables by running the following Transact-SQL statements:

For reporting purposes, you need to find the active user count for each role, and the total active user count. The
result must be ordered by active user count of each role. You must use common table expressions (CTEs).
Which four Transact-SQL segments should you use to develop the solution? To answer, move the appropriate
Transact-SQL segments from the list of Transact-SQL segments to the answer area and arrange them in the
correct order.
Select and Place:

SIMULATION
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.

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?

DRAG DROP
You have a database that stored information about servers and application errors. The database contains the
following tables.
Servers

Errors

You are building a webpage that shows the three most common errors for each server.
You need to return the data for the webpage.
How should you complete the Transact-SQL statement? To answer, drag the appropriate Transact-SQL
segments to the correct location. 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.
Select and Place:

You have a database that stored information about servers and application errors. The database contains the
following tables.
Servers

Errors

You need to return all error log messages and the server where the error occurs most often.
Which Transact-SQL statement should you run?

HOTSPOT
You have the following subqueries: Subquery1, Subquery2, and Subquery3.
You need to replace the three subqueries with named result sets or temporary tables. The following
requirements must be met:

Which replacement techniques should you use? To answer, select the appropriate options in the answer area.
Hot Area:

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 independentof the other questions in this series. Information and details provided in a question apply only to that
question.
You have a table named AuditTrail that tracks modifications to data in other tables. The AuditTrail table is
updated by many processes. Data input into AuditTrail may contain improperly formatted date time values. You
implement a process that retrieves data from the various columns in AuditTrail, but sometimes the process
throws an error when it is unable to convert the data into valid date time values.
You need to convert the data into a valid date time value using the en-US format culture code. If the conversion
fails, a null value must be returned in the column output. The conversion process must not throw an error.
What should you implement?

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 stores sales and order information.
Users must be able to extract information from the tables on an ad hoc basis. They must also be able to
reference the extracted information as a single table.
You need to implement a solution that allows users to retrieve the data required, based on variables defined at
the time of the query.
What should you implement?