Question

How to write this query in Formal Relational Query Languages?

SELECT M.Name, M.Email
FROM Manager AS M, ManagerCertificate AS MC
WHERE M.ID = MC.ManagerID
GROUP BY M.Name
HAVING count distinct (MC.CertificateID) = 2;

Answer 1

RESULT1 ← Manager ⨝ _{ID= ManagerID} ManagerCertificate

RESULT2 ← π _{Manager.Name, Manager.Email(Manager.Name} ℑ _{Count Distinct(CertificateID=2)}(RESULT1))

Frist we have performed a  Join operation on  Manager and ManagerCertificate and stored it into a temporary table named RESULT1.

From the RESULT1 we are performing Project operation with a group by functiion which is represented by ℑ in relational algebra

_{<grouping attributes>} ℑ _{<function list>} (R)