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 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 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
What is the average score of submissions?
assistant
SELECT avg(Scores) FROM submission
human
List the authors of submissions in ascending order of scores.
assistant
SELECT Author FROM submission ORDER BY Scores ASC
human
What is the author of the submission with the highest score?
assistant
SELECT Author FROM submission ORDER BY Scores 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 ;