Query Languages in DBMS: Relational Algebra, SQL and a Worked Query

SQL, relational algebra and relational calculus can describe one database question, but they do not tell the DBMS what to do in the same way. That difference is easy to memorise and forget. A single database question can be traced from plain English through formal notation, intermediate relations and SQL output. Relational algebra is procedural, relational calculus is declarative, and SQL is mostly declarative; their operators and notation require careful tracing.

What a query language lets you ask a DBMS to do

A query requests data from stored relations. A query language supplies the rules for expressing it. “Which CSE students scored at least 80 in DBMS?” describes the result, not a disk page, index or storage path.

The relational model represents tuples and attributes as relations. Relational algebra, relational calculus and SQL express operations or conditions over them.

SQL's query side handles retrieval, while other SQL statements define schemas and change data. Not every DBMS command is a retrieval query or general-purpose program.

Procedural and declarative query languages, using one question

Relational algebra is textbook-procedural because its expressions compose operations. Tuple and domain calculus are declarative because they state properties that qualifying tuples or values must satisfy. SQL is mostly declarative. Its optimiser can choose a physical plan different from the written clause order.

The query uses these three relations:

• STUDENT(SID, Name, Dept)

• COURSE(CID, Title, Dept)

• ENROLLED(SID, CID, Marks)

Return the names of students whose department is CSE, whose course title is DBMS, and whose marks are at least 80. The result must be {Asha, Charu}. Operator-law, tuple-count and division problems need Relational Algebra for GATE: Count Tuples, Prove Equivalence. Cross-language equivalence needs a different habit: keep the intended answer fixed, then check every notation against it.

Relational algebra operators: read the symbols first

Every operator returns another relation. This closure property enables composition.

On the data below, σ_Dept='CSE'(STUDENT) returns SIDs {101, 103, 104, 105}. π_Dept(STUDENT) returns {CSE, ECE}, not five values. π_SID(STUDENT) - π_SID(ENROLLED) returns {105}. The product has 5 × 3 = 15 tuples before filtering.

Read inside out. π_Name(σ_Dept='CSE'(STUDENT)) selects CSE tuples, then keeps Name. σ_Dept='CSE'(π_Name(STUDENT)) is invalid because projection removed Dept.

Worked example: evaluate the algebra step by step

The STUDENT, COURSE and ENROLLED relations contain the following rows.

The query evaluates in named stages:

Bilal disappears at A because his department is ECE. Esha has no matching enrolment at D. Deepa survives the marks test but disappears at E because C3 is not the DBMS course C1.

Express the same result in SQL and relational calculus

The SQL version keeps each join and predicate explicit:

SELECT DISTINCT s.Name
FROM STUDENT AS s
JOIN ENROLLED AS e ON e.SID = s.SID
JOIN COURSE AS c ON c.CID = e.CID
WHERE s.Dept = 'CSE'
  AND c.Title = 'DBMS'
  AND e.Marks >= 80;

DISTINCT matches classical projection's set semantics. SQL without it can retain duplicate names.

Tuple relational calculus expresses the same request as a condition:

{s.Name | STUDENT(s) ∧ s.Dept='CSE' ∧
  ∃e ∃c (ENROLLED(e) ∧ COURSE(c) ∧ e.SID=s.SID ∧
  c.CID=e.CID ∧ c.Title='DBMS' ∧ e.Marks>=80)}

It chooses student names for which enrolment and course tuples exist, link by SID and CID, and satisfy all filters. Domain calculus variables range over attribute values, not tuples. Safe results must come from database relations.

Relational algebra and safe calculus have equivalent expressive power for these standard relational queries. SQL adds practical features and commonly uses bag semantics.

Query-language traps and how to correct them

Selection filters tuples; projection keeps attributes. An attribute removed by projection cannot support a later selection or join. Write each intermediate output schema before counting rows.

Classical algebra removes duplicates, while SQL preserves them unless DISTINCT is used. A natural join matches every same-named attribute. Since Dept occurs in both STUDENT and COURSE, it can add an unintended condition. Join explicitly on SID and CID.

Union, intersection and difference need equal arity and compatible domains. A product does not match keys: 5 × 3 = 15 rows here. Only a predicate reduces it.

How objective and numerical questions test this topic

Questions can ask you to classify languages, identify an output schema, trace tuple counts, or match an expression to a request. Mark each operator's input and output schema and cardinality.

For a fast solved check, π_SID(σ_Marks>=80(ENROLLED)) gives {101, 102, 103, 104}. Intersect it with π_SID(σ_Dept='CSE'(STUDENT)) = {101, 103, 104, 105}:

{101, 102, 103, 104} ∩ {101, 103, 104, 105}
= {101, 103, 104}
cardinality = 3

This is not the DBMS-course answer. SID 104 scored 86 in C3, not C1, so the title filter removes Deepa. To drill the SQL side of this, work through SQL Query MCQs: 12 Solved on SELECT, Joins and Subqueries.

Query languages in one minute, then the next step

• A query states a request for data.

• Relational algebra composes operations.

• Relational calculus states qualifying conditions.

• SQL usually describes the desired result declaratively.

• Every algebra operator returns a relation, which enables composition.

• Classical set semantics and SQL bag semantics must be checked explicitly.

For a college-level route through DBMS and core subjects, use Zero to Hero, Complete CS Course. For topic-by-topic browsing, use CS Fundamentals for Exams & Placements.

Cover the solution and rebuild A through E. Then explain why Bilal, Deepa and Esha disappear at different stages. If you can do both, you understand the query rather than its labels.