0

As part of my SQL course (am a mere novice student) I have a database which is based around students and tests that they can take as part of a study course.

So I have a table of students, a table of tests, a table of authors, a table of questions linked to tests etc.

One assignment asks me to create various views, so for example

this view that shows instances of tests that were passed:

CREATE OR REPLACE VIEW vuStudentsPassedAnyTest
AS 
SELECT outer.passing_grade,s.firstname, s.lastname, h.test_id,h.student_id,h.score
FROM test_history h,students s,test_id outer
WHERE h.student_id=s.student_id 
AND outer.test_id=h.test_id
AND score > = (SELECT passing_grade
                 FROM test_id inner
                 WHERE inner.test_id=h.test_id);

or this one that displays questions that have never been asked

SELECT question_id
FROM questions
MINUS
SELECT question_id
FROM student_answer_history;

So fairly straightforward views in themselves, there are about 5 of them in all.

Here comes the part I'm struggling with:

The second part of the assignment asks me to recreate these views more efficiently using hierarchical retrieval.

I don't see any obvious hierarchy except that one test could be passed by many students, or one student could have several tests they have passed.

I don't think I would use hierarchical queries in these views, except that I'm being asked to..and because there's no table that has a parent id type column I am struggling with how it would even be done.

I am pretty clear on the principle and how it works, say using the hr employees table, where employee_id and manager_id represent a clear parent/child relationship, and which most examples I have found reference, but can anyone give me a steer as to me how it can be incorporated into these kind of views?

thanks!!

user3704497
  • 21
  • 2
  • Your example query suggests that you're using an odd flavor of sql. Perhaps this database has a concept of "hierarchical retrieval" or maybe there's a definition for that in your textbook. So I'm wondering if you're thinking about the wrong kind of hierarchy. – shawnt00 Jun 08 '14 at 14:55
  • hi it can only be the kind of conventional hierarchy i.e. parent>>child using start with/connect by prior etc, as that's the only one we've covered in the course. Problem is I don't see how it's really applicable to these views.. – user3704497 Jun 09 '14 at 15:08

0 Answers0