The purpose of this Sample Question Set is to provide you with information about the Microsoft Developing SQL Databases exam. These sample questions will make you very familiar with both the type and the difficulty level of the questions on the 70-762 certification test. To get familiar with real exam environment, we suggest you try our Sample Microsoft Developing SQL Databases Certification Practice Exam. This sample practice exam gives you the feeling of reality and is a clue to the questions asked in the actual Microsoft Certified Solutions Associate (MCSA) - SQL 2016 Database Development certification exam.
These sample questions are simple and basic questions that represent likeness to the real Microsoft 70-762 exam questions. To assess your readiness and performance with real time scenario based questions, we suggest you prepare with our Premium Microsoft Developing SQL Databases Certification Practice Exam. When you solve real time scenario based questions practically, you come across many difficulties that give you an opportunity to improve.
Microsoft 70-762 Sample Questions:
01. You have a view that includes an aggregate. You must be able to change the values of columns in the view. The changes must be reflected in the tables that the view uses.
You need to ensure that you can update the view. What should you create?
a) table-valued function
b) a schema-bound view
c) a partitioned view
d) a DML trigger
02. You have two tables in a SQL Server database. The first table is named Customers. The second table is named Employees. Your manager requests a list that contains the first name, last name, and phone number of each customer and employee of the company.
You write the following statement (line numbers are included for reference only):
01 CREATE VIEW CustomerPhoneList
02 AS
03 SELECT firstname, lastname, phone
04 FROM HR.Employees
05 INTERSECT
06 SELECT firstname, lastname, phone
07 FROM Sales.Customers
You execute the statement. The results include only the records that are exact matches between the Customers table and the Employees table. You need to return all the records from both tables, even if there are duplicates.
What should you substitute at line 5?
a) INNER JOIN
b) EXCEPT
c) UNION
d) UNION ALL
03. You are a database developer for a company. The company has a server that has multiple physical disks. The disks are not part of a RAID array.
The server hosts three Microsoft SQL Server instances. There are many SQL jobs that run during off-peak hours.
You must monitor the SQL Server instances in real time and optimize the server to maximize throughput, response time, and overall SQL performance.
You need to ensure that the performance of each instance is consistent for the same queried and query plans. What should you do?
a) Create a sys.dm_os_waiting_tasks query.
b) Create a sys.dm_exec_sessions query.
c) Create a Performance Monitor Data Collector Set.
d) Create a sys.dm_os_memory_objects query.
e) Create a sp_conftgure 'max server memory' query.
f) Create aSQL Profiler trace.
g) Create an Extended Event.
04. You use Microsoft SQL Server Profile to evaluate a query named Query1. The Profiler report indicates the following issues:
- At each level of the query plan, a low total number of rows are processed.
- The query uses many operations. This results in a high overall cost for the query.
You need to identify the information that will be useful for the optimizer. What should you do?
a) Start a SQL Server Profiler trace for the event class Auto Stats in the Performance event category.
b) Create one Extended Events session with the sqlserver.missing_column_statistics eventadded.
c) Start a SQL Server Profiler trace for the event class Soft Warnings in the Errors and Warnings event category.
d) Create one Extended Events session with the sqlserver.missing_join_predicate event added.
05. You are designing a solution for a company that operates retail stores. Each store has a database that tracks sales transactions. You create a summary table in the database at the corporate office. You plan to use the table to record the quantity of each product sold at each store on each day. Managers will use this data to identify reorder levels for products.
Every evening stores, must transmit sales data to the corporate office. The data must be inserted into the summary table that includes the StoreID, ProductID, Qtysold, Totprodsales, and Datesold columns.
You need to prevent duplicate rows in the summary table. Each row must uniquely identify the store that sold the product and the total amount sold for that store on a specific date. What should you include in your solution?
a) Create a unique constraint.
b) Create a foreign key constraint to the StoreID column in each of the store tables.
c) Create a rule and bind it to the StoreID column.
d) Create a check constraint.
06. You are experiencing performance issues with the database server. You need to evaluate schema locking issues, plan cache memory pressure points, and backup I/O problems.
What should you create?
a) a System Monitor report
b) a sys.dm_exec_query_stats dynamic management view query
c) an Activity Monitor session in Microsoft SQL Management Studio.
d) a sys.dm_exec_session_wait_stats dynamic management view query
07. You are creating the following two stored procedures:
- A natively-compiled stored procedure
- An interpreted stored procedure that accesses both disk-based and memory-optimized tables
Both stored procedures run within transactions. You need to ensure that cross-container transactions are possible. Which setting or option should you use?
a) the SET TRANSACTION_READ_COMMITTED isolation level for the connection
b) the SERIALIZABLE table hint on disk-based tables
c) the SET MEMORY_OPTIMIZED_ELEVATE_TO_SNAPSHOT=ON option for the database
d) the SET MEMORY_OPTIMIZED_ELEVATE_TO_SNAPSHOT=OFF option for the database
08. You have a database that is experiencing deadlock issues when users run queries. You need to ensure that all deadlocks are recorded in XML format. What should you do?
a) Create a Microsoft SQL Server Integration Services package that uses sys.dm_tran_locks.
b) Enable trace flag 1224 by using the Database Cpmsistency Checker(BDCC).
c) Enable trace flag 1222 in the startup options for Microsoft SQL Server.
d) Use the Microsoft SQL Server Profiler Lock: Deadlock event class.
09. You suspect deadlocks on a database. Which two trace flags in the Microsoft SQL Server error log should you locate?
(Choose two)
a) 1204
b) 1211
c) 1222
d) 2528
e) 3205
10. You are developing a database reporting solution for a table that contains 900 million rows and is 103 GB. The table is updated thousands of times a day, but data is not deleted.
The SELECT statements vary in the number of columns used and the amount of rows retrieved. You need to reduce the amount of time it takes to retrieve data from the table. The must prevent data duplication.
Which indexing strategy should you use?
a) a clustered columnstore index for the table
b) a hash index for the table
c) a clustered index for the table and nonclustered indexes for nonkey columns
d) a nonclustered index for each column in the table
Answers:
|
Question: 01 Answer: b |
Question: 02 Answer: d |
Question: 03 Answer: g |
Question: 04 Answer: d |
Question: 05 Answer: a |
|
Question: 06 Answer: c |
Question: 07 Answer: c |
Question: 08 Answer: c |
Question: 09 Answer: a, c |
Question: 10 Answer: a |
Note: For any error in Microsoft Certified Solutions Associate (MCSA) - SQL 2016 Database Development (70-762) certification exam sample questions, please update us by writing an email on feedback@edusum.com.