Exam 70-461 topic 1 question 170 discussion

Actual exam question from Microsoft's 70-461

Question #: 170
Topic #: 1

SIMULATION -
You have a view named Person.vAdditionalContactInfo that uses data from a table named Customers. The Customers table was created by running the following
Transact-SQL statement:

You need to create a query that returns the first and last names of a customer from the view. If the CustomerID column does not have a value defined, the following message must be added to the query result: "Customer ID Does Not Exist."
Construct the query using the following quidelines:
✑ Do not use aliases for column or table names.
✑ Display the columns in the same order as they are defined in the Customers table.
Part of the correct Transact-SQL has been provided in the answer area below. Enter the code in the answer area that resolves the problem and meets the stated goals or requirements. You can add code within code that has been provided as well as below it.

Show Suggested Answer

Suggested Answer: Please review the explanation part for this answer.
1 SELECT FirstName, LastName, CustomerID =
2 CASE
3 WHEN CustomerID IS NULL THEN "Customer ID Does Not Exist"
4 ELSE CustomerID
5 END
6 FROM Person.vAdditionalContactInfo;
To line 1 add: CustomerID =

To line 2 add: CASE -

To line 3 add: IS NULL -

To line 5 add: END -
References:
https://technet.microsoft.com/en-us/library/ms181765(v=sql.105).aspx https://docs.microsoft.com/en-us/sql/t-sql/queries/is-null-transact-sql

by lilupv at Sept. 1, 2020, 11:29 a.m.

Comments

Submit Cancel

lilupv

4 years, 9 months ago

However CustomerID was marked as not null in the table.... So, when the error will appear?

upvoted 3 times

...