Đang chuẩn bị liên kết để tải về tài liệu:
The Language of SQL- P25

The Language of SQL- P25:Research has shown that, being pressed for time, most readers tend to skip the introduction of any book they happen to read and then proceed immediately to the first real chapter. With that fact firmly in mind, we will only cover relatively unimportant material in the introduction, such as an explanation of what you will and will not learn by reading this book. | 106 Chapter 10 Summarizing Data Notice that quizzes with a score less than 70 aren t shown. For example you can see Alec s quiz score of 74 but not his quiz score of 58. But what if you want to only display data for students who have an average quiz grade of 70 or more Then you want to select on an average not on individual rows. This is where the HAVING keyword comes in. You need to first group grades by student and then apply your selection criteria to an aggregate statistic based on the entire group. The following statement produces what we desire SELECT Student AS Student AVG Grade AS Average Quiz Grade FROM Grades WHERE GradeType Quiz GROUP BY Student HAVINGAVG Grade 70 ORDER BY Student The output is Student Average Quiz Grade Kathy 71.5 Susan 93.5 This SELECT has both a WHERE and a HAVING clause. The WHERE ensures that you only select rows with a GradeType of Quiz. The HAVING guarantees that you only select students with an average score of at least 70. What if you wanted to add a column with the GradeType value If you attempt to add GradeType to the SELECT columnlist the statement will error. This is because all columns must be either listed in the GROUP BY or involved in an aggregation. If you want to show the GradeType column it must be added to the GROUP BY clause as follows SELECT Student AS Student GradeType AS Grade Type AVG Grade AS Average Grade FROM Grades WHERE GradeType Quiz Looking Ahead 107 GROUP BY Student GradeType HAVINGAVG Grade 70 ORDER BY Student The resulting data is Student Grade Type Average Grade Kathy Quiz 71.5 Susan Quiz 93.5 Now that we ve added the HAVING clause to the mix let s recap the general format of the SELECT statement SELECT columnlist FROM tablelist WHERE condition GROUP BY columnlist HAVING condition ORDER BY columnlist It should be emphasized that when employing any of the above keywords in a SELECT they need to be entered in the order shown. For example the HAVING keyword needs to always be after a GROUP BY but before