Register

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

105 views
0 0 votes

The database contains:

$\mathrm{Users}(\mathrm{uid},\mathrm{name},\mathrm{dob},\mathrm{user\_type})$

$\mathrm{Posts}(\mathrm{pid},\mathrm{folder},\mathrm{summary})$

$\mathrm{Folders}(\mathrm{fid},\mathrm{fname})$

$\mathrm{Postings}(\mathrm{post},\mathrm{position},\mathrm{user},\mathrm{ptext})$

The requirement is:

Return posts having both a student posting and an instructor posting.

Which query strategy or strategies satisfy the requirement

  1. (SELECT P.pid, P.folder, P.summary
 FROM Posts AS P
 INNER JOIN Postings AS Ping ON P.pid = Ping.post
 INNER JOIN Users AS U ON Ping.user = U.uid
 WHERE U.user_type = 'student')
INTERSECT
(SELECT P.pid, P.folder, P.summary
 FROM Posts AS P
 INNER JOIN Postings AS Ping ON P.pid = Ping.post
 INNER JOIN Users AS U ON Ping.user = U.uid
 WHERE U.user_type = 'instructor');

     
  2. (SELECT P.pid, P.folder, P.summary
 FROM Posts AS P
 INNER JOIN Postings AS Ping ON P.pid = Ping.post
 INNER JOIN Users AS U ON Ping.user = U.uid
 WHERE U.user_type = 'student')
EXCEPT
(SELECT P.pid, P.folder, P.summary
 FROM Posts AS P
 INNER JOIN Postings AS Ping ON P.pid = Ping.post
 INNER JOIN Users AS U ON Ping.user = U.uid
 WHERE U.user_type = 'instructor');

     
  3. (SELECT P.pid, P.folder, P.summary
 FROM Posts AS P
 INNER JOIN Postings AS Ping ON P.pid = Ping.post
 INNER JOIN Users AS U ON Ping.user = U.uid
 WHERE U.user_type = 'student'
 OR U.user_type = 'instructor');

     
  4. (SELECT P.pid, P.folder, P.summary
 FROM Posts AS P
 INNER JOIN Postings AS Ping ON P.pid = Ping.post
 INNER JOIN Users AS U ON Ping.user = U.uid
 WHERE U.user_type = 'student'
 AND U.user_type = 'instructor');

1 Answer

1 1 vote

 

Suppose post $\mathrm{P1}$ has:

$\begin{array}{|c|c|} \hline \mathrm{Post} & \mathrm{User\ Type} \\ \hline \mathrm{P1} & \mathrm{student} \\ \mathrm{P1} & \mathrm{instructor} \\ \hline \end{array}$

The requirement is satisfied because two different tuples together establish the condition.

Why A works

The first query produces posts having student postings: $\mathrm{P1}$

The second produces posts having instructor postings: $\mathrm{P1}$

Their intersection contains posts appearing in both sets:

$\mathrm{StudentPosts\ INTERSECT\ InstructorPosts}$

Therefore $\mathrm{P1}$ survives.

Why C is wrong

$\mathrm{student\ OR\ instructor}$

means the post has at least one of the two types.

A post containing only a student would incorrectly qualify.

Why D is wrong

The predicate:

$\mathrm{user\_type}=\text{'student'}$

$\mathrm{AND}$

$\mathrm{user\_type}=\text{'instructor'}$

is evaluated on one joined tuple at a time.

A single $\mathrm{user\_type}$ value cannot simultaneously be both strings, so the result is empty.

Why B is wrong

$\mathrm{StudentPosts\ EXCEPT\ InstructorPosts}$

returns posts having student postings but not instructor postings, which is the opposite of the required both condition.

Hence,

Answer : $\boxed{\mathrm{A}}$

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

3 3 votes
1 1 answer
124
124 views
GO Classes asked Sep 30
124 views
GO Classes DPP | GATE CS, DA | DBMS | SQL Queries
The Adjacency List table stores the data for the tree structure shown below. Which SQL keyword should be inserted at $\mathbf{[a]}$ to retrieve the leaf nodes of the tree...
1 1 vote
1 1 answer
94
94 views
GO Classes asked Sep 30
94 views
GO Classes DPP | GATE CS, DA | DBMS | SQL Queries
Consider:SQL Statement $\mathbf{1}$$\mathrm{SELECT\ *}$ $\mathrm{FROM\ R}$ $\mathrm{UNION}$ $\mathrm{SELECT\ *}$ $\mathrm{FROM\ R}$;SQL Statement $\mathbf{2}$$\mathrm{SEL...
1 1 vote
1 1 answer
102
102 views
GO Classes asked Sep 30
102 views
GO Classes DPP | GATE CS, DA | DBMS | SQL Queries
Consider the following $\mathrm{Product}$ table:$$\begin{array}{|c|c|c|c|} \hline \mathrm{ProductID} & \mathrm{Product} & \mathrm{SupplierID} & \mathrm{UnitPrice} \\ \hli...
1 1 vote
1 1 answer
106
106 views
GO Classes asked Sep 30
106 views
GO Classes DPP | GATE CS, DA | DBMS | Relational Operation
Let $\mathrm{R}$ and $\mathrm{S}$ be relations.For which of the following relational operations is it not necessary that $\mathrm{R}$ and $\mathrm{S}$ be union-compatible...