Database: journal_committee

CREATE TABLE IF NOT EXISTS "journal" (
"Journal_ID" int,
"Date" text,
"Theme" text,
"Sales" int,
PRIMARY KEY ("Journal_ID")
);
CREATE TABLE IF NOT EXISTS "editor" (
"Editor_ID" int,
"Name" text,
"Age" real,
PRIMARY KEY ("Editor_ID")
);
CREATE TABLE IF NOT EXISTS "journal_committee" (
"Editor_ID" int,
"Journal_ID" int,
"Work_Type" text,
PRIMARY KEY ("Editor_ID","Journal_ID"),
FOREIGN KEY ("Editor_ID") REFERENCES `editor`("Editor_ID"),
FOREIGN KEY ("Journal_ID") REFERENCES `journal`("Journal_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'