Latest Querying Microsoft SQL Server 2012 70-461 Dumps

NEW QUESTION 1

You use Microsoft SQL Server 2012 to develop a database application. You create a table by using the following definition:
CREATE TABLE Prices (
PriceId int IDENTITY(1,1) PRIMARY KEY,
ActualPrice NUMERIC(16,9), PredictedPrice NUMERIC(16,9)
)
You need to create a computed column based on a user-defined function named udf_price_index. You also need to ensure that the column supports an index.
Which three 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.)

• A. Mastered
• B. Not Mastered

Answer: A

Explanation:

NEW QUESTION 2

You have a view that was created by using the following code:

You need to create an inline table-valued function named Sales.fn_OrdersByTerritory. Sales.fn_OrdersByTerritory must meet the following requirements:
Use one-part names to reference columns.
Return the columns in the same order as the order used in OrdersByTerritoryView.
Part of the correct T-SQL statement has been provided in the answer area. Provide the complete code.

• A. Mastered
• B. Not Mastered

Answer: A

Explanation:
CREATE FUNCTION Sales.fn_OrdersByTerritory (@T int) RETURNS TABLE
AS RETURN ( SELECT
OrderID, OrderDate, SalesTerritoryID, TotalDue
FROM Sales.OrdersByTerritory WHERE SalesTerritoryID=@T
)

NEW QUESTION 3

You administer a Microsoft SQL Server 2012 database that includes a table named Products. The Products table has columns named ProductId, ProductName, and CreatedDateTime.
The table contains a unique constraint on the combination of ProductName and CreatedDateTime. You need to modify the Products table to meet the following requirements:
Remove all duplicates of the Products table based on the ProductName column.
Retain only the newest Products row.
Which Transact-SQL query should you use?

• A. WITH CTEDupRecords AS(SELECT MIN(CreatedDateTime) AS CreatedDateTime, ProductName FROM ProductsGROUP BY ProductName HAVING COUNT(*) > 1)DELETE pFROM Products pJOIN CTEDupRecords cte ON cte.ProductName = p.ProductName AND cte.CreatedDateTime >
• B. CreatedDateTime
• C. WITH CTEDupRecords AS(SELECT MAX(CreatedDateTime) AS CreatedDateTime, ProductName FROM ProductsGROUP BY ProductName HAVING COUNT(*) > 1)DELETE pFROM Products pJOIN CTEDupRecords cte ON
• D. ProductName = cte.ProductName AND p.CreatedDateTime < cte.CreatedDateTime
• E. WITH CTEDupRecords AS(SELECT MIN(CreatedDateTime) AS CreatedDateTime, ProductName FROM ProductsGROUP BY ProductName)DELETE pFROM Products pJOIN CTEDupRecords cte ON
• F. ProductName = cte.ProductName
• G. WITH CTEDupRecords AS(SELECT MAX(CreatedDateTime) AS CreatedDateTime, ProductName FROM ProductsGROUP BY ProductNameHAVING COUNT(*) > 1)DELETE pFROM Products pJOIN CTEDupRecords cte ON
• H. ProductName = cte.ProductName

Answer: B

NEW QUESTION 4

Your database contains a table named Purchases. The table includes a DATETIME column named PurchaseTime that stores the date and time each purchase is made. There is a non-clustered index on the PurchaseTime column.
The business team wants a report that displays the total number of purchases made on the current day. You need to write a query that will return the correct results in the most efficient manner.
Which Transact-SQL query should you use?

• A. SELECT COUNT(*)FROM PurchasesWHERE PurchaseTime = CONVERT(DATE, GETDATE())
• B. SELECT COUNT(*)FROM PurchasesWHERE PurchaseTime = GETDATE()
• C. SELECT COUNT(*)FROM PurchasesWHERE CONVERT(VARCHAR, PurchaseTime, 112) = CONVERT(VARCHAR, GETDATE(), 112)
• D. SELECT COUNT(*)FROM PurchasesWHERE PurchaseTime >= CONVERT(DATE, GETDATE())AND PurchaseTime < DATEADD(DAY, 1, CONVERT(DATE, GETDATE()))

Answer: D

Explanation:
Two answers will return the correct results (the "WHERE CONVERT..." and "WHERE ... AND ... " answers). The correct answer for Microsoft would be the answer that is most "efficient". Anybody have a clue as to
which is most efficient? In the execution plan, the one that I've selected as the correct answer is the query with the shortest duration. Also, the query answer with "WHERE CONVERT..." threw warnings in the execution plan...something about affecting CardinalityEstimate and SeekPlan.
I also found this article, which leads me to believe that I have the correct answer
http://technet.microsoft.com/en-us/library/ms181034.aspx

NEW QUESTION 5

You have an XML schema collection named Sales.InvoiceSchema.
You need to declare a variable of the XML type named XML1. The solution must ensure that XML1 is validated by using Sales.InvoiceSchema.
Which code segment should you use?
To answer, type the correct code in the answer area.

• A. Mastered
• B. Not Mastered

Answer: A

Explanation:
DECLARE @XML1 XML(Sales.InvoiceSchema)
Reference:
http://msdn.microsoft.com/en-us/library/ms176009.aspx

NEW QUESTION 6

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 CustomerId value 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 = 1FOR XML RAW
• B. SELECT OrderId, OrderDate, Amount, Name, Country FROM Orders INNER JOIN Customers ON Orders.CustomerId = Customers.CustomerId WHERE Customers.CustomerId = 1FOR XML RAW, ELEMENTS
• C. SELECT OrderId, OrderDate, Amount, Name, Country FROM Orders INNER JOIN Customers ON Orders.CustomerId = Customers.CustomerId WHERE Customers.CustomerId = 1FOR XML AUTO
• D. SELECT OrderId, OrderDate, Amount, Name, Country FROM Orders INNER JOIN Customers ON Orders.CustomerId - Customers.CustomerId WHERE Customers.CustomerId = 1FOR XML AUTO, ELEMENTS
• E. SELECT Name, Country, OrderId, OrderDate, Amount FROM Orders INNER JOIN Customers ON Orders.CustomerId = Customers.CustomerId WHERE Customers.CustomerId = 1FOR XML AUTO
• F. SELECT Name, Country, OrderId, OrderDate, Amount FROM Orders INNER JOIN Customers ON Orders.CustomerId = Customers.CustomerId WHERE Customers.CustomerId = 1FOR XML AUTO, ELEMENTS
• G. SELECT Name AS '@Name', Country AS '@Country', OrderId, OrderDate, Amount FROM OrdersINNER JOIN Customers ON Orders.CustomerId = Customers.CustomerId WHERE Customers.CustomerId = 1FOR XML PATH ('Customers')
• H. SELECT Name AS 'Customers/Name', Country AS 'Customers/Country', OrderId, OrderDate, Amount FROM OrdersINNER JOIN Customers ON Orders.CustomerId = Customers.CustomerId WHERE Customers.CustomerId = 1FOR XML PATH ('Customers')

Answer: G

NEW QUESTION 7

You develop a Microsoft SQL Server 2012 server database that supports an application. The application contains a table that has the following definition:
CREATE TABLE Inventory (
ItemID int NOT NULL PRIMARY KEY,
ItemsInStore int NOT NULL, ItemsInWarehouse int NOT NULL)
You need to create a computed column that returns the sum total of the ItemsInStore and ItemsInWarehouse values for each row.
The new column is expected to be queried heavily, and you need to be able to index the column. Which Transact-SQL statement should you use?

• A. ALTER TABLE InventoryADD TotalItems AS ItemslnStore + ItemsInWarehouse
• B. ALTER TABLE InventoryADD TotalItems AS ItemsInStore + ItemsInWarehouse PERSISTED
• C. ALTER TABLE InventoryADD TotalItems AS SUM(ItemsInStore, ItemsInWarehouse) PERSISTED
• D. ALTER TABLE InventoryADD TotalItems AS SUM(ItemsInStore, ItemsInWarehouse)

Answer: B

Explanation:
Reference: http://msdn.microsoft.com/en-us/library/ms174979.aspx

NEW QUESTION 8

You are writing a set of queries against a FILESTREAM-enabled database.
You create a stored procedure that will update multiple tables within a transaction.
You need to ensure that if the stored procedure raises a run-time error, the entire transaction is terminated and rolled back.
Which Transact-SQL statement should you include at the beginning of the stored procedure?

• A. SET IMPLICIT_TRANSACTIONS ON
• B. SET TRANSACTION ISOLATION LEVEL SNAPSHOT
• C. SET IMPLICIT_TRANSACTIONS OFF
• D. SET TRANSACTION ISOLATION LEVEL SERIALIZABLE
• E. SET XACT_ABORT OFF
• F. SET XACT_ABORT ON

Answer: F

Explanation:
When SET XACT_ABORT is ON, if a Transact-SQL statement raises a run-time error, the entire transaction is terminated and rolled back.
Reference: SET XACT_ABORT (Transact-SQL) https://msdn.microsoft.com/en-us/library/ms188792.aspx

NEW QUESTION 9

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, RANK() OVER(ORDER BY AVG(Marks) DESC) AS Value FROM StudentMarksGROUP BY StudentCode
• B. SELECT Id, Name, Marks, DENSE_RANK() OVER(ORDER BY Marks DESC) AS RankFROM StudentMarks
• C. SELECT StudentCode as Code, DENSE_RANK() OVER(ORDER BY AVG(Marks) DESC) ASValueFROM StudentMarks GROUP BY StudentCode
• D. SELECT StudentCode as Code, NTILE(2) OVER(ORDER BY AVG(Marks) DESC) AS Value FROM StudentMarksGROUP BY StudentCode
• E. SELECT StudentCode AS Code, Marks AS Value FROM ( SELECT StudentCode, Marks AS Marks,RANK() OVER(PARTITION BY SubjectCode ORDER BY Marks ASC) AS RankFROM StudentMarks) tmp WHERE Rank = 1
• F. SELECT StudentCode AS Code, Marks AS Value FROM (SELECT StudentCode, Marks AS Marks,RANK() OVER(PARTITION BY SubjectCode ORDER BY Marks DESC) AS RankFROM StudentMarks) tmp WHERE Rank = 1
• G. SELECT StudentCode AS Code, Marks AS Value FROM ( SELECT StudentCode, Marks AS Marks,RANK() OVER(PARTITION BY StudentCode ORDER BY Marks ASC) AS RankFROM StudentMarks) tmp WHERE Rank = 1
• H. SELECT StudentCode AS Code, Marks AS Value FROM ( SELECT StudentCode, Marks AS Marks,RANXO OVER(PARTITION BY StudentCode ORDER BY Marks DESC) AS RankFROM StudentMarks) tmp WHERE Rank = 1

Answer: F

NEW QUESTION 10

You develop 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:
Results must not include customers who have not placed any orders.
Which Transact-SQL query should you use?

• A. SELECT CustomerName, OrderDate FROM CustomersLEFT OUTER JOIN OrdersON Customers.CustomerID = Orders.CustomerId
• B. SELECT CustomerName, OrderDate FROM CustomersRIGHT OUTER JOIN OrdersON Customers.CustomerID = Orders.CustomerId
• C. SELECT CustomerName, OrderDate FROM CustomersCROSS JOIN OrdersON Customers.CustomerId = Orders.CustomerId
• D. SELECT CustomerName, OrderDateFROM Customers JOIN OrdersON Customers.CustomerId = Orders.CustomerId

Answer: D

Explanation:
Reference: http://msdn.microsoft.com/en-us/library/ms177634.aspx

NEW QUESTION 11

You develop a Microsoft SQL Server database named ContosoDb. ContosoDb contains a table named Employess that was created by using the following Transact-SQL statement:

You need to create a view that allows the insertion of new records into the Employees table by using the view.

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

Answer: C

NEW QUESTION 12

You are a database developer of a Microsoft SQL Server 2012 database.
The database contains a table named Customers that has the following definition:

You need to ensure that the CustomerId column in the Orders table contains only values that exist in the CustomerId column of the Customer table.
Which Transact-SQL statement should you use?

• A. ALTER TABLE OrdersADD CONSTRAINT FX_Orders_CustomerID FOREIGN KEY (CustomerId) REFERENCESCustomer (CustomerId)
• B. ALTER TABLE CustomerADD CONSTRAINT FK_Customer_CustomerID FOREIGN KEY {CustomerID) REFERENCES Orders (CustomerId)
• C. ALTER TABLE OrdersADD CONSTRAINT CK_Orders_CustomerIDCHECK (CustomerId IN (SELECT CustomerId FROM Customer))
• D. ALTER TABLE Customer ADD OrderId INT NOT NULL;ALTER TABLE CustomerADD CONSTRAINT FK_Customer_OrderID FOREIGN KEY (OrderID) REFERENCES Orders (OrderID);
• E. ALTER TABLE OrdersADD CONSTRAINT PK Orders CustomerId PRIMARY KEY (CustomerID)

Answer: A

Explanation:
Reference: http://msdn.microsoft.com/en-us/library/ms189049.aspx

NEW QUESTION 13

You are developer for a Microsoft Azure SQL Database instance.
You are creating a new stored procedure. The procedure must perform the following tasks in this order:
1. Update a table named OrderHistory.
2. Delete rows from a table named Orders.
3. Delete rows from a table named Customers.
4. Insert rows into a table named ProcessHistory.
You need to ensure that the procedure meets the following requirements:
If either DELETE operation fails, the rest of operation must continue.
If either the UPDATE operation or the INSERT operation fails, the whole procedure should fail and no changes should be retained.
Which four Transact-SQL segments should you use to develop the solution? To answer, move the appropriate Transact-SQL segments from the list of Transact-SQL segments to the answer area and arrange them in the correct order.

• A. Mastered
• B. Not Mastered

Answer: A

Explanation:
When SET XACT_ABORT is ON, if a Transact-SQL statement raises a run-time error, the entire transaction is terminated and rolled back.
When SET XACT_ABORT is OFF, in some cases only the Transact-SQL statement that raised the error is rolled back and the transaction continues processing.
References:
https://docs.microsoft.com/en-us/sql/t-sql/statements/set-xact-abort-transact-sql?view=sql-server-2021

NEW QUESTION 14

You develop a database for a travel application. You need to design tables and other database objects. You create the Airline_Schedules table.
You need to store the departure and arrival dates and times of flights along with time zone information. What should you do?

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

Answer: I

Explanation:
Reference: http://msdn.microsoft.com/en-us/library/ff848733.aspx
Reference: http://msdn.microsoft.com/en-us/library/bb630289.aspx

NEW QUESTION 15

You use a Microsoft SQL Server 2012 database that contains two tables named SalesOrderHeader and SalesOrderDetail. The indexes on the tables are as shown in the exhibit. (Click the Exhibit button.)

You write the following Transact-SQL query:

You discover that the performance of the query is slow. Analysis of the query plan shows table scans where the estimated rows do not match the actual rows for SalesOrderHeader by using an unexpected index on SalesOrderDetail.
You need to improve the performance of the query. What should you do?

• A. Use a FORCESCAN hint in the query.
• B. Add a clustered index on SalesOrderId in SalesOrderHeader.
• C. Use a FORCESEEK hint in the query.
• D. Update statistics on SalesOrderId on both tables.

Answer: D

Explanation:
References: http://msdn.microsoft.com/en-us/library/ms187348.aspx

NEW QUESTION 16

You are maintaining a SQL Server database that uses the default settings. The database contains a table that is defined by the following Transact-SQL statement:

You must write a query that returns the AddressLine1, AddressLine2, and Region fields separated by carriage returns. You must return an empty string for any null values.

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

Answer: A

Explanation:
Char(13) is a carriage return.
Use the IIF construct to return an empty string for NULL values of the Adressline2 column.
IIF returns one of two values, depending on whether theBoolean expression evaluates to true or false in SQL Server.
References: https://msdn.microsoft.com/en-us/library/hh213574.aspx

NEW QUESTION 17

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 ensure that the following requirements are met:
Students must be ranked based on their average marks.
If one or more students have the same average, the same rank must be given to these students.
Consecutive ranks must be skipped when the same rank is assigned.
Which Transact-SQL query should you use?

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

Answer: A

Explanation:
Reference: http://msdn.microsoft.com/en-us/library/ms189798.aspx

NEW QUESTION 18

You generate a daily report according to the following query:

You need to improve the performance of the query. What should you do?

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

Answer: A

NEW QUESTION 19

You administer a Microsoft SQL Server database named Sales. The database is 3 terabytes in size. The Sales database is configured as shown in the following table.

You discover that Sales_2.ndf is corrupt. You need to recover the corrupted data in the minimum amount of time. What should you do?

• A. Perform a file restore.
• B. Perform a transaction log restore.
• C. Perform a restore from a full backup.
• D. Perform a filegroup restore.

Answer: A

NEW QUESTION 20

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 DELETE ON Purchases.Suppliers TO ContosoUser
• B. CREATE PROCEDURE Purchases.PurgeInactiveSuppliers WITH EXECUTE AS USER = 'dbo'ASDELETE FROM Purchases.Suppliers WHERE IsActive = 0 GOGRANT EXECUTE ON Purchases.PurgelnactiveSuppliers TO ContosoUser
• C. GRANT SELECT ON Purchases.Suppliers TO ContosoUser
• D. CREATE PROCEDURE Purchases.PurgeInactiveSuppliers ASDELETE FROM Purchases.Suppliers WHERE IsActive = 0 GOGRANT EXECUTE ON Purchases.PurgeInactiveSuppliers TO ContosoUser

Answer: D

Explanation:
Reference: http://msdn.microsoft.com/en-us/library/ms188354.aspx
Reference: http://msdn.microsoft.com/en-us/library/ms187926.aspx

NEW QUESTION 21

You use Microsoft SQL Server 2012 to develop a database application. You need to create an object that meets the following requirements:
Takes an input variable
Returns a table of values
Cannot be referenced within a view Which object should you use?

• A. Scalar-valued function
• B. Inline function
• C. User-defined data type
• D. Stored procedure

Answer: D

NEW QUESTION 22

You administer a Microsoft SQL Server 2012 server. You plan to deploy new features to an application. You need to evaluate existing and potential clustered and non-clustered indexes that will improve performance.
What should you do?

• A. Query the sys.dm_db_index_usage_stats DMV.
• B. Query the sys.dm_db_missing_index_details DMV.
• C. Use the Database Engine Tuning Advisor.
• D. Query the sys.dm_db_missing_index_columns DMV.

Answer: C

NEW QUESTION 23

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

You need to create a query for a report. The query must meet the following requirements:
Return the last name of the customer who placed the order.
Return the most recent order date for each customer.
Group the results by CustomerID.
Display the most recent OrderDate first.
The solution must support the ANSI SQL-99 standard and must not use table or column aliases.
Part of the correct Transact-SQL has been provided in the answer area below. Enter the Transact-SQL in the answer area that resolves the problem and meets the stated goals or requirements. You can add Transact-SQL within the Transact-SQL segment that has been provided as well as below it.



Use the ‘Check Syntax’ button to verify your work. Any syntax or spelling errors will be reported by line and character position.

• A. Mastered
• B. Not Mastered

Answer: A

Explanation:
1. SELECT LastName,
2 MAX(OrderDate) AS MostRecentOrderDate
3 FROM Customers INNER JOIN Orders ON Customers.CustomerID = Orders.CustomerID 4 GROUP BY CustomerID
5 ORDER BY OrderDate DESC
On line 3 add Customers INNER JOIN Orders ON Customers.CustomerID = Orders.CustomerID On line 4 add CustomerID
On line 5 add OrderDate DESC
References: https://technet.microsoft.com/en-us/library/ms190014(v=sql.105).aspx

NEW QUESTION 24

You need to create a table named OrderDetails that contains the following columns: LineItemTotal, ListPrice, and Quantity. LineItemTotal stores the product of ListPrice and Quantity for each row.
Construct the Transact-SQL for creating the table by using the following guidelines:
Ensure that the calculation for a line item total is not run every time the table is queried.
Do not use any object delimiters.
Store LineItemTotal as the last column in the table.
Part of the correct Transact-SQL has been provided in the answer area below. Enter the Transact-SQL in the answer area that resolves the problem and meets the stated goals or requirements. You can add Transact-SQL within the Transact-SQL segment that has been provided as well as below it.



Use the ‘Check Syntax’ button to verify your work. Any syntax or spelling errors will be reported by line and character position.

• A. Mastered
• B. Not Mastered

Answer: A

Explanation:
1. CREATE TABLE OrderDetails 2. (
3. ListPrice money NOT NULL,
4. Quantity int NOT NULL,
5. LineItemTotal AS (ListPrice * Quantity) PERSISTED 6. )
To line 5 add: LineItemTotal AS (ListPrice * Quantity) PERSISTED

NEW QUESTION 25

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.CustomerIDORDER BY COUNT(o.OrderAmount) ASC) AS Rnk FROM Customer cINNER JOIN Orders oON c.CustomerID = o.CustomerIDGROUP BY c.CustomerID, c.CustomerName,
• B. ShippingCountry) cs WHERE Rnk = 1
• C. SELECT c.CustomerID, c.CustomerName, o.ShippingCountry FROM Customer cINNER JOIN(SELECT CustomerID, ShippingCountry, COUNT(OrderAmount) AS OrderAmount FROM OrdersGROUP BY CustomerID, ShippingCountry) AS o ON c.CustomerID = o.CustomerIDORDER BY OrderAmount DESC
• D. SELECT CustomerID, CustomerName, ShippingCountry FROM(SELECT c.CustomerID, c.CustomerName,
• E. ShippingCountry,RANK() OVER (PARTITION BY
• F. CustomerIDORDER BY
• G. OrderAmount DESC) AS Rnk FROM Customer cINNER JOIN Orders oON c.CustomerID = o.CustomerIDGROUP BY c.CustomerID, c.CustomerName,
• H. ShippingCountry) cs WHERE Rnk = 1
• I. SELECT c.CustomerID, c.CustomerName, o.ShippingCountry FROM Customer cINNER JOIN(SELECT CustomerID, ShippingCountry,RANK() OVER (PARTITION BY CustomerIDORDER BY COUNT(OrderAmount) DESC) AS Rnk FROM OrdersGROUP BY CustomerID, ShippingCountry) AS o ON c.CustomerID = o.CustomerIDWhere o.Rnk = 1

Answer: C

NEW QUESTION 26

You are developing a database that will contain price information.
You need to store the prices that include a fixed precision and a scale of six digits. Which data type should you use?

• A. Float
• B. Money
• C. Smallmoney
• D. Decimal

Answer: D

Explanation:
Decimal is the only one in the list that can give a fixed precision and scale. Reference: http://msdn.microsoft.com/en-us/library/ms187746.aspx

NEW QUESTION 27
......