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
What is the largest submission score?
assistant
SELECT MAX( Scores ) FROM submission;
human
How many submissions have a score larger than 90?
assistant
SELECT COUNT( * ) FROM submission WHERE Scores > 90;
human
How about the number of submissions with a score less than 80?
assistant
SELECT COUNT( * ) FROM submission WHERE Scores < 80;
human
What are 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 ;