You will be using the following tables about a paper review database in the exam. Please read the comments that explain meaning of columns.

You will be using the following tables about a paper review database in the exam.

Please read the comments that explain meaning of columns.

--- a paper review system

drop table paper_review cascade constraint;

drop table paper_author cascade constraint;

drop table paper cascade constraints;

drop table author cascade constraints;

drop table reviewer cascade constraints;

create table reviewer (

rid int, --- reviewer id

rname varchar(50), --- reviewer name

remail varchar(50),-- reviewer email

raffiliation varchar(50),-- reviewer affiliation

primary key (rid)

);

insert into reviewer values(1,'Alex Golden', '','Columbia');

insert into reviewer values(2,'Ann Stonebraker', '','Stanford');

insert into reviewer values(3,'Karen Smith', '','Harvard');

insert into reviewer values(4,'Richard Wallas', '','UMBC');

create table author

(aid int, -- author id

aname varchar(50), -- author name

aemail varchar(50), -- author email

aaffilication varchar(50),-- author affiliation

primary key(aid));

insert into author values(1,'Adam Smith', '','UMBC');

insert into author values(2,'Nancy Chang', '','UMD');

insert into author values(3,'Carrol Steinberg', '','UMB');

insert into author values(4,'Daniel Kerry', '','JHU');

create table paper(

pid int,--- paper id

ptitle varchar(200),--- title of paper

corr_aid int, --- id of corresponding author, only one per paper

sub_date date, --- date of submission

primary key(pid),

foreign key(corr_aid) references author);

insert into paper values(1,'A novel intrusion detection method using deep learning', 1,date '2018-3-1');

insert into paper values(2,'A comparison study of different machine learning methods', 2,date '2018-3-2');

insert into paper values(3,'The benefits of exercises to dementia patients', 3,date '2018-4-3');

create table paper_author

(

pid int,--- paper id

aid int,--- author id, corresponding author will also appear in paper_author table

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,2);

insert into paper_author values(2,1);

insert into paper_author values(3,3);

insert into paper_author values(3,4);

create table paper_review

(

pid int,-- paper id

rid int,--- reviewer id, each paper has multile reviewer assigned, same reviewer

-- can review multiple papers

content varchar(1000), --- content of review

rscore int, --- review score, 1-5

rdate date, --- review date

primary key(pid, rid),

foreign key(pid) references paper,

foreign key(rid) references reviewer

);

insert into paper_review values(1,1,'This is a great paper',5,date '2018-4-1');

insert into paper_review values(1,2,'Execclent paper',4,date '2018-3-28');

insert into paper_review values(2,3,'Nice paper',4,date '2018-4-1');

insert into paper_review values(2,1,'Good paper but I have question regarding figure 2',3,date '2018-4-2');

insert into paper_review values(3,3,'Interesting results',4,date '2018-5-2');

insert into paper_review values(3,4,'Timely paper',4,date '2018-5-3');

commit;

Problem 1: Please write SQL statements to implement the following tasks.

You can ONLY use conditions listed in each task. You cannot add or change conditions by manually looking up data. E.g., if we look for a paper with a certain title, you cannot manually look up the paper's pid. [80 points, 10 points for each task]

Task 1: Return titles of papers submitted in March 2018

Task 2: Return name of corresponding author of the paper titled 'The benefits of exercises to dementia patients'.

Task 3: Return average review score of the paper titled 'The benefits of exercises to dementia patient'

Task 4: Return names of reviewers who reviewed the paper titled 'The benefits of exercises to dementia patients'.

Task 5: Return number of papers each reviewer reviewed, along with name of reviewer.

Task 6: Return names of reviewers who have reviewed at least 2 papers.

Task 7: update the review score by Karen Smith (the name of the reviewer) on paper #3 (3 is paper id) to 5.

Task 8: return names of co-authors for the papers with Adam Smith as corresponding author

Problem 2: [20 points] Please write an anonymous PL/SQL program to print out the sum of

12, 32, 52, .... 992 (the sum of square of odd numbers within 100).