Get the latest Microsoft MCSA 70-761 dumps and 70-761 pdf, pass exam certification
Work hard! It’s not hard to get certified! Share the latest Microsoft MCSA 70-761 exam dump for free. online 70-761 exam practice tests. the latest 70-761 exam questions and answers, and guarantee your skills and exam experience! “Querying Data with Transact-SQL” – 70-761 exam! pass4itsure.com expert recommendation! Top pass rate!
Get the latest Microsoft MCSA 70-761 pdf
[PDF] Free Microsoft 70-761 pdf dumps download from Google Drive: https://drive.google.com/open?id=1FNM7bE-f2LTtpWysfj15-6iutFxJ7rKX
[PDF] Free Full Microsoft pdf dumps download from Google Drive: https://drive.google.com/open?id=1gdQrKIsiLyDEsZ24FxsyukNPYmpSUDDO
Valid information provided by Microsoft officials
Exam 70-761: Querying Data with Transact-SQL – Microsoft: https://www.microsoft.com/en-us/learning/exam-70-761.aspx
This exam is intended for SQL Server database administrators, system engineers, and developers with two or more years of experience who are seeking to validate their skills and knowledge in writing queries.
pass4itsure 70-761 exam Skills measured
This exam measures your ability to accomplish the technical tasks listed below.
- Manage data with Transact-SQL (40–45%)
- Query data with advanced Transact-SQL components (30–35%)
- Program databases by using Transact-SQL (25–30%)
The latest Microsoft MCSA 70-761 exam practice questions test your strength
QUESTION 1
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 stores sales and order information.
Users must be able to extract information from the tables on an ad hoc basis. They must also be able to reference the
extracted information as a single table.
You need to implement a solution that allows users to retrieve the data required, based on variables defined at the time
of the query.
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: C
User-defined functions that return a table data type can be powerful alternatives to views. These functions are referred
to as table-valued functions. A table-valued user-defined function can be used where table or view expressions are
allowed in Transact-SQL queries. While views are limited to a single SELECT statement, user-defined functions can
contain additional statements that allow more powerful logic than is possible in views.
A table-valued user-defined function can also replace stored procedures that return a single result set.
References: https://technet.microsoft.com/en-us/library/ms191165(v=sql.105).aspx
QUESTION 2
SIMULATION
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.
A. Check the answer in explanation.
Correct Answer: A
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:
You must insert the following data into the Customer table:
You need to ensure that both records are inserted or neither record is inserted. Solution: You run the following Transact-
SQL statement:
Does the solution meet the goal?
A. Yes
B. No
Correct Answer: B
Reference: https://docs.microsoft.com/it-it/sql/t-sql/statements/insert-transact-sql?view=sql-server-2017
QUESTION 4
You run the following Transact-SQL statements:
You need to create a query that returns the total number of attendees for each combination of CourseID, CourseDate,
and the following locations: Lisbon, London, and Seattle. The result set should resemble the following:
Which Transact-SQL code segment should you run?
A. SELECT * FROM CourseParticipants PIVOT(SUM(NumParticipants) FOR LocationDescription IN (Lisbon, London,
Seattle))
B. SELECT * FROM CourseParticipants PIVOT(SUM(NumParticipants) FOR LocationDescription IN (Lisbon, London,
Seattle)) as PVTTable
C. SELECT * FROM CourseParticipants UNPIVOT(SUM(NumParticipants) FOR LocationDescription IN (Lisbon,
London, Seattle)
D. SELECT * FROM CourseParticipants UNPIVOT(SUM(NumParticipants) FOR LocationDescription IN (Lisbon,
London, Seattle) AS PVTTable
Correct Answer: B
References: https://www.techonthenet.com/sql_server/pivot.php
QUESTION 5
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 contains a single table named tblVehicleRegistration. The table is defined as follows:
You run the following query:
The query output window displays the following error message: “Conversion failed when converting the varchar value
`AB012\\’ to data type int.”
You need to resolve the error.
Solution: You modify the Transact-SQL statement as follows:
Does the solution meet the goal?
A. Yes
B. No
Correct Answer: A
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 create a table by running the following Transact-SQL statement:
You need to develop a query that meets the following requirements:
Output data by using a tree-like structure.
Allow mixed content types.
Use custom metadata attributes.
Which Transact-SQL statement should you run?
A. B. C. D. E. F. G. H.
Correct Answer: F
In a FOR XML clause, you specify one of these modes: RAW, AUTO, EXPLICIT, and PATH.
The EXPLICIT mode allows more control over the shape of the XML. You can mix attributes and elements at will in
deciding the shape of the XML. It requires a specific format for the resulting rowset that is generated because of query
execution. This row set format is then mapped into XML shape. The power of EXPLICIT mode is to mix attributes and
elements at will, create wrappers and nested complex properties, create space-separated values (for example, OrderID
attribute may have a list of order ID values), and mixed contents.
The PATH mode together with the nested FOR XML query capability provides the flexibility of the EXPLICIT mode in a
simpler manner.
References: https://msdn.microsoft.com/en-us/library/ms178107.aspx
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 table that was created by running the following Transact-SQL statement:
The Products table includes the data shown in the following table:
TotalUnitPrice is calculated by using the following formula:
TotalUnitPrice = UnitPrice * (UnitsInStock + UnitsOnOrder)
You need to ensure that the value returned for TotalUnitPrice for ProductB is equal to 600.00.
Solution: You run the following Transact-SQL statement:
Does the solution meet the goal?
A. Yes
B. No
Correct Answer: A
COALESCE evaluates the arguments in order and returns the current value of the first expression that initially does not
evaluate to NULL. References: https://docs.microsoft.com/en-us/sql/t-sql/language-elements/coalesce-transact-sql
QUESTION 8
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
Application.Cities
Sales.CustomerCategories
Your company is developing a new social application that connects customers to each other based on the distance
between their delivery locations.
You need to write a query that returns the nearest customer.
Solution: You run the following Transact-SQL statement:
The variable @custID is set to a valid customer. Does the solution meet the goal?
A. Yes
B. No
Correct Answer: B
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. B. C. D.
Correct Answer: C
QUESTION 10
You need to create a database object that meets the following requirements:
accepts a product identified as input
calculates the total quantity of a specific product, including quantity on hand and quantity on order
caches and reuses execution plan
returns a value
can be called from within a SELECT statement
can be used in a JOIN clause What should you create?
A.
an extended stored procedure
B.
a user-defined scalar function
C.
a user-defined stored procedure that has an OUTPUT parameter
D.
a temporary table that has a columnstore index
Correct Answer: B
User-defined scalar functions are execution plans that accept parameters, perform an action such as a complex
calculation, and returns the result of that action as a value. The return value can either be a single scalar value or a
result set.
Furthermore the execution plan is cached and reusable.
User-defined scalar functions can also be called from within a SELECT statement and can be used in a JOIN clause.
Incorrect Answers:
A: Using extended stored procedures is not recommended as they has been deprecated. CLR Integration should be
used instead of extended stored procedures.
C: Stored procedures cannot be used in a SELECT statement or in a JOIN clause.
D: A temporary table is a result set and not a value.
References:
https://www.c-sharpcorner.com/UploadFile/996353/difference-between-stored-procedure-and-user-defined-functio/
QUESTION 11
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 are creating indexes in a data warehouse.
You have a dimension table named Table1 that has 10,000 rows. The rows are used to generate several reports.
The reports join a column that is the primary key.
The execution plan contains bookmark lookups for Table1.
You discover that the reports run slower than expected.
You need to reduce the amount of time it takes to run the reports.
Solution: You create a nonclustered index on the primary key column that includes the bookmark lookup columns.
Does this meet the goal?
A. Yes
B. No
Correct Answer: B
QUESTION 12
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 and the date that the customer placed their last order. For customers who
have not placed orders, you must substitute 01/01/1990 for the date. Which Transact-SQL statement should you run?
A. B. C. D.
Correct Answer: A
QUESTION 13
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 run the following Transact-SQL statement:
You need to return the total annual revenue for all customers, followed by a row for each customer that shows the
customer\\’s name and annual revenue. Which Transact-SQL statement should you run?
A. B. C. D. E. F. G. H.
Correct Answer: A
Share Pass4itsure coupons for free
Reasons to choose Pass4itsure
Pass4itsure offers the latest exam practice questions and answers free of charge! Update all exam questions throughout the year, with a number of professional exam experts! To make sure it works! Maximum pass rate, best value for money! Helps you pass the exam easily on your first attempt.
This maybe you’re interested
Summarize:
Collecting the latest and most effective Microsoft MCSA 70-761 exam practice questions to help you improve exam success,
70-761 pdf and 70-761 video learning make it easier to gain knowledge! Full 70-761 exam dump: Experts recommend real leader Pass4itsure. Click here to easily pass the 70-761
