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
Show the colleges that have both authors with submission score larger than 90 and authors with submission score smaller than 80.
assistant
SELECT College FROM submission WHERE Scores > 90 INTERSECT SELECT College FROM submission WHERE Scores < 80
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
Show the result of the submission with the highest score.
assistant
SELECT T1.Result FROM acceptance AS T1 JOIN submission AS T2 ON T1.Submission_ID = T2.Submission_ID ORDER BY T2.Scores DESC LIMIT 1
human
List the authors of submissions in ascending order of scores.
assistant
SELECT Author FROM submission ORDER BY Scores ASC
human
Find the author who achieved the highest score in a submission.
assistant
SELECT Author FROM submission ORDER BY Scores DESC LIMIT 1 ;