PrepAway - Latest Free Exam Questions & Answers

Author: seenagape

Which SQL statement should you use?

You have a table named Sales that lists all of the products sold by your company. The
Sales table contains a column named UnitsSold. UnitsSold contains the total number of
units sold of each product. Each product appears in only one row. You have the following
view: You discover that the view does not return a list of the top 100 selling products. You
need to ensure that the view returns a list of the top 100 selling products. Which SQL
statement should you use?

Which Transact-SQL statement should you use?

You administer a database that includes a table named Customers that contains more than 750
rows. You create a new column named PartitionNumber of the int type in the table.
You need to assign a PartitionNumber for each record in the Customers table.You also need to
ensure that the PartitionNumber satisfies the following conditions:
Always starts with 1.
Starts again from 1 after it reaches 100.
Which Transact-SQL statement should you use?

You need to optimize the performance of the stored procedure

You are a database developer. You plan to design a database solution by using SQL Server
2008. A database contains a table named Customer. The structure of the Customer table is
as shown in the following table. The solution uses the following stored procedure to return
customer details. Users report that the stored procedure takes a long time to execute. You
need to optimize the performance of the stored procedure. What should you do?

What should you recommend?

You have an office in Tokyo and an office in New York. Each office contains a server that
runs SQL Server 2008 Enterprise. The New York office contains a central database that is
mirrored to the Tokyo office for high-availability. You need to recommend a solution that
enables users in the Tokyo office to generate reports locally. The solution must minimize
WAN utilization. What should you recommend?

Which Transact-SQL statement should you use?

You administer a Microsoft SQL Server 2012 database named ContosoDb. The database contains a
table named Suppliers and a column named IsActive in the Purchases schema. You create a new user
named ContosoUser in ContosoDb. ContosoUser has no permissions to the Suppliers table. You need
to ensure that ContosoUser can delete rows that are not active from Suppliers. You also need to
grant ContosoUser only the minimum required permissions. Which Transact-SQL statement should
you use?