## SQL and Relational Algebra short exercises solved

## SQL and Relational Algebra Exercises

1. Consider relation

*R*(_{1}*A, B, C*) and R_{2}(*B,D*), where number of tuples in R_{1},*T*(*R*) = 25 and number of tuples in R_{1}_{2},*T*(_{R2}) = 40, and*B*is a key for*R*. How many tuples_{1}*R*_{1}*⋈ R*will give?_{2}

__Answer:__**40 tuples**

*B is the foreign key in R*

_{2}. The permissible values for attribute B in R_{2}are the values that are stored in attribute B of R_{1}. The value of B can be duplicated. Hence, the result is all the tuples from R_{2}.
2. Write the following query in relational algebra, for
relations

*R*(*a, b*) and*S*(*c, d*):**select***a*,*d***from***R*,*S***where***R.a >*10 and*R.b*=*S.c*.

__Answer:__

*π*(_{a,d}*σ*_{a>}_{10}(*R*

*⋈*

_{b}

_{=c}

*S***))**

3. Write the following relation algebra expression in
SQL, for relations

*Students(regno, name, age)*and*Stu_Tour_Regd(regno, name, tour_amt)*:*π*(_{regno, name}*σ*)_{age > 20}(STUDENTS)*− π*_{regno, name}(STU_TOUR_REGD)

__Answer:__**(SELECT**

*regno, name*FROM*Students*WHERE*age > 20*) EXCEPT (SELECT*regno, name*FROM*Stu_tour_regd*)*This query finds the register number and names of students whose age is greater than 20 and not registered for tour.*

4. For relation

*Student*(*regno, name, dept*), suppose*dept*can be one of {*ite, cse, ece, eee, mech, civil*}. If number of tuples in relation Student is 1000, then what is the size estimate of the query*σ*_{dept }_{= ‘physics’}*(Student)*?

__Answer:__**Nil.**

*Physics is not one of the departments. Hence, no students belong to that department.*

5. How exactly the relational
algebra expression

*R**⋈*_{θ}*S*can be expressed using only the basic relational algebra operators?

__Answer:__

*σ*(_{θ}*R X S*)*The given expression is called as theta join. It is a combination of a join condition (θ) and the join operation (X).*

***********

Go to DBMS - solved exercises page

Go to DBMS - Multiple Choice Questions (MCQs) page

solved exercises in dbms

solved exercises in relational algebra

solved exercises in SQL

SQL and relational algebra short answers

SQL and relational algebra short exercises

## No comments:

## Post a Comment