edited by
1 flag 18,039 views
52 votes
52 votes

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

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

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

  4. nothing; the query has a syntax error

  • 🚩 Edit necessary | 👮 Arjun | 💬 “better answer needed”
edited by
1 flag

5 Answers

Best answer
76 votes
76 votes

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

edited by
1 flag:
✌ Low quality (wizardx5)
7 votes
7 votes
4 it has a syntax error coz u cannot select non aggregate attribute with aggregate function
1 votes
1 votes
Answer is B as per key provided by IIT.
0 votes
0 votes
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.
Answer:

Related questions

47 votes
47 votes
5 answers
1