SQL Strategy - A site to master SQL while executing it on the Web

Home >> SQL Strategy - Functions (SUM, MAX, MIN, AVG, COUNT)

Students learn set functions to find the sum, maximum, minimum, mean, and total number of records extracted.

--Find the maximum and minimum values of SAL (salary).

SELECT | MAX(SAL),MIN(SAL) |

FROM | EMP |

The aggregate function aggregates the values of the columns specified in the argument and returns the result; if the WHERE condition is specified, it returns the aggregate result for the extracted records.

SQL provides the following functions. SUM and AVG cannot take non-numeric arguments.

SUM | Sum of arguments: if NULL, not included in the tally |

MAX | Find the maximum value of the argument. |

MIN | Find the minimum value of the argument. |

AVG | Find the average value of the arguments; if NULL, it is not included in the tally. |

COUNT | If it is NULL, it is not counted.Can be described as COUNT(*). |

Now for the Practice, I will give you two questions.

Which SQL statement for the "Delivery Record" Table yields the largest value?

Delivery Record

Delivery Record

goodsNo | date | quantity |

NP200 | 20031010 | 3 |

FP233 | 20031010 | 2 |

NP200 | 20031011 | 1 |

FP233 | 20031011 | 2 |

A | SELECT AVG(quantity) FROM Delivery Record WHERE goodsNo ='NP200' |

B | SELECT COUNT(*) FROM Delivery Record |

C | SELECT MAX(quantity) FROM Delivery Record |

D | SELECT SUM(quantity) FROM Delivery Record WHERE date = '20031011' |

Answer

B

Explanation

If you verify the execution results in turn, you will get to Answer.

A is the average value of the QUANTITY of WHERE goodsNo ='NP200', so 2

B is the number of records, so 4

C is for the maximum value of QUANTITY, so 3

D is the total value of QUANTITY in WHERE date = '20031011', so 3

Therefore, the largest value, B, is the Answer.

B

Explanation

If you verify the execution results in turn, you will get to Answer.

A is the average value of the QUANTITY of WHERE goodsNo ='NP200', so 2

B is the number of records, so 4

C is for the maximum value of QUANTITY, so 3

D is the total value of QUANTITY in WHERE date = '20031011', so 3

Therefore, the largest value, B, is the Answer.

This completes Functions (SUM, MAX, MIN, AVG, COUNT).

SQL Strategy Menu

1 Introduction

2 SQL Basics

3 Basics of SELECT statement (1)

4 Basics of SELECT statement (2)

5 Sort (ORDER BY)

6 Functions (SUM, MAX, MIN, AVG, COUNT)

7 Extract by range (BETWEEN)

8 Pattern search (LIKE)

9 Grouping (GROUP BY)

10 Selection criteria for grouped tables (HAVING)

11 Duplicate row elimination (DISTINCT)

12 INSERT statement

13 UPDATE statement

14 DELETE statement

15 Inquiry to multiple tables

16 Nested Inquiry