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.

SQL Continued

Consider the following table schemas and then go over the SQL queries and their corresponding expressions 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

Q1 Find the names of all students who took CSE303, but who were not yet assigned a grade.

In this case querying for NULL values is possible as shown below.

Query Expression

SELECT sName

FROM Student, Takes

WHERE Student.matNr = Takes.matNr

AND Takes.classNr = ’CSE303’

AND Takes.grade = NULL

Q2 Find all students who took the course CSE 303 or worked as a TA for this class.

Query Expression

(SELECT Student.matNr, sName

  FROM Student, Takes

  WHERE Student.matNr=Takes.matNr

   AND Takes.classNr= ‘CSE 303’)

   UNION

   (SELECT student.matNr, sName

     FROM Student, TA

     WHERE Student.matNr=TA.matNr

      AND TA.classNr= ‘CSE 303’

Q3 Find all names of students who took any class not given by Professor James.

      Query Expression:

        SELECT  sName

        FROM Student

        WHERE matNr IN

                   (SELECT matNr

                     FROM Takes

                     WHERE classNr NOT IN

                                     (SELECT classNr

                                       FROM class

                                       WHERE pname= ‘James’))

Querying by SQL (Structured Query Language)

We start with some SQL examples related to the University Organization Problem.

Q1 Find the matriculation number (student id) of the student whose name is John.

Query Expression

SELECT  matNr FROM Student WHERE sName=’John’

From the above SQL query expression we note the following:

• The SELECT part is equivalent to a projection in relational algebra, not a selection.
• After SELECT, all attributes must be listed onto which a projection is executed.
• After FROM, all tables must be listed that are necessary for finding the query result.
• After WHERE, all constraints (formulas) for joins and selections (conditions) in the query must be listed.

Q2 Find the students who took the class on CSE 303.

Query Expression:

SELECT sName FROM Student, Takes WHERE Student.matNr=Takes.matNr AND classNr= ‘CSE 303’

Q3 Display the names and salaries of professors whose salary is > than 1 lac.

 Query Expression:

SELECT  * FROM  Professor WHERE psalary > 100000

Q4 Find the matNrs of all students younger than the oldest student named Philips.

Query Expression:

SELECT A.matNr FROM Student A, Student B WHERE B.sName = ‘Philips’ AND

A.age < B.age

Relational Algebra Operations Continued

Modification of the Database

 Deletion

Here are several examples of relational algebra delete operations:

         Delete all of Smith’s account records

         Delete all loans with amount in the range 0 to 50.

    Delete all accounts at branches located in Dhaka.  

 

Insertion

Suppose we wish to insert the fact that Smith has Tk1200 in account A-973 at Kakrail branch. We write:

Updating

Suppose that interest rates are being made, and that all branches are to be increased by 5%.  For this we require an update operation.

 

Suppose that accounts with balances over Tk10,000 receive 6% interest, where as all others receive 5%.