MySmartCoPilot
Ask Passes Your pass 7 days left 1 day left Last day

Your country

Tools that support it use your country for local currency, number formats, units and paper size. Your choice is saved only in this browser.

Database Management Systems (DBMS) Module 4

Relational algebra and relational calculus interview questions

Interview questions on relational algebra and relational calculus: warm-ups, then what each harder question tests and how a good answer starts.

12 questions: 4 warm-ups and 8 with model answers · Levels: Fresher to senior

Warm-up questions

Short questions with short answers, each answered in full.

  1. Fresher

    Why can π branch (Student) return fewer tuples than SELECT branch FROM student?

    Answered in the lesson Relational algebra: the six core operators

  2. Fresher

    What does a natural join return when the two relations share no attribute names, and when they share all of them?

    Answered in the lesson Joins in relational algebra and join sizes

  3. Fresher

    What does R ÷ S return when S is empty?

    Answered in the lesson Division, grouping and extended relational algebra

  4. Fresher

    What makes a tuple relational calculus expression unsafe?

    Answered in the lesson Tuple and domain relational calculus

Questions with model answers

What each question tests, the first step of a good answer and the follow-ups to expect.

  1. Fresher to mid

    A teammate says set difference is redundant in relational algebra, because a selection with NOT can filter tuples out. Show that − cannot be built from σ, π, ∪, × and ρ, and explain what a negated selection can and cannot do.

    What it tests
    Monotonicity of the other five operators, proof by a property every expression keeps, and the gap between a negated condition on one tuple and the absence of matching tuples.
    First step
    Ask what happens to each operator's result when an input relation gains a tuple: the results of σ, π, ∪, × and ρ can only gain tuples too, so every expression built from them is monotone. Then find a difference whose result shrinks when its second input grows.
    Follow-ups

    From the lesson Relational algebra: the six core operators

    Opening the model answers with your pass…

    Model answers open with any MySmartCoPilot pass.

    • Pro pass · 1 week₹79$2.99
    • 30 days₹179$5.99
    • 1 year₹1,699$45

    One payment; nothing renews unless you choose to. The price shown is the total you pay: no fees or charges are added at checkout.

    Choose a passI have a licence key

    It also opens every other in-depth part in Learn and every Pro tool. Sales are final, except where the law gives you a right to withdraw, for example in the EU and the UK (refund policy).

    In the answer:

    • Model answer
    • Follow-up answers
    • Common mistakes
    • Rubric
    • Variants
    • about 3 minutes

  2. Fresher to mid

    Without aggregate functions or sorting, write a relational algebra expression for the courses with the second-highest number of credits in Course(cid, title, dept, credits). How large is the biggest intermediate result for n courses?

    What it tests
    Rename and self-products, the "not beaten by any other tuple" pattern applied twice, ties, and reasoning about the size of intermediate results.
    First step
    Find the maximum first: the courses that no other course beats, from a product of two renamed copies of Course and a difference. The courses that some course beats are the rest; applying the same pattern to the rest gives the courses with the second-highest value.
    Follow-ups

    From the lesson Relational algebra: the six core operators

    In the answer:

    • Model answer
    • Follow-up answers
    • Common mistakes
    • Rubric
    • Variants
    • about 3 minutes

  3. Mid to senior

    A report joins 2 million orders with customers and with their shipping addresses. After a release it returns 2.6 million rows. Using what keys guarantee about join sizes, how do you find the join that fanned out, and how do you fix the report?

    What it tests
    Join-size bounds from keys and foreign keys, testing uniqueness with GROUP BY, one-to-one against one-to-many joins, and fixes that keep one row per order.
    First step
    A join of orders with a table whose join column is unique keeps at most one row per order. Count the rows after each join in turn: the first step that exceeds 2 million joins a side whose join column is not unique. Confirm it with GROUP BY on that column HAVING count(*) > 1.
    Follow-ups

    From the lesson Joins in relational algebra and join sizes

    In the answer:

    • Model answer
    • Follow-up answers
    • Common mistakes
    • Rubric
    • Variants
    • about 3 minutes

  4. Fresher to mid

    List every course with the number of CSE students enrolled in it, including courses with none. Write it in SQL and in extended relational algebra, and name the two places where a first attempt usually loses or miscounts the zero rows.

    What it tests
    Outer joins and where their conditions go, COUNT(*) against COUNT(column) on padded rows, and the extended algebra's outer join with grouping.
    First step
    Start from course and keep every row with LEFT JOIN. Only enrolments of CSE students may count, so the branch test belongs in the ON clause of the join chain, not in WHERE. Then count a column of the student side, which is NULL in every row that does not count.
    Follow-ups

    From the lesson Joins in relational algebra and join sizes

    In the answer:

    • Model answer
    • Follow-up answers
    • Common mistakes
    • Rubric
    • Variants
    • about 3 minutes

  5. Mid to senior

    Find the students who have taken every course offered by the department named like their branch: a CSE student needs every CSE course. Why is this not a single R ÷ S, and how do you write it in relational algebra and in SQL?

    What it tests
    Division whose divisor depends on each candidate, rewriting "every" as "not missing any", correlated NOT EXISTS, and the vacuous case of a branch with no courses.
    First step
    R ÷ S divides every candidate by the same S, but here each student's divisor is a different set, the courses of their branch. Build the pairs each student should have, Student joined with Course on branch = dept, subtract the enrolments, and remove every student who appears among the missing pairs.
    Follow-ups

    From the lesson Division, grouping and extended relational algebra

    In the answer:

    • Model answer
    • Follow-up answers
    • Common mistakes
    • Rubric
    • Variants
    • about 3 minutes

  6. Fresher to mid

    With each student's credit-weighted grade points computed as in extended.sql, list for each branch the students with the highest value, ties included. Write it in extended relational algebra and in SQL without window functions, and say what SELECT branch, name, max(points) with GROUP BY branch gets wrong.

    What it tests
    Composing grouping steps, joining an aggregate back to keep ties, students without grades, integer division, and the bare-column problem of non-grouped columns.
    First step
    Work in three steps. Group the enrolments by student to get each student's points and join Student for the branch. Group those by branch with max to get one best value per branch. Then join the per-student values back on branch and points equal to the best value, which keeps every student who ties.
    Follow-ups

    From the lesson Division, grouping and extended relational algebra

    In the answer:

    • Model answer
    • Follow-up answers
    • Common mistakes
    • Rubric
    • Variants
    • about 3 minutes

  7. Mid to senior

    Write a tuple relational calculus query for the pairs of different students who are enrolled in exactly the same set of courses, then translate it into SQL. What does your query say about two students who have no enrolments at all?

    What it tests
    Set equality as two universal conditions, implication inside ∀, translating each ∀ into NOT EXISTS, and vacuous truth for empty sets.
    First step
    Two sets are equal when each contains the other. Take two student variables s and t with s.sid < t.sid, and require that every enrolment of s has an enrolment of t in the same course, and every enrolment of t one of s. Each "every" becomes a NOT EXISTS around a NOT EXISTS in SQL.
    Follow-ups

    From the lesson Tuple and domain relational calculus

    In the answer:

    • Model answer
    • Follow-up answers
    • Common mistakes
    • Rubric
    • Variants
    • about 3 minutes

  8. Mid to senior

    "Students who passed every course they took" is ∀e ∈ Enrolled (e.sid = s.sid ⇒ passed(e)), and some grades are still NULL. Compare NOT EXISTS (... AND e.grade = 'F') with NOT EXISTS (... AND NOT (e.grade <> 'F')), say whom each wrongly includes, and write a correct query.

    What it tests
    Translating ∀ and ⇒ into NOT EXISTS under three-valued logic, deciding what NULL means for the rule, and vacuous truth for students with no enrolments.
    First step
    Decide first what an ungraded course means for the rule: not passed yet. Then write ∀ as "no enrolment of the student exists that is not passed", and spell out "not passed" so that it is true for NULL: grade = 'F' OR grade IS NULL. Test every version on a student whose only grade is NULL.
    Follow-ups

    From the lesson Tuple and domain relational calculus

    In the answer:

    • Model answer
    • Follow-up answers
    • Common mistakes
    • Rubric
    • Variants
    • about 3 minutes

All Database Management Systems (DBMS) interview questions · The Database Management Systems (DBMS) track