Register

GO Classes DPP | GATE CS, DA | DBMS | SQL

204 views
3 3 votes

Given:

$\mathrm{Homes}(\mathrm{home\_id},\mathrm{city},\mathrm{bedrooms},\mathrm{bathrooms},\mathrm{area})$

$\mathrm{Transactions}(\mathrm{home\_id},\mathrm{buyer\_id},\mathrm{seller\_id},\mathrm{transaction\_date},\mathrm{sale\_price})$

$\mathrm{Buyers}(\mathrm{buyer\_id},\mathrm{name})$

$\mathrm{Sellers}(\mathrm{seller\_id},\mathrm{name})$

Which SQL query returns a duplicate-free set of home IDs for homes that:

  • are in Berkeley,
     
  • have at least $6$ bedrooms,
     
  • have at least $2$ bathrooms,
     
  • were bought by $\text{'Bobby Tables'}$?
     
  1. $\mathrm{SELECT\ H.home\_id}$
    $\mathrm{FROM\ Homes\ H,\ Transactions\ T,\ Buyers\ B}$
    $\mathrm{WHERE\ H.home\_id=T.home\_id}$
    $\mathrm{AND\ T.buyer\_id=B.buyer\_id}$
    $\mathrm{AND\ H.city}=\text{'Berkeley'}$
    $\mathrm{AND\ H.bedrooms}\geq 6$
    $\mathrm{AND\ H.bathrooms}\geq 2$
    $\mathrm{AND\ B.name}=\text{'Bobby Tables'}$;
     
  2. $\mathrm{SELECT\ DISTINCT\ H.home\_id}$
    $\mathrm{FROM\ Homes\ H,\ Transactions\ T,\ Buyers\ B}$
    $\mathrm{WHERE\ H.home\_id=T.home\_id}$
    $\mathrm{AND\ T.buyer\_id=B.buyer\_id}$
    $\mathrm{AND\ H.city}=\text{'Berkeley'}$
    $\mathrm{AND\ H.bedrooms}\geq 6$
    $\mathrm{AND\ H.bathrooms}\geq 2$
    $\mathrm{AND\ B.name}=\text{'Bobby Tables'}$;
     
  3. $\mathrm{SELECT\ DISTINCT\ H.home\_id}$
    $\mathrm{FROM\ Homes\ H,\ Transactions\ T,\ Buyers\ B}$
    $\mathrm{WHERE\ H.home\_id=T.buyer\_id}$
    $\mathrm{AND\ T.home\_id=B.buyer\_id}$
    $\mathrm{AND\ H.city}=\text{'Berkeley'}$
    $\mathrm{AND\ H.bedrooms}\geq 6$
    $\mathrm{AND\ H.bathrooms}\geq 2$
    $\mathrm{AND\ B.name}=\text{'Bobby Tables'}$;
     
  4. $\mathrm{SELECT\ DISTINCT\ H.home\_id}$
    $\mathrm{FROM\ Homes\ H,\ Transactions\ T}$
    $\mathrm{WHERE\ H.home\_id=T.home\_id}$
    $\mathrm{AND\ H.city}=\text{'Berkeley'}$
    $\mathrm{AND\ H.bedrooms}\geq 6$
    $\mathrm{AND\ H.bathrooms}\geq 2$;

1 Answer

2 2 votes
$\mathrm{Homes}$ must join to $\mathrm{Transactions}$ using $\mathrm{home\_id}$, and $\mathrm{Transactions}$ must join to $\mathrm{Buyers}$ using $\mathrm{buyer\_id}$.

The buyer-name condition requires the $\mathrm{Buyers}$ relation.

$\mathrm{DISTINCT}$ is necessary because the output is required to be duplicate-free.

Therefore, the correct query is the one that uses both joins correctly and includes $\mathrm{DISTINCT}$.

Hence,

Answer : $\boxed{\mathrm{B}}$
Voting & Accepting an answer helps improve the community
Answer:
← Previous · GO Classes DPP | GAT...Next → · GO Classes DPP | GAT...
← Previous in category · GO Classes DPP | GAT...Next in category → · GO Classes DPP | GAT...
Position:
Show:

Related questions

0 0 votes
1 1 answer
131
131 views
GO Classes asked Sep 29
131 views
GO Classes DPP | GATE CS, DA | DBMS | SQL
Given the relation: $$\begin{array}{|c|c|c|c|} \hline \mathrm{Sname} & \mathrm{Sid} & \mathrm{Region} & \mathrm{Quota} \\ \hline \mathrm{Frances} & 25 & \mathrm{TX} & 100...
0 0 votes
1 1 answer
134
134 views
GO Classes asked Sep 29
134 views
GO Classes DPP | GATE CS, DA | DBMS | SQL
An auditing company was hired to analyze the database of the SUS (Unified Health System). Its first task is to identify pairs of registered doctors who have the same name...
0 0 votes
1 1 answer
107
107 views
GO Classes asked Sep 29
107 views
GO Classes DPP | GATE CS, DA | DBMS | SQL
Two tables are given below : $$\begin{array}{c@{\qquad}c}\mathrm{\mathbf{Orders}} & \mathrm{\mathbf{Products}} \\[4pt]\begin{array}{|c|c|c|}\hline\mathrm{Date} & \mathrm{...
0 0 votes
1 1 answer
122
122 views
GO Classes asked Sep 29
122 views
GO Classes DPP | GATE CS, DA | DBMS | SQL
A table $\mathrm{Scores}$ stores each student's marks in Japanese and Mathematics.Let,$A$ mean the student's Japanese score is at least the Japanese average.$B$ mean the ...