[2020.9] latest collection of Microsoft Certified Solutions Associate Practice question and answers
adminThe most popular Microsoft Certified Solutions Associate Practice question and tips. (Microsoft 70-761 exam, Microsoft 70-764 exam, Microsoft 70-767 exam, Microsoft 70-768 exam) Exampdfdownload shares the latest exam Practice question and answers for free.
The latest popular Microsoft exam certification pass method:
https://www.lead4pass.com/mcsa.html High pass rate! Cost-effective!
Table of Contents:
- Popular Microsoft 70-761 List
- Popular Microsoft 70-764 List
- Popular Microsoft 70-767 List
- Popular Microsoft 70-768 List
- Lead4Pass Microsoft Certified Solutions Associate Discount Code
- About Lead4pass Brand Advantage
Popular Microsoft 70-761 List
Latest Microsoft 70-761 Exam Video
Exam 70-761: Querying Data with Transact-SQL: https://docs.microsoft.com/en-us/learn/certifications/exams/70-761
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.
Skills measured
- Manage data with Transact-SQL (40-45%)
- Query data with advanced Transact-SQL components (30-35%)
- Program databases by using Transact-SQL (25-30%)
[2020.9] Popular Microsoft 70-761 exam practice questions(1-5)
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 on the review screen.
You have a table named Products that stores information about the products your company sells. The table has a column
named ListPrice that stores retail pricing information for products.
Some products are used only internally by the company. Records for these products are maintained in the Products
table for inventory purposes. The price for each of these products is $0.00. Customers are not permitted to order these
products.
You need to increase the list price for products that cost less than $100 by 10 percent. You must only increase pricing
for products that customers are permitted to order. Solution: You run the following Transact-SQL statement:
Does the solution meet the goal?
A. Yes
B. No
Correct Answer: B
Products with a price of $0.00 would also be increased.
QUESTION 2
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: A
QUESTION 3
You run the following Transact-SQL statement:
You use the table to store data about training courses: when they finished the location and the number of participants in
the courses.
You need to display a result set that shows aggregates for all possible combinations of the number of participants.
Which Transact-SQL statement should you run?
A. SELECT CourseID, CourseDate, SUM(NumParticipants) FROM CourseParticipants GROUP BY CourseID,
CourseDate
B. SELECT CourseID, CourseDate, SUM(DISTINCT NumParticipants) FROM CourseParticipants GROUP BY
CourseID, CourseDate
C. SELECT CourseID, CourseDate, SUM(NumParticipants) FROM CourseParticipants GROUP BY CourseID,
coursemate WITH CUBE
D. SELECT CourseID, CourseDate, SUM(DISTINCT NumParticipants) FROM CourseParticipants GROUP BY
CourseID, CourseDate WITH ROLLUP
Correct Answer: C
The WITH CUBE clause causes the query to compute all possible totals
References: https://blogs.msdn.microsoft.com/craigfr/2007/09/27/aggregation-with-cube/
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 on the review screen.
You create a table named Customer by running the following Transact-SQL statement:
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
As there is two separate INSERT INTO statements we cannot ensure that both or neither records are inserted.
QUESTION 5
DRAG DROP
You are a database administrator for an online retail store. You create a table to track orders by running the following
Transact-SQL statement:
You need to create a report that includes the following information: Total sales for each year Total sales for each
category per year How should you complete the Transact-SQL statement? To answer,
drag the appropriate Transact-SQL segment 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. NOTE: Each
correct selection is worth one point.
Select and Place:
Correct Answer:
Box 1: GROUP BY Box 2: CUBE GROUP BY CUBE creates groups for all possible combinations of columns. For
GROUP BY CUBE (a, b) the results have groups for unique values of (a, b), (NULL, b), (a NULL), and (NULL, NULL).
Example: This code runs a GROUP BY CUBE operation on Country and Region. SELECT Country, Region,
SUM(Sales) AS TotalSales FROM Sales GROUP BY CUBE (Country, Region); References:
https://docs.microsoft.com/en-us/sql/t-sql/queries/select-group-by-transact-sql
[PDF q1 – q13] Free Microsoft 70-761 pdf dumps download from Google Drive: https://drive.google.com/file/d/1KmtQt41gKewZVYt-66dIGxckHtsJOsJP/
Full Microsoft 70-761 brain dumps: https://www.lead4pass.com/70-761.html (Total Questions: 446 Q&A)
Popular Microsoft 70-764 List
Latest Microsoft 70-764 Exam Video
Exam 70-764: Administering a SQL Database Infrastructure：https://docs.microsoft.com/en-us/learn/certifications/exams/70-764
This exam is intended for database professionals who perform installation, maintenance, and configuration tasks. Other responsibilities include setting up database systems, making sure those systems operate efficiently, and regularly storing, backing up, and securing data from unauthorized access.
Skills measured
- Configure data access and auditing (20-25%)
- Manage backup and restore of databases (20-25%)
- Manage and monitor SQL Server instances (35-40%)
- Manage high availability and disaster recovery (20-25%)
[2020.9] Popular Microsoft 70-764 exam practice questions (1-4)
QUESTION 1
You work as a Database Administrator (DBA) for a company named ABC.com.
The company uses a Microsoft SQL Server 2012 infrastructure.
The sales and marketing departments contain a total of 60 users. Each user uses a custom application that stores data
in a SQL Server database.
Each user has a separate database.
A server named ABC-SQL1 hosts the 60 databases.
You need to configure a backup solution for all the databases.
The solution must ensure that any new databases configured on ABC-SQL1 are automatically added to the backup
schedule. The backup schedule consists of a full backup every night, a differential backup every hour and a transaction
log
backup every 15 minutes.
How should you configure the backup solution?
A. You should configure SQL Server Agent jobs.
B. You should configure Change Data Capture.
C. You should configure Policy-Based Management.
D. You should configure a Maintenance Plan.
Correct Answer: D
QUESTION 2
You administer a Microsoft SQL Server 2016 database that includes a table named Application. Events.
Application. Events contain millions of records about user activity in an application.
Records in Application. Events that are more than 90 days old are purged nightly.
When records are purged, table locks are causing contention with inserts.
You need to be able to modify the Application. Events without requiring any changes to the applications that utilize
Application.Events.
Which type of solution should you use?
A. Partitioned tables
B. Online index rebuild
C. Change data capture
D. Change tracking
Correct Answer: A
QUESTION 3
You manage a Microsoft SQL Server instance that has a database named DB1. The instance has a server audit named
Audit1. DB1 uses the following schemas:
You need to implement a database audit specification for database DB1 that meets the following requirements:
Audit only delete statements performed by users in the db_datawriter role.
Audit insert statements for all tables in Schema1 for all users.
How should you complete the Transact-SQL statement? To answer, select the appropriate options in the answer area.
NOTE: Each correct selection is worth one point.
Hot Area:
Correct Answer:
QUESTION 4
You are designing a database named DB1.
Changes will be deployed to DB1 every Wednesday night.
You need to recommend a strategy to deploy the changes to DB1.
The strategy must meet the following requirements:
The strategy must not disrupt backup operations.
DB1 must be unavailable to users while the changes are deployed.
You must be able to undo quickly the entire operation.
What should you recommend? More than one answer choice may achieve the goal. Select the BEST answer.
A. Perform a copy-only database backup before the changes are deployed. If the deployment fails, restore the database
to another server and recover the original. Objects from the restored database.
B. Create a database snapshot. If the deployment fails, recover the objects from the database snapshot.
C. Create a database snapshot. If the deployment fails, revert the database to the database snapshot.
D. Perform a full database backup before the changes are deployed. If the deployment fails, restore the database to
another server and recover the original objects from the restored database.
Correct Answer: C
[PDF q1 – q13] Free Microsoft 70-764 pdf dumps download from Google Drive: https://drive.google.com/file/d/1jWffkOehAQthdtv03iWKlYzONK5QK-DV/
Full Microsoft 70-764 brain dumps: https://www.lead4pass.com/70-764.html (Total Questions: 445 Q&A)
Popular Microsoft 70-767 List
Latest Microsoft 70-767 Exam Video
Exam 70-767: Implementing a Data Warehouse using SQL：https://docs.microsoft.com/en-us/learn/certifications/exams/70-767
This exam is intended for extract, transform, and load (ETL) and data warehouse developers who create business intelligence (BI) solutions. Their responsibilities include data cleansing, in addition to ETL and data warehouse implementation.
Skills measured
- Design, implement and maintain a data warehouse (35-40%)
- Extract, transform and load data (40-45%)
- Build data quality solutions (15-20%)
[2020.9] Popular Microsoft 70-767 exam practice questions (1-5)
QUESTION 1
You are implementing a SQL Server Integration Services (SSIS) package that imports Microsoft Excel workbook data
into a Windows Azure SQL Database database. The package has been deployed to a production server that runs
Windows
Server 2008 R2 and SQL Server 2016.
The package fails when executed on the production server.
You need to ensure that the package can load the Excel workbook data without errors. You need to use the least
amount of administrative effort to achieve this goal.
What should you do?
A. Install a 64-bit ACE driver and execute the package by using the 64-bit run-time option.
B. Enable Address Windowing Extensions (AWE) for the local SQL Server instance.
C. Replace the SSIS Excel source with an SSIS Flat File source.
D. Install a 64-bit ACE driver and replace the Excel source with an OLE DB source.
Correct Answer: D
Explanation: XLSX files, the new file type in Excel 2007/2010 cannot be opened with Excel Source/Destination in SSIS.
To use XLSX in SSIS we need to install the ACE driver and use OLE DB Source/Destination to read/write data in
XLSX
files.
ACE is a set of components that facilitate the transfer of data between existing Microsoft Office files such as Microsoft
Office Access (*.mdb and *.accdb) files and Microsoft Office Excel 7 (*.xls, *.xlsx, and *.xlsb) files to Microsoft SQL
Server.
If the SQL Server or your development box does not have an ACE driver then install that 1st.
Note:
*
The most efficient way to link to SQL Server is using an OLEDB connection manager.
Incorrect: Not B:
*
Address Windowing Extensions (AWE) is a Microsoft Windows application programming interface that allows a 32-bit
software application to access more physical memory than it has a virtual address space.
*
Address Windowing Extensions (AWE) is a set of extensions that allows an application to quickly manipulate physical
memory greater than 4GB. Certain data-intensive applications, such as database management systems and scientific
and engineering software, need access to very large caches of data. In the case of very large data sets, restricting the cache to fit within an application\\’s 2GB of user address space is a severe restriction.
QUESTION 2
A company plans to load data from a CSV file that is stored in a Microsoft Azure Blob storage container.
You need to load the data.
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.
NOTE: Each correct selection is worth one point.
Select and Place:
Correct Answer:
QUESTION 3
You are installing SQL Server Data Quality Services (DQS).
You need to give specific users access to the Data Quality Server.
Which SQL Server application should you use?
A. SQL Server Configuration Manager
B. SQL Server Data Tools
C. SQL Server Management Studio
D. Data Quality Client
Correct Answer: C
Ref: http://msdn.microsoft.com/en-us/library/hh213045.aspx
QUESTION 4
The package uses data from the Products table and the Prices table. Properties of the Prices source are shown in the
OLE DB Source Editor exhibit (Click the Exhibit button.) and the Advanced Editor for Prices exhibit (Click the Exhibit
button.)
You create a Microsoft SQL Server Integration Services (SSIS) package as shown in the SSIS Package exhibit. (Click
the Exhibit button.)
You join the Products and Prices tables by using the reference column.
You need to resolve the error with the package.
For each of the following statements, select Yes if the statement is true. Otherwise, select No.
NOTE: Each correct selection is worth one point.
Hot Area:
Correct Answer:
There are two important sort properties that must be set for the source or upstream transformation that supplies data to
the Merge and Merge Join transformations:
The Merge Join Transformation requires sorted data for its inputs.
If you do not use a Sort transformation to sort the data, you must set these sort properties manually on the source or the upstream transformation.
References: https://docs.microsoft.com/en-us/sql/integration-services/dataflow/transformations/sort-data-for-the-mergeand-merge-join-transformations
QUESTION 5
You are developing a SQL Server Integration Services (SSIS) package to implement an incremental data load strategy.
The package reads data from a source system that uses the SQL Server change data capture (CDC) feature.
You have added a CDC Source component to the data flow to read changed data from the source system.
You need to add a data flow transformation to redirect rows for separate processing of insert, update, and delete
operations.
Which data flow transformation should you use?
A. Audit
B. DQS Cleansing
C. CDC Splitter
D. Pivot
Correct Answer: C
Explanation: The CDC splitter splits a single flow of change rows from a CDC source data flow into different data flows
for Insert, Update and Delete operations Ref: http://msdn.microsoft.com/en-us/library/hh758656.aspx
[PDF q1 – q13] Free Microsoft 70-767 pdf dumps download from Google Drive: https://drive.google.com/file/d/1Dmk8rYFNy6NGkuZ36Cjv-ZZpERKyR9c3/
Full Microsoft 70-767 brain dumps: https://www.lead4pass.com/70-767.html (Total Questions: 402 Q&A)
Popular Microsoft 70-768 List
Latest Microsoft 70-768 Exam Video
Exam 70-768: Developing SQL Data Models: https://docs.microsoft.com/en-us/learn/certifications/exams/70-768
This exam is intended for business intelligence (BI) developers who focus on creating BI solutions that require implementing multidimensional data models, implementing and maintaining OLAP cubes, and implementing tabular data models.
Skills measured
- Design a multidimensional business intelligence (BI) semantic model (25-30%)
- Design a tabular BI semantic model (20-25%)
- Develop queries using Multidimensional Expressions (MDX) and Data Analysis Expressions (DAX) (15-20%)
- Configure and maintain SQL Server Analysis Services (SSAS) (30-35%)
[2020.9] Popular Microsoft 70-768 exam practice questions (1-5)
QUESTION 1
You are a business analyst for a company that uses a Microsoft SQL Server Analysis Services (SSAS) tabular database
for reporting. The database model contains the following tables:
You have been asked to write a query for a report that returns the total sales for each product subcategory, as well as
for each product category.
You need to write the query to return the data for the report.
How should you complete the DAX statement? To answer, drag the appropriate DAX segment to the correct locations.
Each DAX 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.
Select and Place:
Correct Answer:
Box 1:EVALUATE
Box 2:SUMMERIZE
Box 3:ROLLUP
Box 4:\\’Product Subcategory\\’ [\\’Product Subcategory Name]
Note: The behavior of SUMMARIZE is similar to the GROUP BY syntax of a SELECT statement in SQL. For example,
consider the following query.
EVALUATE
SUMMARIZE(
\\’Internet Sales\\’,
\\’Internet Sales\\'[Order Date],
“Sales Amount”, SUM( \\’Internet Sales\\'[Sales Amount] )
)
This query calculates the total of Sales Amount for each date in which there is at least one
order,
producing this result.
QUESTION 2
You are developing a SQL Server Analysis Services (SSAS) tabular project that will be used by the finance, sales, and
marketing teams.
The sales team reports that the model is too complex and difficult to use. The sales team does not need any information
other than sales related resources in the tabular model. The finance and marketing teams need to see all the resources
in the tabular model.
You need to implement a solution that meets the needs of the sales team while minimizing development and
administrative effort. What should you do?
A. Create a separate partition for each team.
B. Create a separate data source for each team.
C. Create a perspective for the sales team.
D. Enable client-side security to filter non-sales data.
Correct Answer: C
QUESTION 3
You need to create the cube processing job and the dimension processing job. Which processing task should you use
for each job? To answer, drag the appropriate processing tasks to the correct locations. Each processing task 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.
Select and Place:
Box 1: ProcessData Processes data only without building aggregations or indexes. If there is data is in the partitions, it
will be dropped before re-populating the partition with source data. Box 2: Process Update Forces a re-read of data and
an update of dimension attributes. Flexible aggregations and indexes on related partitions will be dropped. References: https://docs.microsoft.com/en-us/sql/analysis-services/multidimensionalmodels/processing-options-and-settings-analysisservices
QUESTION 4
You install a SQL Server Analysis Services (SSAS) instance in tabular mode on a server.
While processing a very large tabular model, you receive an out-of-memory error. You identify that the amount of
physical memory in the server is insufficient. Additional physical memory cannot be installed on the server.
You need to configure the server to allow paging to disk by using the operating system page file (pagefile.sys).
Which four actions should you perform in sequence? (To answer, move the appropriate actions from the list of actions to
the answer area and arrange them in the correct order.)
Select and Place:
Correct Answer:
QUESTION 5
You are developing a SQL Server Analysis Services (SSAS) tabular project.
A column named City must be added to the table named Customer. The column will be used in the definition of a
hierarchy. The City column exists in the Geography table that is related to the Customer table.
You need to add the City column to the Customer table.
How should you write the calculation?
A. City:= LOOKUP(Geography[City],Geography[GeographyKey],[GeographyKey])
B. City:= LOOKUPVALUE(Geography[City],Geography[GeographyKey],[GeographyKey]) C
.=RELATED(Geography[City])
C. =RELATED(Geography.City)
D. =VALUES(Geography[City])
E. City:=VALUES(Geography[City])
Correct Answer: C
Explanation: * RELATED Function Returns a related value from another table.
[PDF q1 – q13] Free Microsoft 70-768 pdf dumps download from Google Drive: https://drive.google.com/file/d/1E9l_hD7MEpxlMPJdB43uMpaCccvJt9uh/
Full Microsoft 70-768 brain dumps: https://www.lead4pass.com/70-768.html (Total Questions: 50 Q&A)
Lead4Pass Discount Code 2020
About Lead4pass Brand Advantage
Lead4pass employs the most authoritative exam specialists from Microsoft, Cisco, EMC, HP, CompTIA, etc. We update exam data throughout the year. Highest pass rate! We have a large user base. We are an industry leader!
Choose Lead4Pass to pass the exam with ease!
Summarize:
It’s not easy to pass the exam, but with accurate learning materials and proper practice,
you can crack the exam with excellent results. https://www.lead4pass.com/mcsa.html provides you with the most relevant learning materials that you can use to help you prepare.
Ps.
keywords: Microsoft Certified Solutions Associate, 70-761 dumps, 70-761 exam questions, 70-761 exam dumps, 70-761 practice test, 70-761 study guide,70-764 study guide, 70-764 practice test, 70-764 dumps, 70-764 practice test free, 70-767 practice test, 70-767 exam preparation, 70-767 study guide, 70-767 dumps, 70-767 exam questions, 70-768 exam questions, 70-768 exam dumps, 70-768 dumps, 70-768 pdf, 70-768 exam preparation