How should you complete the Transact-SQL statement?

DRAG DROP
You have a database that tracks orders and deliveries for customers in North America. System versioning is enabled for all tables. The database contains the Sales.Customers, Application. Cities, and Sales. CustomerCategories tables.
Details for the Sales.Customers table are shown in the following table:

image141 - How should you complete the Transact-SQL statement?
Details for the Application.Cities table are shown in the following table:

image143 - How should you complete the Transact-SQL statement?
Details for the Sales.CustomerCategories table are shown in the following table:

image145 - How should you complete the Transact-SQL statement?
You are preparing a promotional mailing. The mailing must only be sent to customers in good standing that live in medium and large cities.
You need to write a query that returns all customers that are not on credit hold who live in cities with a population greater than 10,000.
How should you complete the Transact-SQL statement? To answer, drag the appropriate Transact-SQL segments to the correct locations. Each Transact-SQL segment may be used once, more than once, or not at all. You may need to drag the split bar between panes or scroll to view content.

image177 - How should you complete the Transact-SQL statement?

Answer:

image179 - How should you complete the Transact-SQL statement?

Does the solution meet the goal?

You have a database that tracks orders and deliveries for customers in North America.
The database contains the following tables:

image020 - 
Does the solution meet the goal?
Application. Cities

image022 - 
Does the solution meet the goal?
Sales. CustomerCatagories

image024 - 
Does the solution meet the goal?
The company’s development team is designing a customer directory application. The application must list customers by the area code of their phone number. The area code is defined as the first three characters of the phone number.
The main page of the application will be based on an indexed view that contains the area and phone number for all customers.
You need to return the area code from the PhoneNumber field.
Solution: You run the following Transact-SQL statement:

image028 - 
Does the solution meet the goal?
Does the solution meet the goal?
A. Yes
B. No

Answer: B

CORRECT TEXT

CORRECT TEXT
You create a table named Sales.Orders by running the following Transact-SQL statement:

image105 - CORRECT TEXT
You need to write a query that meets the following requirements:
– removes orders from the table that were placed before January 1, 2012
– uses the date format of YYYYMMDD
– ensures that the order has been shipped before deleting the record
Construct the query using the following guidelines:
– use one-part column names and two-part table names
– do not use functions
– do not surround object names with square brackets
– do not use variables
– do not use aliases for column names and table names

image107 - CORRECT TEXT
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.

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

Answer:
Pending

Does the solution meet the goal?

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

image002 - 
Does the solution meet the goal?
You have the following stored procedure:

image004 - 
Does the solution meet the goal?
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 a product record insert operation fails, the product information must not be permanently written to the database.
Solution: You run the following Transact-SQL statement:

image006 - 
Does the solution meet the goal?
Does the solution meet the goal?
A. Yes
B. No

Answer: B

What should you implement?

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

Does the solution meet the goal?

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

image012 - 
Does the solution meet the goal?
You must insert the following data into the Customer table:

image014 - 
Does the solution meet the goal?
You need to ensure that both records are inserted or neither record is inserted.
Solution: You run the following Transact-SQL statement:

image018 - 
Does the solution meet the goal?
Does the solution meet the goal?
A. Yes
B. No

Answer: A

Which Transact-SQL statement should you run?

You have a database that contains the following tables:
Customer

image129 - 
Which Transact-SQL statement should you run?
Customer Audit

image131 - 
Which Transact-SQL statement should you run?
Where the value of the CustomerID column equals 3, you need to update the value of the CreditLimit column to 1000 for the customer. You must ensure that the change to the record in the Customer table is recorded on the CustomerAudit table.
Which Transact-SQL statement should you run?
A.

image133 - 
Which Transact-SQL statement should you run?
B.

image135 - 
Which Transact-SQL statement should you run?
C.

image137 - 
Which Transact-SQL statement should you run?
D.

image139 - 
Which Transact-SQL statement should you run?

Answer: D

Which Transact-SQL segment should you use for each requirement?

HOTSPOT
You need to develop a Transact-SQL statement that meets the following requirements:
– The statement must return a custom error when there are problems updating a table.
– The error number must be value 50555.
– The error severity level must be 14.
– A Microsoft SQL Server alert must be triggered when the error condition occurs.
Which Transact-SQL segment should you use for each requirement? To answer, select the appropriate Transact-SQL segments in the answer area.

image064 - 
Which Transact-SQL segment should you use for each requirement?

Answer:

image066 - 
Which Transact-SQL segment should you use for each requirement?

Which Transact-SQL statement should you run?

You have a database that contains tables named Customer_CRMSystem and Customer_ HRSystem.
Both tables use the following structure:

image224 - 
Which Transact-SQL statement should you run?
The tables include the following records:
Customer_ CRMSystem

image226 - 
Which Transact-SQL statement should you run?
Customer_ HRSystem

image228 - 
Which Transact-SQL statement should you run?
Records that contain null values for CustomerCode can be uniquely identified by Customer Name.
You need to display customers who appear in both tables and have a proper CustomerCode.
Which Transact-SQL statement should you run?
A.

image230 - 
Which Transact-SQL statement should you run?
B.

image246 - 
Which Transact-SQL statement should you run?
C.

image234 - 
Which Transact-SQL statement should you run?
D.

image236 - 
Which Transact-SQL statement should you run?
E.

image248 - 
Which Transact-SQL statement should you run?

Answer: A

Which Transact-SQL statement should you use?

You have a database that tracks orders and deliveries for customers in North America. System versioning is enabled for all tables. The database contains the Sales.Customers, Application. Cities, and Sales. CustomerCategories tables.
Details for the Sales.Customers table are shown in the following table:

image141 - 
Which Transact-SQL statement should you use?
Details for the Application.Cities table are shown in the following table:

image143 - 
Which Transact-SQL statement should you use?
Details for the Sales.CustomerCategories table are shown in the following table:

image145 - 
Which Transact-SQL statement should you use?
You discover an application bug that impacts customer data for records created on or after January 1, 2014. In order to fix the data impacted by the bug, application programmers require a report that contains customer data as it existed on December 31, 2013.
You need to provide the query for the report.
Which Transact-SQL statement should you use?
A.

image161 - 
Which Transact-SQL statement should you use?
B.

image163 - 
Which Transact-SQL statement should you use?
C.

image165 - 
Which Transact-SQL statement should you use?
D.

image167 - 
Which Transact-SQL statement should you use?

Answer: D