You have a database that contains the tables shown in the exhibit. (Click the Exhibit button).

You need to create a query for a report. The query must meet the following requirements:
NOT use object delimiters.
Return the most recent orders first.
Use the first initial of the table as an alias.
Return the most recent order date for each customer.
Retrieve the last name of the person who placed the order.
Return the order date in a column named MostRecentOrderDate that appears as the last column in the report.
The solution must support the ANSI SQL-99 standard.
Which code segment should you use?
To answer, type the correct code in the answer area.

• A. select C.Lastname, P.MostRecentOrderDate from customers AS C inner join ( select customID, MostRecentOrderDate=max(orderDate) from orders group by customID
  )P
  on C.customerID=P.CustomerID
  order by P.MostRecentOrderDate desc
• B. SELECT LastName, O.OrderDate AS MostRecentOrderDate FROM Customers AS C INNER JOIN Orders AS O ON CustomerID = O.CustomerID ORDER BY O.OrderDate DESC
• C. SELECT c.CustomerID --optional c.LastName, max(o.OrderDate) 'MostRecentOrderDate' FROM Customer c LEFT OUTER JOIN Orders o ON o.CustomerID = c.CustomerID GROUP BY c.CustomerID, c.LastName ORDER BY 3 DESC

You administer a single server that contains a Microsoft SQL Server 2012 default instance on which several production databases have been deployed. You plan to install a new ticketing application that requires the deployment of a database on the server. The SQL login for this application requires sysadmin permissions. You need to ensure that the login for the ticketing application cannot access other production databases. What should you do?

• A. Install a new default SQL Server instance on the server.
• B. Use the SQL Server default instance and enable Contained Databases.
• C. Use the SQL Server default instance and configure a user-defined server role. Add the login for the ticketing application to this role.
• D. Install a new named SQL Server instance on the server.

You create a table that has the StudentCode, SubjectCode, and Marks columns to record mid-year marks for students. The table has marks obtained by 50 students for various subjects. You need to retrieve the students who scored the highest marks for each subject along with the marks. Which Transact-SQL query should you use?

• A. SELECT StudentCode AS Code, Marks AS Value FROM ( SELECT StudentCode, Marks AS Marks, RANXO OVER(PARTITION BY StudentCode ORDER BY Marks DESC) AS Rank
  FROM StudentMarks) tmp
  WHERE Rank = 1
• B. SELECT StudentCode as Code, NTILE(2) OVER(ORDER BY AVG(Marks) DESC) AS Value FROM StudentMarks GROUP BY StudentCode
• C. SELECT StudentCode AS Code, Marks AS Value FROM ( SELECT StudentCode, Marks AS Marks, RANK() OVER(PARTITION BY StudentCode ORDER BY Marks ASC) AS Rank
  FROM StudentMarks) tmp
  WHERE Rank = 1
• D. SELECT StudentCode AS Code, Marks AS Value FROM ( SELECT StudentCode, Marks AS Marks, RANK() OVER(PARTITION BY SubjectCode ORDER BY Marks DESC) AS Rank
  FROM StudentMarks) tmp
  WHERE Rank = 1
• E. SELECT Id, Name, Marks, DENSE_RANK() OVER(ORDER BY Marks DESC) AS Rank FROM StudentMarks
• F. SELECT StudentCode as Code, RANK() OVER(ORDER BY AVG(Marks) DESC) AS Value FROM StudentMarks GROUP BY StudentCode
• G. SELECT StudentCode as Code, DENSE_RANK() OVER(ORDER BY AVG(Marks) DESC) AS Value FROM StudentMarks GROUP BY StudentCode
• H. SELECT StudentCode AS Code, Marks AS Value FROM ( SELECT StudentCode, Marks AS Marks, RANK() OVER(PARTITION BY SubjectCode ORDER BY Marks ASC) AS Rank
  FROM StudentMarks) tmp
  WHERE Rank = 1

You have a database that contains the tables shown in the exhibit. (Click the Exhibit button.)

You have an application named Appl. You have a parameter named @Count that uses the int data type. App1 is configured to pass @Count to a stored procedure. You need to create a stored procedure named usp_Customers for Appl. Usp_Customers must meet the following requirements:
NOT use object delimiters.
Minimize sorting and counting.
Return only the last name of each customer in alphabetical order.
Return only the number of rows specified by the @Count parameter.
The solution must NOT use BEGIN and END statements.
Which code segment should you use?
To answer, type the correct code in the answer area.

• A. CREATE PROCEDURE usp_Customers @Count int AS SELECT TOP(@Count) LastName FROM Customers ORDER BY LastName

You develop a database for a travel application. You need to design tables and other database objects. You need to store media files in several tables. Each media file is less than 1 MB in size. The media files will require fast access and will be retrieved frequently. What should you do?

• A. Use the VARBINARY data type.
• B. Use the FORMAT function.
• C. Use an appropriate collation.
• D. Use the DATETIMEOFFSET data type.
• E. Use a user-defined table type.
• F. Use the CAST function.
• G. Use the DATE data type.
• H. Use the DATETIME2 data type.
• I. Use the DATETIME data type.
• J. Use the TODATETIMEOFFSET function.