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
How many submissions are there?
assistant
SELECT count(*) FROM submission
human
Show the date and venue of each workshop in ascending alphabetical order of the venue.
assistant
SELECT Date , Venue FROM workshop ORDER BY Venue
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
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
How many workshops did each author submit to? Return the author name and the number of workshops.
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 ;