Translation drill: the same query in RA, TRC and SQL

0 · Recall Warm-up

Answer from memory first. Answers are in the Answer Vault at the bottom.

1. How is "students who took every CS course" written in SQL? from Lesson 3.3.9
2. Which TRC form expresses "none"? from Lesson 3.2.2
3. What is the RA formula for R ÷ S? from Lesson 3.1.5

1 · The Hook

GATE often gives a query in one language and four options in another: "Which RA expression is equivalent to this SQL?" or "Which SQL query matches this TRC formula?" The fastest way to answer is to know a few query shapes and recognise them in every language. This lesson writes five shapes three times each.

2 · Predict First

Which shape is "names of students who took no course": a selection, a join, a difference, or a division?

Make a guess in your head. The answer opens Section 3.

3 · Core Concept

Predict First — the answer

A difference (RA: all students − students in ENROLL), which is "¬∃" in TRC and NOT EXISTS / NOT IN / EXCEPT / LEFT JOIN … IS NULL in SQL.

Shape 1 · Filter + pick (σ, π)

"Names of CS students with CGPA above 8."

• RA: πname(σdept='CS' ∧ cgpa>8(STUDENT))
• TRC: { s.name | s ∈ STUDENT ∧ s.dept = 'CS' ∧ s.cgpa > 8 }
• SQL: SELECT DISTINCT name FROM STUDENT WHERE dept = 'CS' AND cgpa > 8;

Result: Asha, Chirag. (DISTINCT makes SQL match RA's set semantics.)

Shape 2 · Join (∃)

"Titles of courses Asha took."

• RA: πtitle(σname='Asha'(STUDENT) ⋈ ENROLL ⋈ COURSE): careful, ⋈ with COURSE also matches dept! Safer: πtitle(πcid(σname='Asha'(STUDENT) ⋈ ENROLL) ⋈ COURSE).
• TRC: { c.title | c ∈ COURSE ∧ ∃s ∈ STUDENT ∃e ∈ ENROLL (s.name = 'Asha' ∧ e.roll = s.roll ∧ e.cid = c.cid) }
• SQL: SELECT C.title FROM STUDENT S JOIN ENROLL E ON S.roll = E.roll JOIN COURSE C ON E.cid = C.cid WHERE S.name = 'Asha';

Result: DBMS, Networks, Circuits. (The unsafe natural-join version would lose Circuits.)

Shape 3 · None (−, ¬∃)

"Names of students who took no course."

• RA: πname((πroll(STUDENT) − πroll(ENROLL)) ⋈ STUDENT)
• TRC: { s.name | s ∈ STUDENT ∧ ¬∃e ∈ ENROLL (e.roll = s.roll) }
• SQL: SELECT name FROM STUDENT S WHERE NOT EXISTS (SELECT * FROM ENROLL E WHERE E.roll = S.roll);

Result: Esha.

Shape 4 · All (÷, ∀ ⇒ ∃, double NOT EXISTS)

"Rolls of students who took every CS course."

• RA: πroll, cid(ENROLL) ÷ πcid(σdept='CS'(COURSE))
• TRC: { s.roll | s ∈ STUDENT ∧ ∀c ∈ COURSE (c.dept = 'CS' ⇒ ∃e ∈ ENROLL (e.roll = s.roll ∧ e.cid = c.cid)) }
• SQL: double NOT EXISTS (Lesson 3.3.9).

Result: 1, 3. (Edge: RA division takes candidates only from ENROLL; the TRC and SQL versions take them from STUDENT. They differ only if the CS course set is empty: then TRC/SQL return all 5 students, RA returns the 4 enrolled ones.)

Shape 5 · Maximum

"Highest CGPA."

• RA: πcgpa(σcgpa is not null(STUDENT)) − πS1.cgpa(σS1.cgpa < S2.cgpa(ρS1(STUDENT) × ρS2(STUDENT))), or extended: γMAX(cgpa)(STUDENT)
• TRC: { s.cgpa | s ∈ STUDENT ∧ ¬∃t ∈ STUDENT (t.cgpa > s.cgpa) }
• SQL: SELECT MAX(cgpa) FROM STUDENT; or … WHERE cgpa >= ALL (SELECT cgpa FROM STUDENT WHERE cgpa IS NOT NULL)

Result: 9.1. (In TRC, Esha's s.cgpa would make t.cgpa > NULL unknown, so ¬∃ is TRUE and NULL would sneak into the answer; GATE questions on calculus usually assume no NULLs.)

Recognition table

4 · Visual

              SOME            NONE             ALL                  MAX
RA            ⋈               −                ÷                    R − (losers)
TRC           ∃ ∧             ¬∃               ∀ ⇒ ∃                ¬∃ bigger
SQL           JOIN / EXISTS   NOT EXISTS       NOT EXISTS(NOT EXISTS) MAX / >= ALL

5 · Worked Examples

Example 1 Easy

Translate to SQL: πcid(σgrade='A'(ENROLL)).

Solution

SELECT DISTINCT cid FROM ENROLL WHERE grade = 'A'; → C1, C2, C3.

Example 2 Medium

Translate to RA: SELECT S.name FROM STUDENT S WHERE S.roll IN (SELECT roll FROM ENROLL WHERE cid = 'C2');

Solution

πname(STUDENT ⋈ πroll(σcid='C2'(ENROLL))) → Asha, Chirag.

Example 3 GATE

Which RA expression matches { s.name | s ∈ STUDENT ∧ ¬∃e ∈ ENROLL (e.roll = s.roll ∧ e.grade = 'A') }? (A) πname(STUDENT ⋈ σgrade≠'A'(ENROLL)) (B) πname(STUDENT) − πname(STUDENT ⋈ σgrade='A'(ENROLL)) (C) πname(STUDENT ⋈ ENROLL) (D) πname(σgrade='A'(STUDENT ⋈ ENROLL))

Solution

(B): everyone minus those with an A → Bhavin, Diya, Esha. (A) gives students with some non-A grade (includes Asha). (C) all enrolled students. (D) students with an A. (B) assumes names are unique; with duplicate names, subtract rolls first.

8 · Connect the Dots

9 · Self-Test

GATE format. Try every question before opening the vault.

Q1 · MCQ · 2 mark(s) — Translation

Which SQL query matches πroll(STUDENT) − πroll(ENROLL)?

1. (A) SELECT roll FROM ENROLL EXCEPT SELECT roll FROM STUDENT;
2. (B) SELECT roll FROM STUDENT WHERE roll IN (SELECT roll FROM ENROLL);
3. (C) SELECT roll FROM STUDENT EXCEPT SELECT roll FROM ENROLL;
4. (D) SELECT S.roll FROM STUDENT S JOIN ENROLL E ON S.roll = E.roll;

Q2 · MCQ · 2 mark(s) — Shape

"Courses that every student has taken" is which shape?

1. (A) Difference
2. (B) Aggregation
3. (C) Join
4. (D) Division

Q3 · NAT · 2 mark(s) — Evaluate

How many rows does this return? SELECT DISTINCT E.cid FROM ENROLL E WHERE NOT EXISTS (SELECT * FROM ENROLL F WHERE F.cid = E.cid AND F.grade = 'A');

Numerical answer: ________

Q4 · MCQ · 1 mark(s) — TRC to SQL

{ c | c ∈ COURSE ∧ ∃e ∈ ENROLL (e.cid = c.cid ∧ e.grade = 'B') } corresponds to:

1. (A) SELECT * FROM ENROLL WHERE grade = 'B';
2. (B) SELECT * FROM COURSE C WHERE C.cid = ALL (SELECT cid FROM ENROLL WHERE grade = 'B');
3. (C) SELECT * FROM COURSE C WHERE EXISTS (SELECT * FROM ENROLL E WHERE E.cid = C.cid AND E.grade = 'B');
4. (D) SELECT * FROM COURSE C WHERE NOT EXISTS (SELECT * FROM ENROLL E WHERE E.cid = C.cid AND E.grade = 'B');

Q5 · NAT · 1 mark(s) — Max

What does SELECT MAX(credits) FROM COURSE WHERE dept = 'CS' return?

Numerical answer: ________

11 · Concept Ledger

Unlocked in this lesson. From here on, these terms may be used.

query shapes (filter, some, none, all, max)RA–TRC–SQL translationrecognition cues