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
What is the name of the youngest editor?
assistant
SELECT Name FROM editor ORDER BY Age ASC LIMIT 1
human
Please show the most common age of editors.
assistant
SELECT Age FROM editor GROUP BY Age ORDER BY COUNT(*) DESC LIMIT 1
human
What are the names and ages of editors?
assistant
SELECT Name , Age FROM editor
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
How many editors are there?
assistant
SELECT count(*) FROM editor
human
What is the average sales of the journals that have an editor whose work type is 'Photo'?
assistant
SELECT avg ( T1.sales ) FROM journal AS T1 JOIN journal_committee AS T2 ON T1.journal_ID = T2.journal_ID WHERE T2.work_type = "Photo" ;