You administer a Microsoft SQL Server 2012 database that includes a table named Products. The Products table has columns named Productld, ProductName, and
CreatedDateTime.
The table contains a unique constraint on the combination of ProductName and CreatedDateTime.
You need to modify the Products table to meet the following requirements:
Remove all duplicates of the Products table based on the ProductName column.
Retain only the newest Products row.
Which Transact-SQL query should you use?
A.
WITH CTEDupRecords
AS
(
SELECT MAX(CreatedDateTime) AS CreatedDateTime, ProductName FROM Products
GROUP BY ProductName
HAVING COUNT(*) > 1
)
DELETE p
FROM Products p
JOIN CTEDupRecords cte ON
B.
ProductName = cte.ProductName
AND p.CreatedDateTime > cte.CreatedDateTime
C.
WITH CTEDupRecords
AS
(
SELECT MAX(CreatedDateTime) AS CreatedDateTime, ProductName FROM Products
GROUP BY ProductName
HAVING COUNT(*) > 1
)
DELETE p
FROM Products p
JOIN CTEDupRecords cte ON
cte.ProductName = p.ProductName
AND cte.CreatedDateTime > p.CreatedDateTime
D.
WITH CTEDupRecords
AS
(
SELECT MIN(CreatedDateTime) AS CreatedDateTime, ProductName FROM Products
GROUP BY ProductName
)
DELETE p
FROM Products p
JOIN CTEDupRecords cte ON
E.
ProductName = cte.ProductName
F.
WITH CTEDupRecords
AS
(
SELECT MAX(CreatedDateTime) AS CreatedDateTime, ProductName FROM Products
GROUP BY ProductName
HAVING COUNT(*) > 1
)
DELETE p
FROM Products p
JOIN CTEDupRecords cte ON
G.
ProductName = cte.ProductName
The correct answer is C
Passed the 70-761 exam last weekend! Scored 8xx!!
I had 48 questions in total, many scenario questions (YES/NO questions) (about Transact-SQL statement).
Pay attention to these topics:
— Writing Select Queries
— Grouping/Aggregating Data
— Common Table Expressions
— Window Functions
— Pivoting and Grouping
— Error Handling
— Transaction and Isolation Levels
— Creating Tables and Views
— XML Data
— JSON (JavaScript Object Notation) Data
— Query Optimization
……etc.
Before taking the actual 70-761 test, please learning those topics deeply and you will pass the test easily!!!
P.S.
Questions on this site MAY not enough for passing, SOME IMPORTANT QUESTIONS ARE NOT AVAILABLE HERE!
I do recommend you to learn the NEWEST & VALID PassLeader 70-761 dumps here:
https://drive.google.com/open?id=0B-ob6L_QjGLpaEZzRVFnOE9OenM
(IT IS THE NEWEST VERSION!!!)
Good Luck!!!
More, for the 70-461 test and 70-761 test have many same topics, you can also refer to the newest 70-461 dumps here for free FYI:
https://drive.google.com/open?id=0B-ob6L_QjGLpfnJldlZxTklTaHM0akpJUzhja2pETHJOS0owMzd4eVk1UTVNQUpvdlVxVWM
Regards!!!
New 70-761 Exam Questions Updated Recently (13/Sep/2017):
NEW 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:
Image URL: ……
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:
Image URL: ……
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.
NEW 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.
NEW QUESTION 79
You need to create a table named MiscellaneousPayment that meets the following requirements:
Image URL: ……
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
NEW QUESTION 80
You have a database that includes the tables shown in the exhibit:
Image URL: ……
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?
Image URL: ……
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.
NEW 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:
Image URL: ……
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.
NEW 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:
Image URL: ……
Details for the Application.Cities table are shown in the following table:
Image URL: ……
Details for the Sales.CustomerCategories table are shown in the following table:
Image URL: ……
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?
Image URL: ……
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.
NEW QUESTION 83
……
P.S. These New 70-761 Exam Questions Were Just Updated From The Real 70-761 Exam, You Can Get The Newest 70-761 Dumps In PDF And VCE From — https://www.passleader.com/70-761.html (98q VCE and PDF)
Good Luck!
BTW, part of new 98Q 70-761 dumps are available here:
https://drive.google.com/open?id=0B-ob6L_QjGLpaEZzRVFnOE9OenM
Best Regards!