drop table paper_review cascade constraints; drop table reviewer cascade constraints; drop table paper_author cascade constraints; drop table paper cascade constraints; drop table editor cascade constraints; drop table author cascade constraints;

create table author ( aid int, -- author id aname varchar(50), --- author name aemail varchar(50), --- author email affliation varchar(50), primary key (aid));

insert into author values(1,'Dr. Chen','chen@umbc.edu','UMBC'); insert into author values(2,'Susan','susan@umbc.edu','UMBC');

insert into author values(3,'Steve','steve@umb.edu','UMB');

insert into author values(4,'Carole','carole@umb.edu','UMB');

create table Editor ( eid int, --- editor ID ename varchar(50), --- editor name eemail varchar(50), --- email primary key (eid));

insert into editor values(1,'Claire','claire.com');

insert into editor values(2,'David','davidl.com');

create table paper( pid int, -- paper id title varchar(50), eid int, -- assigned editor id submit_date date, --- date of submission status int, -- status: 1 accept, 2 under review, 3 revision, 4 reject primary key(pid), foreign key(eid) references editor);

insert into paper values(1,'Comparing big data systems',2,date '2020-1-10',3); insert into paper values(2,'A novel approach of mining EHR data',2,date '2020-2-10',2); insert into paper values(3, 'A new SQL Benchmark', 1, date '2020-1-1',1);

create table paper_author ( pid int, -- paper id aid, -- author id primary key(pid,aid), foreign key(pid) references paper, foreign key(aid) references author);

insert into paper_author values(1,1);

insert into paper_author values(1,2);

insert into paper_author values(2,3); insert into paper_author values(2,4);

insert into paper_author values(3,1); insert into paper_author values(3,2);

create table Reviewer ( rid int, --- reviewer id rname varchar(50), --- reviewer name remail varchar(50), --- reviewer email raffiliation varchar(50), primary key(rid));

insert into reviewer values(1, 'Ellen','ellen.com','Johns Hopkins');

insert into reviewer values(2, 'Cathy','cathy.com','Johns Hopkins');

insert into reviewer values(3, 'Grace','gracel.com','Stanford');

insert into reviewer values(4, 'Eric','eric.com','Stanford');

insert into reviewer values(5, 'Ethan','ethan.edu','UMBC');

create table paper_review ( prid int, -- paper review id pid int, -- paper id rid int, -- reviwer id due_date date, -- review due date receive_date date, -- review received date round int, -- round of review, 1 means first round, 2 means second round, most paper will go through 2 rounds of review decision int, -- 1 accept, 2 minor revision, 3 major revision, 4 reject rcomment varchar(200), -- comments of review primary key(prid), foreign key(pid) references paper, foreign key(rid) references reviewer);

insert into paper_review values(1, 1, 1, date '2020-2-10',date '2020-2-9', 1,3,'Good paper but needs to improve writing');

insert into paper_review values(2, 1, 3, date '2020-2-10',date '2020-2-10', 1,2,'Good paper fix a few typo');

insert into paper_review values(3, 1, 4, date '2020-2-10',date '2020-2-12', 1,3,'Please add more experiments');

insert into paper_review values(4, 2, 2, date '2020-3-10',null, 1,null,null);

insert into paper_review values(5, 2, 1, date '2020-3-10',null, 1,null,null);

insert into paper_review values(6, 2, 3, date '2020-3-10',null, 1,null,null);

insert into paper_review values(7, 3, 4, date '2020-2-1',date '2020-1-15', 1,2,'good paper, fix a few typo');

insert into paper_review values(8, 3, 2, date '2020-2-1',date '2020-1-11', 1,3,'good paper, but please add more related work');

insert into paper_review values(9, 3, 4, date '2020-3-1',date '2020-2-15', 2,1,'all comments addressed');

insert into paper_review values(10, 3, 2, date '2020-3-1',date '2020-2-18', 2,1,'all comments addressed');

Write anonymous PL/SQL code to print out number of reviewers who have NOT submitted round 1 review for the paper titled 'A novel approach of mining EHR data'.

Hint: a review is not submitted if the receive_date column in paper_review table is null.

Please this question is clear enough, please stop commenting "its not clear" if you don't know the answers