The Gateway to Computer Science Excellence
First time here? Checkout the FAQ!
x
+11 votes
541 views

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

  1. Write a relational algebra using $( \Pi, \sigma, \rho, \times)$ to find the list of names which appear more than once in examinee.
  2. Write an SQL query to list the regno of examinees who have a score greater than the average score.
  3. Suppose the relation appears (regno, centr_code) specifies the center where an examinee appears. Write an SQL query to list the centr_code having an examinee of score greater than 80.
asked in Databases by Veteran (68.8k points)
edited by | 541 views

2 Answers

+8 votes
Best answer

1) ρ (exm1,examinee) , ρ(exm2,examinee)

Πexm1.name(emx1 × exm2)
                        σ((emp1.regn0 !=emp2.regn0) and (emp1.name =emp2.name))                          

2)  select regno from examinee
      where score > (select avg(score) from examinee);

3)select centr_code from appears 
where regno IN (SELECT regno from examinee where score>80);

 
answered by Boss (9.9k points)
selected by
Isn't the 2nd answer opposite to what the question is asking?
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.
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
+2 votes

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

answered by Loyal (3.5k points)
edited by
you shouldn't use aggregate functions in where clause
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."
Having can't be used without group by, this way in the second query.

Related questions



Quick search syntax
tags tag:apple
author user:martin
title title:apple
content content:apple
exclude -tag:apple
force match +apple
views views:100
score score:10
answers answers:2
is accepted isaccepted:true
is closed isclosed:true

32,470 questions
39,199 answers
108,999 comments
36,575 users