NEW QUESTION 1
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 need to audit all customer data.
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 G

Answer: B

Explanation:
The FOR SYSTEM_TIME ALL clause returns all the row versions from both the Temporal and History table. Note: A system-versioned temporal table defined through is a new type of user table in SQL Server 2021, here defined on the last line WITH (SYSTEM_VERSIONING = ON…, is designed to keep a full history of data changes and allow easy point in time analysis.
To query temporal data, the SELECT statement FROM<table> clause has a new clause FOR SYSTEM_TIME with five temporal-specific sub-clauses to query data across the current and history tables.
References: https://msdn.microsoft.com/en-us/library/dn935015.aspx

NEW QUESTION 2
You run the following Transact-SQL statements:

You need to create a query that returns the total number of attendees for each combination of CourseID, CourseDate, and the following locations: Lisbon, London, and Seattle. The result set should resemble the following:

Which Transact-SQL code segment should you run?

• A. SELECT *FROM CourseParticipants PIVOT(SUM(NumParticipants) FOR LocationDescription IN (Lisbon, London, Seattle))
• B. SELECT *FROM CourseParticipants PIVOT(SUM(NumParticipants) FOR LocationDescription IN (Lisbon, London, Seattle)) as PVTTable
• C. SELECT *FROM CourseParticipants UNPIVOT(SUM(NumParticipants) FOR LocationDescription IN (Lisbon, London, Seattle)
• D. SELECT *FROM CourseParticipants UNPIVOT(SUM(NumParticipants) FOR LocationDescription IN (Lisbon, London, Seattle) AS PVTTable

Answer: B

Explanation:
References: https://www.techonthenet.com/sql_server/pivot.php

NEW QUESTION 3
You have a table named HumanResources.Employee. You configure the table to use a default history table that contains 10 years of data.
You need to write a query that retrieves the values of the BusinessEntityID and JobTitle fields. You must retrieve all historical data up to January 1, 2021 where the value of the BusinessEntityID column equals 4.
Which four Transact-SQL segments should you use to develop the solution? To answer, move the appropriate Transact-SQL segments to the answer area and arrange them in the correct order.

• A. Mastered
• B. Not Mastered

Answer: A

Explanation:
References:
https://docs.microsoft.com/en-us/sql/relational-databases/tables/querying-data-in-a-system-versioned-temporal-t

NEW QUESTION 4
You have a project management application. The application uses a Microsoft SQL Server database to store data. You are developing a software bug tracking add-on for the application.
The add-on must meet the following requirements:
Allow case sensitive searches for product.
Filter search results based on exact text in the description.
Support multibyte Unicode characters.
You run the following Transact-SQL statement:

Users connect to an instance of the bug tracking application that is hosted in New York City. Users in Seattle must be able to display the local date and time for any bugs that they create.
You need to ensure that the DateCreated column displays correctly. Which Transact-SQL statement should you run?

• A. SELECT Id,Product,DateCreated AT TIME ZONE 'Pacific Standard Time' FROM Bug
• B. SELECT Id,Product, DATEADD(hh, -8, DateCreated) FROM Bug
• C. SELECT Id,Product, TODATETIMEOFFSET(DateCreated, -8) FROM Bug
• D. SELECT Id,Product,CAST(DateCreated AS DATETIMEOFFSET) FROM Bug

Answer: C

Explanation:
References:
https://docs.microsoft.com/en-us/sql/t-sql/functions/todatetimeoffset-transact-sql?view=sql-server-2021

NEW QUESTION 5
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 to that question.
You have a database for a banking system. The database has two tables named tblDepositAcct and tblLoanAcct that store deposit and loan accounts, respectively/ Both tables contain the following columns:

You need to run a query to find the total number of customers who have both deposit and loan accounts. Which Transact-SQL statement should you run?

• A. SELECT COUNT(*)FROM (SELECT AcctNoFROM tblDepositAcctINTERSECTSELECTAcctNoFROM tblLoanAcct) R
• B. SELECT COUNT(*)FROM (SELECT CustNoFROM tblDepositAcctUNIONSELECT CustNoFROMtblLoanAcct) R
• C. SELECT COUNT(*)FROM (SELECT CustNoFROM tblDepositAcctUNION ALLSELECTCustNoFROM tblLoanAcct) R
• D. SELECT COUNT (DISTINCT D.CustNo)FROM tblDepositAcct D, tblLoanAcct LWHERE D.CustNo= L.CustNo
• E. SELECT COUNT(DISTINCT L.CustNo)FROM tblDepositAcct DRIGHT JOIN tblLoanAcct L ON D.CustNo = L.CustNoWHERE D.CustNo IS NULL
• F. SELECT COUNT(*)FROM (SELECT CustNoFROM tblDepositAcctEXCEPTSELECT CustNoFROMtblLoanAcct) R
• G. SELECT COUNT (DISTINCT COALESCE(D.CustNo, L.CustNo))FROM tblDepositAcct DFULLJOIN tblLoanAcct L ON D.CustNo = L.CustNoWHERE D.CustNo IS NULL OR L.CustNo IS NULL
• H. SELECT COUNT(*)FROM tblDepositAcct DFULL JOIN tblLoanAcct L ON D.CustNo = L.CustNo

Answer: A

Explanation:
The SQL INTERSECT operator is used to return the results of 2 or more SELECT statements. However, it only returns the rows selected by all queries or data sets. If a record exists in one query and not in the other, it will be omitted from the INTERSECT results.
References: https://www.techonthenet.com/sql/intersect.php

NEW QUESTION 6
You create a table named Sales.Orders by running the following Transact-SQL statement:

You need to write a query that removes orders from the table that have a Status of Canceled. Construct the query using the following guidelines:


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:
1. DELETE from sales.orders where status='Canceled' Note: On line 1 change calceled to Canceled
Example: Using the WHERE clause to delete a set of rows
The following example deletes all rows from the ProductCostHistory table in the AdventureWorks2012 database in which the value in the StandardCost column is more than 1000.00.
DELETE FROM Production.ProductCostHistory WHERE StandardCost > 1000.00;
References: https://docs.microsoft.com/en-us/sql/t-sql/statements/delete-transact-sql

NEW QUESTION 7
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 to that question.
You have a database for a banking system. The database has two tables named tblDepositAcct and tblLoanAcct that store deposit and loan accounts, respectively. Both tables contain the following columns:

You need to determine the total number of customers who have only loan accounts. Which Transact-SQL statement should you run?

• A. SELECT COUNT(*)FROM (SELECT AcctNoFROM tblDepositAcctINTERSECTSELECTAcctNoFROM tblLoanAcct) R
• B. SELECT COUNT(*)FROM (SELECT CustNoFROM tblDepositAcctUNIONSELECT CustNoFROMtblLoanAcct) R
• C. SELECT COUNT(*)FROM (SELECT CustNoFROM tblDepositAcctUNION ALLSELECTCustNoFROM tblLoanAcct) R
• D. SELECT COUNT (DISTINCT D.CustNo)FROM tblDepositAcct D, tblLoanAcct LWHERE D.CustNo= L.CustNo
• E. SELECT COUNT(DISTINCT L.CustNo)FROM tblDepositAcct DRIGHT JOIN tblLoanAcct L ON D.CustNo = L.CustNoWHERE D.CustNo IS NULL
• F. SELECT COUNT(*)FROM (SELECT CustNoFROM tblDepositAcctEXCEPTSELECT CustNoFROMtblLoanAcct) R
• G. SELECT COUNT (DISTINCT COALESCE(D.CustNo, L.CustNo))FROM tblDepositAcct DFULLJOIN tblLoanAcct L ON D.CustNo = L.CustNoWHERE D.CustNo IS NULL OR L.CustNo IS NULL
• H. SELECT COUNT(*)FROM tblDepositAcct DFULL JOIN tblLoanAcct L ON D.CustNo = L.CustNo

Answer: E

Explanation:
The RIGHT JOIN keyword returns all records from the right table (table2), and the matched records from the left table (table1). The result is NULL from the left side, when there is no match.
References: https://www.w3schools.com/sql/sql_join_right.asp

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 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 9
You are developing a training management application. You run the following Transact-SQL statement:

You must create a report that returns course identifiers and the average evaluation score for each course. The result set must include only one score for each employee for each course.
How should you complete the Transact-SQL statement? To answer, select the appropriate Transact-SQL segments in the answer area.
NOTE: Each correct selection is worth one point.

• A. Mastered
• B. Not Mastered

Answer: A

Explanation:

NEW QUESTION 10
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 several connected tables. The tables contain sales data for customers in the United States only.
You have the following partial query for the database. (Line numbers are included for reference only.)

You need to complete the query to generate the output shown in the following table.

Which statement clause should you add at line 3?

• A. GROUP BY
• B. MERGE
• C. GROUP BY ROLLUP
• D. LEFT JOIN
• E. GROUP BY CUBE
• F. CROSS JOIN
• G. PIVOT
• H. UNPIVOT

Answer: C

Explanation:
In the result sets that are generated by the GROUP BY operators, NULL has the following uses:
If a grouping column contains NULL, all null values are considered equal, and they are put into one NULL group.
When a column is aggregated in a row, the value of the column is shown as NULL. Example of GROUP BY ROLLUP result set:

References: https://technet.microsoft.com/en-us/library/bb522495(v=sql.105).aspx

NEW QUESTION 11
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 distinct customers that appear in both tables.
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: H

Explanation:
To retain the nonmatching information by including nonmatching rows in the results of a join, use a full outer join. SQL Server provides the full outer join operator, FULL OUTER JOIN, which includes all rows from both tables, regardless of whether or not the other table has a matching value.

NEW QUESTION 12
You need to create a table named Sales that meets the following requirements:

Which Transact-SQL statement should you run?

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

Answer: D

Explanation:
datetime2 Defines a date that is combined with a time of day that is based on 24-hour clock. datetime2 can be considered as an extension of the existing datetime type that has a larger date range, a larger default fractional precision, and optional user-specified precision.

NEW QUESTION 13
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 14
You have a database that includes the tables shown in the exhibit. (Click the exhibit button.)

You need to create a list of all customers and the date that the customer placed their last order. For customers who have not placed orders, you must substitute a zero for the order ID and 01/01/1990 for the date.
Which Transact-SQL statement should you run?

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

Answer: A

Explanation:
COALESCE evaluates the arguments in order and returns the current value of the first expression that initially does not evaluate to NULL.
References: https://docs.microsoft.com/en-us/sql/t-sql/language-elements/coalesce-transact-sql

NEW QUESTION 15
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 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 includes the tables shown in the exhibit (Click the Exhibit button.)

You need to create a Transact-SQL query that returns the following information:
- the customer number
- the customer contact name
- the date the order was placed, with a name of DateofOrder
- a column named Salesperson, formatted with the employee first name, a space, and the employee last name
- orders for customers where the employee identifier equals 4
The output must be sorted by order date, with the newest orders first. The solution must return only the most recent order for each customer. Solution: You run the following Transact-SQL statement:

Does the solution meet the goal?

• A. Yes
• B. No

Answer: B

Explanation:
We need a GROUP BY statement as we want to return an order for each customer.

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 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?

• 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:
A TRY_CONVERT function returns a value cast to the specified data type if the cast succeeds; otherwise, returns null.
References: https://msdn.microsoft.com/en-us/library/hh230993.aspx

NEW QUESTION 18
You have a database named DB1 that contains a temporal table named Sales.Customers.
You need to create a query that returns the credit limit that was available to each customer in DB1 at the beginning of 2021.
Which query should you execute?

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

Answer: B

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.
You have a database that contains several connected tables. The tables contain sales data for customers in the United States only.
You need to create a query that generates sample data for a sales table in the database. The query must include every product in the inventory for each customer.
Which statement clause should you use?

• A. GROUP BY
• B. MERGE
• C. GROUP BY ROLLUP
• D. LEFT JOIN
• E. GROUP BY CUBE
• F. CROSS JOIN
• G. PIVOT
• H. UNPIVOT

Answer: C

NEW QUESTION 20
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 several connected tables. The tables contain sales data for customers in the United States only.
You have the following partial query for the database. (Line numbers are included for reference only.)

You need to complete the query to generate the output shown in the following table.

Which statement clause should you add at line 3?

• A. GROUP BY
• B. MERGE
• C. GROUP BY ROLLUP
• D. LEFT JOIN
• E. GROUP BY CUBE
• F. CROSS JOIN
• G. PIVOT
• H. UNPIVOT

Answer: A

NEW QUESTION 21
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: A

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 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: A

Explanation:
With the INSERT INTO..VALUES statement we can insert both values with just one statement. This ensures that both records or neither is inserted.
References: https://msdn.microsoft.com/en-us/library/ms174335.aspx

NEW QUESTION 23
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.

• A. Mastered
• B. Not Mastered

Answer: A

Explanation:

NEW QUESTION 24
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

Explanation:
https://docs.microsoft.com/en-us/sql/t-sql/functions/cast-and-convert-transact-sql?view=sql-server-2021

NEW QUESTION 25
......