SQL Expressions Continued IV

Consider the following table schemas and then go over 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

 

SQL Expressions using Sorting

Q List names, grades of students with the grades in descending order.

SELECT sName, grade

FROM Student, Takes

WHERE Student.matNr=Takes.matNr

ORDER BY grade DESC

Using ORDER BY in the above expression, the grades will be listed in descending order. The default is ASC (ascending order).

Searching for partial strings

The operators LIKE and NOT LIKE use wild card characters.

·         % stands for an arbitrary number (0 or more) of arbitrary characters.

·         _  stands for exactly one arbitrary character

Q1 Find all rooms that were used for classes in CSE.

SELECT room FROM Class WHERE classNr LIKE ‘CSE%’

Q2 Find all students whose names do not end with the letter m.

SELECT sName FROM Student WHERE sName NOT LIKE ‘%m’

Q3 Find all students whose names contain the letter e.

SELECT sName FROM Student WHERE sName LIKE ‘%e%’

SQL Expressions concerning Input of Data

Inserting tuples: (Examples)

INSERT INTO Student VALUES (10, ‘James’)

INSERT INTO Class (pname, classNr) VALUES (‘Jane’, ‘CSE 303’), (‘David’, ‘CSE 304’)

Values that are not specified are automatically either set to NULL or to a default value.

SQL Expressions concerning deletion of tuples

Examples:

DELETE FROM Professor WHERE pname= ‘David’

DELETE FROM Student

·         Deletes all tuples from the table Student

Update of tuples/values

Examples:

Update Class

SET room=’7B03’, day= ‘Friday’

WHERE classNr=’CSE 304’

Several rows can be updated in one statement.

Q. All TAs get a rise of 10% increase of their salary

UPDATE TA

SET tasalary = 1.1 * tasalary

Deletion of tables

DROP TABLE tablename {CASCADE|DELETE}

If CASCADE is specified:

All views and references from other tables that refer to this table are also deleted

If RESTRICT is specified:

Deletion is only executed if the table is not referenced by other tables or views.

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