The latest Microsoft MCSA 70-761 exam dumps is in pass4itsure.com. We have many years of exam experience,
all of our exam databases are updated throughout the year, and pass4itsure Microsoft exam experts will modify the content
at any time based on customer feedback. 100% Guarantee. Easily pass the Microsoft MCSA 70-761 Exam, please select
Pass4itsure 70-761 PDF or 70-761 VCE. Microsoft MCSA official Testing center has released the following
questions and answers: https://www.pass4itsure.com/70-761.html
[PDF] Free Microsoft MCSA 70-761 dumps download from Google Drive:
https://drive.google.com/open?id=1orVAH6wsvrPRrqt-SbLRmOXZzRL1_Y-2
[PDF] Free Full Microsoft dumps download from Google Drive:
https://drive.google.com/open?id=1VBDzuasBbmByXUKyUaZejR3hFzTke722
Exam 70-761: Querying Data with Transact-SQL – Microsoft:
https://www.microsoft.com/en-us/learning/exam-70-761.aspx
Pass4itsure offers the latest Microsoft MCSA 70-761 practice test free of charge (17Q&As)
QUESTION 1
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
Correct Answer: A
Explanation
Explanation/Reference:
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 2
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:
A. Option A
B. Option B
C. Option C
D. Option D.
E. Option E.
F. Option F.
G. Option G.
H. Option H.Correct Answer: G
Explanation
Explanation/Reference:
The following query searches for row versions for Employee row with EmployeeID = 1000 that were active at least for a portion of period between 1st January of 2014 and 1st January 2015 (including the upper boundary):
SELECT * FROM Employee
FOR SYSTEM_TIME
BETWEEN ‘2014-01-01 00:00:00.0000000’ AND ‘2015-01-01 00:00:00.0000000’ WHERE EmployeeID = 1000 ORDER BY ValidFrom;
References: https://msdn.microsoft.com/en-us/library/dn935015.aspx
QUESTION 3
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 create a table named Customer by running the following Transact-SQL statement:
Does the solution meet the goal?
A. Yes
B. No
Correct Answer: A
Explanation
Explanation/Reference:
With the INSERT INTO..VALUES statement we can insert both values with just one statement. This ensures that both records or neither is inserted.
References: https://msdn.microsoft.com/en-us/library/ms174335.aspx
QUESTION 4
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
Correct Answer: B
Explanation
Explanation/Reference:
We should use a WHERE clause, not a HAVING clause. The HAVING clause would refer to aggregate data.
QUESTION 5
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 table named AuditTrail that tracks modifications to data in other tables. The AuditTrail table is updated by many processes. Data input into AuditTrail may contain improperly formatted date time values. You implement a process
that retrieves data from the various columns in AuditTrail, but sometimes the process throws an error when it is unable to convert the data into valid date time values.
You need to convert the data into a valid date time value using the en-US format culture code. If the conversion fails, a null value must be returned in the column output. The conversion process must not throw 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
Correct Answer: H
Explanation
Explanation/Reference:
A TRY_CONVERT function returns a value cast to the specified data type if the cast succeeds; otherwise, returns null.
References: https://msdn.microsoft.com/en-us/library/hh230993.aspx
QUESTION 6
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 is denormalized. Users make frequent changes to data in a primary table.
You need to ensure that users cannot change the tables directly, and that changes made to the primary table also update any related tables.
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
Correct Answer: B
Explanation
Explanation/Reference:
Using an Indexed View would allow you to keep your base data in properly normalized tables and maintain data-integrity while giving you the denormalized “view” of that data.
References: http://stackoverflow.com/questions/4789091/updating-redundant- denormalized-data-automatically-in-sql-server
QUESTION 7
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
Correct Answer: A
Explanation
Explanation/Reference:
The MAX(orderdate) in the SELECT statement makes sure we return only the most recent order.
A WHERE o.empiD =4 clause is correctly used.
GROUP BY is also required.
QUESTION 8
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:
You plan to run the following query to update tasks that are not yet started:
A. Option A
B. Option B
C. Option C
D. Option D
Correct Answer: B
Explanation
Explanation/Reference:
The WHERE clause of the third line should be WHERE ProjectID IS NULL, as we want to count the tasks that are not associated with a project.
QUESTION 9
You have a database named MyDb. You run the following Transact-SQL statements:
A value of 1 in the IsActive 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. SELECT R.RoleName, COUNT(*) AS ActiveUserCount FROM tblRoles RCROSS JOIN (SELECT UserId, RoleId FROM tblUsers WHERE IsActive = 1) UWHERE U.RoleId =
B. RoleIdGROUP BY R.RoleId, R.RoleName
C. SELECT R.RoleName, COUNT(*) AS ActiveUserCount FROM tblRoles RLEFT JOIN (SELECT UserId, RoleId FROM tblUsers WHERE IsActive = 1) UON
D. RoleId =
E. RoleIdGROUP BY R.RoleId, R.RoleName
F. SELECT R.RoleName, U.ActiveUserCount FROM tblRoles R CROSS JOIN(SELECT RoleId, COUNT(*) AS ActiveUserCountFROM tblUsers WHERE IsActive = 1 GROUP BY
G. RoleId) U
H. SELECT R.RoleName, ISNULL (U.ActiveUserCount,0) AS ActiveUserCountFROM tblRoles R LEFT JOIN (SELECT RoleId, COUNT(*) AS ActiveUserCountFROM tblUsers WHERE IsActive = 1 GROUP BY R.RoleId) U
Correct Answer: B
Explanation
QUESTION 10
You have a database named MyDb. You run the following Transact-SQL statements:
A value of 1 in the IsActive 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
Correct Answer: C
Explanation
QUESTION 11
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 are developing a report that displays customer information. The report must contain a grand total column.
You need to write a query that returns the data for the report.
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
Correct Answer: E
Explanation
Explanation/Reference:
Calculate aggregate column through AVG function and GROUP BY clause.
QUESTION 12
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:
A. Option A
B. Option B
C. Option C
D. Option D
E. Option E
F. Option F
G. Option G
H. Option G
Correct Answer: B
Explanation
Explanation/Reference:
The FOR SYSTEM_TIME ALL clause returns all the row versions from both the Temporal and History table.
Note: A system-versioned temporal table defined through is a new type of user table in SQL Server 2016, here defined on the last line WITH (SYSTEM_VERSIONING = ON…, is designed to keep a full history of data changes and allow easy
point in time analysis. To query temporal data, the SELECT statement FROM<table> clause has a new clause FOR SYSTEM_TIME with five temporal-specific sub-clauses to query data across the current and history tables.
References: https://msdn.microsoft.com/en-us/library/dn935015.aspx
QUESTION 13
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:
Does the solution meet the goal?A. Yes
B. No
Correct Answer: B
Explanation
Explanation/Reference:
We need to list all provinces that have at least two large cities. There is no reference to this in the code.
QUESTION 14
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 create a table named Customer by running the following Transact-SQL statement:
Does the solution meet the goal?
A. Yes
B. No
Correct Answer: B
Explanation
Explanation/Reference:
As there are two separate INSERT INTO statements we cannot ensure that both or neither records is inserted.
QUESTION 15
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
Records that contain null values for CustomerCode can be uniquely identified by CustomerName.
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
Correct Answer: E
Explanation
Explanation/Reference:
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 16
You have a database that contains the following tables:
Customer
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. Option A
B. Option B
C. Option C
D. Option D
Correct Answer: D
Explanation
Explanation/Reference:
The OUTPUT Clause returns information from, or expressions based on, each row affected by an INSERT, UPDATE, DELETE, or MERGE statement. These results can be returned to the processing application for use in such things as
confirmation messages, archiving, and other such application requirements. The results can also be inserted into a table or table variable. Additionally, you can capture the results of an OUTPUT clause in a nested INSERT, UPDATE,
DELETE, or MERGE statement, and insert those results into a target table or view.
Note: If the column modified by the .RITE clause is referenced in an OUTPUT clause, the complete value of the column, either the before image in deleted.column_name or the after image in inserted.column_name, is returned to the specified
column in the table variable.
QUESTION 17
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 tracks orders and deliveries for customers in North America. The database contains the following tables:
Sales.Customers
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:
Does the solution meet the goal?
A. Yes
B. No
Correct Answer: A
Explanation
Explanation/Reference:
As the result of the function will be used in an indexed view we should use schemabinding. References: https://sqlstudies.com/2014/08/06/schemabinding-what- why
Conclusion:
Welcome here, above we provide free Microsoft MCSA 70-761 exam dumps and 70-761 PDF. I believe it must be very helpful to you,
free content can only help you to open the first step of learning.
If you want to pass Microsoft MCSA 70-761 for your first exam, select: Pass4itsure 70-761 PDF + 70-761 VCE mode.
100% effective, easy to pass the 70-761 exam: https://www.pass4itsure.com/70-761.html
[PDF] Free Microsoft MCSA 70-761 dumps download from Google Drive:
https://drive.google.com/open?id=1orVAH6wsvrPRrqt-SbLRmOXZzRL1_Y-2
[PDF] Free Full Microsoft MCSA dumps download from Google Drive:
https://drive.google.com/open?id=1VBDzuasBbmByXUKyUaZejR3hFzTke722
Pass4itsure Promo Code 15% Off
related: http://www.downloadzpdf.com/latest-comptia-cv0-001-dump-pdf/
