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
List the authors of submissions in ascending order of scores.
assistant
SELECT Author FROM submission ORDER BY Scores ASC
human
Show the most common college of authors of submissions.
assistant
SELECT College FROM submission GROUP BY College ORDER BY COUNT(*) DESC LIMIT 1
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
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)
human
Which authors have submitted 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 ;