Pass with professional 70-457 actual quiz materials

You administer a Microsoft SQL Server 2012 database. You need to ensure that the size of the transaction log file does not exceed 2 GB. What should you do?

• A. In SQL Server Management Studio, expand the Storage leaf under the database. Select the transaction log file and set the maximum size of the file.
• B. In SQL Server Management Studio, right-click the instance and select Database Settings. Set the maximum size of the file for the transaction log.
• C. Use the ALTER DATABASE...SET LOGFILE command along with the midsize parameter.
• D. In SQL Server Management Studio, right-click the database, select Properties, and then click Files. Open the Transaction log Autogrowth window and set the maximum size of the file.

You administer two instances of Microsoft SQL Server 2012. You deploy an application that uses a database on the named instance. The application is unable to connect to the database on the named instance. You need to ensure that the application can connect to the named instance. What should you do?

• A. Use the Data Quality Client to configure the application.
• B. Use the Master Data Services Configuration Manager to configure the application.
• C. Start the SQL Server Browser Service.
• D. Start the SQL Server Integration Services Service.

You administer a Microsoft SQL Server 2012 database named ContosoDb. Tables are defined as shown in the exhibit. (Click the Exhibit button.)

You need to display rows from the Orders table for the Customers row having the CustomerIdvalue set to 1 in the following XML format.

Which Transact-SQL query should you use?

• A. SELECT OrderId, OrderDate, Amount, Name, Country FROM Orders INNER JOIN Customers ON Orders.CustomerId = Customers. CustomerId WHERE Customers.CustomerId = 1 FOR XML AUTO
• B. SELECT Name, Country, OrderId, OrderDate, Amount FROM Orders INNER JOIN Customers ON Orders.CustomerId = Customers. CustomerId WHERE Customers.CustomerId = 1 FOR XML AUTO
• C. SELECT OrderId, OrderDate, Amount, Name, Country FROM Orders INNER JOIN Customers ON Orders.CustomerId = Customers. CustomerId WHERE Customers.CustomerId = 1 FOR XML RAW
• D. SELECT OrderId, OrderDate, Amount, Name, Country FROM Orders INNER JOIN Customers ON Orders.CustomerId = Customers.CustomerId WHERE Customers.CustomerId = 1 FOR XML AUTO, ELEMENTS
• E. SELECT OrderId, OrderDate, Amount, Name, Country FROM Orders INNER JOIN Customers ON Orders.CustomerId = Customers. CustomerId WHERE Customers=CustomerId = 1 FOR XML RAW, ELEMENTS
• F. SELECT Name, Country, OrderId, OrderDate, Amount FROM Orders INNER JOIN Customers ON Orders.CustomerId = Customers. CustomerId WHERE Customers.CustomerId = 1 FOR XML AUTO, ELEMENTS
• G. SELECT Name AS '@Name', Country AS '@Country', OrderId, OrderDate, Amount FROM Orders INNER JOIN Customers ON Orders.CustomerId = Customers. CustomerId WHERE Customers.CustomerId = 1 FOR XML PATH ('Customers')
• H. SELECT Name AS 'Customers/Name', Country AS 'Customers/Country', OrderId, OrderDate, Amount FROM Orders INNER JOIN Customers ON Orders.CustomerId = Customers. CustomerId WHERE Customers.CustomerId = 1 FOR XML PATH ('Customers')

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 have a Microsoft SQL Server 2012 database that contains tables named Customers and Orders. The tables are related by a column named CustomerID. You need to create a query that meets the following requirements:
Returns the CustomerName for all customers and the OrderDate for any orders that they have placed.
Results must include customers who have not placed any orders.
Which Transact-SQL query should you use?

• A. SELECT CustomerName, CrderDate FROM Customers JOIN Orders ON Customers.CustomerID = Orders.CustomerID
• B. SELECT CustomerName, OrderDate FROM Customers LEFT OUTER JOIN Orders ON Customers.CustomerID = Orders.CustomerID
• C. SELECT CustomerName, OrderDate FROM Customers CROSS JOIN Orders ON Customers.CustomerID = Orders.CustomerID
• D. SELECT CustomerName, OrderDate FROM Customers RIGHT OUTER JOIN Orders ON Customers.CustomerID = Orders.CustomerID