human
Schema:
journal (Journal_ID, Date, Theme, Sales)
editor (Editor_ID, Name, Age)
journal_committee (Editor_ID, Journal_ID, Work_Type)
Please answer with SQL.
assistant
Sure, I understand.
human
Show the names of editors that are on at least two journal committees.
assistant
SELECT T1.Name FROM editor AS T1 JOIN journal_committee AS T2 ON T1.Editor_ID = T2.Editor_ID GROUP BY T1.Name HAVING COUNT(*) >= 2
human
Show the id, name of each editor and the number of journal committees they are on.
assistant
SELECT T1.editor_id , T1.Name , COUNT(*) FROM editor AS T1 JOIN journal_committee AS T2 ON T1.Editor_ID = T2.Editor_ID GROUP BY T1.editor_id
human
Show the names of editors that are on the committee of journals with sales bigger than 3000.
assistant
SELECT T2.Name FROM journal_committee AS T1 JOIN editor AS T2 ON T1.Editor_ID = T2.Editor_ID JOIN journal AS T3 ON T1.Journal_ID = T3.Journal_ID WHERE T3.Sales > 3000
human
Show the names and ages of editors and the theme of journals for which they serve on committees, in ascending alphabetical order of theme.
assistant
SELECT T2.Name , T2.age , T3.Theme FROM journal_committee AS T1 JOIN editor AS T2 ON T1.Editor_ID = T2.Editor_ID JOIN journal AS T3 ON T1.Journal_ID = T3.Journal_ID ORDER BY T3.Theme ASC
human
Show the names of editors and the theme of journals for which they serve on committees.
assistant
SELECT T2.Name , T3.Theme FROM journal_committee AS T1 JOIN editor AS T2 ON T1.Editor_ID = T2.Editor_ID JOIN journal AS T3 ON T1.Journal_ID = T3.Journal_ID
human
Show the distinct themes of journals.
assistant
SELECT DISTINCT Theme FROM journal ;