Relational Algebra, Tuple Calculus & SQL — Practice

Question 1

Name the six primitive relational algebra operators.

Accepted Answer

Selection, projection, union, set difference, Cartesian product and rename.

Question 2

A column has 8 rows of which 3 are null. Give COUNT(*), COUNT(col) and the divisor used by AVG(col).

Accepted Answer

COUNT(*) is 8, COUNT(col) is 5, and AVG divides by 5, since aggregates other than COUNT(*) skip nulls.

Question 3

Which SQL clause filters groups rather than rows, and why can it not be WHERE?

Accepted Answer

HAVING. WHERE is evaluated before GROUP BY, so aggregates do not yet exist when it runs.

Question 4

Express R(A,B) divided by S(B) using only the primitive operators.

Accepted Answer

pi_A(R) minus pi_A((pi_A(R) times S) minus R). The subtracted term is the set of candidates that lack at least one required pairing.

Question 5

R has 20 tuples and S has 5, sharing one attribute. Give the minimum and maximum size of R natural join S and of R left outer join S.

Accepted Answer

The natural join ranges from 0, when nothing matches, to 100, when every tuple of each shares the same value. The left outer join ranges from 20, since every left tuple survives, to 100.

Question 6

Write SQL for students enrolled in every course, using the counting approach, and say why DISTINCT is essential.

Accepted Answer

Select sid from Enrol group by sid having count(distinct cid) equals the count of rows in Course. Without DISTINCT a student enrolling twice in one course would reach the total without covering every course.

Question 7

Why does a SELECT alias work in ORDER BY but not in WHERE?

Accepted Answer

Clauses are evaluated FROM, WHERE, GROUP BY, HAVING, SELECT, ORDER BY. WHERE runs before SELECT, so the alias does not exist yet, while ORDER BY runs after it does.

Question 8

Table Emp(eid, mgr) has one row with mgr null. Explain why SELECT name FROM Emp WHERE eid NOT IN (SELECT mgr FROM Emp) returns nothing.

Accepted Answer

The subquery result includes a null, so the expansion contains eid not equal to null, which evaluates to unknown. A conjunction containing unknown is either false or unknown but never true, and WHERE keeps only true rows, so every row is discarded.

Question 9

Explain why a value comparison greater than ALL over an empty subquery is true while greater than ANY is false.

Accepted Answer

ALL asserts the comparison holds for every returned row, which is vacuously true when there are no rows. ANY asserts it holds for at least one row, which cannot be satisfied by an empty set.

Question 10

State which relational algebra rewrites an optimiser applies before evaluating a join query and why they help.

Accepted Answer

Selections are pushed below joins so both operands shrink before the expensive join runs, a conjunction is split into a cascade so individual conditions reach different operands, projections are pushed down to discard unneeded columns early, and join order is chosen freely because joins are commutative and associative.

Practice — Relational Algebra, Tuple Calculus & SQL

Sign in to save attempts to your dashboard and track your accuracy over time

Find a verified tutor

Join a live course

Browse coaching centres

About

Categories

Support

Documentation

Get Study Tips & Updates

Download Our App

Download for iOS

Download for Android

Popular Cities