You write the following SELECT statement to get the last order date for a particular customer.
You need to create the user-defined function to return the last order date for the specified customer. Which five Transact-SQL statements should you use? (To
answer, move the appropriate SQL statements from the list of statements to the answer area and arrange them in the correct order.)
Select and Place:
Answer:
Explanation:
Note: * First function header * Then declare that the function returns a datetime
* Thirdly begin the function body.
* Fourthly declare the return variable
* At last include the code that retrieves the required date.
Look at 70-461 (v.4) Question 115
I was recommended by one of my friend, he used the 70-761 dumps and said they are helpful. He was right! I passed my Microsoft 70-761 exam yesterday. I was lucky, all my questions in the exams were from the dumps below:
QUESTION 41
You have a database that stored information about servers and application errors.
The database contains the following tables.
Servers
Errors
You need to return all error log messages and the server where the error occurs most often.
Which Transact-SQL statement should you run?
A. Option A
B. Option B
C. Option C
D. Option D
Answer: C
QUESTION 42
Drag and Drop Question
You have a database that stored information about servers and application errors.
The database contains the following tables.
Servers
Errors
You are building a webpage that shows the three most common errors for each server.
You need to return the data for the webpage.
How should you complete the Transact-SQL statement? To answer, drag the appropriate Transact-SQL segments to the correct location. 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.
NOTE: Each correct selection is worth one point.
Answer:
QUESTION 43
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.
Answer: UNPIVOT
QUESTION 44
You have a database named MyDb.
You run the following Transact-SQL statements:
A value of 1 in the Is Active column indicates that a user is active.
You need to create a count for active users in each role. If a role has no active users. you must display a zero as the active users count.
Which Transact-SQL statement should you run?
A. Option A
B. Option B
C. Option C
D. Option D
Answer: C
QUESTION 45
Drag and Drop Question
You create three tables by running the following Transact-SQL statements:
For reporting purposes, you need to find the active user count for each role, and the total active user count. The result must be ordered by active user count of each role.
You must use common table expressions (CTEs).
Which four 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.
Answer:
QUESTION 46
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
Answer: D
QUESTION 47
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 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. Option A
B. Option B
C. Option C
D. Option D
E. Option E
Answer: A
QUESTION 48
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 Customer Name. You need to display a Cartesian product, combining both tables.
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
Answer: G
QUESTION 49
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 Customer Name. You need to create a list of all unique customers that appear in either 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
Answer: E
Explanation:
UNION combines the results of two or more queries into a single result set that includes all the rows that belong to all queries in the union. The UNION operation is different from using joins that combine columns from two tables.
QUESTION 50
……
The full version (82q) is on my blog: http://www.itexams.org/category/microsoft/70-761-dumps
And I will always update the official new released 70-761 questions on this blog, follow me!
Free 70-761 Dumps Files For Free Downloading By 70-761 Exam Candidates.v.2017-09-13.98q
QUESTION 75
Note: This question is part of a series of questions that present the same scenario. Each question in the series contains a unique solution that might meet the stated goals. Some question sets might have more than one correct solution, while others might not have a correct solution.
After you answer a question in this section. You will NOT be able to return to it. As a result, these questions will not appear in the review screen.
A database has two tables as shown in the following database diagram:
You need to list all provinces that have at least two large cities.
A large city is defined as having a population of at least one million residents.
The query must return the following columns:
Solution: You run the following Transact-SQL statement:
Does the solution meet the goal?
A. Yes
B. No
Answer: A
Explanation:
The requirement to list all provinces that have at least two large cities is meet by the WHERE CitySummary.LargeCityCount >=2 clause.
CROSS APPLY will work fine here.
Note:
The APPLY operator allows you to invoke a table-valued function for each row returned by an outer table expression of a query. The table-valued function acts as the right input and the outer table expression acts as the left input. The right input is evaluated for each row from the left input and the rows produced are combined for the final output. The list of columns produced by the APPLY operator is the set of columns in the left input followed by the list of columns returned by the right input.
There are two forms of APPLY: CROSS APPLY and OUTER APPLY. CROSS APPLY returns only rows from the outer table that produce a result set from the table-valued function. OUTER APPLY returns both rows that produce a result set, and rows that do not, with NULL values in the columns produced by the table-valued function.
References: https://technet.microsoft.com/en-us/library/ms175156(v=sql.105).aspx
QUESTION 76
You create a table named Products.Sales by running the following Transact-SQL statement:
CREATE TABLE Products.Sales (
SalesId int IDENTIFY(1,1) PRIMARY KEY,
SalesDate DateTime NOT NULL,
SalesAmount decimal(18,2) NULL
)
You add the following data to the table.
You are developing a report to display monthly sales data.
You need to create a Transact-SQL query to meet the following requirements:
– Retrieve a column for the year followed by a column for each month from January through December.
– Include the total sales amount for each month.
– Aggregate columns by year, month, and then amount.
Construct the query using the following guidelines:
– Use the MONTH keyword as the interval when using the DATANAME function.
– Do not modify the provided IN clause.
– Do not surround object names with square brackets.
– Do not use implicit joins.
– Do not use the DATEPART function.
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.
1. SELECT * FROM
2. (SELECT YEAR(SalesData)) AS Year, DATENAME (MONTH, SalesDate) AS Month,
SalesAmount AS Amount
3.
4. ) AS MonthlySalesData
5.
6. FOR Month IN (January, February, March, April, May, June, July, August, September,
October, November, December))
AS MonthNamePivot
A. Pending
Answer: A
QUESTION 77
Note: This question is part of a series of questions that present the same scenario. Each question in the series contains a unique solution that might meet the stated goals. Some question sets might have more than one correct solution, while others might not have a correct solution.
After you answer a question in this section. You will NOT be able to return to it. As a result, these questions will not appear in the review screen.
You have a database that includes the tables shown in the exhibit (Click the Exhibit button.)
You need to create a Transact-SQL query that returns the following information:
– the customer number
– the customer contact name
– the date the order was placed, with a name of DateofOrder
– a column named Salesperson, formatted with the employee first name, a space, and the employee last name
– orders for customers where the employee identifier equals 4
The output must be sorted by order date, with the newest orders first.
The solution must return only the most recent order for each customer.
Solution: You run the following Transact-SQL statement:
Does the solution meet the goal?
A. Yes
B. No
Answer: B
Explanation:
We should use a WHERE clause, not a HAVING clause. The HAVING clause would refer to aggregate data.
QUESTION 78
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.
Multiple processes use the data from a table named Sales and place it in other databases across the organization. Some of the processes are not completely aware of the data types in the Sales table. This leads to data type conversion errors.
You need to implement a method that returns a NULL value id data conversion fails instead of throwing an error.
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: H
Explanation:
TRY_CONVERT returns a value cast to the specified data type if the cast succeeds; otherwise, returns null.
References: https://docs.microsoft.com/en-us/sql/t-sql/functions/try-convert-transact-sql
QUESTION 79
You need to create a table named MiscellaneousPayment that meets the following requirements:
Which Transact-SQL statement should you run?
A. CREATE TABLE MiscellaneousPayment (Id uniqueidentifier DEFAULT NEWSEQUENTIALID() PRIMARY KEY,Reason varchar(500),Amount money)
B. CREATE TABLE MiscellaneousPayment (Id int identify(1,1)PRIMARY KEY,Reason nvarchar(500),Amount numeric(19,4))
C. CREATE TABLE MiscellaneousPayment (Id uniqueidentifier DEFAULT NEWSEQUENTIALID() PRIMARY KEY,Reason varchar(500),Amount decimal(19,4))
D. CREATE TABLE MiscellaneousPayment (Id uniqueidentifier DEFAULT NEWID() PRIMARY KEY,Reason nvarchar(500),Amount decimal(19,4))
Answer: D
QUESTION 80
You have a database that includes the tables shown in the exhibit. (Click the exhibit button.)
You need to create a list of all customers, the order ID for the last order that the customer placed, and the date that the order was placed. For customers who have not placed orders, you must substitute a zero for the order ID and 01/01/1990 for the date.
Which Transact-SQL statement should you run?
A. Option A
B. Option B
C. Option C
D. Option D
Answer: A
Explanation:
ISNULL Syntax: ISNULL ( check_expression , replacement_value ) author:”Luxemburg, Rosa”
The ISNULL function replaces NULL with the specified replacement value. The value of check_expression is returned if it is not NULL; otherwise, replacement_value is returned after it is implicitly converted to the type of check_expression.
References: https://msdn.microsoft.com/en-us/library/ms184325.aspx
QUESTION 81
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 only loan accounts.
Which Transact-SQL statement should you run?
A. SELECT COUNT(*)FROM (SELECT AcctNoFROM tblDepositAcctINTERSECTSELECT AcctNoFROM tblLoanAcct) R
B. SELECT COUNT(*)FROM (SELECT CustNoFROM tblDepositAcctUNIONSELECT CustNoFROM tblLoanAcct) R
C. SELECT COUNT(*)FROM (SELECT CustNoFROM tblDepositAcctUNION ALLSELECT CustNoFROM tblLoanAcct) R
D. SELECT COUNT (DISTINCT
D.CustNo)FROM tblDepositAcct D, tblLoanAcct LWHERE
D.CustNo = L.CustNo
E. SELECT COUNT(DISTINCT L.CustNo)FROM tblDepositAcct DRIGHT JOIN tblLoanAcct L ON
D.CustNo = L.CustNoWHERE
D.CustNo IS NULL
F. SELECT COUNT(*)FROM (SELECT CustNoFROM tblDepositAcctEXCEPTSELECT CustNoFROM tblLoanAcct) R
G. SELECT COUNT (DISTINCT COALESCE(D.CustNo, L.CustNo))FROM tblDepositAcct DFULL JOIN tblLoanAcct L ON
D.CustNo = L.CustNoWHERE
D.CustNo IS NULL OR L.CustNo IS NULL
H. SELECT COUNT(*)FROM tblDepositAcct DFULL JOIN tblLoanAcct L ON
D.CustNo = L.CustNo
Answer: E
Explanation:
The RIGHT JOIN keyword returns all records from the right table (table2), and the matched records from the left table (table1). The result is NULL from the left side, when there is no match.
References: https://www.w3schools.com/sql/sql_join_right.asp
QUESTION 82
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 on this series.
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:
Details for the Application.Cities table are shown in the following table:
Details for the Sales.CustomerCategories table are shown in the following table:
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. Option A
B. Option B
C. Option C
D. Option D
Answer: D
Explanation:
The datetime datetype defines a date that is combined with a time of day with fractional seconds that is based on a 24-hour clock.
The DATEFROMPARTS function returns a date value for the specified year, month, and day.