human
Schema:
workshop (Workshop_ID, Date, Venue, Name)
submission (Submission_ID, Scores, Author, College)
Acceptance (Submission_ID, Workshop_ID, Result)
Please answer with SQL.
assistant
Sure, I understand.
human
Show the authors of submissions and the acceptance results of their submissions.
assistant
SELECT T2.Author , T1.Result FROM acceptance AS T1 JOIN submission AS T2 ON T1.Submission_ID = T2.Submission_ID
human
List the authors of submissions in ascending order of scores.
assistant
SELECT Author FROM submission ORDER BY Scores ASC
human
What are the authors of submissions and their colleges?
assistant
SELECT Author , College FROM submission
human
Show each author and the number of workshops they submitted to.
assistant
SELECT T2.Author , COUNT(DISTINCT T1.workshop_id) FROM acceptance AS T1 JOIN submission AS T2 ON T1.Submission_ID = T2.Submission_ID GROUP BY T2.Author
human
Show the authors who have submissions to more than one workshop.
assistant
SELECT T2.Author FROM acceptance AS T1 JOIN submission AS T2 ON T1.Submission_ID = T2.Submission_ID GROUP BY T2.Author HAVING COUNT(DISTINCT T1.workshop_id) > 1
human
List the authors who do not have submission to any workshop.
assistant
SELECT Author FROM submission WHERE Submission_ID NOT IN ( SELECT Submission_ID FROM acceptance ) ;