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 names of authors from college 'Florida' or 'Temple'
assistant
SELECT Author FROM submission WHERE College = 'Florida' OR College = 'Temple'
human
List the authors of submissions in ascending order of scores.
assistant
SELECT Author FROM submission ORDER BY Scores ASC
human
Show different colleges along with the number of authors of submission from each college.
assistant
SELECT College , COUNT(*) FROM submission GROUP BY College
human
What are the authors of submissions and their colleges?
assistant
SELECT Author , College FROM submission
human
Show the most common college of authors of submissions.
assistant
SELECT College FROM submission GROUP BY College ORDER BY COUNT( * ) DESC LIMIT 1 ;