GATE CS 2011 Q.46 (CS Q.46): SQL Queries and Integrity Constraints

Database table by name Loan_Records is given below.

Borrower	Bank_Manager	Loan_Amount
Ramesh	Sunderajan	10000.00
Suresh	Ramgopal	5000.00
Mahesh	Sunderajan	7000.00

What is the output of the following SQL query?

SELECT count(*)
FROM (
    (SELECT Borrower, Bank_Manager FROM Loan_Records) AS S
    NATURAL JOIN
    (SELECT Bank_Manager, Loan_Amount FROM Loan_Records) AS T
);

Official answer and solution

Official answer: C
From: Official GATE 2011 answer key

Solution

Trace the projections and natural join on the common attribute Bank_Manager.

Projection $S = \Pi_{\text{Borrower}, \text{Bank\_Manager}}(\text{Loan\_Records})$ contains:

(Ramesh, Sunderajan)
(Suresh, Ramgopal)
(Mahesh, Sunderajan)

Projection $T = \Pi_{\text{Bank\_Manager}, \text{Loan\_Amount}}(\text{Loan\_Records})$ contains:

(Sunderajan, 10000.00)
(Ramgopal, 5000.00)
(Sunderajan, 7000.00)

Natural join $S \bowtie T$ equates the common attribute Bank_Manager:

For Bank_Manager = Sunderajan: 2 records in $S$ join with 2 records in $T$ , yielding $2 \times 2 = 4$ joined tuples.
For Bank_Manager = Ramgopal: 1 record in $S$ joins with 1 record in $T$ , yielding $1 \times 1 = 1$ joined tuple.

Total tuples in the natural join = $4 + 1 = 5$ .

Final answer

The answer is the one in the official GATE answer key. The solution is this site's own explanation.

Source

Paper: GATE 2011 Computer Science and Information Technology (CS)
Question: 46 of 65, printed as CS Q.46
Section: Computer Science and Information Technology

Report an error in this question

GATE questions are the work of the organising institutes. This site is independent and is not affiliated with them.

Question

Solution

Source