Any SQL Expert here,... ?

Discussion in 'General Software' started by Mack, May 20, 2006.

  1. Mack

    Mack Big Geek

    Likes Received:
    2
    Trophy Points:
    18
    If any body know SQL, I am kind of stuck with one Query,..

    Tables:
    Code:
    STUDENT(SSN,NAME,GPA,MAJOR, BIRTHDATE, REGISTRATIONDATE)
    COURSE (CID, COURSETITLE, UNIT)
    ENROLLED (SSN, CID, GRADE)
    PREREQUISITE (CID, PREREQUISITE-CID)
    
    Question:
    Code:
    d.What are the names of all the 
    prerequisites of CIS 4400 
    (prerequisites of CIS 4400, prerequisites 
    of the prerequisites of CIS 4400, and so on)? 
    
    There is to real data,. only a theoratical question,...
    Note: use either SQL or embbeded SQL
     
  2. Mack

    Mack Big Geek

    Likes Received:
    2
    Trophy Points:
    18
    I think I got it,.. thank anyway,...
     
  3. Sniper

    Sniper Administrator Staff Member

    Likes Received:
    59
    Trophy Points:
    63
    Would be interested in the answer, since didn't quite understand the question :)
     
  4. Mack

    Mack Big Geek

    Likes Received:
    2
    Trophy Points:
    18
    It is just theoratical,.. I got the logic,. When i write it in Embbeded SQL I will post the answer,...

    The QUESION
    In order to take CIS 4400 (Databases II) a student must have taken the pre requisists for the curse which is
    CIS 3400(databases I) and inorder to take CIS 3400 a personal should have already taken its prerequisite which is CIS 2200 (Intro
    to computers).
    So I have list the pre req. for CIS 4400 and any pre req that would be required to take the pre req. ,...
     
  5. Mack

    Mack Big Geek

    Likes Received:
    2
    Trophy Points:
    18
    This is what I have come up with. Check if u there are any errors,..
    Thanks

    Solution:
    Code:
    #include <stdio.h>
    #include <stdlib.h>
    EXEC SQL INCLUDE sqlca;
    Main()
    {
    	EXEC SQL BEGIN DELCARE SECTION;
    	Struct couresType
                {
                   String coure
    }
    	//To capture pre requiste
    Int	counter = 1;
     	//To check the course for pre requiste
    Int 	number = 0; 
    	Char	*username = “Manager”;
    	Char	*password = “Manager”;
    	EXEC SQL END DECLARE SECTION;
    
    //Connect to database
    	EXEC SQL CONNECT :username IDENTIFIED BY :password;
    	If (sqlca.sqlcode < 0) exit(-1);
    
    //Establish SQL error handling, then declare cursor for selection
    	EXEC SQL WHENEVER SQLERROR GOTO error;
    	EXEC SQL WHENEVER NOT FOUND GOTO done;
    
    //Check for  prerequisites  of a  COUSRE
    	EXEC SQL DECLARE Check_Prerequisite CURSOR FOR
    SELECT [PREREQUISITE.PREREQUISITE-CID]
    FROM COURSE, PREREQUISITE
    WHERE COURSE.CID= :course AND COURSE.CID=PREREQUISITE.CID;
    
    courseType course[10];
    
    //Algorithm
    counter = 1;
    number = 0;
    while (number<counter)
    {
     number++;
     course[number];
     EXEC SQL FETCH Check_Prerequisite INTO :number[counter]
     counter++;
     while (@@FETCH_STATUS == 0)
     {
      EXEC SQL FETCH Check_Prerequisite INTO :number[counter]
      counter++;
     }
     if (@@FETCH_STATUS!=0)
     {
       counter--;
     }
    }
    
    //Display courses
    Cout << “Pre requisite courses for “ << course[1] << “ are : “ << endl;
    For(i=2;i=<number;i++)
    {
       Cout << course[i] << endl;
    }
    
    Error:
            Cout << “Error”;
    Done:
    	EXEC SQL WHENEVER SQLERROR continue;
    	EXEC SQL CLOSE Check_Prerequisite;
    	EXEC SQL COMMIT WORK RELEASE;
    }
    
     

Share This Page