Week 6 - Three valued logic in SQL

Question 1

What can NULL mean?

Answer 1

TRUE(1)
FALSE(0)
UNKNOWN (0.5)

Question 2

How do you write a value is NULL?

Answer 2

IS NULL

Opposite: IS NOT NULL

Question 3

How do you compare NULL values using AND, OR and NOT?

Answer 3

Use:
TRUE(1)
FALSE(0)
UNKNOWN (0.5)

AND: min value
OR: max value
NOT: 1-x

Question 4

Retrieve the first and last names of all employees who do not have supervisors.

Answer 4

SELECT Fname Lname
FROM EMPLOYEE
WHERE Super_ssn IS NULL

Question 5

What is a nested query?

Answer 5

Nested queries are queries from within another SQL query.

It’s input to the outer queries WHERE via: IN, ALL, EXISTS.

Question 6

What is the difference between a Nested Correlated Query and a Nested Uncorrelated Query?

Answer 6

Correlated nested queries depend on the outcome of the outer query to perform its execution, whereas an uncorrelated nested query doesn’t depend on the outer query for its execution.

This means that correlated queries cannot be executed as standalone queries, whereas uncorrelated nested queries can be executed as stand-alone queries.

Question 7

Consider a SCHOOL database with 2 relations DEPARTMENT and STUDENT. A department will have many students which means the STUDENT table has “dep_id” which is a FK of the department & shows what department the student belongs to.

What would happen if we wanted to retrieve the records of all students from the Computer department.

Answer 7

USE schooldb;

SELECT * FROM
  student 
  WHERE dep_id =
  (
    SELECT id from department WHERE name = 'Computer'
  );

We can see that the inner query retrieves id of the “Computer” department while the outer query retrieves student records with that id value in the dep_id column.

Since we know that in the case of uncorrelated sub-queries the inner query can be executed as standalone query and it will still work.

SELECT id from department WHERE name = ‘Computer’;

Question 8

What does the IN operator do?

Answer 8

Checks whether a value belongs to the inner’s output set (or multiset)

Eg., v ∈ S (v is an element of S)

Question 9

Show the SSN of those employees who work in the projects with number: either 1, or 2, or 3.

Answer 9

SELECT Essn
FROM WORKS_ON
WHERE PNO IN (1,2,3)

The IN operator compares a value v with a set (or multiset) of values V and evaluates to TRUE if v is one of the elements in V.

Question 10

Explain the ALL keyword.

Answer 10

ALL is a keyword that can combine these operators: >, >=, .

Eg., (v > ALL V) returns TRUE if the value v is greater than all the values in the set (or multiset) V.

Question 11

Write a statement for an uncorrelated nested query that returns the names of employees whose salary is greater than the salary of all the employees in department 5.

Answer 11

SELECT Lname, Fname
FROM EMPLOYEE
WHERE Salary>ALL (SELECT Salary
FROM EMPLOYEE
WHERE Dno=5);

Question 12

Consider a SCHOOL database with 2 relations DEPARTMENT and STUDENT.

What would happen if we wanted to retrieve the name, age, and gender of all of the students whose age is greater than the average age of students in their department?

Answer 12

In this case, the outer query will retrieve records of all the students iteratively and each record is passed to the inner query. For each record, the inner query will retrieve average age of the department for the student record passed by the outer query. If the age of the student is greater than average age, the record of the student will be included in the result, and if not not. Let’s see this in action.

USE schooldb;

SELECT   name, gender, age
  FROM     student Greater
  WHERE    age >
  (SELECT   AVG (age)
     FROM     student average
     WHERE      greater.dep_id = average.dep_id) ;

We know that in the case of a correlated sub-query, the inner query cannot be executed as standalone query. You can verify this by executing the following inner query on it’s own & we can test this like:
SELECT   AVG (age)
  FROM     student average    
  WHERE      greater.dep_id = average.dep_id

and it will throw an error

Question 13

Explain EXISTS AND UNIQUE.

Answer 13

Both are boolean functions that return TRUE or FALSE which means they can be used in a WHERE clause condition.

EXISTS is used to check whether the result of a inners output (same as nested query) is empty (contains no tuples) or not, and returns FALSE or TRUE respectively, eg., S ={} or S!={}

Opposite: NOT EXISTS

Question 14

Write an example query that checks if a given employee is working at some department

Answer 14

SELECT E.Fname, E Lname
FROM EMPLOYEE AS E
WHERE EXISTS
(SELECT*FROM DEPARTMENT AS D WHERE E.DNO = D.NUMBER)

Note, that E and D are used to distinguish between the the same attribute in the inner and outer queries. If we didn’t do this in the inner query both department numbers would be read as being from the department.

If an employee is working in a department get the Fname and Lname. The employee FK = department PK. Now EMPLOYEE is defined as a global variable which means we can have access within the inner (nested query). If it is empty, the employee is not working a department, so it returns FALSE.

This is a good example of how we treat NULL values of DNO.

Question 15

Retrieve the names of all students who have a Grade of ‘A’ in ALL of their courses ( ‘distinction’ students) :

STUDENT (Name, StudentID, Class)
COURSE (Name, CourseID, Credits, School)
GRADES (StudentID, CourseID, Grade) /Grade: {‘A’, ‘B’, ‘C’, ‘D’, ‘E’}/

Answer 15

// taking 1 student at a time which means it is a nested correlated query 
// outer query we are dealing with the student tuple 

SELECT S.Name, StudentID
FROM Student
WHERE EXISTS
(SELECT* FROM COURSE AS C WHERE C.name = S.name)
AND
WHERE EXISTS
(SELECT* FROM GRADES AS G WHERE StudentID = CourseID AND Grade = A)

Question 16

Write a query that checks if a given employee:
➢ has at least a dependent and
➢ manages a department, i.e., there exists a department, which is managed by
that employee

Answer 16

SELECT E.Fname, E.Lname
FROM EMPLOYEE AS E
WHERE EXISTS
(SELECT * FROM DEPENDENT AS P WHERE E.SSN = P.Essn)
AND EXISTS
(SELECT*FROM DEPARTMENT AS D WHERE E.SSN = D.Mgr_SSN)

Above, we have defined the employee relation to have a global scope using E. It is using the logical AND condition of two EXISTs operators over 2 sets of nested correlated queries; the first selects all DEPENDENT tuples related to an EMPLOYEE, and the second selects all DEPARTMENT tuples managed by the EMPLOYEE. If at least one of the first and at least one of the second exists, we select the EMPLOYEE tuple.