SQL and Relational Algebra short exercises solved

SQL and Relational Algebra short exercises solved

SQL and Relational Algebra Exercises

1. Consider relation R₁(A, B, C) and R₂(B,D), where number of tuples in R₁, T(R₁) = 25 and number of tuples in R₂, T(_R2) = 40, and B is a key for R₁. How many tuples R₁ ⋈ R₂ will give?

Answer: 40 tuples

B is the foreign key in R₂. The permissible values for attribute B in R₂ are the values that are stored in attribute B of R₁. The value of B can be duplicated. Hence, the result is all the tuples from R₂.

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