Pass exam with 070-461 Top Exam Collection for sure one-shot

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?

• A. CREATE SEQUENCE CustomerSequence AS int
  START WITH 1
  INCREMENT BY 1
  MINVALUE 1
  MAXVALUE 100
  UPDATE Customers SET PartitionNumber = NEXT VALUE FOR CustomerSequence + 1 DROP SEQUENCE CustomerSequence
• B. CREATE SEQUENCE CustomerSequence AS int
  START WITH 0
  INCREMENT BY 1
  MINVALUE 1
  MAXVALUE 100
  UPDATE Customers SET PartitionNumber = NEXT VALUE FOR CustomerSequence
  DROP SEQUENCE CustomerSequence
• C. CREATE SEQUENCE CustomerSequence AS int
  START WITH 1
  INCREMENT BY 1
  MINVALUE 1
  MAXVALUE 100
  CYCLE
  UPDATE Customers SET PartitionNumber = NEXT VALUE FOR CustomerSequence
  DROP SEQUENCE CustomerSequence
• D. CREATE SEQUENCE CustomerSequence AS int
  START WITH 1
  INCREMENT BY 1
  MINVALUE 0
  MAXVALUE 100
  CYCLE
  UPTATE Customers SET PartitionNumber = NEXT VALUE FOR CustomerSequence
  DROP SEQUENCE CustomerSequence

You develop a database application. You create four tables. Each table stores different categories of products.
You create a Primary Key field on each table.
You need to ensure that the following requirements are met:
* The fields must use the minimum amount of space.
* The fields must be an incrementing series of values.
* The values must be unique among the four tables.
What should you do?

• A. Create a SEQUENCE object that uses the INTEGER data type.
• B. Use the INTEGER data type along with IDENTITY
• C. Use the UNIQUEIDENTIFIER data type along with NEWSEQUENTIALID()
• D. Create a TIMESTAMP column.
• E. Create a ROWVERSION column.

You develop a database application for a university. You need to create a view that will be indexed that meets the following requirements:
Displays the details of only students from Canada.
Allows insertion of details of only students from Canada.
Which four Transact-SQL statements should you use? (To answer, move the appropriate SQL statements from the list of statements to the answer area and arrange them in the correct order.)

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 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?

• A. SELECT CustomerID, CustomerName, ShippingCountry
  FROM
  (SELECT c.CustomerID, c.CustomerName,
  o. ShippingCountry,
  RANK() OVER (PARTITION BY c. CustomerID
  ORDER BY o. OrderAmount DESC) AS Rnk
  FROM Customer c
  INNER JOIN Orders o
  ON c.CustomerID = o.CustomerID
  GROUP BY c.CustomerID, c.CustomerName,
  o.ShippingCountry) cs
  WHERE Rnk = 1
• B. SELECT c.CustomerID, c.CustomerName, o.ShippingCountry
  FROM Customer c
  INNER JOIN
  (SELECT CustomerID, ShippingCountry,
  RANK() OVER (PARTITION BY CustomerID
  ORDER BY COUNT(OrderAmount) DESC) AS Rnk
  FROM Orders
  GROUP BY CustomerID, ShippingCountry) AS o
  ON c.CustomerID = o.CustomerID
  Where o.Rnk = 1
• C. SELECT c.CustomerID, c.CustomerName, o.ShippingCountry
  FROM Customer c
  INNER JOIN
  (SELECT CustomerID, ShippingCountry,
  COUNT(OrderAmount) AS OrderAmount
  FROM Orders
  GROUP BY CustomerID, ShippingCountry) AS o
  ON c.CustomerID = o.CustomerID
  ORDER BY OrderAmount DESC
• D. SELECT CustomerID, CustomerName, ShippingCountry
  FROM
  (SELECT c.CustomerID, c.CustomerName, o.ShippingCountry,
  RANK() OVER (PARTITION BY c.CustomerID
  ORDER BY COUNT(o.OrderAmount) ASC) AS Rnk
  FROM Customer c
  INNER JOIN Orders o
  ON c.CustomerID = o.CustomerID
  GROUP BY c.CustomerID, c.CustomerName,
  o.ShippingCountry) cs
  WHERE Rnk = 1