GATE CSE 2001 | Question: 21-a

Question

Consider a relation examinee (regno, name, score), where regno is the primary key to score is a real number.

Write a relational algebra using $( \Pi, \sigma, \rho, \times)$ to find the list of names which appear more than once in examinee.

Comments

SELECT E.name
FROM examinee as E 
GROUP BY E.name 
HAVING count(*)>1

---

 

SELECT E1.name
FROM examinee E1, examinee E2
WHERE
E1.regno <> E2.regno
AND
E1.name = E2.name

 

---

This Question should be tagged under Relational Algebra Topic.  its part-b Databases: GATE CSE 2001 | Question: 21-b is for SQL query. 

Answer 1

$\pi_{\text{exm1.name}}(σ_{(\text{exm1.regno} \neq \text{examinee.regno}) \wedge (\text{emp1.name} = \text{emp2.name}) })(ρ _{\text{exm1}}(\text{examinee}) \times  \text{examinee}) $

Comments

Instead of emp1.name=emp2.name , the query should have exm1.name=examinee.name then the answer is correct. (Note: emp1 and emp2 can't be used because they are not defined in the rename section or any part of the query)

---

i think this should be the correct answer

---

right

Answer 2

1) $\Pi_{name}\left ( \sigma _{count(cnt)>1}\left ( _{name}g_{count(regno)\ as \ cnt}\left ( examinee \right ) \right ) \right )$

2) $SELECT_{regno}FROM\;examinee\; HAVING\;(score>avg(score))$

$\Pi _{regno}\left ( \sigma _{score>avg(score)}\left ( examinee \right ) \right )$

3) $SELECT_{centr\_code}FROM\;examinee\;join\;appears WHERE\;(score>80)$

$\Pi _{center\_code}\left ( \sigma _{score>80}\left ( examinee\Join appears \right ) \right )$

Comments

Having can't be used without group by, this way in the second query.

---

@Sheshang 

I dont think so standard relational algebra allows aggregate functions.

---

2.

 

​​​​​​

Answer 3

This is right answer.