loading..
Русский    English
05:03

Aggregate function to aggregate function page 2

Sure, you'll meet solving such exercises based on sorting with limit on the number of rows result set. However, such solutions are not legitimate from the standpoint of the standard language and, consequently, have different syntax in different implementations. As an example, I will give the solution of our exercise in the syntax of  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 and MySQL.

SQL Server
Console
Execute
  1. SELECT TOP 1 AVG(price) avg_price
  2. FROM Product P JOIN PC ON P.model = PC.model
  3. GROUP BY maker
  4. ORDER BY avg_price DESC;

MySQL
  1. SELECT AVG(price) avg_price
  2. FROM Product P JOIN PC ON P.model = PC.model
  3. GROUP BY maker
  4. ORDER BY avg_price DESC
  5. LIMIT 1;

Both of these solutions take only the first row of the sorted descending a set of average prices.

Do beginners learn SQL is often the problem the definition of maker, for whom is achieved the desired maximum / minimum. In other words, you want to find the maximum average price and the maker, the average price of a PC which is equal to the maximum average price.

Unconventional means to solve this exercise is actually discussed above query:

Console
Execute
  1. SELECT TOP 1 maker, AVG(price) avg_price
  2. FROM Product P JOIN PC ON P.model = PC.model
  3. GROUP BY maker
  4. ORDER BY avg_price DESC;

Using the maker in the column list of SELECT clause is quite acceptable, because by that column runs grouping. However, there is a "trap". It stems from the fact that the maximum can be achieved for several makers and in the formulation of the exercise they need to show everyone, while we limit the sample only one (first) string. In this case dialect  T-SQL (Transact-SQL) is Microsoft`s and Sybase`s proprietary procedural extension to SQL.T-SQL has additional argument WITH TIES. The logically correct solution will look like:

Console
Execute
  1. SELECT TOP 1 WITH TIES maker, AVG(price) avg_price
  2. FROM Product P JOIN PC ON P.model = PC.model
  3. GROUP BY maker
  4. ORDER BY avg_price DESC;

However, for portability of code the standardized solution is to be preferred.

Notes:

Implementation would require a minimum of efforts, if the test solution used for testing, would work on all alleged DBMS without changing its code. Therefore, compliance with standard may be one of requirements specification for the project.

Pages 1 2 3
Tags
aggregate functions Airport ALL AND AS keyword ASCII AVG Battles Bezhaev Bismarck C.J.Date calculated columns Cartesian product CASE cast CHAR CHARINDEX Chebykin check constraint classes COALESCE common table expressions comparison predicates Computer firm CONSTRAINT CONVERT correlated subqueries COUNT CROSS APPLY CTE data type conversion data types database schema DATEADD DATEDIFF DATENAME DATEPART DATETIME date_time functions DDL DEFAULT DEFAULT VALUES DELETE DISTINCT DML duplicates edge equi-join EXCEPT exercise (-2) More tags
The book was updated
yesterday
©SQL-EX,2008 [Evolution] [Feedback] [About] [Links] [Team]
All right reserved.