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?
What should you use?
You have a table that contains a string column. Most of the rows have the same value. You
need to create a multistep stored procedure that meets the following requirements: •
Accepts a text input parameter • Provides optimal performance for all input values • Has
only one step using the input parameter as a predicate What should you use?
Which Transact-SQL query do you use?
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?
Which object should you use?
You use Microsoft SQL Server 2012 to develop a database application.
You need to create an object that meets the following requirements:
Takes an input variable
Returns a table of values
Cannot be referenced within a view
Which object should you use?
Which two actions should you perform?
You are designing a CLR stored procedure that will access a remote Web service. You
need to ensure that the CLR stored procedure can access the remote Web service. Which
two actions should you perform? (Each correct answer presents part of the solution. Choose
two.)
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?
You need to implement a computed column that references a lookup table by using an INNER JOIN against another
You use Microsoft SQL Server 2012 to develop a database application.
You need to implement a computed column that references a lookup table by using an INNER JOIN
against another table.
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?