Database SQL Query question?

Question

I have the following scenario.Two tables transcript : A student transcript that shows what classes
each student took. Prereq what is the prerequisite for each class.
Transcript
STUDID | CLASSID
----------------------------
STID1 | A
STID1 | B
STID1 | C
STID2 | A

Prereq
CLASSID | PREID
--------------------------
D | A
D | B
D | C


Assume that student STID1 and STID2 are enrolled in class D. I need to write a sql query that will return the student id SID of only the student that satisfied all the prerequisites. That would be student STID1. If I join the two tables with "exists" or "in" I get both students back. I can possibly write a cursor to do this, but I need a single sql query.

Help will be appreciated.

Answer 1

You can think of it as a counting problem.



Count the number of prereq courses for CLASSSID D.



Count the number of prereq courses that the student has taken for CLASSSID D.



Only accept rows where the 2 counts are equal.



Counting Prereq's is easy:

SELECT count(*)

FROM Prereq

WHERE classid = 'D';



Counting Prereq's taken is a little harder, but it's just a join and group by:



SELECT STUDID,count(*)

FROM Transcript

JOIN Prereq USING(CLASSID) -- Or: ON Transcript.CLASSID= Prereq.CLASSID if you prefer.

WHERE Prereq.classid = 'D'

GROUP BY STUDID;



Now, you just need a HAVING clause in that select, that compares count(*) to the count(*) returned by the first query.



Something like this (although this is untested and probably not 100% right):

SELECT STUDID

FROM Transcript

JOIN Prereq USING(CLASSID)

WHERE Prereq.classid = 'D'

GROUP BY STUDID

HAVING count(*) =

( SELECT count(*)

FROM Prereq

WHERE classid = 'D');

Answer 2

Assuming that you have a table Enrollment (studid, classid), a cursor-based solution would clearly be far simpler to write. However, a single query (albeit, probably a slow-processing, resource-sucking one) might be:



SELECT studid AS "Student ID", Class, COUNT(*) AS "Missing prerequisites" FROM

(SELECT studid, e.classid AS Class FROM Enrollment e

JOIN Prereq p ON e.classid = p.classid

WHERE NOT EXISTS

(SELECT 1 FROM Transcript t WHERE t.studid = e.studid AND t.classid = p.preid))

GROUP BY 1, 2



If you wanted to allow currently-enrolled classes to count as prerequisites:



SELECT studid AS "Student ID", Class, COUNT(*) AS "Missing prerequisites" FROM

(SELECT studid, e.classid AS Class FROM Enrollment e

JOIN Prereq p ON e.classid = p.classid

WHERE NOT EXISTS

(SELECT 1 FROM Transcript t WHERE t.studid = e.studid AND t.classid = p.preid)

AND NOT EXISTS

(SELECT 1 FROM Enrollment e2 WHERE e2.studid = e.studid AND e2.classid = p.preid))

GROUP BY 1, 2

Answer 3

the only table on your FROM is pupil. in case you pick to seek advice from a column in yet another table on your question, it has to look interior the FROM (and could be on the subject remember of the different tables in some way, the two by a connect or via the the place clause.)