Question

Given the schema, write a query and subquery to do the following:

CREATE TABLE Majors major VARCHAR(12), description VARCHAR, PRIMARY KEY (major) ); CREATE TABLE Course ( courseMajor VARCHAR(12), courseNo VARCHAR(6), credits INTEGER NOT NULL, enroll_limit INTEGER, PRIMARY KEY(courseNo, courseMajor), FOREIGN KEY (courseMajor) REFERENCES Majors (major) CREATE TABLE Tracks trackMajor VARCHAR(12), trackCode VARCHAR(10), title VARCHAR, PRIMARY KEY(trackMajor, trackCode), FOREIGN KEY (trackMajor) REFERENCES Majors(major) CREATE TABLE Student ( SID CHAR(8), sName VARCHAR(30), studentMajor VARCHAR(12), trackCode VARCHAR(10), PRIMARY KEY(SID), FOREIGN KEY (studentMajor, trackCode) REFERENCES Tracks(trackMajor, trackCode) CREATE TABLE Enroll ( courseMajor VARCHAR(12), courseNo VARCHAR(6), SID CHAR(8), grade FLOAT NOT NULL, PRIMARY KEY (courseMajor, courseNo, SID), FOREIGN KEY (courseMajor, courseNo) REFERENCES Course(courseMajor, courseNo), FOREIGN KEY (SID) REFERENCES Student (SID) CREATE TABLE Prereq ( courseMajor VARCHAR(12), courseNO VARCHAR(6), preMajor VARCHAR(12), preCourseNo VARCHAR(6), PRIMARY KEY (courseMajor, courseNo, preMajor, preCourseNo), FOREIGN KEY (courseMajor, courseNo) REFERENCES Course(courseMajor, courseNo), FOREIGN KEY (preMajor, preCourseNO) REFERENCES Course(courseMajor, courseNo) CREATE TABLE TrackRequirements ( trackMajor VARCHAR(12), trackCode VARCHAR(10), courseMajor VARCHAR(12), courseNo VARCHAR(6), PRIMARY KEY (trackMajor, trackCode, courseMajor, courseNo), FOREIGN KEY (trackMajor, trackCode) REFERENCES Tracks(trackMajor, trackCode), FOREIGN KEY (courseNo, courseMajor) REFERENCES Course(courseNo, courseMajor) );

Answer 1

Solution==> Please do comment in case of any query.

Step1:- First we need to select all the courses from table Enroll where courseMajor = "CptS". SQL for this is :-

  select * from enroll where courseMajor ="CptS";

Step2:- Now we need to perform inner join with table Prereq . I have created dummy database for your better understanding. SQL for this is :-

select * from enroll e inner join Prereq p on e.courseMajor = p.courseMajor and e.courseNo = p.courseNo;

Number of Records: 3 courseMajor courseNo SID grade preMajor preCourseNo Cpts 255 12583589 Cpto 155 Cpts 231 12583589 Cpto 131 Cpts 223 12583589 5.4 Cpto 123

Step 3 :- Now we need to find grade in preMajor Courses. SQL for this:-

select A.courseMajor, A.courseNo, A.sID, A.preMajor, A.preCourseNo, B.grade from (select * from enroll e inner join Prereq p on e.courseMajor = p.courseMajor and e.courseNo = p.courseNo) A inner join enroll B on A.preMajor = B.courseMajor and A.preCourseNo = B.CourseNo ;

Number of Records: 3 courseMajor courseNo SID preMajor preCourseNo grade Cpts 255 12583589 Cpto 155 Cpts 231 12583589 Cpto 131 1.7 Cpts 223 12583589 Cpto 123 1.9

Step 4:- Now we need to get the name of the students.SQL is :-

  select D.sname, C.sID , C.courseMajor, C.courseNo from ( select A.courseMajor, A.courseNo, A.sID, A.preMajor, A.preCourseNo, B.grade from (select * from enroll e inner join Prereq p on e.courseMajor = p.courseMajor and e.courseNo = p.courseNo ) A inner join enroll B on A.preMajor = B.courseMajor and A.preCourseNo = B.CourseNo ) C inner join Student D on D.sID = C.sID;

sName SID courseMajor courseNo Alice 12583589 Cpts 255 Alice 12583589 Cpts 231 Alice 12583589 Cpts 223

Step 5:- Filter on the basis of grade.SQL is:-

select D.sname, C.sID , C.courseMajor, C.courseNo from ( select A.courseMajor, A.courseNo, A.sID, A.preMajor, A.preCourseNo, B.grade from (select * from enroll e inner join Prereq p on e.courseMajor = p.courseMajor and e.courseNo = p.courseNo where e.courseMajor = "CptS" ) A inner join enroll B on A.preMajor = B.courseMajor and A.preCourseNo = B.CourseNo ) C inner join Student D on D.sID = C.sID where C.grade < 2;

Number of Records: 2 sName SID courseMajor course No Alice 12583589 Cpts 223 Alice 12583589 Cpts 231

Result SQL:-

select D.sname, C.sID , C.courseMajor, C.courseNo from ( select A.courseMajor, A.courseNo, A.sID, A.preMajor, A.preCourseNo, B.grade from (select * from enroll e inner join Prereq p on e.courseMajor = p.courseMajor and e.courseNo = p.courseNo where e.courseMajor = "CptS" ) A inner join enroll B on A.preMajor = B.courseMajor and A.preCourseNo = B.CourseNo ) C inner join Student D on D.sID = C.sID where C.grade < 2;

Hope you will like the answer.

Thanks.