Views and inline table valued functions -section 5 (final)

Note: T-SQL code is included for reference only not to be edited.
Edit starts here:
----------------------------------------------------------
A view is some saved TSQL to create a virtual table based on the saved definition. The input column names used in views must exist in the other tables in the database. The view, saved object, in the database is reusable whenever needed provided the users have necessary permission to use them. The result set return by views are relational* thus cannot have Order By clause to sort rows, There exception to use Order By applies when part of Row_Number(), Rank(), etc. used in TSQL code to construct the view. Additionally, unlike functions, views do not accept parameters.
 

--Problem statement: Write a view to show products with the same ListPrice in  more than three products:

create VIEW dbo.ProductsSameListPriceOverTwoCounts
AS                                                   -- View, example 5.1
select ProductID
     , ProductName
     , UnitPrice
from (
  select *, cnt = count(*) over (partition by unitprice)
  from dbo.Products
) d where cnt > 3;
 
-- Test:
select * from ProductsSameListPriceOverTwoCounts
--Returns:
ProductID  ProductName                   UnitPrice

67     Laughing Lumberjack Lager          14.00
42     Singaporean Hokkien Fried Mee      14.00
34     Sasquatch Ale                      14.00
25     NuNuCa Nuß-Nougat-Creme            14.00
1      Chai                               18.00
35     Steeleye Stout                     18.00
39     Chartreuse verte                   18.00
76     Lakkalikööri                       18.00

Select allOpen in new window

* The term relation often is mixed up with term relationship which has to do with joining primary keys and foreign keys. However in standard SQL “… a relation, as originally defined by E. F. Codd, is a set of tuples (d1, d2, ..., dn), where each element dj is a member of Dj, a data domain”. SQL Server, Oracle, MySQL etc. are different flavors of the standard SQL These languages use terms rows and columns in place tuples and domains.  

Inline table-valued functions are like views but accept parameters and you may include Order By clause in its definition. Unlike the previous example where constant 3 was hard coded, with inline table-valued function we can include an input parameters like @RepeadedListPriceCoun to enable user to use different values.

-- Problem statement: Write an Inline table-valued function to show products with the same ListPrice 
-- if equal or over RepeadedListPriceCoun provided:

Create Function dbo.fnProductsSameListPriceOver(
@RepeadedListPriceCount As Int) Returns Table
AS                                                   -- Inline table-valued function , example 5.2
Return
select ProductID
     , ProductName
     , UnitPrice
From (
  Select *, cnt = Count(*) over (Partition Yy unitprice) 
  From dbo.Products
) d Where cnt >= @RepeadedListPriceCount;

-- Test:
Select * From fnProductsSameListPriceOver(4);

-- Returns 25 records:
ProductID  ProductName             UnitPrice
47	Zaanse koeken	                9.50
45	Rogede sild	                9.50
21	Sir Rodney's Scones	       10.00
3	Aniseed Syrup	               10.00
74	Longlife Tofu	               10.00
.
.
56	Gnocchi di nonna Alice	       38.00
12	Queso Manchego La Pastora      38.00
27	Schoggi Schokolade	       43.90
63	Vegie-spread	               43.90

Select allOpen in new window

Summary: The readers of this article fall in three categories of pro, intermediate, or beginner. If you are pro, any due correction and improvement you may have greatly appreciated. For intermediate-level professionals, I hope this presentation has made it a bit easier to wrap your mind around the concept of subqueries by recognizing 5 distinct mode of subquery applications 1) Self-contained, 2) Correlated, 3) Derived table, 4) CTEs, and 5) View & inline table-valued Functions. My recommendation for the beginner SQL enthusiasts is to read the SQL related questions present at EE (answered or not, without going through solution provided by others) to come up with your own solution. This is a good metal gym for your SQL brain muscles. In a matter of a few months you will see how easily you are able to suggest a good solution for questions presently are mind boggling.
Edit ends here:
----------------------------------------------------------
also see: https://www.experts-exchange.com/articles/18366/Subqueries-at-a-glance.html