Aggregate function to aggregate function page 3
We give below some standard solutions of the exercise.
1. Use predicate ALL in WHERE clause â ïðåäëîæåíèè WHERE
In natural language, this query sound like follows: "Find makers, for which the average price of PCs no less than the average prices for each of the makers of PCs".
2. Use Inner joins
Here we join a subquery that defines the makers and the average prices on their PC, with a subquery, which defines the maximum average price. The join is performed by the condition of equality of the average price of the first subquery with a maximum price of the second.
3. Use predicate ALL in HAVING clause
This solution differs from the first version of the lack of "extra" request, which had to write only then to be able to use the alias avg_price in WHERE clause (see clause order for SELECT); on the other hand, using aggregate function in WHERE clause are also prohibited by the rules of language.
All of the standard solutions seem heavy, though, and will work in almost all databases. This cumbersome due to repetition in the code is actually one and the same query. However, common table expressions – CTE (common table expression) allows to determine a table in framework of a query for multiple referencings.CTE, which were introduced in recent versions of the standard, allow repeatedly use a one formulated query. For example, solutions 1 and 3 using CTE can be written in the form:
Note that the maintenance of the common table expressions first appeared in A database management system (DBMS) by Microsoft Corporation. SQL(Structured Query Language) is a database computer language designed for the retrieval and management of data in relational database management systems (RDBMS), database schema creation and modification, and database object access control management.SQL Server 2005 and PostgreSQL 8.4.