edited by
10,606 views
54 votes
54 votes

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

  1. $6$
  2. $4$
  3. $2$
  4. $0$
edited by

4 Answers

Best answer
77 votes
77 votes

SQL query will return:
$$\begin{array}{|c|c|c|} \hline \textbf{Roll} &  \textbf {Hostel} \\\hline \text 2369&  7 \\\hline 2581& 6 \\\hline 2643& 5 \\\hline2643  & 5 \\ & \text{Duplicate Row is present}\\&\text {in Hobby table} \\\hline2872 & 5 \\\hline  2926 & 5\\\hline 2959 & 7  \\\hline \end{array}$$

Total $7$ rows are selected.

In RA only distinct values of hostels are selected i.e. $5,6,7 $

SQL row count - RA row count $= 7 - 3 = 4$

Answer is B.

selected by
8 votes
8 votes

The result of SQL Query will be:

Result of JOIN Operation
Roll Name Hostel Marks Hobbyname
2369 Gumma Reddy 7 86 Swimming
2581 Pradeep Pendse 6 92 Cricket
2643 Suhas Kulkarni 5 78 Chess
2643 Suhas Kulkarni 5 78 Hockey
2872 Kiran Vora 5 92 Football
2926 Manoj Kunkalikar 5 94 Cricket
2959 Hemant Karkhanis 7 88 Photography

SQL select statement retains duplicates while projection operator in RA doesn't show duplicates.

$\therefore$Number of rows in SQL Query(7,6,5,5,5,5,7) = 7

And, Number of rows in RA Query(7,6,5) = 3.

Difference = $7-3=4$.

Answer: (B)

1 votes
1 votes
ans B

SQL will not avoid the duplicates.To avoid duplicates in SQL we have to use the keyword "DISTINCT",here in sql it will count the duplicates also , so total no. of rows output by sql is 8 .
Projection will avoid the duplicates so no. of tuples selected=4

difference=8-4=4
0 votes
0 votes

Both queries are the same, but SQL allows duplicates, while Relational Algebra eliminates them.
SQL query: return 7 rows. (Hostel Numbers - 7,6,5,5,5,5,7)

RA query: return 3 rows (Hostel numbers: 7,6,5) // duplicates removed.

So, Option B

Answer:

Related questions