derived table -section 3

Note: T-SQL code is included for reference only not to be edited.
Edit starts here:
----------------------------------------------------------
The derived table is a subquery in a FROM clause. The FROM clause must have an aliased name. It is called a derived table because the fields in the outer SELECT clause query come from this named derived table. There are Inline and External derived table subqueries:

In an inline single derived table, it is mandatory to give an alias to the table in the FROM clause.
In an external single derived table, it is mandatory to list the field name inside () after the FROM table alias name. For T-SQL to build temp table #t see https://www.experts-exchange.com/questions/28688390/How-to-query-using-derived-table-and-multi-CTEs.html

Product  ProdType                                    -- Inline single derived table subquery, example 3.1
A	      x
B	      x
B	      xx
C	      x
D	      x
D	      xx
E	      x 
-- Problem statement: Starting with above data, produce the following (ignore single Prod. Types):
ProdType  ProdType
B	      x
B	      xx
D	      x
D	      xx

Select Product, ProdType
from (
  select *, count(*) over (partition by Product) cnt from #t
) d where cnt > 1;

-- Note: This example will be used later in discussing CTEs.

Select allOpen in new window

10a.pngThe obvious limitation with derived tables is that we cannot have multiple table expressions in a FROM clause whereas with CTEs discussed later we can. Of course we can nest derived to include multiple derived tables but nesting complicates the coding and makes it prone to syntax errors.  

-- Multiple derived tables syntax 
-- (for reasons stated this is possible but not used):

SELECT f1, f2, ...                       
FROM (SELECT ...  
      FROM (SELECT ...  
            FROM Ta 
            WHERE ...) AS Tb
      WHERE ...) AS Tc
WHERE ...) As Td (f1, f2, ...);

Select allOpen in new window

Edit ends here:
----------------------------------------------------------
also see: https://www.experts-exchange.com/articles/18366/Subqueries-at-a-glance.html

Note, Questions on the remaining sections will be added later.