Microsoft 70-761 Querying Data with Transact-SQL

Question 1

Answer 1

Correct Answer: B
With X_ABORT ON the INSERT INTO statement and the transaction will be rolled back when an error is raised, it would then not be possible to ROLLBACK it again in the IF XACT_STATE() <> O ROLLBACK TRANSACTION statement.
Note: A transaction is correctly defined for the INSERT INTO ..VALUES statement, and if there is an error in the transaction it will be caught and the transaction will be rolled back, finally an error 51000 will be raised.
Note: When SET XACT_ABORT is ON, if a Transact-SQL statement raises a run-time error, the entire transaction is terminated and rolled back.
XACT_STATE is a scalar function that reports the user transaction state of a current running request. XACT_STATE indicates whether the request has an active user transaction, and whether the transaction is capable of being committed.
The states of XACT_STATE are:
✑ 0 There is no active user transaction for the current request.
✑ 1 The current request has an active user transaction. The request can perform any actions, including writing data and committing the transaction.
✑ 2 The current request has an active user transaction, but an error has occurred that has caused the transaction to be classified as an uncommittable transaction.

Question 2

Answer 2

Correct Answer: B
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.

Question 3

Answer 3

Correct Answer: Please see explanation
1 WITH ParentCategories pc (CategoryID, Name, PatentCategoryID) AS (SELECT c.categoryID,c.name,c.parentcategoryid
2 FROM sales.categories c
3 WHERE parentcategoryid is not null
4 )
5 SELECT * FROM parentcategories
Note: On Line 1 replace c with WITH ParentCategories pc (CategoryID, Name, PatentCategoryID) AS
Note: The basic syntax structure for a CTE is:
WITH expression_name [( column_name [,…n] ) ]

AS -
( CTE_query_definition )

Question 4

Answer 4

Correct Answer: Please see 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 =

Question 5

Answer 5

SELECT AVG(normalizedreading) as AverageReading, Nearestmountain(location) AS Mountain FROM GroundSensors WHERE normalizedreading is not NULL GROUP BY Nearestmountain(location)

Question 6

Answer 6

Correct Answer: Please see 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;

Question 7

Answer 7

Correct Answer: Please see explanation
1 SELECT top 3 lastname,salesYTD
2 FROM Person AS p INNER JOIN SalesPerson AS s
3 ON p.PersonID = s.SalesPersonID
4 WHERE territoryid is not null
5 order by salesytd desc
Note:
On line 4 add a not before null.
On line 5 change dsec to desc.

Question 8

Answer 8

Correct Answer: C
Would list the customers with duplicates, which would equal the number of accounts.
Incorrect Answers:
A: INTERSECT returns distinct rows that are output by both the left and right input queries operator.
B: Would list the customers without duplicates.
D: Number of customers.
F: EXCEPT returns distinct rows from the left input query that aren’t output by the right input query.

Question 9

Answer 9

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.
Note: RAISERROR syntax:
RAISERROR( { msg_id | msg_str | @local_variable }
{ ,severity ,state }
[,argument [ ,…n] ] )
[WITH option [ ,…n] ]
The LOG option logs the error in the error log and the application log for the instance of the Microsoft SQL Server Database Engine.

Question 10

Answer 10

To compare char(5) and nchar(5) an implicit conversion has to take place.
Explicit conversions use the CAST or CONVERT functions, as in line number 6.

Question 11

Answer 11

Correct Answer: A
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.

Question 12

Answer 12

B. No

Question 13

Answer 13

Box 1: COALESCE -
COALESCE evaluates the arguments in order and returns the current value of the first expression that initially does not evaluate to NULL.

Box 2: T.UserID, p.UserID, -1 -
✑ Return each task’s owner if the task has an owner.
✑ If a task has no owner, but is associated with a project that has an owner, return the project’s owner.
✑ Return the value -1 for all other cases.

Box 3: RIGHT JOIN -
The RIGHT JOIN keyword returns all rows from the right table (table2), with the matching rows in the left table (table1). The result is NULL in the left side when there is no match. Here the right side could be NULL as the projectID of the task could be NULL.

Question 14

Answer 14

B. No

Question 15

Answer 15

B. No

Question 16

Answer 16

Question 17

Answer 17

Correct Answer: D
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.
Incorrect Answers:
B, C, E: NEWQSEQUENTIALID creates a GUID that is greater than any GUID previously generated by this function on a specified computer since Windows was started. A GUID uses more space then IDENTITY value.

Question 18

Answer 18

Correct Answer: A
Incorrect Answers:
B: The count should be on the Cust instance of Sales.Customers as it is to the right side of the join.
C: Need a WHERE statement with an IS NULL clause.
D: Must use a LEFT JOIN to obtain the NULL values.

Question 19

Answer 19

Wildcard character %: Any string of zero or more characters.
For example: If the LIKE ‘5%’ symbol is specified, the Database Engine searches for the number 5 followed by any string of zero or more characters.

Question 20

Answer 20

Correct Answer: H
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.
Incorrect Answers:
A: Inner joins return rows only when there is at least one row from both tables that matches the join condition. Inner joins eliminate the rows that do not match with a row from the other table.
B: INTERSECT returns distinct rows that are output by both the left and right input queries operator.
D: EXCEPT returns distinct rows from the left input query that aren’t output by the right input query.
E: UNION specifies that multiple result sets are to be combined and returned as a single result set, but this will not work here as the CustomerID column values do not match.
F: UNION ALL incorporates all rows into the results. This includes duplicates. If not specified, duplicate rows are removed.
G: A cross join would produce the Cartesian product of the two tables.

Question 21

Answer 21

Question 22

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

Which query should you execute?

A. SELECT

CustomerID,

CustomerName,

CreditLimit

FROM

Sales.Customers

FOR SYSTEM TIME CONTAINED IN 2017-01-01 00:00:00’);

B. SELECT

CustomerID,

CustomerName,

CreditLimit

FROM

Sales.Customers

FOR SYSTEM TIME CONTAINED IN (‘2017-01-01’);

C. SELECT

CustomerID,

CustomerName,

CreditLimit

FROM

Sales.Customers

FOR SYSTEM TIME AS OF 2017-01-01’;

D. SELECT

CustomerID,

CustomerName,

CreditLimit

FROM

Sales.Customers

FOR SYSTEM TIME ALL;

Answer 22

Correct Answer: C
AS OF: Returns a table with a rows containing the values that were actual (current) at the specified point in time in the past.
Incorrect Answers:
A, B: CONTAINED IN has two parameters: CONTAINED IN (<start_date_time> , <end_date_time>)</end_date_time></start_date_time>

Question 23

Answer 23

Question 24

Answer 24

Box 1: SELECT CAST (NULL AS INT) AS ParentTaskID, etc.
This statement selects all tasks with task level 0.
The ParentTaskID could be null so we should use CAST (NULL AS INT) AS ParentTaskID.

Box 2: UNION -
We should use UNION and not UNION ALL as we do not went duplicate rows.
UNION specifies that multiple result sets are to be combined and returned as a single result set.
Incorrect Answers:
Not UNION ALL: ALL incorporates all rows into the results. This includes duplicates. If not specified, duplicate rows are removed.
Box 3, Box 4, Box 5:
These statements select all tasks with task level >0.

Question 25

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 25

B. a user-defined table-valued function

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.

Question 26

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 result, these questions will not appear in the review screen.

You create a table named Products by running the following Transact-SQL statement:

CREATE TABLE Products

ProductID int IDENTITY (1, 1), NOT NULL PRIMARY KEY,

ProductName nvarchar (100), NULL,

UnitPrice decimal (18, 2) NOT NULL,

UnitsInStock int NOT NULL,

UnitsOnOrder int NULL

You have the following stored procedure:

CREATE PROCEDURE InsertProduct

@ProductName nvarchar(100),

@UnitPrice decimal (18, 2),

@UnitsInStock int,

@UnitsOnOrder int

AS

BEGIN

INSERT INTO Products (ProductName, UnitPrice, UnitsInStock, UnitsOnOrder)

VALUES (@ProductName, @UnitPrice, @UnitsInStock, @UnitsOnOrder)

END

You need to modify the stored procedure to meet the following new requirements:

Co Insert product records as single unit of work.

co Return error number 51000 when: product fails to insert into the database.

co If product record insert operation fails, the product information must not be permanently written to the database.

Solution: You run the following Transact-SQL statement:

ALTER PROCEDURE InsertProduct

@ProductName nvarchar (100),

@UnitPrice decimal (18, 2),

@UnitsInStock int,

@UnitsOnOrder int

AS

BEGIN

SET XACT ABORT ON

BEGIN TRY

BEGIN TRANSACTION

INSERT INTO Products (ProductName, UnitPrice, UnitsInStock, UnitsOnOrder)

VALUES (@ProductName, @UnitPrice, @UnitsInStock, @UnitsOnOrder)

COMMIT TRANSACTION

END TRY

BEGIN CATCH

IF XACT_STATE () <> 0 ROLLBACK TRANSACTION

THROW 51000, ‘The product could not be created, 1

END CATCH

END

Does the solution meet the goal?

A. Yes

B. No

Answer 26

B. No

Question 27

You create a table named Products by running the following Transact-SQL statement:

CREATE TABLE Products

ProductID int IDENTITY (1, 1), NOT NULL PRIMARY KEY,

ProductName nvarchar (100), NULL,

UnitPrice decimal (18, 2) NOT NULL,

UnitsInStock int NOT NULL,

UnitsOnOrder int NULL

You have the following stored procedure:

CREATE PROCEDURE InsertProduct

@ProductName nvarchar(100),

@UnitPrice decimal (18, 2),

@UnitsInStock int,

@UnitsOnOrder int

AS

BEGIN

INSERT INTO Products (ProductName, UnitPrice, UnitsInStock, UnitsOnOrder)

VALUES (@ProductName, @UnitPrice, @UnitsInStock, @UnitsOnOrder)

END

You need to modify the stored procedure to meet the following new requirements:

co Insert product records as single unit of work.

c Return error number 51000 when a product fails to insert into the database.

co If product record insert operation fails, the product information must not be permanently written to the database.

Solution: You run the following Transact-SQL statement:

ALTER PROCEDURE InsertProduct

@ProductName nvarchar (100),

@UnitPrice decimal (18, 2),

@UnitsInStock int,

@UnitsOnOrder int

AS

BEGIN

BEGIN TRY

BEGIN TRANSACTION

INSERT INTO Products (ProductName, UnitPrice, UnitsInStock, UnitsOnOrder)

VALUES (@ProductName, @UnitPrice, @UnitsInStock, @UnitsOnOrder)

COMMIT TRANSACTION

END TRY

BEGIN CATCH

IF @@TRANCOUNT > 0 ROLLBACK TRANSACTION

RAISERROR (51000,16, 1)

END CATCH

END

Does the solution meet the goal?

A. Yes

B. No

Answer 27

B. No

Question 28

Answer 28

Correct Answer: A
The OUTPUT Clause returns information from, or expressions based on, each row affected by an INSERT, UPDATE, DELETE, or MERGE statement. These results can be returned to the processing application for use in such things as confirmation messages, archiving, and other such application requirements. The results can also be inserted into a table or table variable. Additionally, you can capture the results of an OUTPUT clause in a nested INSERT, UPDATE, DELETE, or MERGE statement, and insert those results into a target table or view.
Note: If the column modified by the .RITE clause is referenced in an OUTPUT clause, the complete value of the column, either the before image in deleted.column_name or the after image in inserted.column_name, is returned to the specified column in the tablevariable.
Incorrect Answers:
D: The deleted.Creditlimit should be inserted in the second column, the OldCreditLimit column, not the third column.

Question 29

A. SELECT COUNT(*) FROM (SELECT AcctNo FROM tblDepositAcct INTERSECT SELECT AcctNo FROM tblLoanAcct) R

B. SELECT COUNT(*) FROM (SELECT CustNo FROM tblDepositAcct UNION SELECT CustNo FROM tblLoanAcct) R

C. SELECT COUNT(*) FROM (SELECT CustNoFROM tblDepositAcct UNION ALL SELECT CustNo FROM tblLoanAcct) R

D. SELECT COUNT (DISTINCT D.CustNo) FROM tblDepositAcctD, tblLoanAcct L WHERE D.CustNo = L.CustNo

E. SELECT COUNT(DISTINCT L.CustNo) FROM tblDepositAcct D RIGHT JOIN tblLoanAcct L ON D.CustNo = L.CustNo WHERE D.CustNo IS NULL

F. SELECT COUNT(*) FROM (SELECT CustNo FROM tblDepositAcct EXCEPT SELECT CustNo FROM tblLoanAcct) R

G. SELECT COUNT (DISTINCT COALESCE(D.CustNo, L.CustNo)) FROM tblDepositAcct D FULL JOIN tblLoanAcct L ON D.CustNo = L.CustNo WHERE D.CustNo IS NULL OR L.CustNo IS NULL

H. SELECT COUNT(*) FROM tblDepositAcct D FULL JOIN tblLoanAcct L ON D.CustNo = L.CustNo

Answer 29

F. Option F

Question 30

Answer 30

Correct Answer: B
Mathematical equation will only return 10 % of the value.

Question 31

Answer 31

B. No

Question 32

Answer 32

The answer is B.No.

Both insert statements are executed seperately, so failure of one won’t impact another.

Question 33

Answer 33

A. Yes

Question 34

Answer 34

B. No

Question 35

Answer 35

Box 1: UPDATE T SET T.EndTime = P.EndTime
We are updating the EndTime column in the Task table.

Box 2: FROM Task AS T -
Where are updating the task table.
Box 3:INNER JOIN Project AS P on T.ProjectID = P.ProjectID
We join with the Project table (on the ProjectID columnID column).
Box 4: WHERE P.EndTime is NOT NULL AND T.EndTime is NULL
We select the columns in the Task Table where the EndTime column in the Project table has a value (NOT NULL),but where it is NULL in the Task Table.

Question 36

Answer 36

B. No

Question 37

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:

UPDATE Production.Products

SET ListPrice = ListPrice 1.1

WHERE ListPrice

BETWEEN .01 and 99.99

Does the solution meet the goal?

A. Yes

B. No

Answer 37

A. Yes

Question 38

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

UPDATE Production.Products

SET ListPrice = ListPrice 1.1

WHERE ListPrice

BETWEEN 0 and 100

Does the solution meet the goal?

A. Yes

B. No

Answer 38

B. No

Question 39

Answer 39

Question 40

Answer 40

Correct Answer: Answer: B
AS OF: Returns a table with a rows containing the values that were actual (current) at the specified point in time in the past.
Incorrect Answers:
A, B: CONTAINED IN has two parameters: CONTAINED IN (<start_date_time> , <end_date_time>)</end_date_time></start_date_time>

Question 41

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 41

C. GROUP BY ROLLUP

Question 42

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.
All the sales data is stored in a table named table1. You have a table named table2 that contains city names.
You need to create a query that lists only the cities that have no sales.
Which statement clause should you add to the query?

A. GROUP BY

B. MERGE

C. GROUP BY ROLLUP

D. LEFT JOIN

E. GROUP BY CUBE

F. CROSS JOIN

G. PIVOT

H. UNPIVOT

Answer 42

D. LEFT JOIN

Question 43

Answer 43

A. GROUP BY

Question 44

Answer 44

B. DECLARE @startedIasks TABLE(TaskId int, ProjectId int)

UPDATE Task SET StartTime GETDATE () OUTPUT deleted.IaskId, deleted.ProjectId INIO @startedTasks

WHERE StartTime is NULL

SELECT COUNT(*) FROM EstartedTasks WHERE ProjectId IS NULL

Question 45

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

Which query should you execute?

A.

SELECT

CustomerID,

CustomerName,

CreditLimit

FROM

Sales.Customers

FOR SYSTEM TIME CONTAINED IN (‘2017-01-01 00:00:00’));

B.

SELECT

CustomerID,

CustomerName,

CreditLimit

FROM

Sales.Customers

FOR SYSTEM TIME AS OF 2017-01-01 00:00:00’:

C.

SELECT

CustomerID,

CustomerName,

CreditLimit

FROM

Sales.Customers

FOR SYSTEM TIME CONTAINED IN (‘2016-12-31’, ‘2017-01-01’);

D.

SELECT

CustomerID,

CustomerName,

CreditLimit

FROM

Sales.Customers

FOR SYSTEM_TIME BETWEEN ‘2016-12-31’ AND ‘2017-01-01’);

Answer 45

Correct Answer: B
AS OF: Returns a table with a rows containing the values that were actual (current) at the specified point in time in the past.
Incorrect Answers:
A, B: CONTAINED IN has two parameters: CONTAINED IN (<start_date_time> , <end_date_time>)</end_date_time></start_date_time>

Question 46

Answer 46

B. a user-defined scalar function

User-defined scalar functions are execution plans that accept parameters, perform an action such as a complex calculation, and returns the result of that action as a value. The return value can either be a single scalar value or a result set. Furthermore the execution plan is cached and reusable.
User-defined scalar functions can also be called from within a SELECT statement and can be used in a JOIN clause.
Incorrect Answers:
A: Using extended stored procedures is not recommended as they has been deprecated. CLR Integration should be used instead of extended stored procedures.
C: Stored procedures cannot be used in a SELECT statement or in a JOIN clause.
D: A temporary table is a result set and not a value.

Question 47

You create a table by running the following Transact-SQL statement:

CREATE TABLE Customers

CustomerID int NOT NULL PRIMARY KEY CLUSTERED,

FirstName nvarchar (100) NOT NULL,

LastName nvarchar (100) NOI NULL,

TaxIdNumber varchar (20) NOT NULL,

Address nvarchar (1024) NOT NULL,

Annua1Revenue decimal (19,2) NOT NULL,

DateCreated datetime2 (2) NOT NULL,

ValidFrom datetime2(2) GENERATED ALWAYS AS ROW START NOT NULL,

ValidTo datetime2 (2) GENERATED ALWATS AS ROW END NOT NULL,

PERIOD FOR SYSTEM TIME (ValidFrom, ValidTo)

WITH (SYSTEM VERSIONING = ON (HISTORY TABLE CustomersHistory))

You are developing a report that aggregates customer data only for the year 2014. The report requires that the data be denormalized.

You need to return the data for the report.

Which Transact-SQL statement should you run?

A. SELECT FirstName, LastName, SUM (Annual Revenue)

FROM Customers

GROUP BY GROUPING SETS ((FirstName, LastName, AnnualRevenue), () )

ORDER BY FirstName, LastName, AnnualRevenue

B. SELECT CustomerID, FirstName, LastName, TaxIdNumber, Address, AnnualRevenue, DateCreated, ValidFrom, ValidTo

FROM Customers

FOR SYSTEM TIME ALL ORDER BY ValidFrom

C. SELECT c.CustomerID, C.FirstName, c.LastName, c.Address, c.ValidFrom, c.ValidTo

FROM Customers AS

ORDER BY c.CustomerID

FOR JSON AUTO, ROOT (“Customers’)

D. SELECT x FROM (SELECT CustomerID, FirstName, LastName, Address, AnnualRevenue, DateCreated

FROM Customers) AS Customers PIVOI (AVG (AnnualRevenue)

FOR DateCreated IN([2014])) AS PivotCustomers

ORDER BY LastName, FirstName

E. SELECT CustomerID, G (Annua1Revenue)

AS AverageAnnualRevenue, FirstName, LastName, Address, DateCreated

FROM Customers WHERE YEAR(DateCreated) >= 2014

GROUP BY CustomerID, FirstName, LastName, Address, DateCreated

F. SELECT c.CustomerID, c.FirstName, c.LastName, c.Address, c.ValidFrom, c.ValidTo

FROM Customers AS C ORDER BY c.CustomerID FOR XML PATH (“‘CustomerData’), root (‘Customers’)

G. SELECT CustomerID, FirstName, LastName, TaxIdNumber, Address, ValidFrom, ValidTo

FROM Customers FOR SYSTEM TIME

BETWEEN ‘2014-01-01 00:00:00.000000’ AND 2015-01-01 00:00:00.000000’

H. SELECT CustomerID, FirstName, LastName, TaxIdNumber, Address, ValidFrom, Validlo

FROM Customers

WHERE DateCreated

BETWEEN ‘20140101’ AND ‘20141231’

Answer 47

G. SELECT CustomerID, FirstName, LastName, TaxIdNumber, Address, ValidFrom, ValidTo

FROM Customers FOR SYSTEM TIME

BETWEEN ‘2014-01-01 00:00:00.000000’ AND 2015-01-01 00:00:00.000000’

Question 48

You create a table by running the following Transact-SQL statement:

CREATE TABLE Customers

CustomerID int NOT NULL PRIMARY KEY CLUSTERED,

FirstName nvarchar(100) NOT NULL,

LastName nvarchar (100) NOT NULL,

TaxIdNumber varchar(20) NOT NULL,

Address nvarchar (1024) NOT NULL,

Annua1Revenue decimal (19,2) NOT NULL,

DateCreated datetime2 (2) NOT NULL,

ValidFrom datetime2 (2) GENERATED ALWAYS AS ROW START NOT NULL,

ValidTo datetime2 (2) GENERATED ALWATS AS ROW END NOT NULL,

PERIOD FOR SYSTEM TIME (ValidFrom, ValidTo)

WITH (SYSTEM_VERSIONING ON (HISTORY TABLE CustomersHistory))

You need to return normalized data for all customers that were added in the year 2014.

Which Transact-SQL statement should you run?

A. SELECT FirstName, LastName, SUM (Annua1Revenue)

FROM Customers

GROUP BY GROUPING SETS((FirstName, LastName, AnnualRevenue), ())

ORDER BY FirstName, LastName, Annual Revenue

B. SELECT FirstName, LastName, Address

FROM Customers

FOR SYSTEM TIME ALL ORDER BY ValidFrom

C. SELECT c.CustomerID, c.FirstName, c.LastName, c.Address, c,ValidFrom, c.ValidTo

FROM Customers AS c

ORDER BY c. CustomerID

FOR JSON AUTO, ROOT (‘Customers’)

D. SELECT FROM (SELECT CustomerID, FirstName, LastMame, Address, AnnualRevenue, DateCreated

FROM Customers) AS Customers PIVOT (AVG (Annua1Revenue)

FOR DateCreated IN([2014J)) AS PivotCustomers

ORDER BY LastName, FirstName

E. SELECT CustomerID, AVG(AnnualRevenue)

AS AverageAnnualRevenue, FirstName, LastName, Address, DateCreated

FROM Customers WHERE YEAR(DateCreated) >= 2014

GROUP BY CustomerID, FirstName, LastName, Address, DateCreated

F. SELECT c.CustomerID, c.FirstName, c.LastName, c.Address, c.ValidFrom, c.ValidTo

FROM Customers AS c ORDER BY c.CustomerID

FOR XML PATH (‘CustomerData’), root (‘Customers’)

G. SELECT CustomerID, FirstName, LastName, TaxIdNumber, Address, ValidFrom, ValidTo

FROM Customers FOR SYSTEM TIME

BETWEEN ‘2014-01-01 00:00:00.000000’ AND ‘2015-01-01 00:00:00.000000’

H. SELECT CustomerID, FirstName, LastName, TaxIdNumber, Address, ValidFrom, ValidTo

FROM Customers

WHERE DateCreated

BETWEEN ‘20140101’ AND 20141231’

Answer 48

G. SELECT CustomerID, FirstName, LastName, TaxIdNumber, Address, ValidFrom, ValidTo

FROM Customers FOR SYSTEM TIME

BETWEEN ‘2014-01-01 00:00:00.000000’ AND ‘2015-01-01 00:00:00.000000’

Question 49

You run the following Transact-SQL statement:

CREATE TABLE Customers

CustomerID int NOT NULL PRIMARY KEY CLUSTERED,

FirstName nvarchar(100) NOT NULL,

LastName nvarchar(100) NOT NULL,

TaxIdNumber varchar (20) NOT NULL,

Address nvarchar (1024) NOT NULL,

AnnualRevenue decimal (19,2) NOT NULL,

DateCreated datetime2(2) NOT NULL,

ValidFrom datetime2 (2) GENERATED ALWAYS AS ROW START NOT NULL,

ValidTo datetime2 (2) GENERATED ALWATS AS ROW END NOT NULL,

PERIOD FOR SYSTEM TIME (ValidFrom, ValidTo)

WITH (SYSTEM_VERSIONING ON (HISTORY TABLE CustomersHistory))

You need to return the total annual revenue for all customers, followed by row for each customer that shows the customer’s name and annual revenue.

Which Transact-SQL statement should you run?

A. SELECT FirstName, LastName, 8UM(Annua1Revenue)

FROM Customers

GROUP BY GROUPING SETS ( (FirstName, LastName, AnnualRevenue), ())

ORDER BY FirstName, LastName, AnnualRevenue

B. SELECT FirstName, LastName, Address

FROM Customers

FOR SYSTEM TIME ALL ORDER BY ValidFrom

C. SELECT c.CustomerID, C.FirstName, c.LastName, c.Address, c.ValidFrom, c.ValidTo

FROM Customers AS c

ORDER BY c. CustomerID

FOR JSON AUTO, ROOT (‘Customers’)

D. SELECT * FROM (SELECT CustomerID, FirstName, LastName, Address, AnnualRevenue, DateCreated

FROM Customers) AS Customers PIVOI (AVG (Annua1Revenue)

FOR DateCreated IN([2014])) AS PivotCustomers

ORDER BY LastName, FirstName

E. SELECT CustomerID, AVG(AnnualRevenue)

AS AverageAnnualRevenue, FirstName, LastName, Address, DateCreated

FROM Customers WHERE YEAR (DateCreated) >= 2014

GROUP BY CustomerID, FirstName, LastName, Address, DateCreated

F. SELECT c.CustomerID, C.FirstName, c.LastName, c.Address, c.ValidFrom, c.Validlo

FROM Customers AS c ORDER BY c.CustomerID

FOR XML PATH (‘CustomerData’), root (‘Customers’)

G. SELECT CustomerID, FirstName, LastName, TaxIdNumber, Address, ValidFrom, ValidTo

FROM Customers FOR SYSTEM TIME

BETWEEN 2014-01-01 00:00:00.000000’ AND $2015-01-01 00:00:00.0000001

H. SELECT CustomerID, FirstName, LastName, TaxIdNumber Address, ValidFrom, Validto

FROM Customers

WHERE DateCreated

BETWEEN 20140101’ AND 20141231’

Answer 49

A. SELECT FirstName, LastName, 8UM(Annua1Revenue)

FROM Customers

GROUP BY GROUPING SETS ( (FirstName, LastName, AnnualRevenue), ())

ORDER BY FirstName, LastName, AnnualRevenue

Question 50

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:

CREATE TABLE Customers (

CustomerID int NOT NULL PRIMARY KEY CLUSTERED,

FirstName nvarchar (100) NOT NULL,

LastName nvarchar (100) NOT NULL,

TaxIdNumber varchar (20) NOT NULL,

Address nvarchar (1024) NOT NULL,

AnnualRevenue decimal(19,2) NOT NULL,

DateCreated datetime2(2) NOT NULL,

ValidFrom datetime2 (2) GENERATED ALWAYS AS ROW START NOT NULL,

ValidTo datetime2 (2) GENERATED ALWATS AS ROW END NOT NULL,

PERIOD FOR SYSTEM TIME (ValidFrom, ValidIo)

WITH (SYSTEM VERSIONING = ON (HISTORY TABLE = CustomersHistory))

You need to develop a query that meets the following requirements:

c Output data by using tree-like structure.

Allow mixed content types.

co Use custom metadata attributes.

Which Transact-SQL statement should you run?

A. SELECT FirstName, LastName, 8UM(Annua1Revenue)

FROM Customers

GROUP BY GROUPING SETS( (FirstName, LastName, AnnualRevenue), ())

ORDER BY FirstName, LastName, AnnualRevenue

B. SELECT FirstName, LastName, Address

FROM Customers

FOR SYSTEM TIME ALL ORDER BY ValidFrom

C. SELECT c.CustomerID, c.FirstName, c.LastName, c.Address, c.Validfrom, c.Validlo

FROM Customers AS

ORDER BY c.CustomerID

FOR JSON AUTO, ROOT (‘Customers’)

D. SELECT * FROM (SELECT CustomerID, FirstName, LastName, Address, AnnualRevenue, DateCreated

FROM Customers) AS Customers PIVOT (AVG (AnnualRevenue)

FOR DateCreated IN([2014])) AS PivotCustomers

ORDER BY LastName, FirstName

E. SELECT CustomerID, AVG (Annua1Revenue)

AS AverageAnnualRevenue, FirstName, LastName, Address, DateCreated

FROM Customers WHERE YEAR (DateCreated) >= 2014

GROUP BY CustomerID, FirstName, LastName, Address, DateCreated

F. SELECT c. CustomerID, c.FirstName, c.LastName, c.Address, c.ValidFrom, c.ValidTo

FROM Customers AS c ORDER BY c.CustomerID

FOR XML PATH (‘CustomerData’), root (‘Customers’)

G. SELECT CustomerID, FirstName, LastName, TaxIdNumber, Address, ValidFrom, ValidTo

FROM Customers FOR SYSTEM IIME

BETWEEN ‘2014-01-01 00:00:00.000000’ AND ‘2015-01-01 00:00:00.000000’

H. SELECT CustomerID, FirstName, LastName, TaxIdNumber, Address, ValidFrom, ValidTo

FROM Customers

WHERE DateCreated

BETWEEN 20140101’ AND ‘20141231’

Answer 50

F. SELECT c. CustomerID, c.FirstName, c.LastName, c.Address, c.ValidFrom, c.ValidTo

FROM Customers AS c ORDER BY c.CustomerID

FOR XML PATH (‘CustomerData’), root (‘Customers’)

Question 51

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. an extended stored procedure

B. a user-defined table-valued function

C. a user-defined stored procedure that has an OUTPUT parameter

D. a memory-optimized table that has updated statistics

Answer 51

B. a user-defined table-valued function

Question 52

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 are building a stored procedure that will be used by hundreds of users concurrently.
You need to store rows that will be processed later by the stored procedure. The object that stores the rows must meet the following requirements:
✑ Be indexable
✑ Contain up-to-date statistics
✑ Be able to scale between 10 and 100,000 rows
The solution must prevent users from accessing one another’s data.
Solution: You create a global temporary table in the stored procedure.
Does this meet the goal?

A. Yes

B. No

Answer 52

A. Yes

Question 53

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 are building a stored procedure that will be used by hundreds of users concurrently.
You need to store rows that will be processed later by the stored procedure. The object that stores the rows must meet the following requirements:
✑ Be indexable
✑ Contain up-to-date statistics
✑ Be able to scale between 10 and 100,000 rows
The solution must prevent users from accessing one another’s data.
Solution: You create a local temporary table in the stored procedure.
Does this meet the goal?

A. Yes

B. No

Answer 53

B. No

Question 54

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 are building a stored procedure that will be used by hundreds of users concurrently.
You need to store rows that will be processed later by the stored procedure. The object that stores the rows must meet the following requirements:
✑ Be indexable
✑ Contain up-to-date statistics
✑ Be able to scale between 10 and 100,000 rows
The solution must prevent users from accessing one another’s data.
Solution: You create a table variable in the stored procedure.
Does this meet the goal?

A. Yes

B. No

Answer 54

B. No

Question 55

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 are creating indexes in a data warehouse.
You have a dimension table named Table1 that has 10,000 rows. The rows are used to generate several reports.
The reports join a column that is the primary key.
The execution plan contains bookmark lookups for Table1.
You discover that the reports run slower than expected.
You need to reduce the amount of time it takes to run the reports.
Solution: You create a hash index on the primary key column.
Does this meet the goal?

A. Yes

B. No

Answer 55

B. No

Question 56

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 are creating indexes in a data warehouse.
You have a dimension table named Table1 that has 10,000 rows. The rows are used to generate several reports.
The reports join a column that is the primary key.
The execution plan contains bookmark lookups for Table1.
You discover that the reports run slower than expected.
You need to reduce the amount of time it takes to run the reports.
Solution: You create a clustered index on the primary key column.
Does this meet the goal?

A. Yes

B. No

Answer 56

A. Yes

Question 57

Answer 57

Box 1: All transactions are rolled back.
The first IF-statement, IF @CODE = ‘C2323’ AND @ApplicationID = 1, will be true, an error will be raised, the error will be caught in the CATCH block, and the only transaction that has been started will be rolled back.
Box 2: Only Log1, Log2, and Log3 tables are updated.
The second IF-statement, IF @Code = ‘C2323’, will be true, so the second transaction will be rolled back, but log1, log2, and log3 was updated before the second transaction.

Question 58

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 are creating indexes in a data warehouse.
You have a dimension table named Table1 that has 10,000 rows. The rows are used to generate several reports.
The reports join a column that is the primary key.
The execution plan contains bookmark lookups for Table1.
You discover that the reports run slower than expected.
You need to reduce the amount of time it takes to run the reports.
Solution: You create a nonclustered index on the primary key column that includes the bookmark lookup columns.
Does this meet the goal?

A. Yes

B. No

Answer 58

B. No

Question 59

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

Solution: You recommend the following query:

SELECT

Cust.CustomerName,

NumberOfOrders = COUNT (*)

FROM

Sales.Customers Cust

LEFT JOIN

Sales.Orders Ord

ON Cust.CustomerID = Ord.OrderID

GROUP BY

Cust.CustomerName;

Does this meet the goal?

A. Yes

B. No

Answer 59

B. No

Question 60

Note: This question is part of a series of questions that present the same scenario. Each question in the series contains unique solution that might meel 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 Sales.Orders.

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

Solution: You recommend the following query:

SELECT

Cust.CustomerName,

NumberOfOrders = COUNT (Cust.CustomerID)

FROM

Sales.Customers Cust

LEFT JOIN

Sales.Orders Ord

ON Cust CustomerID = Ord.OrderID

GROUP BY

Cust.CustomerName

Does this meet the goal?

A. Yes

B. No

Answer 60

A. Yes

Question 61

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 Sales. Orders.

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

Solution: You recommend the following query:

SELECT

Cust.CustomerName,

NumberofOrders = COUNT (Ord.OrderID)

FROM

Sales.Customers Cust

LEFT JOIN

Sales.Orders Ord

ON Cust.CustomerID = Ord.OrderID

GROUP BY

Cust.CustomerName;

Does this meet the goal?

A. Yes

B. No

Answer 61

B. No

Question 62

Answer 62

A. Yes

Question 63

Answer 63

A. SELECT C.CustomerID, ISNULL (MAX (OrderDate), ‘19000101’)

FROM Sales.Customer C LEFT OUTER JOIN Sales.SalesOrderHeader SOH

ON C.CustomerID = SOH.CustomerID

GROUP BY C.CustomerID

ORDER BY C.CustomerID

Question 64

Answer 64

Question 65

Answer 65

Question 66

Answer 66

Answer 67

Correct Answer: See explanation below

1. SELECT avg(P.ProductPrice) AS Average, min(P.ProductsInStock) AS LowestNumber, max(P.ProductPrice) AS HighestPrice
2. FROM Sales.Products AS P

Make the additions to line 1.

Question 68

Answer 68

Box 1: TRY”¦CATCH -
The TRY…CATCH Transact-SQL construct implements error handling for Transact-SQL that is similar to the exception handling in the Microsoft Visual C# and
Microsoft Visual C++ languages. A group of Transact-SQL statements can be enclosed in a TRY block. If an error occurs in the TRY block, control is passed to another group of statements that is enclosed in a CATCH block.
Box 2: RAISERROR(50555, 14, 1 ‘The update failed.”) WITH LOG
We must use RAISERROR to be able to specify the required severity level of 14, and we should also use the LOG option, which Logs the error in the error log and the application log for the instance of the Microsoft SQL Server Database Engine, as this enable a MS MS SQL SERVER alert to be triggered.
Note: 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.
Incorrect Answers:
Not THROW: THROW does not have a severity parameter.

Question 69

You are developing a mobile app to manage meetups. The app allows for users to view the 25 closest people with similar interests. You have a table that contains records

for approximately two million people. You create the table by running the following Transact-SQL statement:

CREATE TABLE Person (

PersonID INT,

Name NVARCHAR (155) NOT NULL,

Location GEOGRAPHY,

Interests NVARCHAR (MAX)

You create the following table valued function to generate lists of people:

CREATE FUNCTION dbo.nearby (@person AS INT)

RETURNS @Res TABLE

Personid INT NOT NULL,

Location GEOGRAPHY

)

AS

BEGIN

END

You need to build a report that shows meetings with at least two people only.

What should you use?

A. OUTER APPLY

B. CROSS APPLY

C. PIVOT

D. LEFT OUTER JOIN

Answer 69

B. CROSS APPLY

Question 70

You develop and deploy : 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. “c Support multibyte Unicode characters.

You run the following Transact-SQL statement:

CREATE TABLE Bug (

Id UNIQUEIDENTIFIER NOT NULL,

Product NVARCHAR (255) NOT NULL,

Description NVARCHAR (max) NOT NULL,

DateCreated DATETIME NULL,

ReportingUser VARCHAR(50) NULL

You need to ensure that users can perform searches of descriptions.

Which Transact-SQL statem should you run?

A.DECLARE @term NVARCHAR(255)

SELECT Id, Description

FROM Bug

WHERE CHARINDEX(@term, Description) > 0

B. DECLARE @term NVARCHAR(255)

SELECT Id, Description

FROM Bug

WHERE DIFFERENCE (@term, Description) > 0

C. DECLARE @term NVARCHAR(255)

SELECT Id, Description

FROM Buq

WHERE CONTAINS (Description, ‘$@termt’)

D. DECLARE @term NVARCHAR(255)

SELECT Id, Description

FROM Bug

WHERE CONTAINS (Description, @term)

Answer 70

D. DECLARE @term NVARCHAR(255)

SELECT Id, Description

FROM Bug

WHERE CONTAINS (Description, @term)

Question 71

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 71

C. Add the txt value to an OUTPUT parameter of SP2.

Question 72

Answer 72

Question 73

Answer 73

Question 74

Answer 74

Question 75

Answer 75

Question 76

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 are creating indexes in a data warehouse.
You have a dimension table named Table1 that has 10,000 rows. The rows are used to generate several reports.
The reports join a column that is the primary key.
The execution plan contains bookmark lookups for Table1.
You discover that the reports run slower than expected.
You need to reduce the amount of time it takes to run the reports.
Solution: You create a nonclustered index on the primary key column that does NOT include columns.
Does this meet the goal?

• A. Yes
• B. No

Answer 76

Correct Answer: A

Question 77

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:

CREATE TABLE Customer

CustomexID int IDENTITY(1,1) PRIMARY KEY,

FirstName varchar(50) NULL,

LastName varchar (50) NOT NULL,

DateOfBirth date NOT NULL,

CreditLimit money CHECK (CreditLimit 10000),

TownID int NULL REFERENCES Town (TOWNID),

CreatedDate datetime DEFAULT (GETDATE () )

You create a cursor by running the following Transact-SQL statement:

DECLARE cur CURSOR

FOR

SELECT LastName, CreditLimit

FROM Customer

DECLARE @LastName varchar (50), @CreditLimit money

OPEN cur

FETCH NEXT FROM cur INTO @LastName, @CreditLimit

WHILE (@@FETCH_STATUS = 0)

BEGIN

FETCH NEXT FROM cur INTO @LastName, @CreditLimit

END

CLOSE cur

DEALLOCATE cur

the credit limit is zero, you must delete the customer record while fetching data.

You need to add the DELETE statement.

Solution: You add the following Transact-SQL statement:

IF @CreditLimit = 0

DELETE Customer

WHERE CustomerID IN (SELECT CustomerID)

FROM Customer WHERE LastName = @LastName)

Does the solution meet the goal?

A. Yes

B. No

Answer 77

Correct Answer: B
Use a WHERE CURRENT OF clause, which deletes at the current position of the specified cursor.

Question 78

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:

CREATE TABLE Customer

CustomerID int IDENTITY(1,1) PRIMARY KEY,

FirstName varchar(50) NULL,

LastName varchar(50) NOT NULL,

DateOfBirth date NOT NULL,

CreditLimit money CHECK (CreditLimit < 10000),

TownID int NULL REFERENCES Town (TOWnID),

CreatedDate datetime DEFAULT (GETDATE () )

You create a cursor by running the following Transact-SQL statement:

DECLARE cur CURSOR

FOR

SELECT LastName, CreditLimit

FROM Customer

DECLARE @LastName varchar(50), @CreditLimit money

OPEN cur

FETCH NEXT FROM cur INTO @LastName, @CreditLimit

WHILE (@@FETCH_STATUS = 0)

BEGIN

FETCH NEXT FROM cur INTO @LastName, @CreditLimit

END

CLOSE cur

DEALLOCATE cur

If the credit limit is zero, you must delete the customer record while fetching data.

You need to add the DELETE statement.

Solution: You add the following Transact-SQL statement:

IF @CreditLimit = 0

DELETE Customer

WHERE CURRENT OF cur

Does the solution meet the goal?

A. Yes

B. No

Answer 78

Correct Answer: A
CURRENT OF specifies that the DELETE is performed at the current position of the specified cursor.

Question 79

Answer 79

Question 80

Answer 80

Question 81

Answer 81

Question 82

You have a table named Table1 that contains 200 million rows. Table1 contains a column named SaleDate that has a data type of DateTime2(3).

Users report that the following query runs slowly.

Select SalesPerson, count(*)

FROM tablel

Where year(SaleDate) = 2017

GROUP BY SalesPerson

You need to reduce the amount of time it takes to run the query.

What should you use to replace the WHERE statement?

A. WHERE SaleDate >= ‘2017-01-01’ AND SaleDate <‘2018-01-01’

B. WHERE cast(SaleDate as varchar(10)) BETWEEN ‘2017-01-01’ AND ‘2017-12-31’

C. WHERE cast(SaleDate as date) BETWEEN ‘2017-01-01’ AND ‘2017-12-31’

D. WHERE 2017 = year(SaleDate)

Answer 82

C. WHERE cast(SaleDate as date) BETWEEN ‘2017-01-01’ AND ‘2017-12-31’

Question 83

Answer 83

D. SELECT ComplaintID, Customer Transcript FROM Complaints WHERE CustomerTranscript like ‘%defective product%’

Question 84

Answer 84

B. SELECT * FROM CourseParticipants PIVOT(SUM(NumParticipants) FOR LocationDescription IN (Lisbon, London, Seattle)) as PVTTable

Question 85

You have a project management application. The application uses 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.

co Filter search results based on exact text in the description.

c Support multibyte Unicode characters.

You run the following Transact-SQL statement:

CREATE TABLE Bug

Id UNIQUEIDENTIFIER NOT NULL,

Product NVARCHAR(255) NOT NULL,

Description NVARCHAR (max) NOT NULL,

DateCreated DATETIME NULL,

ReportingUser VARCHAR(50) NULL

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 85

A. SELECT Id,Product, DateCreated AT TIME ZONE ‘Pacific Standard Time’ FROM Bug

Question 86

Answer 86

Question 87

Answer 87

Answer 88

1. SELECT p.productname
2. FROM Production.Categories As c
3. JOIN Production.Products As p
   ON ( c.categoryid = p.categoryid )
4. WHERE c.categoryname = ‘Beverages’

Question 89

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 Person that contains information about employees. Users are requesting a way to access specific columns from the Person table without specifying the Person table in the query statement. The columns that users can access will be determined when the query is running against the data. There are some records that are restricted, and a trigger will evaluate whether the request is attempting to access a restricted record.
You need to ensure that users can access the needed columns while minimizing storage on the database server.
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 89

B. a view

Question 90

You need to create an indexed view that requires logic statements to manipulate the data that the view displays.
Which two database objects should you use? Each correct answer presents a complete solution.

A. a user-defined table-valued function

B. a CRL function

C. a stored procedure

D. a user-defined scalar function

Answer 90

Correct Answer: AC
You can create a database object inside an instance of SQL Server that is programmed in an assembly created in the Microsoft .NET Framework common language runtime (CLR). Database objects that can leverage the rich programming model provided by the common language runtime include aggregate functions, functions, stored procedures, triggers, and types.

Question 91

You have a table that was created by running the following Transact-SOL statement:

CREATE TABLE Courses(

CourseID INT IDENTITY(1,1) NOT NULL,

Course VARCHAR (50) NULL)

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

{

“Courses”:

“Course ID”:1,

“Name”:”Database Development”

),

“Course ID”:2,

“Name”:”Programming in c#”

A. SELECT CourseID AS [Course ID], Course as Name FROM Courses FOR JSON PATH(‘Courses)

B. SELECT CourseID AS ‘Course ID’ Course AS Name FROM Courses FOR JSON ROOT(Courses)

C. SELECT CourselD AS [Course ID], Course AS Name FROM Courses FOR JSON AUTO, ROOT(‘Courses’)

D. SELECT CourseID AS ‘Course ID’, Course AS Name FROM Courses FOR JSON AUTO, INCLUDE_NULL_VALUES(‘Courses*)

Answer 91

C. SELECT CourseID AS [Course ID], Course AS Name FROM Courses FOR JSON AUTO, ROOT(‘Courses’)

Question 92

Answer 92

C. SELECT salesID, customer, amount FROM SalesNorth UNION ALL SELECT salesID, customer, amount FROM SalesSouth

Question 93

Answer 93

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 } ]
[;]
Box2: 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 19through 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.

Question 94

You have a date related query that would benefit from an indexed view.
You need to create the indexed view.
Which two Transact-SQL functions can you use? Each correct answer presents a complete solution.
NOTE: Each correct selection is worth one point.

• A. DATEADD
• B. AT TIME ZONE
• C. GETUTCDATE
• D. DATEDIFF

Answer 94

A. DATEADD

D. DATEDIFF

Question 95

You are developing a database to track employee progress relative to training goals. You run the following Transact-SQL statements:

CREATE TABLE Employees(

EmployeeID INT IDENTITY(1,1) NOT NULL,

Name VARCHAR(150) NULL,

CONSTRAINT PK_Employees PRIMARY KEY CLUSTERED

EmployeeID ASC

WITH (STATISTICS_NORECOMPUTE - OFF, IGNORE_DUP KEY OFF) ON PRIMARY

ON PRIMARY

CREATE TABLE CoursesTaken(

CourseID INT NOT NULL,

EmployeeID INT NOT NULL,

CourseTakenOn DATE NULL,

CONSTRAINT PK CoursesTaken PRIMARY KEY CLUSTERED

CourseID ASC, EmployeeID ASC

) WITH (STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY OFF) ON PRIMARY

CREATE TABLE Courses(

CourseID INT IDENTITY(1,1) NOT NULL,

Course VARCHAR(50) NULL,

CONSTRAINT PK_Courses PRIMARY KEY CLUSTERED

CourseID ASC

) WITH (STATISTICS_NORECOMPUTE - OFF, IGNORE_DUP KEY 1 OFF) ON PRIMARY

ON PRIMARY

You must build a report that shows all Employees and the courses that they have taken. Employees that have not taken training courses must still appear in the report. The

report must display NULL in the course column for these employees.

You need to create a query for the report.

A. SELECT e.Name, c.Course

FROM dbo.Courses

JOIN dbo.CoursesTaken ct ON c.CourseID = ct.CourseID

INNER JOIN dbo. Employees e ON ct. EmployeeID = e. EmployeeID

B. SELECT e.Name, c.Course

FROM dbo.Courses c

JOIN dbo.CoursesTaken ct ON c.CourseID = ct.CourseID

JOIN dbo. Employees e ON ct. EmployeeID = e.EmployeeID

C. SELECT e.Name, c.Course

FROM dbo.Courses c

JOIN dbo.CoursesTaken ct ON c. CourseID = ct.CourseID

LEFT JOIN dbo.Employees e ON ct. EmployeeID e.EmployeeID

D. SELECT e.Name, c.Course

FROM doo.Courses C

JOIN dbo.CoursesTaken ct ON c.CourseID = ct.CourseID

RIGHT JOIN dbo.Employees e ON ct. EmployeeID e.EmployeeID

Answer 95

D. SELECT e.Name, c.Course

FROM doo.Courses C

JOIN dbo.CoursesTaken ct ON c.CourseID = ct.CourseID

RIGHT JOIN dbo.Employees e ON ct. EmployeeID e.EmployeeID

Question 96

Answer 96

Correct Answer: B
We cannot use the column alias Salesperson in the GROUP BY clause, since in Oracle and SQL Server, you cannot use a term in the GROUP BY clause that you define in the SELECT clause because the GROUP BY is executed before the SELECT clause.

Question 97

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 Sales. Orders.

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

Solution: You recommend the following query:

SELECT

Cust.CustomerName,

NumberOfOrders = COUNT (Ord.OrderID)

FROM

Sales.Customers Cust

LEFT JOIN

Sales.Orders Ord

ON Cust.CustomerID = Ord.OrderID

GROUP BY

Cust.CustomerName;

HAVING

COUNT (Ord.OrderID) > 0;

Does this meet the goal?

A. Yes

B. No

Answer 97

Correct Answer: B

Question 98

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 correct solution.

the review screen. 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

You create a table named Products by running the following Transact-SQL statement:

CREATE TABLE Products

ProductID int IDENTITY (1, 1), NOT NULL PRIMARY KEY,

ProductName nvarchar (100), NULL,

UnitPrice decimal (18, 2) NOT NULL,

UnitsInStock int NOT NULL,

Units OnOrder int NULL

You have the following stored procedure:

CREATE PROCEDURE InsertProduct

@ProductName nvarchar(100),

@UnitPrice decimal (18, 2),

@UnitsInStock int,

@UnitsOnOrder int

AS

BEGIN

INSERT INTO Products (ProductName, UnitPrice, UnitsInStock, UnitsOnOrder)

VALUES (@ProductName, @UnitPrice, @UnitsInStock, @UnitsOnOrder)

END

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 : product record insert operation fails, the product information must not be permanently written to the database.

Solution: You run the following Transact-SQL statement:

ALTER PROCEDURE InsertProduct

@ProductName nvarchar (100),

@UnitPrice decimal (18, 2),

@UnitsInStock int,

@UnitsOnOrder int

AS

BEGIN

SET XACT ABORT ON

BEGIN TRY

BEGIN TRANSACTION

INSERT INTO Products (ProductName, UnitPrice, UnitsInStock, UnitsOnOrder)

VALUES (@ProductName, @UnitPrice, @UnitsInStock, @UnitsOnOrder)

COMMIT TRANSACTION

END TRY

BEGIN CATCH

THROW 51000, The product could not be created, 1

END CATCH

END

Does this meet the goal?

A. Yes

B. No

Answer 98

Correct Answer: B
XACT_STATE is a scalar function that reports the user transaction state of a current running request. XACT_STATE indicates whether the request has an active user transaction, and whether the transaction is capable of being committed.
The states of XACT_STATE are:
✑ 0 There is no active user transaction for the current request.
✑ 1 The current request has an active user transaction. The request can perform any actions, including writing data and committing the transaction.
✑ 2 The current request has an active user transaction, but an error has occurred that has caused the transaction to be classified as an uncommittable transaction.
Example of correct use:

BEGIN CATCH -

• • Test XACT_STATE for 0, 1, or -1.
• • If 1, the transaction is committable.
• • If -1, the transaction is uncommittable and should
• • be rolled back.
• • XACT_STATE = 0 means there is no transaction and
• • a commit or rollback operation would generate an error.
• • Test whether the transaction is uncommittable.

IF (XACT_STATE()) = -1 -

BEGIN -
PRINT ‘The transaction is in an uncommittable state.’ +
‘ Rolling back transaction.’
ROLLBACK TRANSACTION;
END;
– Test whether the transaction is active and valid.

IF (XACT_STATE()) = 1 -

BEGIN -
PRINT ‘The transaction is committable.’ +
‘ Committing transaction.’
COMMIT TRANSACTION;
END;
END CATCH;

Question 99

You are performing a code review of stored procedures. Code at line SP03 fails to run (Line numbers are included for reference only.)

SP01 BEGIN TRY

SP02 BEGIN TRANSACTION

SP03

SP04 COMMIT TRANSACTION

SP05 END TRY

SP06 BEGIN CATCH

SP07

SP08 ROLLBACK TRANSACTION

SP09 END CATCH

You need to ensure that transactions are rolled back when an error occurs.

Which Transact-SQL segment should you insert at line SP07?

A. @@Error <> 0

B. If @@ TRANCOUNT = 0

C. f @@ TRANCOUNT > 0

D. If @@ Error = 0

Answer 99

Correct Answer: C
Using TRY…CATCH in a transaction
The following example shows how a TRY…CATCH block works inside a transaction. The statement inside the TRY block generates a constraint violation error.
BEGIN TRANSACTION;

BEGIN TRY -
– Generate a constraint violation error.

DELETE FROM Production.Product -
WHERE ProductID = 980;

END TRY -

BEGIN CATCH -

SELECT -

ERROR_NUMBER() AS ErrorNumber -
,ERROR_SEVERITY() AS ErrorSeverity
,ERROR_STATE() AS ErrorState
,ERROR_PROCEDURE() AS ErrorProcedure
,ERROR_LINE() AS ErrorLine
,ERROR_MESSAGE() AS ErrorMessage;

IF @@TRANCOUNT > 0 -
ROLLBACK TRANSACTION;
END CATCH;

IF @@TRANCOUNT > 0 -
COMMIT TRANSACTION;

Question 100

Answer 100

Question 101

Answer 101

A TRY block must be immediately followed by an associated CATCH block. Including any other statements between the END TRY and BEGIN CATCH statements generates a syntax error.

Question 102

Answer 102

Box 1: Deterministic -
The definition of an indexed view must be deterministic. A view is deterministic if all expressions in the select list, as well as the WHERE and GROUP BY clauses, are deterministic.

Box 2: SCHEMABINDING -
Create the view by using the WITH SCHEMABINDING option.

Box 3: unique clustered -
The first index created on a view must be a unique clustered index.

Question 103

Answer 103

D.

SELECT ComplaintID, ComplaintTranscript FROM Complaints

WHERE CONTAINS (CustomerTranscript, ‘defective’) AND CONTAINS (CustomerTranscript, ‘product’)

Question 104

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:

CREATE TABLE Customer

CustomerID int IDENTITY(1,1) PRIMARY KEY,

FirstName varchar(50) NULL,

LastName varchar(50) NOT NULL,

DateOfBirth date NOT NULL,

CreditLimit money CHECK (CreditLimit < 10000),

TovnID int NULL REFERENCES Town (TownID)

CreatedDate datetime DEFAULT (GETDATE () )

You create a cursor by running the following Transact-SQL statement:

DECLARE cur CURSOR

FOR

SELECT LastName, CreditLimit

FROM Customer

DECLARE (LastName varchar (50), @CreditLimit money

OPEN cur

FETCH NEXT FROM cur INTO @LastName, @CreditLimit

WHILE (@@FETCH STATUS 0)

BEGIN

FETCH NEXT FROM cur INTO @LastName, @CreditLimi

END

CLOSE cur

DEALLOCATE cur

If the credit limit is zero, you must delete the customer record while fetching data.

You need to add the DELETE statement.

Solution: You add the following Transact-SQL statement:

IF @CreditLimit = 0

DELETE TOP (1) Customer

WHERE LastName = @LastName

Does the solution meet the goal?

A. Yes

B. No

Answer 104

Correct Answer: B. No
Use a WHERE CURRENT OF clause, which deletes at the current position of the specified cursor.

Question 105

Answer 105

Box 1: GROUP BY -

Box 2: CUBE -
GROUP BY CUBE creates groups for all possible combinations of columns. For GROUP BY CUBE (a, b) the results has groups for unique values of (a, b), (NULL, b), (a, NULL), and (NULL, NULL).
Example: This code runs a GROUP BY CUBE operation on Country and Region.
SELECT Country, Region, SUM(Sales) AS TotalSales

FROM Sales -
GROUP BY CUBE (Country, Region);

Question 106

HOTSPOT -
You are creating a database solution to track sales achievements of your training courses. You run the following statements:

You plan to add courses to a table named HighlightedCourses. You must add courses that have been delivered to more than 100 participants only.
If the total number of participants for a course is lower than 100, the course must not be added to the HighlightedCourses table. In addition, an error message must be displayed and remaining Transact-SQL code must not run.

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.

Hot Area:

Answer 106

Correct Answer: Explanation
Box 1: THROW -
TRHOW raises an exception and transfers execution to a CATCH block of a TRY…CATCH construct.
If a TRY…CATCH construct is not available, the statement batch is terminated. The line number and procedure where the exception is raised are set.

Box 2: IF (@TotalParticipants < 100)
Incorrect Answers:
Not BREAK: BREAK exits the current WHILE loop. If the current WHILE loop is nested inside another, BREAK exits only the current loop, and control is given to the next statement in the outer loop.
Not RAISERROR: Microsoft recommends that THROW should be used instead of RAISERROR.

Question 107

Answer 107

Correct Answer: Explanation
Box 1: LEFT JOIN -
We want a list of all product photos, and whether the product has a primary photo.

Box 2: FULL OUTER JOIN -
The FULL OUTER JOIN keyword return all records when there is a match in either left (table1) or right (table2) table records.

Box 3: AND

Question 108

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 are building a stored procedure that will be used by hundreds of users concurrently.

You need to store rows that will be processed later by the stored procedure. The object that stores the rows must meet the following requirements:
✑ Be indexable
✑ Contain up-to-date statistics
✑ Be able to scale between 10 and 100,000 rows
The solution must prevent users from accessing one another’s data.
Solution: You create a user-defined table in the stored procedure.

Does this meet the goal?

• A. Yes
• B. No

Answer 108

B. No

Question 109

Answer 109

Question 110

You run the following Transact-SQL statement:

CREATE TABLE CourseParticipants

CourseID INT NOT NULL,

CourseDate DATE NOT NULL,

LocationDescription VARCHAR(100) NOT NULL,

NumParticipants INT NOT NULL

)

You use the table to store data about training courses: when they finished the location, and the number of participants in the courses.

You need to display a result set that shows aggregates for all possible combinations of the number of participants.

Which Transact-SQL statement should you run?

A. SELECT CourseID, CourseDate, SUM(NumParticipants) FROM CourseParticipants GROUP BY CourseID, CourseDate

B. SELECT CourseID, CourseDate, SUM(DISTINCT NumParticipants) FROM CourseParticipants GROUP BY CourselD, CourseDate

C. SELECT CourseID, CourseDate, SUM(NumParticipants) FROM CourseParticipants GROUP BY CourseID, CourseDate WITH CUBE

D. SELECT CourseID, CourseDate, SUM(DISTINCT NumParticipants) FROM CourseParticipants GROUP BY CourseID, CourseDate WITH ROLLUP

Answer 110

C. SELECT CourseID, CourseDate, SUM(NumParticipants) FROM CourseParticipants GROUP BY CourseID, CourseDate WITH CUBE

Question 111

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.

Which Transact-SQL statement should you run?

A. SELECT c.CustomerCode, c.CustomerName, h.CustomerCode, h.CustomerName

FROM Customer_CRMSystem c INNER JOIN Customer HRSystem h

ON c.CustomerCode = h.CustomerCode AND c.CustomerName = h.CustomerName

B. SELECT CustomerCode, CustomerNane

FROM Customer_CRMSystem

INTERSECT

SELECI CustomerCode, CustomerName

FROM Customer HRSystem

C. SELECT c.CustomerCode, c.CustomerName

FROM Customer CRMSystem

LEFT OUTER JOIN Customer HRSystem h

ON c.CustomerCode = h.CustomerCode

WHERE h.CustomerCode IS NULL AND c.CustomerCode IS NOT NULL

D. SELECT CustomerCode, CustomerName

FROM Customer_CRMSystem

EXCEPT

SELECT CustomerCode, CustomerName

FROM Customer HRSystem

E. SELECT CustomerCode, CustomerName

FROM Customer_CRMSystem

UNION

SELECT CustomerCode, CustomerName

FROM Customer HRSystem

F. SELECT CustomerCode, CustomerName

FROM Customer_CRMSystem

UNION ALL

SELECT CustomerCode, CustomerName

FROM Customer HRSystem

G. SELECT c.CustomerCode, c.CustomerName, h.CustomerCode, h.CustomerName FROM Customer_CRMSystem

CROSS JOIN Customer HRSystem h

H. SELECT c.CustomerCode, c.CustomerName, h.CustomerCode, h.CustomerName

FROM Customer CRMSystem

FULL OUTER JOIN Customer HRSystem h

ON c.CustomerCode = h.CustomerCode AND c.CustomerName = h.CustomerName

Answer 111

A: INNER JOIN returns records that have matching values in both tables but it returns duplicate records.

Question 112

Answer 112

Correct Answer: B
A transaction is correctly defined for the INSERT INTO .VALUES statement, and if there is an error in the transaction it will be caught ant he transaction will be rolled back. However, error number 51000 will not be returned, as it is only used in an IF @ERROR = 51000 statement.
Note: @@TRANCOUNT returns the number of BEGIN TRANSACTION statements that have occurred on the current connection.

Question 113

Answer 113

Correct Answer: Please see explanation
SELECT Average(NormalizedReading), NearestMountain(SensorID)

FROM GroundSensors -
WHERE TREMOR IS NOT 0 AND NormalizedReading IS NOT NULL
GROUP BY NearestMountain(SensorID)
GROUP BY is a SELECT statement clause that divides the query result into groups of rows, usually for the purpose of performing one or more aggregations on each group. The SELECT statement returns one row per group.

Question 114

Answer 114

Question 115

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

CREATE TABLE tblRoles (

Roleld int NOT NULL IDENTITY(1,1) PRIMARY KEY CLUSTERED,

RoleName varchar (20) NOT NULL

CREATE TABLE tblUsers

Userid int NOT NULL IDENTITY(10000,1) PRIMARY KEY CLUSTERED,

UserName varchar (20) UNIQUE NOT NULL,

RoleId int NULL FOREIGN KEY REFERENCES tbRoles (RoleId),

IsActive bit NOT NULL DEFAULI (1)

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. SELECT R.RoleName, COUNT (U.UserId) AS ActiveUserCount FROM tblRoles R

LEFT JOIN (SELECT UserId, RoleId FROM tblUsers WHERE IsActive = 1) U ON U.RoleId = R.RoleId

GROUP BY R.RoleId, R.RoleName

B. SELECT R.RoleName, U.ActiveUserCount FROM tblRoles R CROSS JOIN

(SELECT COUNT (*) AS ActiveUserCount FROM tblUsers WHERE IsActive = 1) U

C. Select R.RoleName, U.ActiveUserCount FROM tbiRoles R CROSS JOIN

(SELECT Roleld, COUNT(“) AS ActiveUserCount FROM tblUsers WHERE IsActive = 1

GROUP BY Roleld) U

D. SELECT R.RoleName, COUNT(*) ActiveUserCount FROM tblRoles R

INNER JOIN tblUsers U ON U.Roleld = R.Roleld

WHERE U.IsActive = 1 Group BY R.Roleld, R. RoleName

Answer 115

A. SELECT R.RoleName, COUNT (U.UserId) AS ActiveUserCount FROM tblRoles R

LEFT JOIN (SELECT UserId, RoleId FROM tblUsers WHERE IsActive = 1) U ON U.RoleId = R.RoleId

GROUP BY R.RoleId, R.RoleName

Question 116

Answer 116

Question 117

Answer 117

Box 1: Scalar -
The return value of a function can either be a scalar (single) value or a table.

Box 2: Table-Valued -
The APPLY operator allows you to invoke a table-valued function for each row returned by an outer table expression of a query. The table-valued function acts as the right input and the outer table expression acts as the left input. The right input is evaluated for each row from the left input and the rows produced are combined for the final output. The list of columns produced by the APPLY operator is the set of columns in the left input followed by the list of columns returned by the right input.

Question 118

Answer 118

Box 1: SELECT..COALESCE”¦
The COALESCE function evaluates the arguments in order and returns the current value of the first expression that initially does not evaluate to NULL.
Box 2: ..LEFT OUTER JOIN..
The LEFT JOIN (LEFT OUTER JOIN) keyword returns all rows from the left table (table1), with the matching rows in the right table (table2). The result is NULL in the right side when there is no match. A customer might have no orders so the right table must be allowed have a NULL value.

Box 3: ON c.custid = o.custid -
We JOIN on the custID column, which is available in both tables.

Box 4: GROUP BY c.custid -

Question 119

Answer 119

Box 1: custid -
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.

Box2: postalcode -
postalcode is declared as NOT NULL, which means that a value must be inserted.

Box 3: region -
Fax is also a correct answer. Both these two columns are declared as NULL, which means that data entry is optional.

Question 120

Answer 120

Correct Answer: See the solution below
DELETE FROM Sales.Orders -
WHERE OrderDate < ‘2012-01-01’ AND ShippedDate NOT NULL

Answer 121

On ordering: ASC | DESC -
Specifies that the values in the specified column should be sorted in ascending or descending order. ASC sorts from the lowest value to highest value. DESC sorts from highest value to lowest value. ASC is the default sort order. Null values are treated as the lowest possible values.

Answer 122

Question 123

Answer 123

Correct Answer: A
ISNULL Syntax: ISNULL ( check_expression , replacement_value ) author:”Luxemburg, Rosa”
The ISNULL function replaces NULL with the specified replacement value. The value of check_expression is returned if it is not NULL; otherwise, replacement_value is returned after it is implicitly converted to the type of check_expression.

Question 124

Answer 124

Correct Answer: D
The OUTPUT Clause returns information from, or expressions based on, each row affected by an INSERT, UPDATE, DELETE, or MERGE statement. These results can be returned to the processing application for use in such things as confirmation messages, archiving, and other such application requirements. The results can also be inserted into a table or table variable. Additionally, you can capture the results of an OUTPUT clause in a nested INSERT, UPDATE, DELETE, or MERGE statement, and insert those results into a target table or view.
Note: If the column modified by the .RITE clause is referenced in an OUTPUT clause, the complete value of the column, either the before image in deleted.column_name or the after image in inserted.column_name, is returned to the specified column in the tablevariable.
Incorrect Answers:
C: The deleted.Creditlimit should be inserted in the second column, the OldCreditLimit column, not the third column.

Question 125

Answer 125

Correct Answer: A
If the INSERT INTO statement raises an error, the statement will be caught and an error 51000 will be thrown. In this case no records will have been inserted.
Note:
You can implement error handling for the INSERT statement by specifying the statement in a TRY”¦CATCH construct.
If an INSERT statement violates a constraint or rule, or if it has a value incompatible with the data type of the column, the statement fails and an error message is returned.

Question 126

Answer 126

Box 1: RANK() OVER -
RANK returns the rank of each row within the partition of a result set. The rank of a row is one plus thenumber of ranks that come before the row in question.
ROW_NUMBER and RANK are similar. ROW_NUMBER numbers all rows sequentially (for example 1, 2, 3, 4, 5).
Incorrect Answers:
DENSE_RANK returns the rank of rows within the partition of a result set, without any gaps in the ranking. The rank of a row is one plus the number of distinct ranks that come before the row in question.
Box 2: (PARTITION BY CityID ORDER BY MIN(AccountOpenedDate) DESC)
Syntax for RANK: RANK ( ) OVER ( [partition_by_clause] order_by_clause )

Box 3: GROUP BY CityID -

Question 127

Answer 127

Correct Answer: A
Using Cross Joins -
A cross join that does not have a WHERE clause produces the Cartesian product of the tables involved in the join. The size of a Cartesian product result set is the number of rows in the first table multiplied by the number of rows in the second table.
However, if a WHERE clause is added, the cross join behaves as an inner join.
B: You can use the IIF in the ON-statement.
IIF returns one of two values, depending on whether the Boolean expression evaluates to true or false in SQL Server.

Question 128

Answer 128

Correct Answer: D
The datetime datetype defines a date that is combined with a time of day with fractional seconds that is based on a 24-hour clock.
The DATEFROMPARTS function returns a date value for the specified year, month, and day.
Incorrect Answers:
A: ValidFrom should be less (<) than @sdate AND ValidTo should be greater (>) than @edate.
B: We should add a day with DATEADD, not subtract one day.
C: We cannot compare a date to an exact datetime.

Question 129

Answer 129

Question 130

Answer 130

Box 1: 0, 1, 2, 3, 4 -
Pivot example:
– Pivot table with one rowand 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.

Question 131

Answer 131

Box 1: IN (
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 subquery is a subquery that has a result set of one column. This column must have the same data type as test_expression.

Box 2: WHERE -

Box 3: AND [IsOnCreditHold] = 0 -
Box 4: )

Question 132

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 Products that contains information about the products that your company sells. The table contains many columns that do not always contain values.
You need to implement an ANSI standard method to convert the NULL values in the query output to the phrase “Not Applicable”.
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 132

F. the ISNULL function

The ISNULL function replaces NULL with the specified replacement value.

Question 133

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 is denormalized. Users make frequent changes to data in a primary table.
You need to ensure that users cannot change the tables directly, and that changes made to the primary table also update any related tables.
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 133

B. a view

Using an Indexed View would allow you to keep your base data in properly normalized tables and maintain data-integrity while giving you the denormalized “view” of that data.

Question 134

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?

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 134

C. a table-valued function

User-defined functions that return a table data type can be powerful alternatives to views. These functions are referred to as table-valued functions. A table-valued user-defined function can be used where table or view expressions are allowed in Transact-SQL queries. While views are limited to a single SELECT statement, user-defined functions can contain additional statements that allow more powerful logic than is possible in views.
A table-valued user-defined function can also replace stored procedures that return a single result set.

Question 135

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 135

H. the TRY_CONVERT function

Correct Answer: H
A TRY_CONVERT function returns a value cast to the specified data type if the cast succeeds; otherwise, returns null.

Question 136

Answer 136

Subquery1: common table expression (CTE)
A common table expression (CTE) can be thought of as a temporary result set that is defined within the execution scope of a single SELECT, INSERT, UPDATE,
DELETE, or CREATE VIEW statement. A CTE is similar to a derived table in that it is not stored as an object and lasts only for the duration of the query. Unlike a derived table, a CTE can be self-referencing and can be referenced multiple times in the same query.
Subquery2: global temporary table
Global temporary tables are visible to any user and any connection after they are created, and are deleted when all users that are referencing the table disconnect from the instance of SQL Server.
Subquery3: local temporary table
Local temporary tables are visible only to their creators during the same connection to an instance of SQL Server as when the tables were first created or referenced. Local temporary tables are deleted after the user disconnects from the instance of SQL Server.

Question 137

Answer 137

Correct Answer: C

Question 138

Answer 138

Question 139

Answer 139

Correct Answer: C

Question 140

Answer 140

Correct Answer: Please see explanation

UNPIVOT must be used to rotate columns of the Rawsurvey table into column values.

Question 141

Answer 141

Question 142

Answer 142

Correct Answer: D
EXCEPT returns distinct rows from the left input query that aren’t output by the right input query.

Question 143

Answer 143

Correct Answer: A
When there are null values in the columns of the tables being joined, the null values do not match each other. The presence of null values in a column from one of the tables being joined can be returned only by using an outer join (unless the WHERE clause excludes null values).

Question 144

Answer 144

Correct Answer: G
A cross join that does not have a WHERE clause produces the Cartesian product of the tables involved in the join. The size of a Cartesian product result set is the number of rows in the first table multiplied by the number of rows in the second table.

Question 145

Answer 145

Correct Answer: E
UNION combines the results of two or more queries into a single result set that includes all the rows that belong to all queries in the union. The UNION operation is different from using joins that combine columns from two tables.
Incorrect Answers:
F: UNION ALL incorporates all rows into the results. This includes duplicates. If not specified, duplicate rows are removed.

Question 146

Answer 146

Correct Answer: B
As there are two separate INSERT INTO statements we cannot ensure that both or neither records are inserted.

Question 147

Answer 147

Box1: RETURNS TABLE -
The function should return the following information:
✑ all customer information for the customer
✑ the total number of orders for the customer
✑ the total price of all orders for the customer
✑ the average quantity of items per order

Box 2: COUNT -
The function should return the total number of orders for the customer.

Box 3: SUM -
The function should return the total price of all orders for the customer.

Box 3. AVG -
The function should return the average quantity of items per order.

Box 4: GROUP BY -
Need to use GROUP BY for the aggregate functions.

Question 148

Answer 148

Box 1: FUNCTION -
To be able to return a value we should use a scalar function.
CREATE FUNCTION creates a user-defined function in SQL Server and Azure SQL Database. The return value can either be a scalar (single) value or a table.
Box 2: RETURNS decimal(18,2)
Use the same data format as used in the UnitPrice column.

Box 3: BEGIN -
Transact-SQL Scalar Function Syntax include the BEGIN ..END construct.
CREATE [OR ALTER] FUNCTION [schema_name.] function_name
( [{ @parameter_name [ AS][type_schema_name.] parameter_data_type
[= default] [READONLY] }
[,…n]
]
)

RETURNSreturn_data_type -
[WITH <function_option> [ ,...n] ]<br></br>[AS]<br></br><br></br>BEGIN -<br></br>function_body<br></br><br></br>RETURN scalar_expression -<br></br><br></br>END -<br></br>[;]<br></br>Box 4: @OrderPrice * @CalculatedTaxRate<br></br>Calculate the price including tax.<br></br><br></br>Box 5: END -<br></br>Transact-SQL Scalar Function Syntax include theBEGIN ..END construct.</function_option>

Question 149

Answer 149

Box 1: XACT_ABORT -
XACT_ABORT specifies whether SQL Server automatically rolls back the current transaction when a Transact-SQL statement raises a run-time error.
When SET XACT_ABORT is ON, if a Transact-SQL statement raises a run-timeerror, the entire transaction is terminated and rolled back.

Box 2: COMMIT -
Commit the transaction.

Box 3: XACT_STATE -

Box 4: ROLLBACK -

Rollback the transaction -

Box 5: THROW -
THROW raises an exception and the severity is set to 16.
Requirement: Data modifications that are unsuccessful are rolled back. The exception severity level is set to 16 and a value of -1 is returned.

Question 150

Answer 150

Box1: CREATE FUNCTION”¦@OrderID
Include definition for the “¦@OrderID parameter.
Box 2: RETURNS decimal(18,2)
The function is defined to return a scalar value.
Box 3: AS BEGIN “¦
Declare the local variables of the function.
Box 4: SET @CalculatedTaxRate = (..
Calculate the tax rate.
Box 5: RETURN @CalculatedRate END
Return a scalar value.

Question 151

Answer 151

Correct Answer: B
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 2016, 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

clause has a new clause FOR SYSTEM_TIME with five temporal-specific sub-clauses to query data across the current and history tables.

Question 152

Answer 152

Correct Answer: G
The following query searches for row versions for Employee row with EmployeeID = 1000 that were active at least for a portion of period between 1st January of
2014 and 1st January 2015 (including the upper boundary):

SELECT * FROM Employee -

FOR SYSTEM_TIME -
BETWEEN ‘2014-01-01 00:00:00.0000000’ AND ‘2015-01-01 00:00:00.0000000’
WHERE EmployeeID = 1000ORDER BY ValidFrom;

Question 153

Answer 153

Correct Answer: E
Calculate aggregate column through AVG function and GROUP BY clause.

Question 154

Answer 154

Correct Answer: C
JSON can be used to pass AJAX updates between the client and the server.
Export data from SQL Server as JSON, or format query results as JSON, by adding the FOR JSON clause to a SELECT statement.
When you use the FOR JSON clause, you can specify the structure of the output explicitly, or let the structure of the SELECT statement determine the output.

Question 155

Answer 155

Correct Answer: G

Question 156

Answer 156

F.

In a FOR XML clause, you specify one of these modes: RAW, AUTO, EXPLICIT, and PATH.
✑ The EXPLICIT mode allows more control over the shape of the XML. You can mix attributes and elements at will in deciding the shape of the XML. It requires a specific format for the resulting rowset that is generated because of query execution. This row set format is then mapped into XML shape. The power of
EXPLICIT mode is to mix attributes and elements at will, create wrappers and nested complex properties, create space-separated values (for example,
OrderID attribute may have a list of order ID values), and mixed contents.
✑ The PATH mode together with the nested FOR XML query capability provides the flexibility of the EXPLICIT mode in a simpler manner.

Question 157

Answer 157

B. No, due to double quotation mark in statement

Question 158

Answer 158

B. No

We need to list all provinces that have at least two large cities. There is no reference to this in the code.

Question 159

Answer 159

B. No

The SQL CROSS JOIN produces a result set which is the number of rowsin the first table multiplied by the number of rows in the second table if no WHERE clause is used along with CROSS JOIN. This kind of result is called as Cartesian Product.
This is not what is required in this scenario.

Question 160

Answer 160

A. Yes

The requirement to list all provinces that have at least two large cities is meet by the WHERE CitySummary.LargeCityCount >=2 clause.
CROSS APPLY willwork fine here.
Note:
The APPLY operator allows you to invoke a table-valued function for each row returned by an outer table expression of a query. The table-valued function acts as the right input and the outer table expression acts as the left input. The right input is evaluated for each row from the left input and the rows produced are combined for the final output. The list of columns produced by the APPLY operator is the set of columns in the left input followed by the list of columns returned by the right input.
There are two forms of APPLY: CROSS APPLY and OUTER APPLY. CROSS APPLY returns only rows from the outer table that produce a result set from the table-valued function. OUTER APPLY returns both rows that produce a result set, and rows that do not, with NULL values in the columns produced by the table- valued function.

Question 161

Answer 161

A. Yes

The following indicates a correct solution:
✑ The function returns a nvarchar(10) value.
✑ Schemabinding is used.
✑ SELECT TOP 1 “¦ gives a single value
Note: nvarchar(max) is correct statement.
nvarchar [( n | max )]
Variable-length Unicode string data. n defines the string length and can be a value from 1 through 4,000. max indicates that the maximum storage size is 2^31-1 bytes (2 GB).

Question 162

Answer 162

B. No

We should use a WHERE clause, not a HAVING clause. The HAVING clause would refer to aggregate data.

Question 163

Answer 163

A. Yes

The MAX(orderdate) in the SELECT statement makes sure we return only the most recent order.
AWHERE o.empiD =4 clause is correctly used.
GROUP BY is also required.

Question 164

Answer 164

B. No

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

Question 165

Answer 165

Question 166

Answer 166

Correct Answer: D
Incorrect Answers:
A: For column Reason we must use nvarchar, not varchar, as multilingual values must be supported. NEWSEQUENTIALID cannot be referenced in queries. In addition, the money datatype uses rounding and will result in rounding errors.
B: We cannot use INT for the Id column as new values must be automatically generated.
C: For column Reason we must use nvarchar, not varchar, as multilingual values must be supported.
E: NEWSEQUENTIALID cannot be referenced in queries.
F: The money datatype uses rounding and will result in rounding errors. We should use decimal instead.
Note: 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.

Answer 167

Correct Answer: Please see 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

Question 168

Answer 168

Correct Answer: B
The function should return nvarchar(10) and not a TABLE.

Question 169

Answer 169

1 SELECT * FROM
2 (SELECT YEAR(SalesData)) AS Year, DATENAME (MONTH, SalesDate) AS Month, SUM(SalesAmount) AS Amount
3 FROM Products.Sales GROUP BY Year, Month
4 ) AS MonthlySalesData
5 PIVOT SUM(Amount)
6 FOR Month IN (January, February, March, April, May, June, July, August, September, October, November, December))

AS MonthNamePivot -
Note:
Line 2: Add SUM( ) around SalesAmount
Line 3: Add: FROM Products.Sales GROUP BY Year, Month
Line 5: Add: PIVOT SUM(Amount)

Question 170

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 170

H. the TRY_CONVERT function

TRY_CONVERT returns a value cast to the specified data type if the cast succeeds; otherwise, returns null.

Question 171

Answer 171

D. SELECT c.custid FROM Sales.Customers c LEFT OUTER JOIN Sales.Order o ON c.custid = o.custid WHERE orderid IS NULL

Inner joins return rows only when there is at least one row from both tables that matches the join condition. Inner joins eliminate the rows that do not match with a row from the other table. Outer joins, however, return all rows from at least one of the tables or views mentioned in the FROM clause, as long as those rows meet any WHERE or HAVING search conditions. All rows are retrieved from the left table referenced with a left outer join, and all rows from the right table referenced in a right outer join. All rows from both tables are returned in a full outer join.

Question 172

Answer 172

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)

Answer 173

Use two CTE expressions, one for salesYear and one for SalesQuarter, and combine them with a SELECT statement.
Note: A common table expression (CTE) can be thought of as a temporary result set that is defined within the execution scope of a single SELECT, INSERT,
UPDATE, DELETE, or CREATE VIEW statement. A CTE is similar to a derived table in that it is not stored as an object and lasts only for the duration of the query.

Answer 174

Box 1: COALESCE -
COALESCE evaluates the arguments in order and returns the current value of the first expression that initially does not evaluate to NULL.
If MiddleName is NULL, FirstName must be displayed. If both FirstName and MiddleName have null values, the world Unknown must be displayed.
The following example shows how COALESCE selects the data from the first column that has a nonnull value.
SELECT Name, Class, Color, ProductNumber,
COALESCE(Class, Color, ProductNumber) AS FirstNotNull
FROM Production.Product;
Not NULLIF: NULLIF returns the first expression if the two expressions are not equal. If the expressions are equal, NULLIF returns a null value of the type of the first expression.

Box 2: COALESCE -
If RegionCodeis NULL, the word Unknown must be displayed.
References:
https://docs.microsoft.com/en-us/sql/t-sql/language-elements/coalesce-transact-sql

Answer 175

Box 1: common table expression (CTE)
A common tableexpression (CTE) can be thought of as a temporary result set that is defined within the execution scope of a single SELECT, INSERT, UPDATE,
DELETE, or CREATE VIEW statement. A CTE is similar to a derived table in that it is not stored as an object and lasts only for the duration of the query. Unlike a derived table, a CTE can be self-referencing and can be referenced multiple times in the same query.
A CTE can be used to:
✑ Create a recursive query. For more information, see Recursive Queries Using CommonTable Expressions.
✑ Substitute for a view when the general use of a view is not required; that is, you do not have to store the definition in metadata.
✑ Enable grouping by a column that is derived from a scalar subselect, or a function that is either not deterministic or has external access.
✑ Reference the resulting table multiple times in the same statement.
From Scenario: Report1: This report joins data from SalesSummary with the Employee table and other tables. You plan to create an object to support Report1.
The object has the following requirements:
✑ be joinable with the SELECT statement that supplies data for the report can be used multiple times with the SELECT statement for the report

✑ be usable only with the SELECT statement for the report
✑ not be savedas a permanent object

Box 2: view -
From scenario: Report2: This report joins data from SalesSummary with the Employee table and other tables.
You plan to create an object to support Report1. The object has the following requirements:
✑ be joinable with theSELECT statement that supplies data for the report
✑ can be used multiple times for this report and other reports
✑ accept parameters
✑ be saved as a permanent object

Answer 176

From scenario: Sales Manager report: This report lists each sales manager and the total sales amount for all employees that report to the sales manager.
Box 1:..WHERE Title=’Sales representative’
The valid values for the Title column are Sales Representative manager, and CEO.
First we define the CTE expression.
Note: A common table expression (CTE) can be thought of as a temporary result set that is defined within the execution scope of a single SELECT, INSERT,
UPDATE, DELETE, or CREATE VIEW statement. A CTE is similar to a derived table in that it is not stored as an object and lasts only for the duration of the query.
Unlike a derived table, a CTE can be self-referencing and can be referenced multiple times in the same query.
Box 2:
Use the CTE expression one time.

Box 3: UNION -
Box 4:
Use the CTE expression a second time.
References:
https://technet.microsoft.com/en-us/library/ms190766(v=sql.105).aspx

Question 177

Answer 177

Question 178

Answer 178

Correct Answer: B

Question 179

Answer 179

Correct Answer: B
As the result of the function will be used in an indexed view we should use schemabinding.

Question 180

Answer 180

Correct Answer: G
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.
Consider a join of the Product table and the SalesOrderDetail table on their ProductID columns. The results show only the Products that have sales orders on them. The ISO FULL OUTER JOIN operator indicates that all rows from both tables are to be included in the results, regardless of whether there is matching data in the tables.
You can include a WHERE clause with a full outer join to return only the rows where there is no matching data between the tables. The following query returns only those products that have no matching sales orders, as well as those sales orders that are not matched to a product.
USE AdventureWorks2008R2;

GO -
– The OUTER keyword following the FULL keyword is optional.

SELECT p.Name, sod.SalesOrderID -

FROM Production.Product p -
FULL OUTER JOIN Sales.SalesOrderDetail sod

ON p.ProductID = sod.ProductID -

WHERE p.ProductID IS NULL -

OR sod.ProductID IS NULL -

ORDER BY p.Name -

Question 181

Answer 181

E.

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.

Question 182

Answer 182

A.

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.

Question 183

Answer 183

B. No

Products with a price of $0.00 would also be increased.

Question 184

Answer 184

B. No

Products with a price of $0.00 would also be increased.

Question 185

Answer 185

B. No

Products with a price between $0.00 and $100 will be increased, while products with a price of $0.00 would not be increased.

Question 186

Answer 186

A. Yes

ISNULL ( check_expression , replacement_value )
Arguments:
check_expression
Is the expression to be checked for NULL. check_expression can be of any type. replacement_value
Is the expression to be returned if check_expression is NULL. replacement_value must be of a type that is implicitly convertible to the type of check_expresssion.

Question 187

Answer 187

A. Yes

COALESCE evaluates the arguments in order and returns the current value of the first expression that initially does not evaluate to NULL.

Question 188

Answer 188

B. No

The NULL value in the UnitsOnOrder field would cause a runtime error.

Question 189

Answer 189

SELECT DISTINCT ServerID, Loglessage FROM Errors AS el

WHERE LogMessage IN

SELECT TOP 1 e2.LogMessage FROM Errors AS e2

WHERE e2.LogMessage = el.LogMessage AND e2.ServerID < el.ServerID

ORDER BY e2.Occurrences

)

Question 190

Answer 190

Correct Answer: B
We need SELECT TOP 1 @areacode =.. to ensure that only one value is returned.

Question 191

Answer 191

C. 2,500

Question 192

Answer 192

AS OF: Returns a table with a rows containing the values that were actual (current) at the specified point in time in the past.
CONTAINED IN: If you search for non-current row versions only, we recommend you to use CONTAINED IN as it works only with the history table and will yield the best query performance.
Incorrect Answers:
Not ALL: Returns the union of rows that belong to the current and the history table.

Question 193

You are building a stored procedure that will update data in a table named Table1 by using a complex query as the data source.
You need to ensure that the SELECT statement in the stored procedure meets the following requirements:
✑ Data being processed must be usable in several statements in the stored procedure.
✑ Data being processed must contain statistics.
What should you do?

A. Update Table1 by using a common table expression (CTE).

B. Insert the data into a temporary table, and then update Table1 from the temporary table.

C. Place the SELECT statement in a derived table, and then update Table1 by using a JOIN to the derived table.

D. Insert the data into a table variable, and then update Table1 from the table variable.

Answer 193

B. Insert the data into a temporary table, and then update Table1 from the temporary table.

Temp Tables…
Are real materialized tables that exist in tempdb
Have dedicated stats generated by the engine

Can be indexed -

Can have constraints -
Persist for the life of the current CONNECTION
Can be referenced by other queries or subproce
Incorrect Answers:
A: CTEs do not have dedicated stats. They rely on stats on the underlying objects
C: Unlike a derived table, a CTE can be self-referencing and can be referenced multiple times in the same query.

Question 194

Answer 194

Correct Answer: A
COALESCE evaluates the arguments in order and returns the current value of the first expression that initially does not evaluate to NULL.

Question 195

Answer 195

B. filtered nonclustered with a getdate() predicate in the WHERE statement clause
A filtered index is an optimized nonclustered index especially suited to cover queries that select from a well-defined subset of data. It uses a filter predicate to index a portion of rows in the table. A well-designed filtered index can improve query performance as well as reduce index maintenance and storage costs compared with full-table indexes.
Creating a filtered index can reduce disk storage for nonclustered indexes when a full-table index is not necessary.

Question 196

Answer 196

E. GROUP BY CUBE

Example of GROUP BY CUBE result set:
In the following example, the CUBE operator returns a result set that has one grouping for all possible combinations of columns in the CUBE list and a grand total grouping.

Question 197

Answer 197

C. GROUP BY ROLLUP

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.

Question 198

Answer 198

F. CROSS JOIN
A cross join that does not have a WHERE clause produces the Cartesian product of the tables involved in the join. The size of a Cartesian product result set is the number of rows in the first table multiplied by the number of rows in the second table.

Question 199

You create a table by running the following Transact-SQL statement:

CREATE TABLE Customers

CustomerID int NOT NULL PRIMARY KEY CLUSTERED,

FirstName nvarchar (100) NOT NULL,

LastName nvarchar (100) NOT NULL,

TaxIdNumber varchar(20) NOT NULL,

Address nvarchar(1024) NOT NULL,

AnnualRevenue decimal (19,2) NOT NULL,

DateCreated datetime (2) NO NULL,

ValidFrom datetime2(2) GENERATED ALWAYS AS ROW START NOT NULL,

Validto datetime2 (2) GENERATED ALWAYS AS ROW END NOT NULL,

PERIOD FOR SYSTEM TIME (ValidFrom, ValidTo)

WITH (SYSTEM VERSIONING = ON (HISTORY TABLE = CustomerHistory))

You need to view all customer data.

Which Transact-SQL statement should you run?

A. SELECT FirstName, LastName, SUM(AnnualRevenue)

FROM Customers

GROUP BY GROUPING SETS (FirstName, LastName, AnnualRevenue), ())

ORDER BY FirstName, LastName, AnnualRevenue

B. SELECT CustomerID, FirstName, LastName, TaxIdNumber, Address,

AnnualRevenue, DateCreated, ValidFrom, ValidTo

FROM Customers

FOR SYSTE TIME ALL ORDER BY ValidFrom

C. SELECT c.CustomerId, c.FirstName, c.LastName, c.Address, c.Validfrom,

c.ValidTo

FROM Customers AS c

ORDER BY c.CustomerID

FOR JSON AUTO, ROOT ( ‘Customers’)

D. SELECT FROM (SELECT CustomerID, FirstName, LastName, Address,

AnnualRevenue, DateCreated

FROM Customers) AS Customers PIVOT(AVG (AnnualRevenue)

FOR DateCreated IN([2014])) AS PivorCustomers

ORDER BY LastName, FirstName

E. SELECT CustomerID, AVG(AnnualRevenue)

AS AverageAnnualRevenue, FirstName, LastName, Address, DateCreated

FROM Customers WHERE Year (DateCreated) >= 2014

Group BY CustomerID, FirstName, LastName, Address, DateCreated

F. SELECT c.CustomerId, c.FirstName, c.LastName, c.Address,

c.Validfrom, c.ValidTo

FROM Customers AS C ORDER BY c.CustomerID

FOR XML PATH (‘CustomerData’), root (‘Customers’)

G. SELECT CustomerID, FirstName, LastName, TaxIdNumber, Address,

ValidFrom, ValidTo

FROM Customers FOR SYSTEM TIME

BETWEEN 2014-01-01 00:00:00.0000000’ AND ‘2015-01-01 00:00:00.0000000’

H. SELECT CustomerID, FirstName, LastName, TaxIdNumber, Address,

ValidFrom, ValidTo

FROM Customers

WHERE DateCreated

BETWEEN ‘20140101’ and 20141231’

H.

Answer 199

Correct Answer: B
The FOR SYSTEM_TIME ALL clause returns all the row versions from both the Temporal and History table.

Question 200

Answer 200

Correct Answer: Please see explanation
1 SELECT Rawcount
2 from (select cityid,questioned,rawcount) AS t1
3 unpivot
4 (rawcount for questioned in (QuestionID)) AS t2
5 JOIN t2
6. ON t1.CityName = t2.cityName
UNPIVOT must be used to rotate columns of the Rawsurvey table into column values.