Register

GO Classes DPP | GATE CS, DA | DBMS | WHERE vs HAVING

ago
103 views
0 0 votes

The relation $\mathrm{ExamResult}$ stores examination records for the previous three years:

$\mathrm{ExamResult}(\mathrm{StudentNo},\mathrm{ExamDate},\mathrm{Score},\mathrm{ClassName})$.

Which SQL query retrieves the class name and average score for classes whose average score during academic year $\mathbf{2014}$ was at least $\mathbf{600}$?

Academic year $2014$ runs from $\text{'2014-04-01'}$ through $\text{'2015-03-31'}$.

  1. SELECT ClassName, AVG(Score)
FROM ExamResult
GROUP BY ClassName
HAVING AVG(Score) >= 600;

     
  2. SELECT ClassName, AVG(Score)
FROM ExamResult
WHERE ExamDate BETWEEN '2014-04-01' AND '2015-03-31'
GROUP BY ClassName
HAVING AVG(Score) >= 600;

     

  3. SELECT ClassName, AVG(Score)
FROM ExamResult
WHERE ExamDate BETWEEN '2014-04-01' AND '2015-03-31'
GROUP BY ClassName
HAVING Score >= 600;

     
  4. SELECT ClassName, AVG(Score)
FROM ExamResult
WHERE Score >= 600
GROUP BY ClassName
HAVING MAX(ExamDate)
       BETWEEN '2014-04-01' AND '2015-03-31';

1 Answer

0 0 votes

There are two different restrictions.

First, only examination rows from academic year $2014$ should participate in the calculation. 

That is a row-level condition, so it belongs in $\mathrm{WHERE}$.

$\mathrm{WHERE\ ExamDate\ BETWEEN}\ \text{'2014-04-01'}\ \mathrm{AND}\ \text{'2015-03-31'}$

After those rows are selected, they are grouped by class:

$\mathrm{GROUP\ BY\ ClassName}$

Then the average is calculated for each group. We retain only groups whose average is at least 600:

$\mathrm{HAVING\ AVG(Score)}\geq 600$

The conceptual flow is:

$\mathrm{WHERE}$ $\rightarrow$ $\mathrm{GROUP\ BY}$ $\rightarrow$ $\mathrm{AVG}$ $\rightarrow$ $\mathrm{HAVING}$

Option A incorrectly includes examination records from all three years.

Option C applies $\mathrm{Score}\geq 600$ as if $\mathrm{Score}$ had one value for the entire group.

Option D first discards all individual scores below $600$, which changes the average itself.

 

For eg, 

 

So, Answer : $\boxed{\mathrm{B}}$

ago
Voting & Accepting an answer helps improve the community
Answer:
← Previous · GATE BM,CE1,CH,EY,GG...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
108
108 views
GO Classes asked 6 days ago
108 views
GO Classes DPP | GATE CS, DA | DBMS | SQL Queries
The database contains relations $\mathrm{animals}$ and $\mathrm{visits}$, with $\mathrm{aid}$ identifying an animal.Which SQL query returns the names of animals that visi...
0 0 votes
1 1 answer
87
87 views
GO Classes asked 6 days ago
87 views
GO Classes DPP | GATE CS, DA | DBMS | Evaluation Order
Conceptually, in which order does SQL process the following three operations?$\mathrm{WHERE}\rightarrow\mathrm{HAVING}\rightarrow\mathrm{GROUP\ BY}$ $\mathrm{WHERE}\right...
0 0 votes
1 1 answer
97
97 views
GO Classes asked 6 days ago
97 views
GO Classes DPP | GATE CS, DA | DBMS | SQL
The $\mathrm{Employee}$ relation contains:$$\begin{array}{|c|c|c|c|} \hline \mathrm{EmployeeNo} & \mathrm{Name} & \mathrm{DateOfBirth} & \mathrm{Department} \\ \hline 000...
0 0 votes
1 1 answer
96
96 views
GO Classes asked 6 days ago
96 views
GO Classes DPP | GATE CS, DA | DBMS | GROUP BY & ORDER BY
The relation is:$\mathrm{Midterm}(\mathrm{ClassName},\mathrm{SubjectName},\mathrm{StudentNo},\mathrm{Name},\mathrm{Score})$.The objective is to calculate the average scor...