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 different colleges along with the number of authors of submission from each college.
assistant
SELECT College , COUNT(*) FROM submission GROUP BY College
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
List the authors of submissions in ascending order of scores.
assistant
SELECT Author FROM submission ORDER BY Scores ASC
human
Show the authors who have submissions 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
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
Sort the each workshop in alphabetical order of the venue. Return the date and venue of each workshop.
assistant
SELECT Date , Venue FROM workshop ORDER BY Venue ;