Free Microsoft 70-461 Exam Dumps Questions & Answers

You have a database that contains the following related tables:

You create a view named OrderSummary by using the following Transact-SQL statement:

For each of the following statements, select Yes if the statement is true. Otherwise, select No.

Correct Answer:

Explanation:
The SalesOrderID column is used in a join statement and cannot be updated.
The Status column is used can be updated.
The SubTotal column is an aggregate column and cannot be updated.

You develop a Microsoft SQL Server database that contains a table named Employee, defined as follows:

You need to create a view that contains two computed columns representing the month and the year of the [HireDate] of each Employee.
Which function should you use?

• A.TRYDATEDIFF( )
• B.CONVERT( )
• C.DATENAME( )
• D.MONTH( ) and YEAR( )

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?

• A.GRANT SELECT ON Purchases.Suppliers TO ContosoUser
• B.GRANT DELETE ON Purchases.Suppliers TO ContosoUser
• C.CREATE PROCEDURE Purchases.PurgeInactiveSuppliers
  AS
  DELETE FROM Purchases.Suppliers WHERE IsActive = 0
  GO
  GRANT EXECUTE ON Purchases.PurgeInactiveSuppliers TO ContosoUser
• D.CREATE PROCEDURE Purchases.PurgeInactiveSuppliers
  WITH EXECUTE AS USER = 'dbo'
  AS
  DELETE FROM Purchases.Suppliers WHERE IsActive = 0
  GO
  GRANT EXECUTE ON Purchases.PurgelnactiveSuppliers TO ContosoUser

You have a database named Sales 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.
* 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.
The solution must support the ANSI SQL-99 standard.
Part of the correct T-SQL statement has been provided in the answer area. Provide the complete code.

• A.SELECT C .LastName,
  MAX(O.OrderDate) AS MostRecentOrderDate
  FROM Customers AS C INNER JOIN Orders AS O
  ON C .CustomerID
  GROUP BY C .Lastname
  ORDER BY MAX (O.OrderDate) DESC
• B.SELECT C .LastName,
  MAX(O.OrderDate) AS MostRecentOrderDate
  FROM Customers AS C INNER JOIN Orders AS O
  ON C .CustomerID=O.CustomerID
  GROUP BY C .Lastname
  ORDER BY MAX (O.OrderDate) DESC

You develop a Microsoft SQL Server database. You design a table to store product information. The table is defined as follows:

You need to meet the following requirements:
* If a product has a product category value "REGULAR", the product should have a sub-category with a length of two characters.
* If a product has the product category other than "REGULAR", the product may or may not have a sub-category.
Which Transact-SQL statement should you use?

• A.Option
  
• B.Option
  
• C.Option
• D.Option