Home » Microsoft » 70-461 v.2 » You are maintaining a SQL Server database that uses the default settings.
You are maintaining a SQL Server database that uses the default settings. The database contains a table that is defined by the following Transact-SQL statement
You must write a query that returns the AddressLine1, AddressLine2, and Region fields separated by carriage returns. You must return an empty string for any null values
A. Option A
B. Option B
C. Option C
D. Option D
Correct Answer: A
Explanation/Reference:
Explanation:
Char(13) is a carriage return.
Use the IIF construct to return an empty string for NULL values of the Adressline2 column.
IIF returns one of two values, depending on whether theBoolean expression evaluates to true or false in SQL Server
References: https://msdn.microsoft.com/en-us/library/hh213574.aspx
I agree with you up to a point, Alex.
As we are selecting address lines and region, AddressLine2 is the only 1 that can be NULL (see table) – so A will also give you the correct result.
This question was obviously adapted from another question which says “Which two queries can you use?”
By having left off those last 6 words, it creates confusion, as both A and D apply, but we are being asked to provide 1 option only.
The answer is D
check out the SQL doc https://docs.microsoft.com/en-us/sql/t-sql/functions/concat-transact-sql?view=sql-server-2017
CONCAT implicitly converts null values to empty strings.
The answer A is wrong because only check Addressline2, question clearly asks for any null value