Scribd
Upload
14 views3 pages

Chapter 4-SQL

The document explains the concept of grouping data in SQL using the GROUP BY clause, which produces a summary row for each group. It outlines the requirements for the SELECT clause when using GROUP BY, including the allowed column types and set functions. Several SQL examples demonstrate how to extract average scores, count subjects, and determine total scores from a 'Score' table.

Uploaded by

Yadanar Zaw
Copyright
© All Rights Reserved
We take content rights seriously. If you suspect this is your content, claim it here.
Available Formats
Download as PDF, TXT or read online on Scribd
14 views3 pages

Chapter 4-SQL

The document explains the concept of grouping data in SQL using the GROUP BY clause, which produces a summary row for each group. It outlines the requirements for the SELECT clause when using GROUP BY, including the allowed column types and set functions. Several SQL examples demonstrate how to extract average scores, count subjects, and determine total scores from a 'Score' table.

Uploaded by

Yadanar Zaw
Copyright
© All Rights Reserved
We take content rights seriously. If you suspect this is your content, claim it here.
Available Formats
Download as PDF, TXT or read online on Scribd

Grouping of data

- The groups the data from the SELECT table(s) and produces a single summary row for each
group by using GROUP BY clause called a grouped query
- The columns named in the GROUP BY clause are called the grouping columns.
- The ISO standard requires the SELECT clause and the GROUP BY clause.
- When GROUP BY is used, each item in the SELECT list must be single-valued per group.
- Further, the SELECT clause may contain only:
o column names
o set functions
 SUM - sum of group
 AVG - average of group
 MIN - minimum value of group
 MAX - maximum value of group
 COUNT - number of records
o constants
o an expression involving combinations of the above

- The following SQL statements are the sample of grouping data using GROUP BY
1. From the “Score” table, determine and extract average score of each subject.
SELECT SubjectNumber, AVG(Score) FROM Score GROUP BY SubjectNumber;

1
2. From the “Score” table, determine and extract the number of subjects for which the
respective student took an examination.
SELECT StudentNumber, COUNT(*) AS NumberOfSubjects FROM Score GROUP
BY StudentNumber;

3. From the “Score” table, determine and extract the number of days for which the
respective student took an examination.
SELECT StudentNumber, COUNT(DISTINCT ExaminationDate) AS
NumberOfDays FROM Score GROUP BY StudentNumber;

4. From the “Score” table, extract students having TotalScore (total of score) of 150 or more.
SELECT StudentNumber, SUM(Score) AS TotalScore FROM Score GROUP BY
StudentNumber HAVING SUM(Score) >= 150;

2
5. From the “Score” table, determine the highest score for each student and extract in the
ascending order
SELECT StudentNumber, MAX(Score) FROM Score GROUP BY StudentNumber
ORDER BY 2;

You might also like