SQL - solutions adda

Question 28

Consider the following relational schema:

Student (school-id, sch-roll-no, sname, saddress)
School (school-id, sch-name, sch-address, sch-phone)
Enrolment(school-id, sch-roll-no, erollno, examname)
ExamResult(erollno, examname, 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 S.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

Database-Management-SystemSQLGATE 2008-IT

Question 28 Explanation:

If select clause consist of aggregate and non-aggregate columns, all non-aggregate columns in the select clause must appear in Group By clause. But in this Group By clause consists school-id instead of school-name.

Correct Answer: D

Question 28 Explanation:

If select clause consist of aggregate and non-aggregate columns, all non-aggregate columns in the select clause must appear in Group By clause. But in this Group By clause consists school-id instead of school-name.