Question

Write ONE SQL statement for each of the following tasks. 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.

Answer 1

Answer:

1) SELECT author.name, author.email

FROM author, paper_author, paper

WHERE author.id = paper_author.id

AND paper_author.id = paper.id

AND UPPER(paper.title) = ('A novel approach of mining EHR data');

2) SELECTpaper_review.id, paper_review. decision, paper_review.comment

FROM paper_review, Paper

WHERE paper_review.id = paper.id

AND paper.title = 'comparing big-data systems';

3) SELECT Paper.title, COUNT(Paper_author.id)

FROM Paper, Paper_author

WHERE Paper.id = Paper_author.id

GROUP BY Paper.title;

4) SELECT Author.name

FROM Author

WHERE Author.id IN (SELECT Paper_author.id

FROM Paper_author

GROUP BY Paper_author.id

HAVING COUNT(Paper_author.id) >= 2);

5) SELECT DISTINCT a.name

FROM Author AS a

INNER JOIN paper_author AS pa

ON a.id=pa.id

WHERE EXISTS (

SELECT 1

FROM Author aa

INNER JOIN  paper_author paa

ON aa.id=paa.id  

WHERE name='Dr. Chen'

AND paa.id = pa.id

AND aa.id != a.id

);

Explanation:

The SQL statements return the queries from six different tables in the database