CORRECT TEXT
You work as a SQL Server 2012 database developer at ABC.com. You are developing a query for
a database driven Web application that allows visitors to vote for the cricket player of the week.
The number of votes is stored in a table named WeeklyVotes that has columns named Week,
PlayerName, Votes.
You need to write a Transact-SQL query that ranks the top 30 cricket players by the average votes
over the last 12 months. You want the top 10 cricket players to have a rank of 1, the next 10 to
have a rank of 2, and the last 10 to have a rank of 3.
Which of the following SELECT statement would you use?
To answer, type the correct code in the answer area.

You work as a database administrator at ABC.com. ABC.com has a SQL Server 2012 database
named ProductsDB. The ProductsDB database is shown in the following database diagram:

You need to write a Transact-SQL query that displays all the products received by a single
supplier in the following XML format:
<Suppliers SupplierID=”22″ Company=”Company Name” ContactNumber=”510 250 6400″>
<Products ProductID=”100″ UnitPrice=”249.00″ UnitsInStock=”7″ />
<Products ProductID=”118″ UnitPrice=”559.00″ UnitsInStock=”12″ />
</Suppliers>
Which of the following SELECT statement would you write?

You work as a database administrator at ABC.com. ABC.com has a SQL Server 2012 database
named SalesDB. The SalesDB is shown in the following database diagram:

You need to write a Transact-SQL query that display a single row in the following XML format:
<Invoices InvoiceID=”1001″ Date=”2012-10-01T00:00:00″, Value=”1000.00″ Customer=”Customer
Name” ShippedTo=”Customer City” />
Which of the following SELECT statement would you write?

You work as a database administrator at ABC.com. ABC.com has a SQL Server 2012 database
named ProductsDB. The relevant part of the ProductsDB is shown in the following database
diagram:

You need to write a Transact-SQL query that display a single row in the following XML format:
<row ProductID=”1001″ Product=”Product Name”, Price=”24.99″ InStock=”16″
Supplier=”Company Name” Contact=”Contact Name” Phone=”346 959 2215″ />
Which of the following SELECT statement would you write?

You work as a database developer at ABC.com. ABC.com has a SQL Server 2012 database
named SalesDB that has a table named WeeklySales. The WeeklySales table records the sales
amount for each of ABC.com’s 20 sales representitives.
You need to write a Transact-SQL query that ranks the sales representatives by the average sales
amount for the past year. You want the sales representatives with the same average sales amount
to have the same rank with the subsequent rank being skipped.
Which ranking function should you use?