You are a database developer for an application hosted on a Microsoft SQL Server 2012 server. The database contains two tables that have the following
definitions:
Global customers place orders from several countries. You need to view the country from which each customer has placed the most orders.
Which Transact-SQL query do you use?
A.
SELECT c.CustomerID, c.CustomerName, o.ShippingCountry FROM Customer c
INNER JOIN
(SELECT CustomerID, ShippingCountry,
RANK() OVER (PARTITION BY CustomerID
ORDER BY COUNT(OrderAmount) DESC) AS Rnk
FROM Orders
GROUP BY CustomerID, ShippingCountry) AS o
ON c.CustomerID = o.CustomerID
WHERE o.Rnk = 1
B.
SELECT c.CustomerID, c.CustomerName, o.ShippingCountry FROM
(SELECT c.CustomerID, c.CustomerName, o.ShippingCountry, RANK() OVER (PARTITION BY CustomerID
ORDER BY COUNT(o.OrderAmount) ASC) AS Rnk
FROM Customer c
INNER JOIN Orders o
ON c.CustomerID = o.CustomerID
GROUP BY c.CustomerID, c.CustomerName, o.ShippingCountry) cs WHERE Rnk = 1
C.
SELECT c.CustomerID, c.CustomerName, o.ShippingCountry FROM Customer c
INNER JOIN
(SELECT CustomerID, ShippingCountry,
RANK() OVER (PARTITION BY CustomerID
ORDER BY OrderAmount DESC) AS Rnk
FROM Orders
GROUP BY CustomerID, ShippingCountry) AS o
ON c.CustomerID = o.CustomerID
WHERE o.Rnk = 1
D.
SELECT c.CustomerID, c.CustomerName, o.ShippingCountry FROM Customer c
INNER JOIN
(SELECT CustomerID, ShippingCountry,
COUNT(OrderAmount) DESC) AS OrderAmount
FROM Orders
GROUP BY CustomerID, ShippingCountry) AS o
ON c.CustomerID = o.CustomerID
ORDER BY OrderAmount DESC
(2017/Sep/12)New 70-764 Exam Questions and Answers Free Updated Today! Share:
QUESTION
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 have five servers that run Microsoft Windows 2012 R2. Each server hosts a Microsoft SQL Server instance.
The topology for the environment is shown in the following diagram.
image_thumb
You have an Always On Availability group named AG1. The details for AG1 are shown in the following table.
image_thumb[1]
Instance1 experiences heavy read-write traffic. The instance hosts a database named OperationsMain that is four terabytes (TB) in size. The database has multiple data files and filegroups. One of the filegroups is read_only and is half of the total database size.
Instance4 and Instance5 are not part of AG1. Instance4 is engaged in heavy read-write I/O.
Instance5 hosts a database named StagedExternal. A nightly BULK INSERT process loads data into an empty table that has a rowstore clustered index and two nonclustered rowstore indexes.
You must minimize the growth of the StagedExternal database log file during the BULK INSERT operations and perform point-in-time recovery after the BULK INSERT transaction. Changes made must not interrupt the log backup chain.
You plan to add a new instance named Instance6 to a datacenter that is geographically distant from Site1 and Site2. You must minimize latency between the nodes in AG1.
All databases use the full recovery model. All backups are written to the network location \\SQLBackup\. A separate process copies backups to an offsite location. You should minimize both the time required to restore the databases and the space required to store backups. The recovery point objective (RPO) for each instance is shown in the following table.
image_thumb[2]
Full backups of OperationsMain take longer than six hours to complete.
All SQL Server backups use the keyword COMPRESSION.
You plan to deploy the following solutions to the environment.
The solutions will access a database named DB1 that is part of AG1.
The wait statistics monitoring requirements for the instances are described in the following table.
image_thumb[3]
You need to create a backup plan for Instance4.
Which backup plan should you create?
A. Weekly full backups, nightly differential. No transaction log backups are necessary.
B. Weekly full backups, nightly differential backups, transaction log backups every 5 minutes.
C. Weekly full backups, nightly differential backups, transaction log backups every 12 hours.
D. Weekly full backups, nightly differential backups, nightly transaction log backups.
Answer: B
Explanation:
QUESTION
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.
A company has an on-premises Microsoft SQL Server environment and Microsoft Azure SQL Database instances. The environment hosts a customer database named DB1.
Customers connect to hosted database instances by using line-of-business applications. Developers connect by using SQL Server Management Studio (SSMS).
You need to grant the developers permission to alter views for DB1 while following the principle of least privilege.
Which permission should you grant?
A. DDLAdmin
B. db_datawriter
C. dbcreator
D. dbo
E. View Database State
F. View Server State
G. View Definition
H. sysadmin
Answer: A
Explanation:
QUESTION
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 observe that several indexes are fragmented.
You need to rebuild the indexes.
What should you use?
A. Activity Monitor
B. Sp_who3
C. Object Explorer in the SQL Server Management Studio (SSMS)
D. SQL Server Data Collector
E. SQL Server Data Tools (SSDT)
F. SQL Server Configuration Manager
Answer: C
Explanation:
QUESTION
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 are examining information about users, sessions, and processes in an on-premises Microsoft SQL Server 2016 Standard Edition server.
You need to identify waits for resources and return only the following information:
What should you use?
A. Activity Monitor
B. Sp_who3
C. SQL Server Management Studio (SSMS) Object Explorer
D. SQL Server Data Collector
E. SQL Server Data Tools (SSDT)
F. SQL Server Configuration Manager
Answer: E
Explanation:
QUESTION
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 collect performance metrics on multiple Microsoft SQL Server instances and store the data in a single repository.
You need to examine disk usage, query statistics, and server activity without building custom counters.
What should you use?
A. Activity Monitor
B. Sp_who3 stored procedure
C. Object Explorer in the Microsoft SQL Server Management Studio (SSMS)
D. SQL Server Data Collector
E. SQL Server Data Tools (SSDT)
F. SQL Server Configuration Manager
Answer: D
Explanation:
QUESTION
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 sections, you will NOT be able to return to it. As a result, these questions will not appear in the review screen.
A company has an on-premises Microsoft SQL Server environment and Microsoft Azure SQL Database instances. The environment hosts several customer databases.
One customer reports that their database is not responding as quickly as the service level agreements dictate.
You observe that the database is fragmented.
You need to optimize query performance.
Solution: You reorganize all indexes.
Does the solution meet the goal?
A. Yes
B. No
Answer: A
Explanation:
QUESTION
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 sections, you will NOT be able to return to it. As a result, these questions will not appear in the review screen.
A company has a server that runs Microsoft SQL Server 2016 Web edition. The server has a default instance that hosts a database named DB1.
You need to ensure that you can perform auditing at the database level for DB1.
Solution: You migrate DB1 to the default instance on a server that runs Microsoft SQL Server 2016 Standard edition.
Does the solution meet the goal?
A. Yes
B. No
Answer: B
QUESTION
You have a database that stores information for a shipping company.
You create a table named Customers by running the following Transact-SQL statement. (Line numbers are included for reference only.)
image_thumb[4]
You need to ensure that salespeople can view data only for the customers that are assigned to them.
Which Transact-SQL segment should you insert at line 07?
A. RETURNS varchar(20)WITH Schemabinding
B. RETURNS dbo.CustomersORDER BY @salesPerson
C. RETURNS tableORDER BY @salesPerson
D. RETURNS tableWITH Schemabinding
Answer: D
QUESTION
Hotspot Question
You manage a Microsoft-SQL Server database named sales Orders.
You need to verify the integrity of the database and attempt to repair any errors that are found. Repair must not cause any data to be lost in the database.
How should you complete the DBCC command? To answer, select the appropriate options in the answer area.
Answer:
Explanation:
QUESTION
Hotspot Question
You have a Microsoft SQL Server instance that hosts a database named DB1 that contains 800 gigabyte (GB) of data.
The database is used 24 hours each day.
You implement indexes and set the value of the Auto Update Statistics option set to True.
Users report that queries take a long time to complete.
You need to identify statistics that have not been updated for a week for tables where more than 1,000 rows changed.
How should you complete the Transact-SQL statement? To answer, configure the appropriate Transact-SQL segments in the answer area.
Answer:
I am sorry that pics and answers explanation cannot be showed here but you can go to check the full version here: http://www.freevceplus.com/?s=70-764