title: SQL Query School Course pre Requisite
body: <p>Scripts:
  Build Schema script provided. 
  for each student ,  need to print if he missed any pre-requisites, plus the current application course from the application table . it should not print any course already registered in register table. . Expected result given inside the script.</p>
  
  <pre><code>create table applications ( student_id INT, requested_course VARCHAR(4) ) ;
  INSERT INTO applications ( student_id, requested_course )
  values ( 1,'ML'),(2,'CMP'),(3,'ML');
  
  CREATE TABLE register
  (student_id int, registered varchar(4))
  ;
  
  INSERT INTO register
  (student_id, registered)
  VALUES
  (1, 'HS'),
  (1, 'PHY'),
  (1, 'CMP'),
  (2, 'MTH')
  ;
  
  CREATE TABLE prerequisites
  (course VARCHAR(4),prerequisite_course VARCHAR(4))
  ;
  
  INSERT INTO prerequisites
  (course, prerequisite_course)
  VALUES
  ('MTH', 'HS'),
  ('PHY', 'HS'),
  ('CHM', 'HS'),
  ('CMP', 'MTH'),
  ('ML', 'CMP'),
  ('AP', 'CMP')
  ;
  </code></pre>
  
  <p>Final Query ( My try) - not working.</p>
  
  <pre><code>Select rq.student_id, rq.requested_course as course from applications rq
  Where not exists ( select 1 from register rg where
  rg.student_id=rq.student_id and rg.registered=rq.requested_course)
  
  Union
  
  Select rq.student_id,st.prerequisite_course as course from applications rq
  Join prerequisites st ON st.course=rq.requested_course
  Where not exists ( select 1 from register rg where
  rg.student_id=rq.student_id and rg.registered=rq.requested_course
  and st.prerequisite_course=rg.registered )
  order by student_id
  ;
  </code></pre>
  
  <p>Expected result</p>
  
  <pre><code>Student_id Course
  -----------------
    1.    'MTH'
    1     'ML'
    2     'HS'
    2     'CMP'
    3     'HS'
    3     'MTH'
    3     'CMP'
    3     'ML'
  </code></pre>
  
  <p>for student ID 1 : he is opting for ML.So it will be included. He already registered for CMP.So CMP excluded. Now he did not do pre-req MTH . So final result : MTH and ML</p>
  
  <p>for Student ID 2: he is opting for CMP. One of the pre-req MTH is in register table, HS is not. So expected result: CMP, HS.</p>
  
  <p>for student ID 3: Opting for ML.So ML included.no pre-reqs are in registered table. So for 3, expected result HS, MTH, CMP,  ML.</p>