in Databases edited by
2,171 views
16 votes
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.
in Databases edited by
2.2k views

3 Comments

Can anyone please post SQL query for this?
0

equivalent SQL query :-

select e1.name

from examine e1

where exist ( select *

              from examine e2

              where e1.regno ≠ e2.regno and e1.name = e2.name );
3
Thank you
0

2 Answers

17 votes
 
Best answer
$\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}) $
edited by

9 Comments

Isn't the 2nd answer opposite to what the question is asking?
3
Those who are looking a query 1 with a :O face, the conditions are written for cross product beneath the 'X' symbol that is not a separate line. Also please someone confirms if 'σ" is needed there while writing conditions, I don't think so that it was needed.

Instead of the cross product, we can use condition join with same conditions.
0
IN clause in oracle has a limit of maximum 1000 values that can be compared at a time.

In that case, query using natural join can help
3
We also can write it as

Select name from Examinee where not exists (Select distinct name from Examinee);
0
What is emp2 ?

It should be examinee.name
3

Will it give correct result for two different person with same name and different registration number.

1

@Devesh_Kumar

The query is designed only to select the tuples where registration numbers are not same, but name is same.

So, it'll obviously give correct result for two different persons with same name.

0
What is emp1 and emp2 in ur answer
0

Hi @Ayush Upadhyaya@Shaik Masthan
How above RA will ensure that two person having same name appear more than once in examination?

0
1 vote

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 )$

edited by

5 Comments

you shouldn't use aggregate functions in where clause
5
you are right @Yashaswini. I accept it. I am changing it. and for other readers' concern in case you don't kno, i am just stating here that, "aggregate function can not be used with WHERE clause where it is a main query, let say outer query. We should use HAVING clause for it. but an interesting rule is, aggregate function can be used with WHERE clause if this WHERE belong to the sub-query or nested-query."
1
Having can't be used without group by, this way in the second query.
4

@Sheshang 

I dont think so standard relational algebra allows aggregate functions.

0

2.

 

​​​​​​

1

Related questions