GATE | Gate IT 2005 | Question 68
A table ‘student’ with schema (roll, name, hostel, marks), and another table ‘hobby’ with schema (roll, hobbyname) contains records as shown below:
Table: Student
Roll |
Name |
Hostel |
Marks |
1798 |
Manoj Rathod |
7 |
95 |
2154 |
Soumic Banerjee |
5 |
68 |
2369 |
Gumma Reddy |
7 |
86 |
2581 |
Pradeep Pendse |
6 |
92 |
2643 |
Suhas Kulkarni |
5 |
78 |
2711 |
Nitin Kadam |
8 |
72 |
2872 |
Kiran Vora |
5 |
92 |
2926 |
Manoj Kunkalikar |
5 |
94 |
2959 |
Hemant Karkhanis |
7 |
88 |
3125 |
Rajesh Doshi |
5 |
82 |
Table: hobby
Roll |
Hobbyname |
1798 |
chess |
1798 |
music |
2154 |
music |
2369 |
swimming |
2581 |
cricket |
2643 |
chess |
2643 |
hockey |
2711 |
volleyball |
2872 |
football |
2926 |
cricket |
2959 |
photography |
3125 |
music |
3125 |
chess |
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’ = ∏hostel ((σs.roll = H.roll (σmarks > 75 and roll > 2000 and roll < 3000 (S)) X (H))
The difference between the number of rows output by the SQL statement and the number of tuples in S’ is
(A) 6
(B) 4
(C) 2
(D) 0
Answer: (B)
Explanation: Output of above Query
Roll |
Hostel |
2369 |
7 |
2581 |
6 |
2643 |
5 |
2643 |
5 |
2872 |
5 |
2926 |
5 |
2959 |
7 |
Total rows selected by running SQL Query: 7
Total rows by Relation Algebra : 4 i.e 5,6,7 (Unique values only)
7-3=4
Answer is B
Quiz of this Question
Please comment below if you find anything wrong in the above post
Last Updated :
28 Jun, 2021
Like Article
Save Article
Share your thoughts in the comments
Please Login to comment...