Home » SQL & PL/SQL » SQL & PL/SQL » pre requisit question (10g, vista)
pre requisit question [message #568556] Sun, 14 October 2012 00:35 Go to next message
rzkhan
Messages: 320
Registered: March 2005
Senior Member
I have two tables:


drop table prereqs
/
 create table prereqs
 (
 course_seq_no number(4),
 pre_req number(4),
 prtype varchar2(7)
 )
/




drop table src;

create table src
(
reg_no number(8),
course_seq_no number(4),
semester_code number(1),
semester_year number(4),
gradecharvalue varchar2(2)
)
/



---with sample data




 insert into prereqs values(169,244,'PassAll');
 insert into prereqs values(169,160,'PassAll');
 insert into prereqs values(96,277,'PassAny');
 insert into prereqs values(96,643,'PassAny');



insert into src values(2013192,277,3,2010,'B');
insert into src values(2013192,148,3,2010,'C+');
insert into src values(2013192,244,3,2010,'B-');
insert into src values(2013192,1,3,2010,'B+');
insert into src values(2013192,367,3,2010,'D');
insert into src values(2013192,155,3,2010,'A-');
insert into src values(2013192,3,3,2010,'C+');
insert into src values(2013192,242,3,2010,'C+');
insert into src values(2013192,5,1,2011,'C');
insert into src values(2013192,156,1,2011,'B-');
insert into src values(2013192,250,1,2011,'B+');
insert into src values(2013192,248,1,2011,'C+');
insert into src values(2013192,149,1,2011,'C+');
insert into src values(2013192,243,1,2011,'W');
insert into src values(2013192,162,2,2011,'C');
insert into src values(2013192,205,1,2011,'B-');
insert into src values(2013192,159,3,2011,'F');
insert into src values(2013192,150,3,2011,'B+');
insert into src values(2013192,64,3,2011,'C');
insert into src values(2013192,66,3,2011,'B-');
insert into src values(2013192,244,3,2011,'D+');
insert into src values(2013192,164,3,2011,'C+');
insert into src values(2013192,170,3,2011,'C-');
insert into src values(2013192,322,1,2011,'B-');
insert into src values(2013192,243,2,2011,'D+');
insert into src values(2013192,165,1,2012,'B-');
insert into src values(2013192,152,1,2012,'C-');
insert into src values(2013192,68,1,2012,'B-');
insert into src values(2013192,70,1,2012,'B-');
insert into src values(2013192,664,1,2012,'B-');
insert into src values(2013192,158,1,2012,'C');
insert into src values(2013192,126,3,2012,'');
insert into src values(2013192,151,3,2012,'');
insert into src values(2013192,159,3,2012,'');
insert into src values(2013192,180,3,2012,'');
insert into src values(2013192,666,3,2012,'');
insert into src values(2013192,172,3,2012,'');
insert into src values(2013192,169,3,2012,'');

insert into src values(2013200,96,3,2012,'');
insert into src values(2013200,277,1,2012,'F');

-------------------------------------------------------


tables used:

SRC: Course registration table
prereqs: pre requisit table


Key: course_seq_no


Rules:

Students register their courses in SRC. its pre requisit are present in table prereqs.
Pre requisits are of two types. PassAll and PassAny.

1 if PassAll then all pre requisits should be passed. (Pass means not having F grade) 

example:

FOR COURSE_SEQ_NO 169 there are two pre requisits 244 and 160.

This means that for regno 2013192, course 169, two courses ie. 244 and 160 should exist in SRC
and both must not have gradecharvalue 'F' in last regisered semester.


2 if PassAny then any prerequisit should be passed. (Pass means not having F grade) 


example:

FOR COURSE_SEQ_NO 96 there are two pre requisits 277 and 643.

This means that for regno 2013192, course 96, ANY OF THESE TWO pre_reqs ie. 277 and 643
should exist in SRC
and AT LEAST ONE must HAVE gradecharvalue <> 'F' in ANY regisered semester.


Question:


Now I have to find out what regnos have voilated the rules.

ie.


regno	course_seq_no	pre_req		remarks
2013192	169		160		(not registerd)
2013200	96		277		F

	
 


Any help please using simple SQL or PLSQL .


RZKHAN

[Updated on: Sun, 14 October 2012 00:46]

Report message to a moderator

Re: pre requisit question [message #568558 is a reply to message #568556] Sun, 14 October 2012 01:23 Go to previous messageGo to next message
John Watson
Messages: 4081
Registered: January 2010
Location: Global Village
Senior Member
What code have you written so far? If you post it here and explan which part you are having trouble with, someone may help.
Or are you stuck at the the design stage? For example, are you still trying to write pseudocode that describes the flow? If so, again, post what you have done so far.
Re: pre requisit question [message #568583 is a reply to message #568558] Sun, 14 October 2012 22:25 Go to previous messageGo to next message
rzkhan
Messages: 320
Registered: March 2005
Senior Member
One approach I used:



Using Simple query, I have the following code just for one student..
this code used for only courses of type PassAll.


SQL> select v1.*,v2.*
  2  from
  3  (select s.reg_no,s.course_seq_no as seq1,p.*
  4  from src s, prereqs p
  5  where s.course_seq_no=p.course_seq_no
  6  and semester_code=3 
  7  and semester_year=2012
  8  and reg_no=2013192
  9  ) v1,
 10  (select reg_no,course_seq_no,gradecharvalue
 11  ,semester_code,semester_year
 12   from src
 13  where course_seq_no in
 14  (select pre_req from prereqs)
 15  and reg_no in
 16  (select distinct reg_no from src
 17  where semester_code=3 and semester_year=2012
 18  and reg_no not like '2012%'
 19  and reg_no=2013192)
 20  )v2
 21  where v1.pre_req=v2.course_seq_no(+)
 22  /

    REG_NO       SEQ1 COURSE_SEQ_NO    PRE_REQ PRTYPE      REG_NO COURSE_SEQ_NO GR SEMESTER_CODE SEMESTER_Y
---------- ---------- ------------- ---------- ------- ---------- ------------- -- ------------- ---
   2013192        169           169        244 PassAll    2013192           244 B-             3          2010
   2013192        169           169        244 PassAll    2013192           244 D+             3          2011
   2013192        169           169        160 PassAll



here I can view pre_req 160 with NULL grade. But how can I run this query for all voilations and all students?




[Updated on: Sun, 14 October 2012 22:52]

Report message to a moderator

Re: pre requisit question [message #568650 is a reply to message #568583] Mon, 15 October 2012 08:08 Go to previous messageGo to next message
joy_division
Messages: 4453
Registered: February 2005
Location: East Coast USA
Senior Member
rzkhan wrote on Sun, 14 October 2012 23:25


18 and reg_no not like '2012%'
19 and reg_no=2013192)


How can reg_no be both a number and character string?
Re: pre requisit question [message #568739 is a reply to message #568650] Tue, 16 October 2012 03:30 Go to previous message
rzkhan
Messages: 320
Registered: March 2005
Senior Member
Sir

it is stored as NUMBER but no problem if I want to select all NUMBERS starting with 2012. so we can use LIKE '2012%'

However, I can put my main question as simple as below.

"Select all those students who have not registered and passed the pre requisit course/s"
How ?



[Edit MC: Code tags are for code not for text]

[Updated on: Tue, 16 October 2012 03:54] by Moderator

Report message to a moderator

Previous Topic: Create Record Type with rowtype + one field
Next Topic: Sql query
Goto Forum:
  


Current Time: Thu Apr 17 04:51:13 CDT 2014

Total time taken to generate the page: 0.20845 seconds