A table 'student' with schema (roll, name, hostel, marks), and another table 'hobby' with schema (roll, hobbyname) contains records as shown below:
$$\overset{\text{Table: student}}{\begin{array}{|c|c|c|c|} \hline \textbf{Roll} & \textbf {Name} & \textbf {Hostel} & \textbf{Marks} \\\hline \text{1798} & \text{Manoj Rathor} & \text{7} & \text{95} \\\hline \text{2154} & \text{Soumic Banerjee} & \text{5} & \text{68}\\\hline \text{2369} & \text{Gumma Reddy} & \text{7} & \text{86}\\\hline\text{2581} & \text{Pradeep pendse} & \text{6} & \text{92}\\\hline \text{2643} & \text{Suhas Kulkarni} & \text{5} & \text{78} \\\hline \text{2711} & \text{Nitin Kadam} & \text{8} & \text{72}\\\hline \text{2872}& \text{Kiran Vora} & \text{5} & \text{92}\\\hline\text{2926} & \text{Manoj Kunkalikar} & \text{5} & \text{94}\\\hline \text{2959}& \text{Hemant Karkhanis} & \text{7} & \text{88}\\\hline\text{3125} & \text{Rajesh Doshi} & \text{5} & \text{82}\\\hline \end{array}} \qquad \overset{\text{Table: hobby}}{\begin{array}{|c|c|} \hline \textbf{Roll} & \textbf {Hobby Name} \\\hline \text{1798} & \text{chess} \\\hline \text{1798} & \text{music} \\\hline \text{2154} & \text{music} \\\hline \text{2369} & \text{swimming}\\\hline \text{2581} & \text{cricket} \\\hline \text{2643} & \text{chess}\\\hline\text{2643} & \text{hockey} \\\hline \text{2711} & \text{volleyball}\\\hline \text{2872} & \text{football} \\\hline \text{2926} & \text{cricket} \\\hline \text{2959} & \text{photography} \\\hline \text{3125} & \text{music}\\\hline \text{3125}& \text{chess}\\\hline \end{array}}$$
The following SQL query is executed on the above tables:
select hostel
from student natural join hobby
where marks >= 75 and roll between 2000 and 3000;
Relations $S$ and $H$ with the same schema as those of these two tables respectively contain the same information as tuples. A new relation $S’$ is obtained by the following relational algebra operation:
$$S’ = \Pi_{\text{hostel}} ((\sigma_{s.roll = H.roll} (\sigma_{marks > 75\text{ and }roll > 2000\text{ and }roll < 3000} (S)) \times (H))$$
The difference between the number of rows output by the SQL statement and the number of tuples in $S’$ is
- $6$
- $4$
- $2$
- $0$
