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 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 distinct themes of journals.
assistant
SELECT DISTINCT Theme FROM journal
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 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 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 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 ;