Consider the schema



Student(Id: INTEGER, Name:STRING, Address:STRING, Status:STRING)

Transcript(StudId:INTEGER, CrsCode:STRING, Semester:STRING, Grade:STRING)

(a) The following query



SELECT S.Name

FROM Student S

WHERE 3 >

SELECT COUNT (*)

FROM Transcript T

WHERE S.Id = T.StudId AND

T.Grade = 'A' AND T.Semester = 'S2002'

(a) The following query



SELECT S.Name

FROM Student S

WHERE 3 >

SELECT COUNT (*)

FROM Transcript T

WHERE S.Id = T.StudId AND

T.Grade = 'A' AND T.Semester = 'S2002'

returns the names of all students who got an A in a more than three course in the Spring
semester, 2002. Assuming that the inner and outer queries are optimized separately, what
index would you choose for the inner query? What query plan would it be reasonable to
expect the DBMS to use?
Solution:
A clustered B+ tree index on Transcript with search key (StudId, Grade, Semester).
Scan Student, for each row search the index, the A grades for the corresponding student
in S2002 are grouped and can be easily counted.

b) Convert the query to a non-nested form.
Solution



SELECT S.Name

FROM Student S, Transcript T

WHERE S.Id = T.StudId

GROUP BY S.Id, S.Name, T.Semester, T.Grade

HAVING T.Semester = 'S2002' AND T.Grade = 'A'

AND COUNT(*) > 3

(c) Suggest an index for the new query and a possible query plan.
Solution:
One possibility is to use a clustered B+ tree index with search key Id on Student and the
same index as described above on Transcript. Since the tables are pre-sorted on the Id
attribute, a sort-merge join can be used without having to do any sorting, the A grades
can be counted and appropriate student names can be output in a single pass over the
tables.