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

https://youtu.be/esSG9AnMiSc?si=nMMcKWAafcRgxjlE        

best explanation

---

why option b is incorrect, all logical flow is valid here. @Deepak Poonia sir check this

---

If we think logically, like school id can be mapped to school name, cuz each school name has a unique school id in the best case scenario, but there could be two schools with same names and different school id because sch-name id not the primary key

if we group by school id and try to display school name it should work(logically speaking) because each school id may have a school name, but what if it was other way around like

group by school name and select school id, there maybe conflicts and errors cause two schools could have same names but different school ids, so it cant be displayed thats why the query looks like it will work but in theory it wont!!

@Tushar Rana

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

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 S.school-id IN (SELECT school-id
                                FROM student
                                GROUP BY school-id
                                 HAVING COUNT (*) > 200)
GROUP By school-id

All non aggregate coloums in SELECT caluse must appear in GROUP BY Caluse

---

here ans D is okay, but can anyone tell me what does internal query returns ??

or please explain, what if it was select school-id, count(*) in the begning ...

---

Then answer should be c.

---

@pc boss here what is mean by non aggregate coloums ? 

---

every SQL query terminates by a semicolon. Isn't it reason for sytax error??

@pc I read in an answer by arjun sir that All non aggregate coloums in SELECT caluse must appear in GROUP BY Caluse is not necessary now. It's given in books but practically not necessary

---

give the link..

---

Aggergate collums comes from the word aggregate functions such as avg(average),min,max,sum here when one of these collums  present in select clause these need not be in group by clause.

---

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 S.school-id IN (SELECT school-id
                                FROM student
                                GROUP BY school-id
                                 HAVING COUNT (*) > 200)
GROUP By school-name

then answer will be

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.

---

http://searchsqlserver.techtarget.com/answer/ISO-ANSI-SQL-and-the-GROUP-BY-clause

Expressions in the GROUP BY clause can contain columns of the tables, derived tables or views in the FROM clause. The columns are not required to appear in the SELECT clause <select> list.

Each table or view column in any nonaggregate expression in the <select> list must be included in the GROUP BY list:

Not all attributes used in the GROUP BY clause need to appear in the SELECT clause

Discussion: https://gateoverflow.in/47/gate2012_15

---

@prashant

I don't think that is correct answer 

(SELECT school-id  FROM student GROUP BY school-id  HAVING COUNT (*) > 200)

don't check schools with " school with more than 200 students appearing in exams"

It just check no of students must be greater than 200.

Also each student can enroll in multiple schools and courses (as no primary key constraint given for student school-id, sch-roll-no -> can be like(1,1) and (2,1) i.e. student with id 1 is with school 1 and 2.) 

(he can enroll for multiple courses because , as no PK constraints given

Enrolment(school-id sch-roll-no, erollno) -> can be (1,1,1) (1,1,2) (2,1,1) 

i.e.  (1,1,1) -> student with sch-roll-no =1 which is with school-id =1 enrolled for erollno=1

      (1,1,2) -> student with sch-roll-no =1 which is with school-id =1 enrolled for erollno=2

      (2,1,1)  ->  student with sch-roll-no =1 which is with school-id =2 enrolled for erollno=1

Hence correct answer would be 

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 .

Please reply and correct me if my answer is wrong 

also did you noticed apart from mistake in group by clause there is 

S.school-id  S is not alias of anything

---

Answer is B as per key provided by IIT.

---

Yes, exactly. S is not an alias of anything.

Doesn't that also makes the query syntax wrong?

---

Can u plz elaborate these terms?
Group by X

X can be super set of non aggregate attribute? or exactly all non aggregate attribute??

Aggregate and non aggregate column?

---

@mehul yeah that's a valid point...these will also be one of the reasons for error ..

---

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...

---

@satyaAchar yes you are right. 

Note- "If we use atleast one aggregate function (in select clause) then "No Unaggregate attribute allowed"(unless there is a group by clause).

---

But their should be at leat 100 in one exam

---

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

this statement is false @satyaAchar

Answer 2

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

Comments

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

---

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

---

How do you know its version dependent

Answer 3

D

there are two reasons for syntax error:-

1. aliasing S cannot be found in the from clause
2. attribute present in group by clause is not appering in select clause

Comments

The query essentially performs the following steps:

1. Joins the School, Enrolment, and ExamResult tables based on matching school-id, examname, and erollno.

2. Filters the results to include only rows where the marks is 100.

3. Uses a subquery to filter school-id values that have more than 200 students enrolled (COUNT (*) > 200).

4. Groups the final results by school-id and selects the school name and the count of 100s scored by its students.

Given this understanding, let's evaluate the multiple-choice options:

Option 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.

• This option closely matches the logic of the query.

Option 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.

• This option does not match as it doesn't specifically state that the students are appearing in exams.

Option 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.

• This option doesn't fully match the logic as it mentions the number of its students scoring 100, not just the count of 100s scored.

Option D: nothing; the query has a syntax error.

• This option is incorrect as the query has no syntax errors.

So, the best match is Option 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.

Answer 4

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 5

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?

Answer 6

If aggregated attribute is used in the select clause then it can have only those unaggregated attributes which are in group by clause. Here the group by clause uses school-id and select clause has sch-name as unaggregated attribute hence this syntax is not correct.

https://gateoverflow.in/47/gate-cse-2012-question-15 this gate pyq is also based on the same concept.