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’))