Here is a question about data base sql, I need someone to help me write the query, thank you so much

 

 

Here is my schema
create table student (sid);
create table course (cid);
create table prerequisite (cid, precid);
create table record (sid, cid, qtr, year, grade);
insert into student values ('A_001');
insert into student values ('A_002');
insert into student values ('A_003');
insert into student values ('A_004');
insert into student values ('A_005');
insert into student values ('A_006');
insert into course values ('CSE105');
insert into course values ('CSE107');
insert into course values ('CSE132A');
insert into course values ('CSE132B');
insert into course values ('CSE132X');
insert into prerequisite values ('CSE132B', 'CSE132A');
insert into prerequisite values ('CSE132X', 'CSE132B');
insert into prerequisite values ('CSE132X', 'CSE107');
insert into record values ('A_001', 'CSE105', 'F', 2015, 3);
insert into record values ('A_001', 'CSE132A', 'F', 2015, 4);
insert into record values ('A_002', 'CSE132A', 'S', 2016, 4);
insert into record values ('A_003', 'CSE132A', 'S', 2016, 3);
insert into record values ('A_001', 'CSE132B', 'F', 2016, 1);
insert into record values ('A_002', 'CSE132B', 'F', 2016, 4);
insert into record values ('A_003', 'CSE132B', 'F', 2016, 2);
insert into record values ('A_004', 'CSE132A', 'F', 2016, 2);
insert into record values ('A_005', 'CSE132A', 'F', 2016, 3);
insert into record values ('A_002', 'CSE107', 'F', 2016, 4);
insert into record values ('A_003', 'CSE107', 'F', 2016, 4);
insert into record values ('A_005', 'CSE107', 'F', 2016, 2);
insert into record values ('A_002', 'CSE105', 'W', 2017, 4);
insert into record values ('A_003', 'CSE105', 'W', 2017, 3);
insert into record values ('A_004', 'CSE105', 'W', 2017, 3);
insert into record values ('A_005', 'CSE105', 'W', 2017, 2);
insert into record values ('A_006', 'CSE105', 'W', 2017, 1);
insert into record values ('A_004', 'CSE132B', 'W', 2017, 4);
insert into record values ('A_005', 'CSE132B', 'W', 2017, 2);
insert into record values ('A_001', 'CSE132X', 'S', 2017, 4); insert into record values ('A_002', 'CSE132X', 'S', 2017, 4);
insert into record values ('A_003', 'CSE132X', 'S', 2017, 3);
insert into record values ('A_004', 'CSE132X', 'S', 2017, 4);
insert into record values ('A_005', 'CSE132X', 'S', 2017, 2);
insert into record values ('A_006', 'CSE107', 'S', 2017, 3);
I need two queries, here is the detail
List the students who have taken all prerequisites for CSE132X with a grade of 2 or higher.
The answer should have one attribute sid. Note that, if CSE132X has no prerequisites, then
all students should be in the answer. Provide two SQL queries, using nested sub-queries in
different ways:
• with NOT IN tests only;
• with NOT EXISTS tests only;
Here is the expected result
sid
---------A_002
A_003
A_005

Attachments:

• 1493795998-sql1.docx