CREATE VIEW country2 AS SELECT co.Code, UPPER(co.Name) AS UpperName, COUNT(cl.Language) AS OfficialLanguages FROM country AS co JOIN countrylanguage AS cl ON cl.CountryCode = co.Code WHERE cl.isOfficial = ’T’ GROUP BY co.Code; 

This definition is valid because: 

{co.Code} -> {co.*} 

In the view result, the first selected column is co.Code , which is also the group column and thus determines all other selected expressions: 

{country2.Code} -> {country2.*} 

MySQL understands this and uses this information, as described following. 

This query displays countries, how many different official languages they have, and how many cities they have, by joining the view with the city  table: 

SELECT co2.Code, co2.UpperName, co2.OfficialLanguages, COUNT(*) AS Cities FROM country2 AS co2 JOIN city ci ON ci.CountryCode = co2.Code GROUP BY co2.Code; 

This query is valid because, as seen previously: 

{co2.Code} -> {co2.*} 

MySQL is able to discover a functional dependency in the result of a view and use that to validate a query which uses the view. The same would be true if country2  were a derived table (or common table expression), as in: 

## Combinations of Functional Dependencies 

SELECT co2.Code, co2.UpperName, co2.OfficialLanguages, COUNT(*) AS Cities FROM (  SELECT co.Code, UPPER(co.Name) AS UpperName,  COUNT(cl.Language) AS OfficialLanguages  FROM country AS co JOIN countrylanguage AS cl  ON cl.CountryCode=co.Code  WHERE cl.isOfficial=’T’  GROUP BY co.Code ) AS co2 JOIN city ci ON ci.CountryCode = co2.Code GROUP BY co2.Code; 

MySQL is able to combine all of the preceding types of functional dependencies (key based, equality based, view based) to validate more complex queries. 

## 12.21 Window Functions 

MySQL supports window functions that, for each row from a query, perform a calculation using rows related to that row. The following sections discuss how to use window functions, including descriptions of the OVER  and WINDOW  clauses. The first section provides descriptions of the nonaggregate window functions. For descriptions of the aggregate window functions, see Section 12.20.1, “Aggregate Function Descriptions” . 

