Note: This question is part of a series of questions that use the same or similar answer choices. An answer choice may be correct for more than one question in the series. Each question is independent of the other questions in this series. Information and details provided in a question apply to that question.
You have a database for a banking system. The database has two tables named tblDepositAcct and tblLoanAcct that store deposit and loan accounts, respectively. Both tables contain the following columns:

You need to determine the total number of customers who have either deposit accounts or loan accounts, but not both types of accounts.
Which Transact-SQL statement should you run?

• 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 CustNo
  FROM tblDepositAcct
  UNION ALL
  SELECT CustNo
  FROM tblLoanAcct) R
• D.SELECT COUNT (DISTINCT D.CustNo)
  FROM tblDepositAcct D, 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

Comment: *

Name: *

Email: *

Verification: *

You have a table named Cities that has the following two columns: CityID and CityName. The CityID column uses the int data type, and CityName uses nvarchar(max).
You have a table named RawSurvey. Each row includes an identifier for a question and the number of persons that responded to that question from each of four cities. The table contains the following representative data:

A reporting table named SurveyReport has the following columns: CityID, QuestionID, and RawCount, where RawCount is the value from the RawSurvey table.
You need to write a Transact-SQL query to meet the following requirements:
- Retrieve data from the RawSurvey table in the format of the SurveyReport table.
- The CityID must contain the CityID of the city that was surveyed.
- The order of cities in all SELECT queries must match the order in the RawSurvey table.
- The order of cities in all IN statements must match the order in the RawSurvey table.
Construct the query using the following guidelines:
- Use one-part names to reference tables and columns, except where not possible.
- ALL SELECT statements must specify columns.
- Do not use column or table aliases, except those provided.
- Do not surround object names with square brackets.

Part of the correct Transact-SQL has been provided in the answer area below. Enter the code in the answer area that resolves the problem and meets the stated goals or requirements. You can add code within the code that has been provided as well as below it.

Use the Check Syntax button to verify your work.
Any syntax or spelling errors will be reported by line and character position.

Comment: *

Name: *

Email: *

Verification: *

You are creating a training management application. You run the following Transact-SQL statement:

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

Correct Answer:

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 create a table by running the following Transact-SQL statement:

You need to return normalized data for all customers that were added in the year 2014.
Which Transact-SQL statement should you run?
A:

B:

C:

D:

E:

F:

G:

H:

• A.Option A
• B.Option B
• C.Option C
• D.Option D
• E.Option E
• F.Option F
• G.Option G
• H.Option H

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 tables named Customer_CRMSystem and
Customer_HRSystem. Both tables use the following structure:

The tables include the following records:
Customer_CRMSystem

Customer_HRSystem

Records that contain null values for CustomerCode can be uniquely identified by CustomerName.
You need to display a list of customers that do not appear in the Customer_HRSystem table.
Which Transact-SQL statement should you run?

• A.Option A
• B.Option B
• C.Option C
• D.Option D
• E.Option E
• F.Option F
• G.Option G
• H.Option H

Comment: *

Name: *

Email: *

Verification: *