March 5, 2020 at 11:51 pm
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.
Scripts:
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')
;
Final Query ( My try) - not working.
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
;
/*Expected result
Student_id Course
-----------------
1. 'MTH'
1 'ML'
2 'HS'
2 'CMP'
3 'HS'
3 'MTH'
3 'CMP'
3 'ML'
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
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.
for student ID 3: Opting for ML.So ML included.no pre-reqs are in registsred table. So for 3, expected result HS, MTH, CMP, ML.
*/
March 6, 2020 at 12:08 am
Yeah I get that this is a "Course requires Prerequisite" question, generally speaking. But what's your question?
how about explaining the real-world scenario you're trying to figure out? Something like "I'm trying to figure out how to return all the courses a student is missing in order to take @nextCourse... I asked this question a few years ago and got some helpful responses.
March 6, 2020 at 12:15 am
updated original post
March 6, 2020 at 12:36 am
Please read this article:
Your post lacks several things that enable folks like me to help you:
I mean, sorry, but we're not psychic. Are you looking for something like "Given the following courses and prerequisites tables, and a student's Transcript (courses he has taken/passed), how do I determine all the courses for which he meets all the prerequisites?" At the moment, I'm not even sure what your question is.
March 6, 2020 at 5:45 am
If i update the pre_requisite table to add all recursive dependencies, then my query working.
INSERT INTO prerequisites
(course,prerequisite_course)
VALUES
('MTH', 'HS'),
('PHY', 'HS'),
('CHM', 'HS'),
('CMP', 'MTH'),
('CMP','HS'),
('ML', 'CMP'),
('ML','MTH'),
('ML','HS'),
('AP', 'CMP'),
('AP','MTH'),
('AP','HS')
;
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 all
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 (
st.prerequisite_course=rg.registered ))
order by student_id
;
Viewing 6 posts - 1 through 5 (of 5 total)
You must be logged in to reply to this topic. Login to reply