We help IT Professionals succeed at work.
Get Started

Oracle - SQL  question

mytfein
mytfein asked
on
539 Views
Last Modified: 2014-05-19
Hi EE,

My user is asking the following question:

Show me a list of:
       a) Medical Students
       b) enrolled in 2012
       c) who took a Required course in 2012, that already took in a previous year (before 2012)
               in other words, who repeated a course

I am coding/running the SQL in PL/Developer.

To make it easier to test, I coded it as nested SELECTS,  this way i can Highlight each piece and run it in a separate
SQL window.

It's taking too long to run.

Notes:  2 tables:
                      ACADEMIC_STUDY   - shows the semesters that students attended

                                                               i am pulling the latest semester the student attended so that it distincts to one
                                                               row per student MAX(ACADEMIC_PERIOD)  using a function

                      STUDENT_COURSE  - shows the semesters and courses for semester that student took
 
Notes:  The Semester field is ACADEMIC_PERIOD
                    We have three semesters for ACADEMIC_YEAR
                                      201210   summer
                                      201220   fall
                                      201230   spring

               Student's latest semester could be 201230  if active student
               Student's latest semester could be 201220 if took a leave of absence

               If Student's latest semester is 201230, then for sure he has 201210.201220 record in ACADEMIC STUDY
               but we only want one row for a student to keep things neat, which is why we select the MAX(ACADEMIC_PERIOD)


I am not a SQL expert.

Any help you can provide, if you feel a different approach would be better etc.
     would be very appreciated.

tx! Sandra   Below is the SQL

SELECT
            STUDENT_COURSEA.AC_ACADEMIC_YEAR
          , STUDENT_COURSEA.AC_ACADEMIC_PERIOD  
          , STUDENT_COURSEA.AC_COLLEGE
          , STUDENT_COURSEA.AC_ID
          , STUDENT_COURSEA. AC_STUDENT_CLASSIFICATION
          , STUDENT_COURSEA.AC_NAME
          , STUDENT_COURSEA.AC_PRIMARY_PROGRAM_IND
          , STUDENT_COURSEA.AC_Calc_MaxTerm
         
         , STUDENT_COURSEA.ID
         , STUDENT_COURSEA.COURSE_IDENTIFICATION
         , STUDENT_COURSEA.ACADEMIC_PERIOD
         , STUDENT_COURSEA.FINAL_GRADE        

         , CASE WHEN
                      EXISTS
                                  (
                                     SELECT  SC.ID
           \                                       , SC.ACADEMIC_YEAR
                                                   , SC.COURSE_IDENTIFICATION
                                           
                                     FROM STUDENT_COURSE  SC
                                     
                                     WHERE SC.ID = STUDENT_COURSEA.ID
                                                  AND
                                           SC.COURSE_IDENTIFICATION = STUDENT_COURSEA.COURSE_IDENTIFICATION
                                                  AND
                                           SC.ACADEMIC_YEAR < '2012'
                                   )
                       THEN
                             'Y'
                       ELSE
                             'N'
           END
                                                     as Calc_IfExistsFlag    
 
FROM

(
SELECT
            ACADEMIC_STUDY.ACADEMIC_YEAR       AS AC_ACADEMIC_YEAR
          , ACADEMIC_STUDY.ACADEMIC_PERIOD     AS AC_ACADEMIC_PERIOD
          , ACADEMIC_STUDY.COLLEGE             AS AC_COLLEGE
          , ACADEMIC_STUDY.ID                  AS AC_ID
          , ACADEMIC_STUDY.STUDENT_CLASSIFICATION  AS AC_STUDENT_CLASSIFICATION
          , ACADEMIC_STUDY.NAME                AS AC_NAME
          , ACADEMIC_STUDY.PRIMARY_PROGRAM_IND AS AC_PRIMARY_PROGRAM_IND
          , ODSMGR.DMC_MAX_TERM_FOR_STUDENTS_PRGM(ACADEMIC_STUDY.Person_UID,'2012'||'30','N', ACADEMIC_STUDY.Program) AS AC_Calc_MaxTerm
         
         , STUDENT_COURSE.ID
         , STUDENT_COURSE.COURSE_IDENTIFICATION
         , STUDENT_COURSE.ACADEMIC_PERIOD
         , STUDENT_COURSE.FINAL_GRADE        
     
           
     
FROM
        ACADEMIC_STUDY             ACADEMIC_STUDY
        LEFT JOIN
                 STUDENT_COURSE    STUDENT_COURSE
                                    ON
                  (ACADEMIC_STUDY.ACADEMIC_YEAR = STUDENT_COURSE.ACADEMIC_YEAR
                                        AND                    
                              ACADEMIC_STUDY.ID = STUDENT_COURSE.ID)

WHERE

     ACADEMIC_STUDY.COLLEGE = 'MD'      
                    AND
      ACADEMIC_STUDY.ACADEMIC_YEAR = '2012'
                    AND
      ACADEMIC_STUDY.ACADEMIC_PERIOD =  ODSMGR.DMC_MAX_TERM_FOR_STUDENTS_PRGM  (ACADEMIC_STUDY.Person_UID,'2012'||'30','N', ACADEMIC_STUDY.Program)              
     
                    AND
             ( SUBSTR(STUDENT_COURSE.COURSE_IDENTIFICATION,5,1) IN ('1', '2', '3')
                                         OR
                          STUDENT_COURSE.COURSE_IDENTIFICATION = 'MEDI4003')  
                   
       
) STUDENT_COURSEA
Comment
Watch Question
CERTIFIED EXPERT
Most Valuable Expert 2012
Distinguished Expert 2020
Commented:
This problem has been solved!
Unlock 1 Answer and 22 Comments.
See Answer
Why Experts Exchange?

Experts Exchange always has the answer, or at the least points me in the correct direction! It is like having another employee that is extremely experienced.

Jim Murphy
Programmer at Smart IT Solutions

When asked, what has been your best career decision?

Deciding to stick with EE.

Mohamed Asif
Technical Department Head

Being involved with EE helped me to grow personally and professionally.

Carl Webster
CTP, Sr Infrastructure Consultant
Ask ANY Question

Connect with Certified Experts to gain insight and support on specific technology challenges including:

  • Troubleshooting
  • Research
  • Professional Opinions
Did You Know?

We've partnered with two important charities to provide clean water and computer science education to those who need it most. READ MORE