You run the following Transact-SQL statement:

You need to ensure that you can insert data into the table.
What are the characteristics of the data? To answer, select the appropriate options in the answer area.

Correct Answer:

Explanation

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.
Box 2: 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.
References: https://msdn.microsoft.com/en-us/library/ms174979.aspx

Comment: *

Name: *

Email: *

Verification: *

Note: This question is part of a series of questions that use the same scenario. For your convenience, the scenario is repeated in each question. Each question presents a different goal and answer choices, but the text of the scenario is exactly the same in each question in this series.
You query a database that includes two tables: Project and Task. The Project table includes the following columns:


Task level is defined using the following rules:

You need to determine the task level for each task in the hierarchy.
Which five Transact-SQL segments should you use to develop the solution? To answer, move the appropriate Transact-SQL segments from the list of Transact-SQL segments to the answer area and arrange them in the correct order.

Correct Answer:

Explanation

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: 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.
References:
https://msdn.microsoft.com/en-us/library/ms180026.aspx

Comment: *

Name: *

Email: *

Verification: *

You have two tables as shown in the following image:

You need to analyze the following query. (Line numbers are included for reference only.)

Use the drop-down menus to select the answer choice that completes each statement based on the information presented in the graphic.
NOTE: Each correct selection is worth one point.

Correct Answer:

Explanation

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.
References:
https://docs.microsoft.com/en-us/sql/t-sql/data-types/data-type-conversion-database-engine#implicit-and-explicit

Comment: *

Name: *

Email: *

Verification: *

Note: This question is part of a series of questions that use the same or similar answer choices. An answer choice may be correct for more than one question in the series. Each question is independent of the other questions in this series. Information and details provided in a question apply only to that question.
You have a database that contains several connected tables. The tables contain sales data for customers in the United States only.
You have the following partial query for the database. (Line numbers are included for reference only.)

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

Which statement clause should you add at line 3?

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

Correct Answer: C

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

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

Comment: *

Name: *

Email: *

Verification: *

You have a database that contains the following tables:

You need to write a query that returns a list of all customers who have not placed orders.
Which Transact-SQL statement should you run?

• A.SELECT c.custidFROM Sales.Customers c INNER JOIN Sales.Order oON c.custid = o.custid
• B.SELECT custid FROM Sales.CustomersINTERSECTSELECT custid FROM
  Sales.Orders
• C.SELECT c.custidFROM Sales.Customers c LEFT OUTER Sales.Order oON c.custid = o.custid
• D.SELECT c.custidFROM Sales.Customers c LEFT OUTER JOIN Sales.Order o ON c.custid = o.custidWHERE orderid IS NULL

Comment: *

Name: *

Email: *

Verification: *