Question

Transcribe the following SQL queries into an equivalent relational algebra expression.

1.

SELECT s_number , AVG( grade )
FROM grades
GROUP BY s_number
HAVING AVG( grade ) > 5 . 5 ;

2.

SELECT SUM( volume )
FROM fertilizer_applications
LEFT JOIN crop_field
ON fertilizer_applications . crop_field_id = crop_field . id
GROUP BY field_number
HAVING field_number = 3 ;

Answer 1

1.

SELECT s_number , AVG( grade )
FROM grades
GROUP BY s_number
HAVING AVG( grade ) > 5 . 5 ;

============================================================================================

π_{s_number,AVG(grade)} σ_{AVG(grade)>5.5} γ_{s_number,AVG(grade)} (grades)

============================================================================================

2.

SELECT SUM( volume )
FROM fertilizer_applications
LEFT JOIN crop_field
ON fertilizer_applications . crop_field_id = crop_field . id
GROUP BY field_number
HAVING field_number = 3 ;

============================================================================================

π_SUM(volume) σ_{field_number=3} γ_{field_number,SUM(volume)} σcrop_field⟕_{fertilizer_applications.crop_field_id=crop_filed.id}fertilizer_applications(fertilizer_applications)