By Piyush Wairale · GATE DA Educator & Course Instructor, IIT Madras BS Programme · Updated August 2026

The Relational Model and Its Keys

A relation is a table: each row is a tuple, each column an attribute drawn from a domain. Keys are constraint machinery GATE tests directly: a superkey is any attribute set that uniquely identifies tuples; a candidate key is a minimal superkey; the primary key is the candidate key you designate (non-null, unique); a foreign key references a primary key in another table, enforcing referential integrity. In our running example, Student.rollno is the primary key; {rollno, name} is a superkey but not a candidate key (not minimal); Enrolled.rollno is a foreign key to Student, and Enrolled’s primary key is the pair (rollno, course). Databases and warehousing get a full section in the official GATE DA syllabus — see the DBMS & warehousing notes for the broader map.

The Two Tables Everything Below Runs On

The Seven Relational Algebra Operators, Each Worked

σ — selection (rows). σ_{dept=’CSE’}(Student) → rows 1 (Asha) and 3 (Meena). 2 tuples.

π — projection (columns). π_dept(Student) → {CSE, EE, ME} — 3 tuples, not 5: projection removes duplicates, because relations are sets.

⋈ — natural join. Student ⋈ Enrolled matches on the shared attribute rollno → 5 tuples (one per Enrolled row; John, rollno 4, disappears — no enrolment). Each result tuple carries rollno, name, dept, course, marks.

× — cartesian product. Student × Enrolled pairs every student with every enrolment: 5 × 5 = 25 tuples, most meaningless — which is why a join is σ_match applied to ×.

∪ — union. π_rollno(σ_{course=’DBMS’}(Enrolled)) ∪ π_rollno(σ_{course=’ML’}(Enrolled)) = {1, 2, 5} ∪ {1, 3} = {1, 2, 3, 5}. Union requires compatible schemas and removes duplicates.

− — set difference. π_rollno(Student) − π_rollno(Enrolled) = {1,2,3,4,5} − {1,2,3,5} = {4} — students enrolled in nothing. Difference is how algebra says “NOT”.

ρ — rename. ρ_S2(Student) gives the same table a second name — needed for self-joins, e.g. pairing students in the same dept.

SELECT-FROM-WHERE, Evaluated Row by Row

SELECT name FROM Student WHERE dept = 'CSE';

Logical evaluation: FROM loads all 5 Student rows; WHERE keeps rows where dept = ‘CSE’ (rollno 1 and 3); SELECT projects name. Output: Asha, Meena — exactly π_name(σ_{dept=’CSE’}(Student)). Note the written order (SELECT first) is the reverse of the execution order — that inversion is where alias-scoping traps come from.

GROUP BY + HAVING, Worked with the Aggregates

SELECT s.dept, COUNT(*) AS n, AVG(e.marks) AS avg_marks
FROM Student s JOIN Enrolled e ON s.rollno = e.rollno
GROUP BY s.dept
HAVING AVG(e.marks) > 70;

Step 1 — join (5 matched rows): (Asha, CSE, 85), (Asha, CSE, 78), (Ravi, EE, 60), (Meena, CSE, 92), (Sara, EE, 70).

Step 2 — group by dept: CSE = {85, 78, 92}, EE = {60, 70}.

Step 3 — aggregates: CSE: n = 3, avg = 255/3 = 85. EE: n = 2, avg = 130/2 = 65.

Step 4 — HAVING avg > 70: keeps CSE, drops EE. Output: one row, (CSE, 3, 85). Remember the division of labour: WHERE filters rows before grouping, HAVING filters groups after aggregation — swapping them is the classic error.

Joins and Row-Count Reasoning

On our tables (join on rollno): INNER = 5 rows (John excluded). LEFT (Student left) = 6 rows — the 5 matches plus (John, ME, NULL, NULL). RIGHT = 5 rows — every Enrolled row already matches a student. FULL = 6 rows — union of both. General rule for row counting: matches contribute once per matching pair; unmatched rows contribute one NULL-padded row in the joins that preserve their side.

Nested Queries: IN, EXISTS, NOT EXISTS

-- Students enrolled in no course (answer: John)
SELECT name FROM Student s
WHERE NOT EXISTS (SELECT 1 FROM Enrolled e
                  WHERE e.rollno = s.rollno);
-- equivalent: WHERE s.rollno NOT IN (SELECT rollno FROM Enrolled)

Evaluate NOT EXISTS per student: Asha, Ravi, Meena, Sara each have enrolments → excluded; John has none → kept. Output: John — the SQL twin of the algebra difference π_rollno(Student) − π_rollno(Enrolled). One NULL caution GATE loves: NOT IN behaves treacherously if the subquery returns a NULL (result becomes empty), while NOT EXISTS is safe — prefer it, and expect an MCQ on the distinction.

The SQL ↔ Relational Algebra Dictionary

Three GATE-Style Problems, Solved

Problem 1 (NAT). On our tables, how many rows does this return?

SELECT s.name, e.course
FROM Student s LEFT JOIN Enrolled e ON s.rollno = e.rollno
WHERE s.dept <> 'ME';

Solution. LEFT JOIN gives 6 rows (5 matches + John/NULL). WHERE drops John’s row (dept = ‘ME’). Answer: 5. Watch the trap: putting the dept filter in the ON clause instead of WHERE would keep John as a NULL-padded row (6 rows) — ON vs WHERE placement changes outer-join results.

Problem 2 (MCQ). Which algebra expression returns names of students with marks > 80 in some course? (a) π_name(σ_marks>80(Student ⋈ Enrolled)) (b) σ_name(π_marks>80(Student ⋈ Enrolled)) (c) π_name(Student) − π_name(Enrolled) (d) σ_marks>80(π_name(Student ⋈ Enrolled))

Solution. (a) — join, filter rows, project names (result: Asha 85, Meena 92). (b) and (d) misuse the operators: π takes attribute lists, σ takes conditions, and in (d) the projection discards marks before the selection needs it.

Problem 3 (NAT). What does SELECT COUNT(DISTINCT rollno) FROM Enrolled return, and what does AVG(marks) over course = ‘DBMS’ return?

Solution. Distinct rollnos in Enrolled: {1, 2, 3, 5} → 4. DBMS marks: (85 + 60 + 70)/3 = 71.67. COUNT(*) would give 5 — the DISTINCT changes the answer, and GATE prints both as options.

Common Mistakes to Avoid

Forgetting that π dedupes and SELECT doesn’t. π_dept(Student) has 3 tuples; SELECT dept FROM Student returns 5 rows. Add DISTINCT to match.

Aggregates in WHERE. WHERE AVG(marks) > 70 is illegal — aggregates belong in HAVING (or a subquery).

NULL comparisons. NULL = NULL is not true — use IS NULL. Aggregates ignore NULLs, and NOT IN with a NULL-producing subquery returns nothing.

Counting cartesian rows as join rows. × multiplies (5×5 = 25); ⋈ keeps only matches (5). Read which operator the expression actually uses.

ON vs WHERE in outer joins. Filters in ON are applied before NULL-padding; filters in WHERE run after and can silently delete the padded rows.

How GATE DA Asks SQL and Relational Algebra

Recurring shapes: (1) NAT — number of rows returned by a query over 2 small printed tables (build the intermediate result, count); (2) MCQ — pick the equivalent algebra expression, or the query that computes a described set; (3) NAT — an aggregate value after GROUP BY/HAVING; (4) MCQ — key definitions, duplicate semantics, NULL behaviour. The data is always small enough to enumerate by hand — discipline beats cleverness. Round out the section with the full DBMS & warehousing notes and keep your syllabus tracker updated.

FAQs: SQL & Relational Algebra for GATE DA

Keep building: consolidate the section with the DBMS & warehousing notes, revisit the algorithmic side in the BFS & DFS deep dive, and track your coverage against the GATE DA 2027 syllabus. New problem-solving sessions drop regularly on my YouTube channel — subscribe so you don’t miss them.