SQL Continued III

Consider the following table schemas and then go over SQL queries and their corresponding expressions using functions and grouping in response to the queries that follow:

Student:

matNr | sName

Professor:

pname | psalary

Class:

classNr | room | day | pname

                                     fk

Takes:

matNr | classNr | grade

TA:

matNr | classNr | hours | tasalary

SQL Expressions using functions

Functions:

·         AVG()    ( = average)

·         MAX()

·         MIN()

·         COUNT()  (= number of tuples)

·         SUM()

·         DISTINCT()   (= list every result value just once; remove                        

                                         duplicates)

                    

      

Q1 Find the number of tuples in table Professor.

SELECT COUNT(*) FROM Professor

Q2 List the sum of all professors’ salaries, the highest, the smallest, and the average salary in the table.

SELECT SUM(psalary), MAX(psalary), MIN(psalary), AVG(psalary) FROM Professor

Q3 What is the number of different classes for which teaching assistants work?

SELECT COUNT(DISTINCT(classNr)) FROM TA

SQL Expressions using grouping

Q1 List for every class the classNr, the number of students who took the class, and the average exam grade.

SELECT classNr, COUNT(*), AVG(grade)

FROM Takes

GROUP BY classNr

What happens using the GROUP BY clause can be explained as follows:

Let us consider the following arbitrary Takes table:

matNr	classNr	grade
01	CSE 303	4
02	CSE 303	3
02	CSE 304	4
03	CSE 304	4

The group by clause is going to group on the classNrs meaning there will be two groups of classNrs : CSE 303 and CSE 304, and for each group the classNr, number of students who took the class and average grade will be listed. So the result will be:

classNr	no of students	average grade
CSE 303	2	3.5
CSE 304	2	4

GROUP BY must always be used whenever the Select part contains attributes with aggregate functions and attributes to which no aggregate functions are applied. In these cases all attributes without aggregate function must be listed in the GROUP BY clause.

Q2 List for every class the classNr, the number of students who took the class, and the average exam grade where the grade average is better than 3.

SELECT classNr, COUNT(*), AVG(grade)

FROM Takes

GROUP By classNr

HAVING AVG(grade) > 3

A GROUP BY clause can be extended by a HAVING clause which gives a constraint that is applied to the group results.