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, which must meet the
following requirements:
Accept the @T integer parameter.
Use one-part names to reference columns.
Filter the query results by SalesTerritoryID.
Return the columns in the same order as the order used in OrdersByTerritoryView.
Which code segment should you use?
To answer, type the correct code in the answer area.
A.
Explanation:
CREATE FUNCTION Sales.fn_OrdersByTerritory (@T int)
RETURNS TABLE
AS
RETURN
(
SELECT OrderID,OrderDate,SalesTerrirotyID,TotalDue
FROM Sales.OrdersByTerritory
WHERE SalesTerritoryID = @T
)
Hi,
I think it’s better group by the result on CustomerId, because two customers could have the same last name.
Select LastName, Max(OrderDate) as MostRecentOrderDate
From Customer as C Inner Join Orders as O
ON C.CustomerId = O.CustomerId
Group by C.CustomerId
Order by MostRecentOrderDate DESC
I’d assume it has to be “dbo.Customers” instead of “Sales.Customers” because in the exhibit there is only said “Customers” and not “Customers (Sales)” as it would be if the Customers table resided in the Sales schema.
The view must be created in the Sales schema.
“Group by CustomerId” is not correct, this would result in an error Column ‘Customers.LastName’ is invalid in the select list because it is not contained in either an aggregate function or the GROUP BY clause.message”
So the submitted answer is (almost) correct, beside one little mistake: the ORDER BY clause (“ORDER BY O.OrderDate”) would also result in such an error message thats why it has to be “ORDER BY Max(OrderDate) DESC” or (better) “Order by MostRecentOrderDate DESC” (as it is in the first reply).
The query submitted as answer fulfills the requirements but I don’t understand why the “PARTITION BY ProductCatalog.UnitPrice” is necessary?
I would use the following query:
SELECT ProductCatalog.CatID, ProductCatalog.CatName, ProductCatalog.ProductID,
ProductCatalog.ProdName, ProductCatalog.UnitPrice,
RANK() OVER (ORDER BY ProductCatalog.UnitPrice DESC) AS PriceRank
FROM Sales.ProductCatalog
ORDER BY ProductCatalog.UnitPrice DESC
if you don’t partition by price,the same price will has difference rank.
so, i guess this is why use ‘partition by’ is necessary.
sorry,i tested it,actually you are right….the ‘partition by’ is unnecessary
I have the exact same problem with this answer.
It doesn’t make sense at all.
If you partition on UnitPrice, all the UnitPrices within a single partition will be the same! No use Ranking them.
Leaving out the Partition By clause will rank the prices throughout the table i would think and would make better sense.
A better scenario would be if you would want to know how the UnitPrices would rank within the Categories: OVER (PARTITION BY ProductCatalog.CatID ORDER BY ProductCatalog.UnitPrice DESC)
Hi! I’ve been following your blog for a long time now and
finally got the bravery to go ahead and give you a shout out from Huffman Texas!
Just wanted to mention keep up the great job! Quest
Bars blogesaurus
I’ll right away clutch your rss as I can not in finding your e-mail subscription hyperlink or newsletter service.
Do you have any? Kindly allow me understand so that I may just subscribe.
Thanks. Quest Bars blogesaurus
Correct Answer:
SELECT c.LastName
, Max(o.OrderDate) MostRecentOrderDate
FROM Sales.Orders o
JOIN Sales.Customer c ON o.CustomerID = c.CustomerID
Group by c.LastName
Order by MostRecentOrderDate DESC
The question is to select the last name.
I’m particulary agree with Naser. We cannot group by the ‘LastName’. I think the solution should be like grouping by two fields: ‘CustomerId’ and ‘LastName’.
Please correct me if needed.
i think u are right
PARTITION BY is not necessary and also ORDER BY is redundant becuase RANK will sort the rows .
I think the right answer should be:
SELECT C.CustomerID, C.LastName, MAX(O.OrderDate) as MostRecentOrderDate
FROM Sales.Orders O
INNER JOIN Sales.Customers C on C.CustomerID = O.CustomerID
GROUP BY C.CustomerID
ORDER BY MostRecentOrderDate DESC
The question says to retrieve LastName and MostRecentOrderDate, but it doesn’t say, the query ONLY has to retrieve these two columns. So, I maybe the correct answer should include the CustomerID column.
I would agree with David.
We need to group by CustomerID and LastName, to avoi LastName duplicates.
PLUS
we could order by either alias or the sum() expression – both work fine
Brackets are not necessary here
IMO:
select
max(C.LastName) as LastName,
max(aprod.dtInsertDate) as MostRecentOrderDate
from Orders as O
inner join Customers as C on (O.CustomerID=C.CustomerID)
group by O.CustomerId
order by MostRecentOrderDate desc
go
select
I agree with the fact that may be multiple LastName with differente CustomerID due the absence of unique constraint, so the proposed answer isn’t correct.
The recommendation is to use (SQL) standard if equivalent standard is available:
CREATE PROCEDURE usp_Customers @Count int
AS
SELECT Customers.LastName
FROM Customers
ORDER BY Customers.LastName
OFFSET 0 ROWS FETCH NEXT @Count ROWS ONLY
The ‘standard’ syntax contains brackets bcos this is an inline table-valued function. This function type has no FUNCTION BODY; instead the function-body is a SELECT statement that returns a TABLE value (not a scalar value).
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, C.CustomerID
ORDER BY MostRecentOrderDate DESC;
–CustomerID serves as tie-breaker for common lastname
@Akbar and Luiz:
Both tables are not from SALES schema. Maybe you could have implied they’re from ‘dbo’ schema, as in “FROM dbo.Customers AS c INNER JOIN dbo.Orders AS o”
SELECT c.CustomerID, c.LastName, MAX(o.OrderDate) AS MostRecentOrderDate
FROM Sales.Customers c
INNER JOIN Sales.Orders o
ON c.CustomerID = o.CustomerID
GROUP BY c.CustomerID, c.LastName
ORDER BY o.OrderDate DESC
I agree with David. And if CustomerID appeared FOLLOWING ‘SELECT’, it has to be in ‘GROUP BY’. So we need to group by c.CustomerID and c.LastName together.
The two ORDER BY UnitPrice has different meaning. We need to keep them all.
Just delete ‘PARTITION BY UnitPrice’. It’s not make sense.
answer is
SELECT SALESTERRITORYID,
PRODUCTID,
AVG(UNITPRICE),
MAX(ORDERQTY),
MAX(DISCOUNTAMOUNT)
FROM SALES.DETAILS
GROUP BY SALESTERRITORYID,PRODUCTID
–Sort aggregates by SalesTerritoryID, and then by ProductID.
–Order the results in descending order from SalesTerritoryID to ProductID.
ORDER BY SALESTERRITORYID DESC, PRODUCTID DESC
CREATE FUNCTION Sales.fn_OrdersByTerritory( @T int )
Returns Table
as
Select OrderID,OrderDate,SalesTerritoryID,TotalDue
From Sales.OrdersTerritory
where SalesTerritoryID = @T
Alter Table
ADD ListItemTotal as (ListPrice * ItemStore)
Create View Sales.uv_CustomerFullName
WITH SCHEMA BINDING
Select Firstname,lastname from
Sales.Customers
Select OrderID, SUM(ExtendedAmount) as TotalSales
from sales.details
group by OrderID
Order by OrderiID
Declare @XMl1 XML(Sales.InvoiceSchema)
Select ProductCatalog.CatID, ProductCatalog.CatName, ProductCatalog..ProductID,ProductCatalog..ProductName,ProductCatalog..Unit Price, from sales.productCatalog,
RANK() OVER (ORDER BY ProductCatalog.UnitPrice DESC) AS PriceRank
From SAles.ProductCatalog
order by productCatalog.unitprice desc
CREATE TABLE Sales.OrderDetails ( ListPrice money not null,
Quantity int not null, LineItemTotal as (ListPrice * Quantity) PERSISTED)
CREATE PROCEDURE usp_Customers @Count int
AS
SELECT TOP(@Count) Customers.LastName
FROM Customers
ORDER BY Customers.LastName
why top?
Here are a few of the web sites we advise for our visitors.
Why not just alter the OrderDetails table? like Unar said?
WEll it does say you need to create perhaps that is why?
I just thought that the table has been already created xD
because the question said:
You deploy a new server that has SQL Server 2012 installed. You need to create a table named Sales.
OrderDetails on the new server.
so, i think we need to create a new table on the new server.
The info talked about in the write-up are a few of the very best accessible.
Here are several of the web-sites we advise for our visitors
check beneath, are some absolutely unrelated sites to ours, nevertheless, they are most trustworthy sources that we use
The details talked about inside the report are a number of the best readily available
very few sites that happen to become detailed beneath, from our point of view are undoubtedly very well worth checking out
check beneath, are some absolutely unrelated internet sites to ours, nevertheless, they may be most trustworthy sources that we use
Answer has to be like this.
SELECT ProductCatalog.CatID,
ProductCatalog.CatName,
ProductCatalog.ProductID,
ProductCatalog.ProdName,
ProductCatalog.UnitPrice,
RANK() OVER (ORDER BY ProductCatalog.UnitPrice DESC) AS PriceRank
FROM Sales.ProductCatalog
ORDER BY ProductCatalog.UnitPrice DESC
we prefer to honor many other world-wide-web websites around the internet, even though they arent linked to us, by linking to them. Underneath are some webpages really worth checking out
Wonderful story, reckoned we could combine a number of unrelated information, nonetheless truly really worth taking a look, whoa did 1 study about Mid East has got a lot more problerms at the same time
Here are several of the web-sites we suggest for our visitors
I have a doubt! Do we have to reference Column name too with the two part reference? Or just the table name?
New 70-461 Exam Questions and Answers (5/May/2017 Updated):
NEW QUESTION 169
You have a database that contains a table named Customer. The customer table contains a column named LastName that has a column definition of varchar(50). An application named App1 reads from the table frequently. You need to change the column definition to nvarchar(100). The solution must minimize the amount of time it takes for App1 to read the data. Which statement should you execute?
Image URL: passleader.org/wp-content/uploads/2017/05/passleader-70-461-dumps-1691.png
Answer: C
Explanation:
To change the data type of a column in a SQL Server (or Microsoft access) table, use the following syntax:
ALTER TABLE table_name
ALTER COLUMN column_name datatype
NEW QUESTION 170
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:
Image URL: passleader.org/wp-content/uploads/2017/05/passleader-70-461-dumps-1701.png
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.
NEW QUESTION 171
A table named Profits stores the total profit made each year within a territory. The Profits table has columns named Territory, Year, and Profit. You need to create a report that displays the profits made by each territory for each year and its previous year. Which Transact-SQL query should you use?
A. SELECT Territory, Year, Profit, LEAD(Profit, 1, 0) OVER (PARTITION BY Territory ORDER BY Year) AS PreviousYearProfit FROM Profits
B. SELECT Territory, Year, Profit, LAG(Profit, 1, 0) OVER (PARTITION BY Year ORDER BY Territory) AS PreviousYearProfit FROM Profits
C. SELECT Territory, Year, Profit, LAG(Profit, 1, 0) OVER (PARTITION BY Territory ORDER BY Year) AS PreviousYearProfit FROM Profits
D. SELECT Territory, Year, Profit, LEAD(Profit, 1, 0) OVER (PARTITION BY Year ORDER BY Territory) AS PreviousYearProfit FROM Profits
Answer: C
Explanation:
LAG accesses data from a previous row in the same result set without the use of a self-join in SQL Server 2016. LAG provides access to a row at a given physical offset that comes before the current row. Usethis analytic function in a SELECT statement to compare values in the current row with values in a previous row. Use ORDER BY Year, not ORDER BY Territory.
Example: the following example uses the LAG function to return the difference in sales quotas fora specific employee over previous years. Notice that because there is no lag value available for the first row, the default of zero (0) is returned.
USE AdventureWorks2012;
GO
SELECT BusinessEntityID, YEAR(QuotaDate) AS SalesYear, SalesQuota AS CurrentQuota,
LAG(SalesQuota, 1,0) OVER (ORDER BY YEAR(QuotaDate)) AS PreviousQuota
FROM Sales.SalesPersonQuotaHistory
WHERE BusinessEntityID = 275 and YEAR(QuotaDate) IN (‘2005′,’2006’);
Incorrect Answers:
A, D: LEAD accesses data from a subsequent row in the same result set without the use of a self-join in SQL Server 2016. LEAD provides access to a row at a given physical offset that follows the current row. Use this analytic function in a SELECT statement to compare values in the current row with values in a following row.
B: Use ORDER BY Year, not ORDER BY Territory.
NEW QUESTION 172
Your database contains a table named Products that has columns named ProductID and Name. You want to write a query that retrieves data from the Products table sorted by Name listing 15 rows at a time. You need to view rows 31 through 45. Which Transact-SQL query should you create?
Image URL: passleader.org/wp-content/uploads/2017/05/passleader-70-461-dumps-1721.png
Answer: C
Explanation:
The OFFSET-FETCH clause provides you with an option to fetch only a window or page of results from the result set. OFFSET-FETCH can be used only with the ORDER BY clause.
Example: skip first 10 rows from the sorted resultset and return next 5 rows.
SELECT First Name + ‘ ‘ + Last Name FROM Employees ORDER BY First Name OFFSET 10 ROWS FETCH NEXT 5 ROWS ONLY;
NEW QUESTION 173
A database named AdventureWorks contains two tables named Production.Product and Sales.SalesOrderDetail. The tables contain data on the available products and a detailed order history. The Production.Product table contains the following two columns:
– ProductID
– Name
The Sales.SalesOrderDetail table contains the following three columns:
– SalesOrderID
– ProductID
– OrderQty
You need to create a query listing all of the products that were never ordered. Which statements should you execute?
Image URL: passleader.org/wp-content/uploads/2017/05/passleader-70-461-dumps-1731.png
Answer: A
Explanation:
EXCEPT and INTERSECT returns distinct rows by comparing the results of two queries. EXCEPT returns distinct rows from the left input query that aren’t output by the right input query. Column names or aliases in ORDER BY clauses must reference column names returned by the left-side query.
Example: the following query returns any distinct values from the query to the left of the EXCEPT operator that are not also found on the right query.
— Uses AdventureWorks
SELECT CustomerKey
FROM FactInternetSales
EXCEPT
SELECT CustomerKey
FROM DimCustomer
WHERE DimCustomer.Gender = ‘F’
ORDER BY CustomerKey;
–Result: 9351 Rows (Sales to customers that are not female.)
Incorrect Answers:
B: Column names or aliases in ORDER BY clauses must reference column names returned by the left-side query.
C, D: INTERSECT returns distinct rows that are output by both the left and right input queries operator.
NEW QUESTION 174
You plan to write a query for a new business report that will contain several nested queries. You need to ensure that a nested query can call a table-valued function for each row in the main query. Which query operator should you use in the nested query?
A. CROSS APPLY
B. INNER JOIN
C. OUTER JOIN
D. PIVOT
Answer: A
Explanation:
The APPLY operator allows you to invoke a table-valued function for each row returned by an outer table expression of a query. The table-valued function acts as the right input and the outer table expression acts as the left input. The right input is evaluated for each row from the left input and the rows produced are combined for the final output. The list of columns produced by the APPLY operator is the set of columns in the left input followed by the list of columns returned by the right input. There are two forms of APPLY: CROSS APPLY and OUTER APPLY. CROSSAPPLY returns only rows from the outer table that produce a result set from the table-valued function. OUTER APPLY returns both rows that produce a result set, and rows that do not, with NULL values in the columns produced by the table-valued function.
NEW QUESTION 175
You are designing a table for a SQL Server database. The table uses horizontal partitioning. You have the following requirements:
– Each record in the table requires a unique key.
– You must minimize table fragmentation as the table grows.
You need to choose the appropriate data type for the key value. What should you do?
A. Use the NEWID function to create a unique identifier.
B. Use the NEWSEQUENTIALID function to create a unique identifier.
C. Generate a random value that uses the bigint datatype.
D. Generate a random value that uses the char(16) data type.
Answer: B
Explanation:
Horizontal partitioning divides a table into multiple tables. Each table then contains the same number of columns, but fewer rows. For example, a table that contains 1 billion rows could be partitioned horizontally into 12 tables, with each smaller table representing one month of data for a specific year. Any queries requiring data for a specific month only reference the appropriate table.
NEWSEQUENTIALID creates a GUID that is greater than any GUID previously generated by this function on a specified computer since Windows was started. After restarting Windows, the GUID can start again from a lower range, but is still globally unique. When a GUID column is used as a row identifier, using NEWSEQUENTIALID can be faster than using the NEWID function. This is because the NEWID function causes random activity and uses fewer cached data pages. Using NEWSEQUENTIALID also helps to completely fill the data and index pages.
NEW QUESTION 176
You are a database developer of a Microsoft SQL Server database. You are designing a table that will store Customer data from different sources. The table will include a column that contains the CustomerID from the source system and a column that contains the SourceID. A sample of this data is as shown in the following table.
Image URL: passleader.org/wp-content/uploads/2017/05/passleader-70-461-dumps-1761.jpg
You need to ensure that the table has no duplicate CustomerID within a SourceID. You also need to ensure that the data in the table is in the order of SourceID and then CustomerID. Which Transact- SQL statement should you use?
A. CREATE TABLE Customer
(SourceID int NOT NULL,
CustomerID int NOT NULL,
CustomerName varchar(255) NOT NULL
CONSTRAINT UQ_Customer UNIQUE
(SourceID, CustomerID));
B. CREATE TABLE Customer
(SourceID int NOT NULL UNIQUE,
CustomerID int NOT NULL UNIQUE,
CustomerName varchar(255) NOT NULL);
C. CREATE TABLE Customer
(SourceID int NOT NULL PRIMARY KEY CLUSTERED,
CustomerID int NOT NULL UNIQUE,
CustomerName varchar(255) NOT NULL);
D. CREATE TABLE Customer
(SourceID int NOT NULL,
CustomerID int NOT NULL,
CustomerName varchar(255) NOT NULL,
CONSTRAINT PK_Customer PRIMARY KEY CLUSTERED
(SourceID, CustomerID));
Answer: D
Explanation:
A PRIMARY KEY is a constraint that enforces entity integrity for a specified column or columns by using a unique index. Only one PRIMARY KEY constraint can be created for each table. We need to use both SourceID and CustomerID, in that order, in the PRIMARY KEY constraint.
NEW QUESTION 177
Drag and Drop Question
You maintain a SQL Server database that is used by a retailer to track sales and salespeople. The database includes two tables and two triggers that is defined by the following Transact-SQL statements:
Image URL: passleader.org/wp-content/uploads/2017/05/passleader-70-461-dumps-1771.jpg
During days with a large sales volume, some new sales transaction fail and report the following error:
Arithmetic overflow error converting expression to data type int.
You need to ensure that the two triggers are applied once per sale, and that they do not interfere with each other. How should you complete the relevant Transact-SQL statement? To answer, drag the appropriate Transact-SQL segments to the correct location or locations. Each Transact-SQL segment may be used once, more than once, or not at all. You may need to drag the split bar between panes or scroll to view content.
Image URL: passleader.org/wp-content/uploads/2017/05/passleader-70-461-dumps-1772.jpg
Answer:
Image URL: passleader.org/wp-content/uploads/2017/05/passleader-70-461-dumps-1773.png
Explanation:
Box 1: OFF
Box 2: 0
Only direct recursion of AFTER triggers is prevented when the RECURSIVE_TRIGGERS database option is set to OFF. To disable indirect recursion of AFTER triggers, also set the nested triggers server option to 0.
Note:
Both DML and DDL triggers are nested whena trigger performs an action that initiates another trigger. These actions can initiate other triggers, and so on. DML and DDL triggers can be nested up to 32 levels. You can control whether AFTER triggers can be nested through the nested triggers server configuration option. If nested triggers are allowed and a trigger in the chain starts an infinite loop, the nesting level is exceeded and the trigger terminates.
NEW QUESTION 178
……
P.S. You Can Get The Newest 70-461 Dumps In PDF And VCE From — http://www.passleader.com/70-461.html (200q VCE and PDF)
Good Luck!
By the way, part of that new 200Q 70-461 dumps are available here:
https://drive.google.com/open?id=0B-ob6L_QjGLpfnJldlZxTklTaHM0akpJUzhja2pETHJOS0owMzd4eVk1UTVNQUpvdlVxVWM
Best Regards!
The question is very confusing as it also stated “Return the most recent orders first”. Does this mean the result should contain orderid ( id does say “return orders” after all)??
— Naser is correct, but has supplied invalid code
— Unless the Customer LastNames are unique, the code would
— return incorrect values (Miss Duplicate Customer Names)
— Please use below for proof
— This Creates tables and records to prove the point
CREATE TABLE TestCustomer
(
CustomerID INT NOT NULL,
LastName varchar(100)
)
CREATE TABLE TestOrders
(
OrderID INT NOT NULL,
OrderDate datetime NOT NULL,
CustomerID INT NOT NULL
)
INSERT INTO TestCustomer (CustomerID, LastName) VALUES(1,’Smith’)
INSERT INTO TestCustomer (CustomerID, LastName) VALUES(2,’Jones’)
INSERT INTO TestCustomer (CustomerID, LastName) VALUES(3,’Smith’)
–Add 3 orders dated yesterday
INSERT INTO TestOrders (OrderID, OrderDate, CustomerID) VALUES(1,Getdate()-1,1)
INSERT INTO TestOrders (OrderID, OrderDate, CustomerID) VALUES(2,Getdate()-1,2)
INSERT INTO TestOrders (OrderID, OrderDate, CustomerID) VALUES(3,Getdate()-1,3)
–Add 3 orders dated today
INSERT INTO TestOrders (OrderID, OrderDate, CustomerID) VALUES(1,Getdate(),1)
INSERT INTO TestOrders (OrderID, OrderDate, CustomerID) VALUES(2,Getdate(),2)
INSERT INTO TestOrders (OrderID, OrderDate, CustomerID) VALUES(3,Getdate(),3)
SELECT
C.LastName,
Max(O.OrderDate) AS MostRecentOrderDate
FROM TestCustomer AS C Inner Join TestOrders AS O
ON C.CustomerId = O.CustomerId
GROUP BY C.LastName
ORDER BY MostRecentOrderDate DESC
–Result
–MostRecentOrderDate for One of the “Smith” Customers is missing
–1 Smith 2017-08-23 22:52:34.340
–2 Jones 2017-08-23 22:52:34.337
SELECT
C.LastName,
Max(O.OrderDate) AS MostRecentOrderDate
FROM TestCustomer AS C Inner Join TestOrders AS O
ON C.CustomerId = O.CustomerId
GROUP BY C.CustomerID
ORDER BY MostRecentOrderDate DESC
–Result
–Msg 8120, Level 16, State 1, Line 2
–Column ‘TestCustomer.LastName’ is invalid in the select list because it is not contained in either an aggregate function or the GROUP BY clause.
SELECT
C.CustomerID,
C.LastName,
Max(O.OrderDate) AS MostRecentOrderDate
FROM TestCustomer AS C Inner Join TestOrders AS O
ON C.CustomerId = O.CustomerId
GROUP BY C.CustomerID, C.LastName
ORDER BY MostRecentOrderDate DESC
–Result
–All Customers with all most Recent Order only are displayed as requested
P.S. You can remove C.CustomerID from the SELECT statement as it is in the GROUP BY, but in the real world you would not know which customer this was. The question does not say what you “shouldn’t” retrieve, just what you must retrieve. Therefore I have included it in the answer to address this shortcoming.
P.P.S use the DROP commands to cleanup after the test…
DROP TABLE TestCustomer
DROP TABLE TestOrders
CREATE PROCEDURE usp_Customers
@Count INT
AS
SELECT COUNT(@Count) ,Customers.LastName
FROM Customers
ORDER BY Customers.LastName
Why do we need to put ORDER BY ? I don’t see the relevance according to the question.