Big Data Systems HPI

Quiz 0 Distributed Systems


Question

Consider the following two relations. R contains students and the lectures they attend. S is a set of lectures. Alt text

What does the following SQL statement do?

SELECT r.Name 
    FROM R r 
 WHERE NOT EXISTS (
           (SELECT s.Lecture 
              FROM S s ) 
          EXCEPT 
           (SELECT DISTINCT v.Lecture 
              FROM R v 
             WHERE v.Name = r.Name )
           )

Answer

Return all Students that attend all lectures in S. (Result = Julia, Amanda)

Evaluate the query from the inner to the outer part.

( SELECT DISTINCT v.Lecture FROM R v WHERE v.Name = r.Name )

The lectures the student attended.

( SELECT s.Lecture FROM S s )
EXCEPT

Minus all lectures

SELECT r.Name
FROM R r
WHERE NOT EXISTS (

Select all Students, where this result is empty.

So in the end all students are selected where the course list has no courses with the exception of the taken courses.


Comments