Suppose you have created tables in assignment (Tables created in Assignment1) Author table has the following columns
aid, -- author id
aname, --- author name
aemail, --- author email
affliation, --- author affliation
Editor table has the following columns
eid, --- editor ID
ename, --- editor name
eemail, --- email
paper table has the following columns
pid, -- paper id
title, -- paper title
eid, -- assigned editor id
submit_date, --- date of submission
status, -- status: 1 accept, 2 under review, 3 revision, 4 reject
paper_author table has the following columns
pid, -- paper id
aid, -- author id
Reviewer table has the following columns
rid, --- reviewer id
rname, --- reviewer name
remail, --- reviewer email
paper_review table has the following columns
prid, -- paper review id
pid, -- paper id
rid, -- reviwer id
due_date, -- review due date
receive_date, -- review received date
round, -- round of review, 1 means first round, 2 means second round, most paper will go through 2 rounds of review
decision, -- 1 accept, 2 minor revision, 3 major revision, 4 reject
rcomment, -- comments of review
1. Please write one SQL statement for each the following problems. You can use the attached
SQL statements to create the tables.
Problem 1. Please write ONE SQL statement for each of the following tasks using tables created in Problem 1. Note that you can only use conditions specified in the task description and cannot manually look up data and add conditions. E. g., in task 1, you cannot manually look up category ID for carpet cleaner. You want to make sure your code will work regardless of what data are in the database (e. g., whether carpet cleaner has a category ID of 1 or 100).
Task 1: Return names and emails of authors of the paper titled 'A novel approach of mining EHR data'.
Task 2: Return reviewer ID, review decision, and review comments of the paper titled 'Comparing big data systems'.
Task 3: Return paper title and number of authors for each paper.
Task 4: Return names of authors who have at least two papers.
Task 5: List names of authors who have co-authored with Dr. Chen.
Hint: use self join