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

Open 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

Open 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: http://www.experts-exchange.com/articles/18366/Subqueries-at-a-glance.html
LVL 34
Mike EghtebasDatabase and Application DeveloperAsked:
Who is Participating?
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

Mark BullockQA Engineer IIICommented:
Some suggestions:
1. Change metal to mental
2. Change questions presently to questions which presently
3. Change ", There" to  ". The"
0

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
Mike EghtebasDatabase and Application DeveloperAuthor Commented:
Thank you.
0
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Proofreading

From novice to tech pro — start learning today.

Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.