Student (school-id, sch-roll-no, sname, saddress) School (school-id, sch-name,…

2008

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?

C

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
 /* Add code here. Remove these lines if not writing code */

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

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

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

  4. D.

    nothing; the query has a syntax error

Attempted by 92 students.

Show answer & explanation

Correct answer: D

Issue: the original query uses S.school-id in the WHERE clause but there is no table or alias named S defined (the School table is aliased as C). This causes a syntax/semantic error.

Fixed query:

SELECT C.sch-name, COUNT(*)

FROM School C

JOIN Enrolment E ON E.school-id = C.school-id

JOIN ExamResult R ON E.examname = R.examname AND E.erollno = R.erollno

WHERE R.marks = 100

AND C.school-id IN (

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

)

GROUP BY C.school-id, C.sch-name;

What this corrected query returns:

  • It restricts schools to those having more than 200 rows in the Student table (so: more than 200 registered students).

  • For each such school it returns the school's name and the count of joined rows where R.marks = 100, i.e. the number of perfect-score exam rows for that school's students.

  • If a student scored 100 in multiple exams, each 100 is counted separately.

If instead you want the number of distinct students who scored 100 at least once, use:

SELECT C.sch-name, COUNT(DISTINCT E.sch-roll-no) -- or COUNT(DISTINCT E.erollno) if erollno identifies a student

FROM School C

JOIN Enrolment E ON E.school-id = C.school-id

JOIN ExamResult R ON E.examname = R.examname AND E.erollno = R.erollno

WHERE R.marks = 100

AND C.school-id IN (SELECT school-id FROM Student GROUP BY school-id HAVING COUNT(*) > 200)

GROUP BY C.school-id, C.sch-name;

Summary: the original query fails due to the undefined alias S. After fixing aliases and grouping, the query returns school names and counts of perfect-score rows for schools that have more than 200 students; to count distinct students with at least one perfect score, use COUNT(DISTINCT ...).

Explore the full course: Gate Guidance By Sanchit Sir