CREATE TABLE journal (
    Journal_ID NUMBER PRIMARY KEY,
    Date TEXT,
    Theme TEXT,
    Sales NUMBER
);
CREATE TABLE editor (
    Editor_ID NUMBER PRIMARY KEY,
    Name TEXT,
    Age NUMBER
);
CREATE TABLE journal_committee (
    Editor_ID NUMBER PRIMARY KEY,
    Journal_ID NUMBER,
    Work_Type TEXT,
    FOREIGN KEY ("Journal_ID") REFERENCES journal("Journal_ID"),
    FOREIGN KEY ("Editor_ID") REFERENCES editor("Editor_ID")
);

Question: How many editors are there?
SQL: SELECT count(*) FROM editor

Question: List the names of editors in ascending order of age.
SQL: SELECT Name FROM editor ORDER BY Age ASC

Question: What are the names and ages of editors?
SQL: SELECT Name ,  Age FROM editor

Question: List the names of editors who are older than 25.
SQL: SELECT Name FROM editor WHERE Age  >  25

Question: Show the names of editors of age either 24 or 25.
SQL: SELECT Name FROM editor WHERE Age  =  24 OR Age  =  25

Question: What is the name of the youngest editor?
SQL: SELECT Name FROM editor ORDER BY Age ASC LIMIT 1

Question: What are the different ages of editors? Show each age along with the number of editors of that age.
SQL: SELECT Age ,  COUNT(*) FROM editor GROUP BY Age

Question: Please show the most common age of editors.
SQL: SELECT Age FROM editor GROUP BY Age ORDER BY COUNT(*) DESC LIMIT 1

Question: Show the distinct themes of journals.
SQL: SELECT DISTINCT Theme FROM journal

Question: Show the names of editors and the theme of journals for which they serve on committees.
SQL: 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

Question: For each journal_committee, find the editor name and the journal theme.
SQL: 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

Question: Show the names and ages of editors and the theme of journals for which they serve on committees, in ascending alphabetical order of theme.
SQL: 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

Question: Show the names of editors that are on the committee of journals with sales bigger than 3000.
SQL: 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

Question: Show the id, name of each editor and the number of journal committees they are on.
SQL: 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

Question: Show the names of editors that are on at least two journal committees.
SQL: 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

Question: List the names of editors that are not on any journal committee.
SQL: SELECT Name FROM editor WHERE editor_id NOT IN (SELECT editor_id FROM journal_committee)

Question: List the date, theme and sales of the journal which did not have any of the listed editors serving on committee.
SQL: SELECT date ,  theme ,  sales FROM journal EXCEPT SELECT T1.date ,  T1.theme ,  T1.sales FROM journal AS T1 JOIN journal_committee AS T2 ON T1.journal_ID  =  T2.journal_ID

Question: What is the average sales of the journals that have an editor whose work type is 'Photo'?
SQL: 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'