GATE IT 2008 | Question: 74

Question

Consider the following relational schema:

• $\text{Student} (\underline{\text{school-id}, \text{sch-roll-no}}, \text{sname}, \text{saddress})$
• $\text{School} (\underline{\text{school-id}}, \text{sch-name}, \text{sch-address}, \text{sch-phone})$
• $\text{Enrolment}(\underline{\text{school-id}, \text{sch-roll-no}}, \text{erollno}, \text{examname})$
• $\text{ExamResult}(\underline{\text{erollno}, \text{examname}}, \text{marks})$

What does the following SQL query output?

SELECT	sch-name, COUNT (*)
FROM	School C, Enrolment E, ExamResult R
WHERE	E.school-id = C.school-id
AND
E.examname = R.examname AND E.erollno = R.erollno
AND
R.marks = 100 AND E.school-id IN (SELECT school-id
                                FROM student
                                GROUP BY school-id
                                 HAVING COUNT (*) > 200)
GROUP By school-id

• A. for each school with more than $200$ students appearing in exams, the name of the school and the number of $100s$ scored by its students 

• B. for each school with more than $200$ students in it, the name of the school and the number of $100s$ scored by its students

• C. for each school with more than $200$ students in it, the name of the school and the number of its students scoring $100$ in at least one exam 

• D. nothing; the query has a syntax error

Comments

As per the rule, Any Attribute that is present in the having clause without being aggregated must appear in the group by clause, otherwise query is erroneous

---

If suppose alias “s” was present in the querry, then option “d” would be correct right?

@Ishrat Jahan

---

 rish1602

 

If suppose alias “s” was present in the query, then option “d” would be correct right?
 

 here in this query there are more than one error! so for both the reasons the answer will be D.

 

Answer 1

Correct Answer: D

If Select clause consist aggregate and non - aggregate columns. All non aggregate columns in the Select clause must appear in Group By clause. But in this query Group by clause consists of school-id instead of school-name

http://weblogs.sqlteam.com/jeffs/archive/2007/07/20/but-why-must-that-column-be-contained-in-an-aggregate.aspx

https://dba.stackexchange.com/questions/319724/sqlite-how-does-count-work-without-group-by

Comments

for each school with more than 200 students in it, the name of the school and the number of 100s scored by its students.
Isn't this statement is correct instead of 200 students appearing in exam?

---

If GROUP BY sch-name is given

Output will be:

 There are two conditions. First, for each school more than 200 students in it [SELECT school-id FROM student GROUP BY school-id HAVING COUNT (*) > 200] AND Second, for each school display the count where R.marks=100 i.e count of 100s for each school.

So, answer would be (B) if every thing was correct.

---

All the attributes used in group by clause need to appear in the select clause.

Any Attribute that is not present in the group by clause must appear only inside the aggregate function in the select clause..

This means if other attribute(which are not present in group by clause) have to apeare on the select clause then it should with the aggregate function...

Am i right @pC sir...

Answer 2

4 it has a syntax error coz u cannot select non aggregate attribute with aggregate function

Comments

If school-id was present in select clause along with school name would it generate an error?

@Arjun

---

answer would be B or C if the query ran successfully?

---

Actually such version-dependent questions shouldn’t be asked.

Answer 3

Answer is B as per key provided by IIT.

Comments

Before 2011 official keys are not released by gate organizing institute..

---

How you got key provided by IIT?. It would be B if GROUP BY sch-name is present instead of school-id.

Answer 4

All the non-group expressions(non-aggregate functions) that exist in the select clause along with group functions(aggregate functions) must and should be present in "Group By " Clause but it is not vice-versa.

Therefore, option is D.

Comments

D.

simple logic alias of 's' not present in query

---

fenil_2022

is it the neccessary condition?